SCCM SQL Report detailing IP Address, Computer Name, Last Logon Timestamp, Username, AD Site, Physical Memory, Computer Manufacturer, Computer Model, and finally Serial Number.
----------------------------------------------------------------------------------------
SELECT
v_RA_System_IPAddresses. ResourceID,
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
FROM
v_RA_System_IPAddresses
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
v_RA_System_IPAddresses.
v_R_System.Name0 As 'Name',
v_RA_System_IPAddresses.IP_
v_R_System.Last_Logon_
v_R_System.User_Name0 as UserName,
v_R_System.AD_Site_Name0 as ADSite,
v_GS_X86_PC_MEMORY.
v_GS_COMPUTER_SYSTEM.
v_GS_COMPUTER_SYSTEM.Model0 as Model,
v_GS_PC_BIOS.SerialNumber0 as SerialNumber
FROM
v_RA_System_IPAddresses
INNER JOIN v_R_System ON v_RA_System_IPAddresses.
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.
left join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID = v_R_System.ResourceID
where v_RA_System_IPAddresses.IP_
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