Wednesday, December 16, 2015

SCCM Report for computer name last boot time last hardware scan

The following SCCM report will provide Computer Name, Manufacturer, Model, Serial number, OS Version, Service Pack, Last logged on User, OS deployed date, Architecture, IP Address, Last Boot Time and Last H/W Scan for a nominated collection.
Change the collection ID as required.
********************************************************************************
SELECT DISTINCT CS.Name0 AS 'Computer Name', CS.Manufacturer0 AS 'Manufacturer', CS.Model0 AS 'Model', BIOS.SerialNumber0 AS 'Bios serial', OS.Caption0 AS 'OS Version', 
                      OS.CSDVersion0 AS 'Service Pack', CS.UserName0 AS 'Logged on User', OS.InstallDate0 AS [OS deployed on], SYS.SystemType0 AS Architecture, 
                      v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address], OS.LastBootUpTime0 AS [Last Boot Time], 
                      v_GS_WORKSTATION_STATUS.LastHWScan AS [Last H/W Scan]
FROM v_GS_COMPUTER_SYSTEM AS CS RIGHT OUTER JOIN
                      v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = CS.ResourceID RIGHT OUTER JOIN
                      v_GS_SYSTEM AS SYS ON SYS.ResourceID = CS.ResourceID RIGHT OUTER JOIN
                      v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = CS.ResourceID INNER JOIN
                      v_GS_SYSTEM_ENCLOSURE AS ES ON SYS.ResourceID = ES.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON BIOS.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID LEFT OUTER JOIN
                      v_FullCollectionMembership ON CS.ResourceID = v_FullCollectionMembership.ResourceID LEFT OUTER JOIN
                      v_RA_System_IPAddresses ON BIOS.ResourceID = v_RA_System_IPAddresses.ResourceID
WHERE     (v_FullCollectionMembership.CollectionID = 'BAC00260')
GROUP BY CS.Manufacturer0, CS.Model0, ES.ChassisTypes0, BIOS.SerialNumber0, CS.Name0, OS.Caption0, OS.CSDVersion0, CS.UserName0, OS.InstallDate0, SYS.SystemType0, v_RA_System_IPAddresses.IP_Addresses0, OS.LastBootUpTime0, v_GS_WORKSTATION_STATUS.LastHWScan
********************************************************************************
More SCCM custom reports can be found here

No comments:

Post a Comment