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 = 'SMS00001')
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

3 comments:

  1. Hi Venu,

    this query is not working on sccm 1910, can you please help out, I'm looking at getting a report similar to this one but this does not seem to work

    ReplyDelete
  2. Singireddi I'd also like this fancy query of yours working on 1910 or 2002. Help a brother out! :)

    ReplyDelete
    Replies
    1. Mr Chapman, do you still need help with this?? I can sort it out for you.

      Delete