Collection bazlı donanım bilgileri almak için bir SQL query


 

Bu şekilde (hepsi bir arda Smile) bu raporu sccm de bulamazsınız:

Computer Name, Domain, IP Adress, MAC, Top Console User, Operating System, Service Pack Level, Memory, Description (Motherboard), Device ID (Motherboard), Primary Bus Typer, Revisision Number, Processor, Disk Space, Free Disk Space

Istenirse bu sql query Rapor olarakta eklenebilir: SMS Colletion ID yerine @varibale ekleyin ve Rapor optionlarında bir prompt olarak çekebilirsiniz.

select  distinct
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 AS [IP Address],
v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0 AS [MAC],  [Top Console User] = CASE  when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')  then 'Unknown'
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_GS_MOTHERBOARD_DEVICE.Description0 AS [Description],
v_GS_MOTHERBOARD_DEVICE.DeviceID0 AS [DeviceID],
v_GS_MOTHERBOARD_DEVICE.PrimaryBusType0 AS [PrimaryBusType],
v_GS_MOTHERBOARD_DEVICE.RevisionNumber0 AS [RevisionNumber],
v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],  (Select sum(Size0)  from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
  where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and
  v_FullCollectionMembership.CollectionID = 'SMS00001') As [Disk Space (MB)],  (Select sum(v_GS_LOGICAL_DISK.FreeSpace0)  from v_GS_LOGICAL_DISK

inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )  where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = 'SMS00001') As [Free Disk Space (MB)]  from v_R_System_Valid 
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)  left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)  inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) 
inner join v_GS_MOTHERBOARD_DEVICE on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) 
inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)  inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) 
inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)
inner join v_GS_NETWORK_ADAPTER_CONFIGUR on (v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = v_R_System_Valid.ResourceID) Where (v_FullCollectionMembership.CollectionID = 'SMS00001') AND (IPAddress0 IS NOT NULL) 
Order by v_R_System_Valid.Netbios_Name0

 

image

 

 

Source: http://www.mycloud-tr.com/

İsmail Şen

Comments (0)

Skip to main content