Saturday, May 10, 2014

SCCM report for machine name, serial number, user full name

This reports provides Machine name, Machine manufacturer, Model, Serial number, AD Site, Last hardware scan, Discovery time, user name and user full name.
********************************************************************
SELECT     sys.Netbios_Name0 AS AssetID, v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], sys.AD_Site_Name0 AS [AD Site Location], vWorkstationStatus.LastHardwareScan AS [Last Hardware Scan], MAX(v_AgentDiscoveries.AgentTime) AS Discovery_time, sys.User_Name0 AS [User Name], usr.Full_User_Name0 AS [Full User Name]
FROM v_AgentDiscoveries
INNER JOIN v_R_User AS usr ON v_AgentDiscoveries.ResourceId = usr.ResourceID RIGHT OUTER JOIN v_GS_PC_BIOS INNER JOIN       v_R_System_Valid AS sys ON v_GS_PC_BIOS.ResourceID = sys.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM ON sys.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN                 vWorkstationStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = vWorkstationStatus.ResourceID ON usr.User_Name0 = sys.User_Name0
GROUP BY sys.Netbios_Name0, sys.User_Name0, sys.AD_Site_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0, vWorkstationStatus.LastHardwareScan, usr.Full_User_Name0
**********************************************************************

More SCCM custom reports can be found here

3 comments:

  1. Really helpful, thank you!

    ReplyDelete
  2. I get syntax error near serial number?

    ReplyDelete
  3. Hi Stephen,
    Make sure you don't have any extra characters when you copy the query.
    I have just tested again and it works for me.
    Cheers,
    Venu

    ReplyDelete