Wednesday, September 7, 2016

SCCM SQL Report - Lots of Info

SCCM SQL Report detailing IP Address, Computer Name, Last Logon Timestamp, Username, AD Site, Physical Memory, Computer Manufacturer, Computer Model, and finally Serial Number.  


v_R_System.Name0 As 'Name',
v_RA_System_IPAddresses.IP_Addresses0 As 'IP Addresses',
v_R_System.Last_Logon_Timestamp0 as "Last Logon Timestamp",
v_R_System.User_Name0 as UserName,
v_R_System.AD_Site_Name0 as ADSite,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024 as Memory,
v_GS_COMPUTER_SYSTEM.Manufacturer0 as Manufacturer,
v_GS_COMPUTER_SYSTEM.Model0 as Model,
v_GS_PC_BIOS.SerialNumber0 as SerialNumber

INNER JOIN v_R_System ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID
left join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
left join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
left join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID = v_R_System.ResourceID

where v_RA_System_IPAddresses.IP_Addresses0 like '1%'
order by Name asc


Because SCCM grabs IPv6 during its inventory you will find this report also showing IPv6 address's so I limited the IP scope to only find address's that start with a "1", you can modify this as you see fit (or remove it completely).  Also, this will show you the RAM in megabytes, if you'd like to go one step further you can simply change the eighth line to divide once more by 1024 (or remove the division to simply show KB).


(v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024) /1024 as Memory,


No comments:

Post a Comment