Config Manager 2012 (SCCM) Most Amazing Hardware Inventory SQL Report

[new] Last Updated Sept 2015

Adding CPU Information and Windows Updates Scan Information

See Also:

Introduction

I will be talking about one of my favorite parts when it comes to configuring Configuration Manager 2012/R2 infrastructure. By now, you have the main SCCM roles configured including the reporting point, and you start collecting hardware inventory data from your machines.

The part that is so interesting is to take this raw data that exist in your Configuration Manager database, and transform it into a valuable information that you can look at, and have a good understanding of your network resources.

I was trying to generate a SQL report that gives me machines in my network with almost full hardware inventory in a very well formatted way. If you are using SCCM queries (WQL) to generate report, I may argue that queries are good for SCCM operations and collection membership. They are not the preferred way for reporting.

SQL reports using Reporting Point is the preferred way to generate reports in Configuration Manager using native SQL queries. To do that, you should have some knowledge of the SCCM database schema and start browsing using ResourceID as the identifier of resources inside the SCCM SQL database.

Challenges

One of the challenging parts is to understand the SQL database schema, and to find out where the data resides. There are many tables out there, and you will be surprised that using the built in SQL viewes might help in your SQL JOIN statements. You can always open the definition of existing built-in SQL reports and learn the most used tables, and get a good insight about the database structure.

The second challenge and the most difficult challenge is the duplication of records. a certain machine can appear multiple times in the database, each time with different meaning.

Say for example a machine has 4 GB physical memory, installed in four different hardware memory slots. If you do a JOIN into the memory table, you will get four records for that machine, each time with a different memory slot number.

SCCM 2012 SQL Report 1

You may also find two records for the same machine, once when it was running Windows 7, and one when it was upgraded to Windows 8, and so on.

You may also find the same machine name with two different ResourceID values. This happens if for example you format a machine with the same name, in this case a new ResourceID will be recorded for the same machine name !!

The duplicate records are the most difficult problem in SCCM queries. I handle it by inspecting every duplicate records and take the last seen online time and pickup the newest one. This way, i can eliminate any duplicate that could happen. More over, when the same resource ID appears with different memory slots, i aggregate this info and sum up the total memory in one slot.

Finally, i recently added a variable in the SQL query called (@BackInTime) that you can use to filter the results according to the machines who recently reported information. The value is set to 30 days initially, and this means that we will only get results from machines that reported data in the last 30 days.

Solution

To write a SQL query that solves all the previous challenges, I had to start with the v_R_System SYS view, and then eliminate duplication by using the MAX(Creation_Date0) as my criteria. From there, I started to JOIN other tables, and each time the JOIN is performed, I am eliminating duplication by a way or another. The GROUP BY statement has most of the logic that solves the duplication criteria

Report Data

  • Machine Name : without any duplicates in the way.
  • Active Directory Site
  • User Name
  • Top User : the most user appearing to log to this machine
  • Windows Version: the last Windows version installed, removing any duplicates in the way/
  • Windows Service Pack
  • Machine Manufacturer
  • Machine Model
  • Serial Number
  • BIOS Date
  • BIOS Version 
  • Managed Date: the date in which SCCM started to manage this machine.
  • Physical Memory: Aggregate memory installed in all memory slots, removing any duplicates in the way.
  • Number of memory slots.
  • Type of the machine : X64 or X86
  • Logical Disk Size in GB
  • CPU Information [New]:
    • CPU Type
    • Number of sockets
    • Number of cores
    • Number of logical processors
  • Windows Update Scan Information [New]
    • Last Windows Update scan.
    • Last Windows Update scan error.
    • Last location for Windows Update scan.
  • PC Type: enumeration of all values of ChassisTypes0. This can be one of the following:
    1. when ‘1’ then ‘Other’
    2. when ‘2’ then ‘Unknown’
    3. when ‘3’ then ‘Desktop’
    4. when ‘4’ then ‘Low Profile Desktop’
    5. when ‘5’ then ‘Pizza Box’
    6. when ‘6’ then ‘Mini Tower’
    7. when ‘7’ then ‘Tower’
    8. when ‘8’ then ‘Portable’
    9. when ‘9’ then ‘Laptop’
    10. when ’10’ then ‘Notebook’
    11. when ’11’ then ‘Hand Held’
    12. when ’12’ then ‘Docking Station’
    13. when ’13’ then ‘All in One’
    14. when ’14’ then ‘Sub Notebook’
    15. when ’15’ then ‘Space-Saving’
    16. when ’16’ then ‘Lunch Box’
    17. when ’17’ then ‘Main System Chassis’
    18. when ’18’ then ‘Expansion Chassis’
    19. when ’19’ then ‘SubChassis’
    20. when ’20’ then ‘Bus Expansion Chassis’
    21. when ’21’ then ‘Peripheral Chassis’
    22. when ’22’ then ‘Storage Chassis’
    23. when ’23’ then ‘Rack Mount Chassis’
    24. when ’24’ then ‘Sealed-Case PC’
    25. else ‘Undefinded’

SQL Query


DECLARE @Today AS DATE
SET @Today = GETDATE()

DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )

SELECT DISTINCT 
 SYS.ResourceID,
 SYS.Name0 'Name', 
 SYS.AD_Site_Name0 'ADSite', 
 CS.UserName0 'User Name',
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END AS TopUser,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, 
 REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
 CS.Manufacturer0 'Manufacturer',
 CS.Model0 Model,
 BIOS.SerialNumber0 'Serial Number', 
 CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, 
 BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, 
 (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', 
 SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', 
 COUNT(RAM.ResourceID) '# Memory Slots',
 REPLACE (cs.SystemType0,'-based PC','') 'Type',
 SUM(D.Size0) / 1024 AS 'Disk Size GB',
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
 CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
 CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
 SYS.Client_Version0 as 'SCCM Agent Version',
 CPU.Manufacturer AS 'CPU Man.',
 CPU.[Number of CPUs] AS '# of CPUs',
 CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
 CPU.[Logical CPU Count] AS 'Logical CPU Count', 
 US.ScanTime AS ' Windows Updates Scan Time' ,
 US.LastErrorCode AS ' Windows Updates Last Error Code' ,
 US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END AS 'PC Type'
FROM
 v_R_System SYS
 INNER JOIN (
 SELECT 
 Name0,
 MAX(Creation_Date0) AS Creation_Date
 FROM 
 dbo.v_R_System 
 GROUP BY
 Name0
 ) AS CleanSystem
 ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
 LEFT JOIN v_GS_COMPUTER_SYSTEM CS 
 ON SYS.ResourceID=cs.ResourceID
 LEFT JOIN v_GS_PC_BIOS BIOS 
 ON SYS.ResourceID=bios.ResourceID
 LEFT JOIN (
 SELECT
 A.ResourceID,
 MAX(A.[InstallDate0]) AS [InstallDate0]
 FROM
 v_GS_OPERATING_SYSTEM A
 GROUP BY
 A.ResourceID
 ) AS X
 ON SYS.ResourceID = X.ResourceID
 INNER JOIN v_GS_OPERATING_SYSTEM OS 
 ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM 
 ON SYS.ResourceID=ram.ResourceID
 LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
 ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
 ON SYS.ResourceID = U.ResourceID 
 LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
 LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
 LEFT JOIN v_CH_ClientSummary CH
 ON SYS.ResourceID = CH.ResourceID
 LEFT JOIN (
 SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.ResourceID,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
 CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
 FROM [dbo].[v_GS_PROCESSOR] CPU
 GROUP BY
 CPU.SystemName0,
 CPU.Manufacturer0,
 CPU.Name0,
 CPU.NumberOfCores0,
 CPU.NumberOfLogicalProcessors0,
 CPU.ResourceID
 ) CPU
 ON CPU.ResourceID = SYS.ResourceID
 LEFT JOIN v_UpdateScanStatus US
 ON US.ResourceID = SYS.ResourceID
WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
 CH.LastOnline BETWEEN @BackInTime AND GETDATE()
 GROUP BY
 SYS.Creation_Date0 ,
 SYS.Name0 , 
 SYS.ResourceID ,
 SYS.AD_Site_Name0 ,
 CS.UserName0 ,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), 
 REPLACE (OS.CSDVersion0,'Service Pack','SP'),
 CS.Manufacturer0 ,
 CS.Model0 ,
 BIOS.SerialNumber0 ,
 REPLACE (cs.SystemType0,'-based PC','') ,
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
 CONVERT(VARCHAR(26), WS.LastHWScan, 101),
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END,
 CPU.Manufacturer, 
 CPU.[Number of CPUs] ,
 CPU.[Number of Cores per CPU], 
 CPU.[Logical CPU Count],
 US.ScanTime ,
 US.LastErrorCode ,
 US.LastScanPackageLocation ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END ,
 CONVERT (DATE,BIOS.ReleaseDate0) , 
 BIOS.SMBIOSBIOSVersion0 ,
 SYS.Client_Version0 ,
 CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
 ORDER BY SYS.Name0

 

Results in SQL Management Studio

If you open the SQL management Studio, connect to your SCCM database, and ran the previous SQL query as is, you will get the results as shown below:

SCCM 2012 SQL Report 2

If you are using SQL reporting Services to generate the report, you can customize the report in a well formatted way to get the shown result below:

SCCM 2012 SQL Report 3

Same Query but with more detailed memory info

So now, we will generate the same previous report but we will display the memory slots this time. So if a machine has 2 GB of memory, by installing 2 (1 GB) slots, then the machine will appear two times, each time with 1GB of RAM and the number of memory slot.

DECLARE @Today AS DATE
SET @Today = GETDATE()

DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )

SELECT DISTINCT 
 SYS.ResourceID,
 SYS.Name0 'Name', 
 SYS.AD_Site_Name0 'ADSite', 
 CS.UserName0 'User Name',
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END AS TopUser,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, 
 REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
 CS.Manufacturer0 'Manufacturer',
 CS.Model0 Model,
 BIOS.SerialNumber0 'Serial Number', 
 CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, 
 BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, 
 (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', 
 RAM.Capacity0 'Memory GB',
 DeviceLocator0 'MemorySlot',
 REPLACE (cs.SystemType0,'-based PC','') 'Type',
 SUM(D.Size0) / 1024 AS 'Disk Size GB',
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
 CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
 CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
 SYS.Client_Version0 as 'SCCM Agent Version',
 CPU.Manufacturer AS 'CPU Man.',
 CPU.[Number of CPUs] AS '# of CPUs',
 CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
 CPU.[Logical CPU Count] AS 'Logical CPU Count', 
 US.ScanTime AS ' Windows Updates Scan Time' ,
 US.LastErrorCode AS ' Windows Updates Last Error Code' ,
 US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END AS 'PC Type'
FROM
 v_R_System SYS
 INNER JOIN (
 SELECT 
 Name0,
 MAX(Creation_Date0) AS Creation_Date
 FROM 
 dbo.v_R_System 
 GROUP BY
 Name0
 ) AS CleanSystem
 ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
 LEFT JOIN v_GS_COMPUTER_SYSTEM CS 
 ON SYS.ResourceID=cs.ResourceID
 LEFT JOIN v_GS_PC_BIOS BIOS 
 ON SYS.ResourceID=bios.ResourceID
 LEFT JOIN (
 SELECT
 A.ResourceID,
 MAX(A.[InstallDate0]) AS [InstallDate0]
 FROM
 v_GS_OPERATING_SYSTEM A
 GROUP BY
 A.ResourceID
 ) AS X
 ON SYS.ResourceID = X.ResourceID
 INNER JOIN v_GS_OPERATING_SYSTEM OS 
 ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM 
 ON SYS.ResourceID=ram.ResourceID
 LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
 ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
 ON SYS.ResourceID = U.ResourceID 
 LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
 LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
 LEFT JOIN v_CH_ClientSummary CH
 ON SYS.ResourceID = CH.ResourceID
 LEFT JOIN (
 SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.ResourceID,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
 CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
 FROM [dbo].[v_GS_PROCESSOR] CPU
 GROUP BY
 CPU.SystemName0,
 CPU.Manufacturer0,
 CPU.Name0,
 CPU.NumberOfCores0,
 CPU.NumberOfLogicalProcessors0,
 CPU.ResourceID
 ) CPU
 ON CPU.ResourceID = SYS.ResourceID
 LEFT JOIN v_UpdateScanStatus US
 ON US.ResourceID = SYS.ResourceID
WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
 CH.LastOnline BETWEEN @BackInTime AND GETDATE()
 GROUP BY
 SYS.Creation_Date0 ,
 SYS.Name0 , 
 SYS.ResourceID ,
 SYS.AD_Site_Name0 ,
 CS.UserName0 ,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), 
 REPLACE (OS.CSDVersion0,'Service Pack','SP'),
 CS.Manufacturer0 ,
 CS.Model0 ,
 BIOS.SerialNumber0 ,
 REPLACE (cs.SystemType0,'-based PC','') ,
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
 CONVERT(VARCHAR(26), WS.LastHWScan, 101),
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END,
 RAM.Capacity0 ,
 DeviceLocator0 ,
 CPU.Manufacturer, 
 CPU.[Number of CPUs] ,
 CPU.[Number of Cores per CPU], 
 CPU.[Logical CPU Count],
 US.ScanTime ,
 US.LastErrorCode ,
 US.LastScanPackageLocation ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END ,
 CONVERT (DATE,BIOS.ReleaseDate0) , 
 BIOS.SMBIOSBIOSVersion0 ,
 SYS.Client_Version0 ,
 CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
 ORDER BY SYS.Name0 

 

Download the SQL Qeuries

 

67 comments on “Config Manager 2012 (SCCM) Most Amazing Hardware Inventory SQL Report

  1. Pingback: Config Manager 2012/R2 Build and Customize SQL Reports | Ammar Hasayen - Blog

      • I am not fan of IP/MAC info as you will have machines with multiple NICs or/and multiple IPs.
        IP will change frequently, especially in DHCP environment, and the SCCM data is not real time data, right, and it depends on the inventory time interval.

  2. Thanks for the above reports Ammar, works fine. I however would like to have it customized so I can select a Device Collection I want the report run on. Currently having issues to make that working.

      • For the moment I use the following query to get physical processor information from servers (excluding Virtual Machines):

        SELECT v_R_System.Netbios_Name0, v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
        v_GS_PROCESSOR.NumberOfCores0, v_GS_PROCESSOR.NumberOfLogicalProcessors0, v_GS_PROCESSOR.Name0, v_GS_OPERATING_SYSTEM.Caption0 AS Expr1
        FROM v_R_System INNER JOIN
        v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID INNER JOIN
        v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
        v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
        WHERE (v_R_System.Is_Virtual_Machine0 = 0) AND (v_GS_OPERATING_SYSTEM.Caption0 LIKE N’%server%’)
        GROUP BY v_R_System.Netbios_Name0, v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, v_GS_PROCESSOR.NumberOfCores0,
        v_GS_PROCESSOR.NumberOfLogicalProcessors0, v_GS_PROCESSOR.Name0, v_GS_OPERATING_SYSTEM.Caption0

        Could you add the number of physical processors to your extended query maybe?

  3. To get information for particular device collection,please add the below line above Group By and replace Collection id with your own:
    inner join dbo.v_FullCollectionMembership DFC on dfc.ResourceID = sys.ResourceID where dfc.CollectionID = ‘Collection ID’

  4. When I attempt to use the script for getting the processor information it’s throwing several syntax errors. Would it be possible to be repost that in a clean format or snag a copy via email?

  5. Will do. The only other thing I see is that for some reason Lenovo machine model names are numeric versus the standard well known model names (Think Pad, etc..). Anyone have any ideas on how to get that information instead of Model # 2466EJ3?

    • Ammar great SQL

      Hi Time Narc

      try this

      SELECT DISTINCT
      sys.Name0 ‘Machine’,
      sys.AD_Site_Name0 ‘ADSite’,
      CS.UserName0 ‘User Name’,
      CASE
      WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
      ELSE U.TopConsoleUser0
      END as TopUser,
      REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) OS,
      REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
      CS.Manufacturer0 ‘Manufacturer’,
      CS.Model0 Model,
      bd.TargetPlatform0,
      BIOS.SerialNumber0 ‘Serial Number’,
      BIOS.ReleaseDate0 as BIOSDate,
      BIOS.SMBIOSBIOSVersion0 as BIOSVersion,
      (SELECT CONVERT(DATE,sys.Creation_Date0)) ‘Managed Date’,
      SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
      COUNT(RAM.ResourceID) ‘# Memory Slots’,
      REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
      SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
      CASE SE.ChassisTypes0
      when ‘1’ then ‘Other’
      when ‘2’ then ‘Unknown’
      when ‘3’ then ‘Desktop’
      when ‘4’ then ‘Low Profile Desktop’
      when ‘5’ then ‘Pizza Box’
      when ‘6’ then ‘Mini Tower’
      when ‘7’ then ‘Tower’
      when ‘8’ then ‘Portable’
      when ‘9’ then ‘Laptop’
      when ’10’ then ‘Notebook’
      when ’11’ then ‘Hand Held’
      when ’12’ then ‘Docking Station’
      when ’13’ then ‘All in One’
      when ’14’ then ‘Sub Notebook’
      when ’15’ then ‘Space-Saving’
      when ’16’ then ‘Lunch Box’
      when ’17’ then ‘Main System Chassis’
      when ’18’ then ‘Expansion Chassis’
      when ’19’ then ‘SubChassis’
      when ’20’ then ‘Bus Expansion Chassis’
      when ’21’ then ‘Peripheral Chassis’
      when ’22’ then ‘Storage Chassis’
      when ’23’ then ‘Rack Mount Chassis’
      when ’24’ then ‘Sealed-Case PC’
      else ‘Undefinded’
      END AS ‘PC Type’
      FROM
      v_R_System SYS
      INNER JOIN v_GS_BuildData0 BD on bd.ResourceID=Sys.ResourceID
      INNER JOIN (
      SELECT
      Name0,
      MAX(Creation_Date0) AS Creation_Date
      FROM
      dbo.v_R_System
      GROUP BY
      Name0
      ) AS CleanSystem
      ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
      LEFT JOIN v_GS_COMPUTER_SYSTEM CS
      ON sys.ResourceID=cs.ResourceID
      LEFT JOIN v_GS_PC_BIOS BIOS
      ON sys.ResourceID=bios.ResourceID
      LEFT JOIN (
      SELECT
      A.ResourceID,
      MAX(A.[InstallDate0]) AS [InstallDate0]
      FROM
      v_GS_OPERATING_SYSTEM A
      GROUP BY
      A.ResourceID
      ) AS X
      ON sys.ResourceID = X.ResourceID
      INNER JOIN v_GS_OPERATING_SYSTEM OS
      ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
      LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
      ON sys.ResourceID=ram.ResourceID
      LEFT OUTER join dbo.v_GS_LOGICAL_DISK D
      ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
      LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
      ON SYS.ResourceID = U.ResourceID
      LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE on SYS.ResourceID = SE.ResourceID
      LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En on SYS.ResourceID = En.ResourceID

      GROUP BY
      sys.Creation_Date0, sys.Name0,
      sys.AD_Site_Name0 , CS.UserName0 ,REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) , REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ,
      CS.Manufacturer0 , CS.Model0 ,BIOS.SerialNumber0 , REPLACE (cs.SystemType0,’-based PC’,”),
      CASE
      WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
      ELSE U.TopConsoleUser0
      END,
      CASE SE.ChassisTypes0
      when ‘1’ then ‘Other’
      when ‘2’ then ‘Unknown’
      when ‘3’ then ‘Desktop’
      when ‘4’ then ‘Low Profile Desktop’
      when ‘5’ then ‘Pizza Box’
      when ‘6’ then ‘Mini Tower’
      when ‘7’ then ‘Tower’
      when ‘8’ then ‘Portable’
      when ‘9’ then ‘Laptop’
      when ’10’ then ‘Notebook’
      when ’11’ then ‘Hand Held’
      when ’12’ then ‘Docking Station’
      when ’13’ then ‘All in One’
      when ’14’ then ‘Sub Notebook’
      when ’15’ then ‘Space-Saving’
      when ’16’ then ‘Lunch Box’
      when ’17’ then ‘Main System Chassis’
      when ’18’ then ‘Expansion Chassis’
      when ’19’ then ‘SubChassis’
      when ’20’ then ‘Bus Expansion Chassis’
      when ’21’ then ‘Peripheral Chassis’
      when ’22’ then ‘Storage Chassis’
      when ’23’ then ‘Rack Mount Chassis’
      when ’24’ then ‘Sealed-Case PC’
      else ‘Undefinded’
      END ,

      BIOS.ReleaseDate0 ,
      BIOS.SMBIOSBIOSVersion0,
      bd.targetplatform0

      ORDER BY sys.Name0

  6. Hi, I am just looking to take your report and have it pull all of that info based on an SCCM collection using the ID#. Is this possible?

    Thanks

  7. This query is great! Thanks for putting it together and sharing it! I am hoping you can help me with some duplicates being returned. In my usage scenario I need the ResourceID to be unique, but ~ 12 computers are being returned with duplicate entries (out of 4200 items, so very small %). The first 11 are laptops, where the duplicate is reporting back as the docking station. The last case appears to be the same machine with two different serial numbers. I’m guessing a motherboard replacement at some point.

    How can I modify the query to make sure these aren’t being returned?

    • Interesting….

      Could you please run

      select ResourceID from v_GS_PC_BIOS
      GROUP BY ResourceID HAVING COUNT(ResourceID)>1

      and if you have a result here then the BIOS table is showing same resourceID with two different serials.

      One you have the duplicate resourceID or IDs, take one of them (for example 12345678) and run:

      select * from v_GS_PC_BIOS where resourceid = ‘123456’

      and send me the results so i can identify how to get the newest one.

      • Hello Ammar, I sent the details over to you via email. Let me know if you need any more information.
        Thanks, Brent

  8. I have about 37 computers that are excluded from the report. They should be caught by the else case of “unknown”. Is there any reason that a computer would not be included?

  9. Nice sql thing. Since 2010 I’ve been using something similar at work, but I also have the cpu codename in the script, to help guess the age of a system. Maybe you can use that. In certain cases it’ll generate duplicate rows in servers running 32nm core i cpus, but overall it works well. Since we until recently never even considered purchasing AMD cpu’s, not a lot of those are in the list.

    CASE WHEN substring(ProcessorId0, 12, 4) = ‘4065’ THEN ‘Haswell (22nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘306A’ THEN ‘IvyBridge (22nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘206A’ THEN ‘SandyBridge (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘306F’ THEN ‘SandyBridge (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘206D’ THEN ‘SandyBridge-E/EN/EP (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘2065’ THEN ‘Arrandale/Clarksdale(45/32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘206C’ THEN ‘Gulftown/Westmere-EP (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘206F’ THEN ‘Westmere-EX (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘106E’ THEN ‘Clarksfield/Lynnfield/Jasper Forest (45nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘106A’ THEN ‘Bloomfield/Nehalem-EP (45nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘206E’ THEN ‘Nehalem-EX (45nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘1067’ THEN ‘Yorkfield/Wolfdale/Penryn/Harpertown (45nm core 2)’
    WHEN substring(ProcessorId0, 12, 4) = ‘106D’ THEN ‘Donnington (45nm core 2)’
    WHEN substring(ProcessorId0, 12, 4) = ‘006F’ THEN ‘Clovertown/Kentsfield/Conroe/Merom/Woodcrest (65nm core 2)’
    WHEN substring(ProcessorId0, 12, 4) = ‘1066’ THEN ‘Merom/Conroe (65nm Core 2)’
    WHEN substring(ProcessorId0, 12, 4) = ‘0066’ THEN ‘Cedar Mill/Presler (65nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ‘0063’ THEN ‘Nocona (90nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ‘0064’ THEN ‘Irwindale/Prescott (90nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ‘006D’ THEN ‘Dothan (90nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ‘3065’ THEN ‘Cloverview (32nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘3066’ THEN ‘Cedarview (32nm Bonnell)’
    WHEN substring(ProcessorId0, 12, 4) = ‘0266’ THEN ‘Lincroft (45nm Bonnell)’
    WHEN substring(ProcessorId0, 12, 4) = ‘016C’ THEN ‘Pineview/Silverthorne (45nm Bonnell)’
    WHEN substring(ProcessorId0, 12, 4) = ‘006C’ THEN ‘Undocumented Xeon 5000 (65nm Core 2)’
    WHEN substring(ProcessorId0, 12, 4) = ’00F2′ THEN ‘Northwood/Barnias/Prestonia/Gallatin (130nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ’00F4′ THEN ‘Prescott/Paxville (90nm Netburst)’
    WHEN substring(ProcessorId0, 12, 4) = ‘106C’ THEN ‘Diamondville/Pineview/Silverthorne/Lincroft (45nm Bonnell)’
    WHEN substring(ProcessorId0, 12, 4) = ‘306C’ THEN ‘Haswell (22nm core i)’
    WHEN substring(ProcessorId0, 12, 4) = ‘306D’ THEN ‘Broadwell (14nm core i)’
    WHEN substring(ProcessorId0, 9, 8) = ‘00630F01’ THEN ‘Kavari (28nm 4gen APU)’
    WHEN substring(ProcessorId0, 9, 8) = ‘00000000’ THEN ‘Unknown’
    ELSE ‘Undocumented/AMD ‘ + substring(ProcessorId0, 9, 8) END AS Codename

  10. Hello! Great, great query tjank you! Is it possible to get periferals like keyboard, mouse and monitor information also? That would be top notch! Thank you again!

  11. To get information for particular device collection, change collection id XXXXXXXX with your one
    =====================================
    DECLARE @Today AS DATE
    SET @Today = GETDATE()

    DECLARE @BackInTime AS DATE
    SET @BackInTime = DATEADD(DAY, -30, @Today )

    SELECT DISTINCT
    SYS.ResourceID,
    SYS.Name0 ‘Name’,
    SYS.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END AS TopUser,
    REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’)),’Enterprise’,’EE’) ),’Standard’,’ST’)),’Microsoft®’,”)),’Server’,’SRV’)),’Windows’,’Win’) OS,
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
    CS.Manufacturer0 ‘Manufacturer’,
    CS.Model0 Model,
    BIOS.SerialNumber0 ‘Serial Number’,
    CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate,
    BIOS.SMBIOSBIOSVersion0 AS BIOSVersion,
    (SELECT CONVERT(DATE,SYS.Creation_Date0)) ‘Managed Date’,
    SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
    COUNT(RAM.ResourceID) ‘# Memory Slots’,
    REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
    SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
    CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS ‘Last Reboot Date/Time’,
    CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS ‘Install Date’,
    CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS ‘Last Hardware Inventory’,
    CONVERT(VARCHAR(26), CH.LastOnline, 101) AS ‘Last Seen Online’,
    SYS.Client_Version0 as ‘SCCM Agent Version’,
    CPU.Manufacturer AS ‘CPU Man.’,
    CPU.[Number of CPUs] AS ‘# of CPUs’,
    CPU.[Number of Cores per CPU] AS ‘# of Cores per CPU’,
    CPU.[Logical CPU Count] AS ‘Logical CPU Count’,
    US.ScanTime AS ‘ Windows Updates Scan Time’ ,
    US.LastErrorCode AS ‘ Windows Updates Last Error Code’ ,
    US.LastScanPackageLocation AS ‘ Windows Updates Last Package Location’ ,
    CASE SE.ChassisTypes0
    WHEN ‘1’ THEN ‘Other’
    WHEN ‘2’ THEN ‘Unknown’
    WHEN ‘3’ THEN ‘Desktop’
    WHEN ‘4’ THEN ‘Low Profile Desktop’
    WHEN ‘5’ THEN ‘Pizza Box’
    WHEN ‘6’ THEN ‘Mini Tower’
    WHEN ‘7’ THEN ‘Tower’
    WHEN ‘8’ THEN ‘Portable’
    WHEN ‘9’ THEN ‘Laptop’
    WHEN ’10’ THEN ‘Notebook’
    WHEN ’11’ THEN ‘Hand Held’
    WHEN ’12’ THEN ‘Docking Station’
    WHEN ’13’ THEN ‘All in One’
    WHEN ’14’ THEN ‘Sub Notebook’
    WHEN ’15’ THEN ‘Space-Saving’
    WHEN ’16’ THEN ‘Lunch Box’
    WHEN ’17’ THEN ‘Main System Chassis’
    WHEN ’18’ THEN ‘Expansion Chassis’
    WHEN ’19’ THEN ‘SubChassis’
    WHEN ’20’ THEN ‘Bus Expansion Chassis’
    WHEN ’21’ THEN ‘Peripheral Chassis’
    WHEN ’22’ THEN ‘Storage Chassis’
    WHEN ’23’ THEN ‘Rack Mount Chassis’
    WHEN ’24’ THEN ‘Sealed-Case PC’
    ELSE ‘Undefinded’
    END AS ‘PC Type’
    FROM
    v_R_System SYS
    INNER JOIN (
    SELECT
    Name0,
    MAX(Creation_Date0) AS Creation_Date
    FROM
    dbo.v_R_System

    GROUP BY
    Name0
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
    LEFT JOIN v_GS_COMPUTER_SYSTEM CS
    ON SYS.ResourceID=cs.ResourceID
    LEFT JOIN v_GS_PC_BIOS BIOS
    ON SYS.ResourceID=bios.ResourceID
    LEFT JOIN (
    SELECT
    A.ResourceID,
    MAX(A.[InstallDate0]) AS [InstallDate0]
    FROM
    v_GS_OPERATING_SYSTEM A
    GROUP BY
    A.ResourceID
    ) AS X
    ON SYS.ResourceID = X.ResourceID

    INNER JOIN v_GS_OPERATING_SYSTEM OS
    ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
    LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
    ON SYS.ResourceID=ram.ResourceID
    LEFT JOIN v_FullCollectionMembership DFC
    ON SYS.ResourceID = DFC.ResourceID
    LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
    ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
    LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
    ON SYS.ResourceID = U.ResourceID
    LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
    LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
    LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
    LEFT JOIN v_CH_ClientSummary CH
    ON SYS.ResourceID = CH.ResourceID
    LEFT JOIN (
    SELECT
    DISTINCT(CPU.SystemName0) AS [System Name],
    CPU.Manufacturer0 AS Manufacturer,
    CPU.ResourceID,
    CPU.Name0 AS Name,
    COUNT(CPU.ResourceID) AS [Number of CPUs],
    CPU.NumberOfCores0 AS [Number of Cores per CPU],
    CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
    FROM [dbo].[v_GS_PROCESSOR] CPU
    GROUP BY
    CPU.SystemName0,
    CPU.Manufacturer0,
    CPU.Name0,
    CPU.NumberOfCores0,
    CPU.NumberOfLogicalProcessors0,
    CPU.ResourceID
    ) CPU
    ON CPU.ResourceID = SYS.ResourceID
    LEFT JOIN v_UpdateScanStatus US
    ON US.ResourceID = SYS.ResourceID
    WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND DFC.CollectionID = ‘XXXXXXXX’ AND
    CH.LastOnline BETWEEN @BackInTime AND GETDATE()
    GROUP BY
    SYS.Creation_Date0 ,
    SYS.Name0 ,
    SYS.ResourceID ,
    SYS.AD_Site_Name0 ,
    CS.UserName0 ,
    REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’)),’Enterprise’,’EE’) ),’Standard’,’ST’)),’Microsoft®’,”)),’Server’,’SRV’)),’Windows’,’Win’),
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’),
    CS.Manufacturer0 ,
    CS.Model0 ,
    BIOS.SerialNumber0 ,
    REPLACE (cs.SystemType0,’-based PC’,”) ,
    CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
    CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
    CONVERT(VARCHAR(26), WS.LastHWScan, 101),
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END,
    CPU.Manufacturer,
    CPU.[Number of CPUs] ,
    CPU.[Number of Cores per CPU],
    CPU.[Logical CPU Count],
    US.ScanTime ,
    US.LastErrorCode ,
    US.LastScanPackageLocation ,
    CASE SE.ChassisTypes0
    WHEN ‘1’ THEN ‘Other’
    WHEN ‘2’ THEN ‘Unknown’
    WHEN ‘3’ THEN ‘Desktop’
    WHEN ‘4’ THEN ‘Low Profile Desktop’
    WHEN ‘5’ THEN ‘Pizza Box’
    WHEN ‘6’ THEN ‘Mini Tower’
    WHEN ‘7’ THEN ‘Tower’
    WHEN ‘8’ THEN ‘Portable’
    WHEN ‘9’ THEN ‘Laptop’
    WHEN ’10’ THEN ‘Notebook’
    WHEN ’11’ THEN ‘Hand Held’
    WHEN ’12’ THEN ‘Docking Station’
    WHEN ’13’ THEN ‘All in One’
    WHEN ’14’ THEN ‘Sub Notebook’
    WHEN ’15’ THEN ‘Space-Saving’
    WHEN ’16’ THEN ‘Lunch Box’
    WHEN ’17’ THEN ‘Main System Chassis’
    WHEN ’18’ THEN ‘Expansion Chassis’
    WHEN ’19’ THEN ‘SubChassis’
    WHEN ’20’ THEN ‘Bus Expansion Chassis’
    WHEN ’21’ THEN ‘Peripheral Chassis’
    WHEN ’22’ THEN ‘Storage Chassis’
    WHEN ’23’ THEN ‘Rack Mount Chassis’
    WHEN ’24’ THEN ‘Sealed-Case PC’
    ELSE ‘Undefinded’
    END ,
    CONVERT (DATE,BIOS.ReleaseDate0) ,
    BIOS.SMBIOSBIOSVersion0 ,
    SYS.Client_Version0 ,
    CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
    ORDER BY SYS.Name0

    • I’m trying to do this by collection as well, and I’m having a problem getting this line to work. I receive the following, any ideas? Thanks.

      Msg 4104, Level 16, State 1, Line 128
      The multi-part identifier “DFC.CollectionID” could not be bound.

  12. Howdy,
    I as well would like to use User Full Name – I’m trying to join with v_r_User using Unique_User_Name0 = CS.UserName0, but not having much luck..

  13. Hello,

    We are finding the memory information from the first SQL query to be incorrect. The “Physical Memory” number calculated is not equal to the amount of “Installed Memory” in the machine. Instead, this “Physical Memory” number seems to be the Max Amount of Total Physical Memory that can be installed based off the number of DIMMs. Is anyone else seeing this as well?

  14. Hi, how are you?. The REPORT is amazing!, but im willing to add the MAC & IP Addresses fields since we have static IP’s in our servers. Can you make another version to achieve this please?. Is it hard to also report is the server have SQL installed?, and what about server installed roles?. Thanks & Regards!.

  15. First off great SQL Query much appreciated. I see above people asking about adding Full username I have this working on the report… Just add a few lines of code. You need to join the view.

    After
    LEFT JOIN v_GS_COMPUTER_SYSTEM CS
    ON sys.ResourceID=cs.ResourceID

    Add Right below
    LEFT JOIN v_R_User US
    ON CS.UserName0 = US.Unique_User_Name0

    In the Select Distinct you add where ever you want in the query but I put my after END as TopUser,

    I added the following columns
    US.displayName0 AS [Display Name],
    CASE
    WHEN US.department0 IS NULL THEN ‘NO Department Assigned’
    ELSE US.department0
    END AS [Department],
    US.Full_Domain_Name0 AS [Domain],
    US.Full_User_Name0 AS [User Name],

    Then you have to add these fields to the Group by towards the end of the query

    Here is the overall query:
    SELECT DISTINCT
    sys.Name0 ‘Machine’,
    sys.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END as TopUser,
    US.displayName0 AS [Display Name],
    CASE
    WHEN US.department0 IS NULL THEN ‘NO Department Assigned’
    ELSE US.department0
    END AS [Department],
    US.Full_Domain_Name0 AS [Domain],
    US.Full_User_Name0 AS [User Name],
    REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) OS,
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
    CS.Manufacturer0 ‘Manufacturer’,
    CS.Model0 Model,
    BIOS.SerialNumber0 ‘Serial Number’,
    BIOS.ReleaseDate0 as BIOSDate,
    BIOS.SMBIOSBIOSVersion0 as BIOSVersion,
    (SELECT CONVERT(DATE,sys.Creation_Date0)) ‘Managed Date’,
    SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
    COUNT(RAM.ResourceID) ‘# Memory Slots’,
    REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
    SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
    CASE SE.ChassisTypes0
    when ‘1’ then ‘Other’
    when ‘2’ then ‘Unknown’
    when ‘3’ then ‘Desktop’
    when ‘4’ then ‘Low Profile Desktop’
    when ‘5’ then ‘Pizza Box’
    when ‘6’ then ‘Mini Tower’
    when ‘7’ then ‘Tower’
    when ‘8’ then ‘Portable’
    when ‘9’ then ‘Laptop’
    when ’10’ then ‘Notebook’
    when ’11’ then ‘Hand Held’
    when ’12’ then ‘Docking Station’
    when ’13’ then ‘All in One’
    when ’14’ then ‘Sub Notebook’
    when ’15’ then ‘Space-Saving’
    when ’16’ then ‘Lunch Box’
    when ’17’ then ‘Main System Chassis’
    when ’18’ then ‘Expansion Chassis’
    when ’19’ then ‘SubChassis’
    when ’20’ then ‘Bus Expansion Chassis’
    when ’21’ then ‘Peripheral Chassis’
    when ’22’ then ‘Storage Chassis’
    when ’23’ then ‘Rack Mount Chassis’
    when ’24’ then ‘Sealed-Case PC’
    else ‘Undefinded’
    END AS ‘PC Type’
    FROM
    v_R_System SYS
    INNER JOIN (
    SELECT
    Name0,
    MAX(Creation_Date0) AS Creation_Date
    FROM
    dbo.v_R_System
    GROUP BY
    Name0
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 and sys.Creation_Date0 = CleanSystem.Creation_Date
    LEFT JOIN v_GS_COMPUTER_SYSTEM CS
    ON sys.ResourceID=cs.ResourceID
    LEFT JOIN v_R_User US
    ON CS.UserName0 = US.Unique_User_Name0
    LEFT JOIN v_GS_PC_BIOS BIOS
    ON sys.ResourceID=bios.ResourceID
    LEFT JOIN (
    SELECT
    A.ResourceID,
    MAX(A.[InstallDate0]) AS [InstallDate0]
    FROM
    v_GS_OPERATING_SYSTEM A
    GROUP BY
    A.ResourceID
    ) AS X
    ON sys.ResourceID = X.ResourceID
    INNER JOIN v_GS_OPERATING_SYSTEM OS
    ON X.ResourceID=OS.ResourceID and X.InstallDate0 = OS.InstallDate0
    LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
    ON sys.ResourceID=ram.ResourceID
    LEFT OUTER join dbo.v_GS_LOGICAL_DISK D
    ON sys.ResourceID = D.ResourceID and D.DriveType0 = 3
    LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
    ON SYS.ResourceID = U.ResourceID
    LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE on SYS.ResourceID = SE.ResourceID
    LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En on SYS.ResourceID = En.ResourceID

    GROUP BY
    sys.Creation_Date0, sys.Name0,
    sys.AD_Site_Name0 , CS.UserName0 ,REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’) , REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ,
    CS.Manufacturer0 , CS.Model0 ,BIOS.SerialNumber0 , REPLACE (cs.SystemType0,’-based PC’,”),US.displayName0,US.department0,US.Full_Domain_Name0,US.Full_User_Name0,
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END,
    CASE SE.ChassisTypes0
    when ‘1’ then ‘Other’
    when ‘2’ then ‘Unknown’
    when ‘3’ then ‘Desktop’
    when ‘4’ then ‘Low Profile Desktop’
    when ‘5’ then ‘Pizza Box’
    when ‘6’ then ‘Mini Tower’
    when ‘7’ then ‘Tower’
    when ‘8’ then ‘Portable’
    when ‘9’ then ‘Laptop’
    when ’10’ then ‘Notebook’
    when ’11’ then ‘Hand Held’
    when ’12’ then ‘Docking Station’
    when ’13’ then ‘All in One’
    when ’14’ then ‘Sub Notebook’
    when ’15’ then ‘Space-Saving’
    when ’16’ then ‘Lunch Box’
    when ’17’ then ‘Main System Chassis’
    when ’18’ then ‘Expansion Chassis’
    when ’19’ then ‘SubChassis’
    when ’20’ then ‘Bus Expansion Chassis’
    when ’21’ then ‘Peripheral Chassis’
    when ’22’ then ‘Storage Chassis’
    when ’23’ then ‘Rack Mount Chassis’
    when ’24’ then ‘Sealed-Case PC’
    else ‘Undefinded’
    END ,

    BIOS.ReleaseDate0 ,
    BIOS.SMBIOSBIOSVersion0

    ORDER BY sys.Name0

    • Full User Name is located in v_r_user.Full_User_Name0, v_r_User.Unique_User_Name0 would be the best bet for join as it’s the same as v_GS_System_Console_Usage.TopConsoleUser0/v_R_System.User_Name0

  16. Report with MAC and IP addresses in case you need them
    ===========================
    DECLARE @Today AS DATE
    SET @Today = GETDATE()

    DECLARE @BackInTime AS DATE
    SET @BackInTime = DATEADD(DAY, -30, @Today )

    SELECT DISTINCT
    SYS.ResourceID,
    SYS.Name0 ‘Name’,
    MACADDRESS.MAC_Addresses0 as ‘MAC Address’,
    IPADDRESS.IPAddress0 as ‘IP Address’,
    SYS.AD_Site_Name0 ‘ADSite’,
    CS.UserName0 ‘User Name’,
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END AS TopUser,
    REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’)),’Enterprise’,’EE’) ),’Standard’,’ST’)),’Microsoft®’,”)),’Server’,’SRV’)),’Windows’,’Win’) OS,
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’) ‘Service Pack’,
    CS.Manufacturer0 ‘Manufacturer’,
    CS.Model0 Model,
    BIOS.SerialNumber0 ‘Serial Number’,
    CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate,
    BIOS.SMBIOSBIOSVersion0 AS BIOSVersion,
    (SELECT CONVERT(DATE,SYS.Creation_Date0)) ‘Managed Date’,
    SUM(ISNULL(RAM.Capacity0,0)) ‘Memory (MB)’,
    COUNT(RAM.ResourceID) ‘# Memory Slots’,
    REPLACE (cs.SystemType0,’-based PC’,”) ‘Type’,
    SUM(D.Size0) / 1024 AS ‘Disk Size GB’,
    CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS ‘Last Reboot Date/Time’,
    CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS ‘Install Date’,
    CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS ‘Last Hardware Inventory’,
    CONVERT(VARCHAR(26), CH.LastOnline, 101) AS ‘Last Seen Online’,
    SYS.Client_Version0 as ‘SCCM Agent Version’,
    CPU.Manufacturer AS ‘CPU Man.’,
    CPU.[Number of CPUs] AS ‘# of CPUs’,
    CPU.[Number of Cores per CPU] AS ‘# of Cores per CPU’,
    CPU.[Logical CPU Count] AS ‘Logical CPU Count’,
    US.ScanTime AS ‘ Windows Updates Scan Time’ ,
    US.LastErrorCode AS ‘ Windows Updates Last Error Code’ ,
    US.LastScanPackageLocation AS ‘ Windows Updates Last Package Location’ ,

    CASE SE.ChassisTypes0
    WHEN ‘1’ THEN ‘Other’
    WHEN ‘2’ THEN ‘Unknown’
    WHEN ‘3’ THEN ‘Desktop’
    WHEN ‘4’ THEN ‘Low Profile Desktop’
    WHEN ‘5’ THEN ‘Pizza Box’
    WHEN ‘6’ THEN ‘Mini Tower’
    WHEN ‘7’ THEN ‘Tower’
    WHEN ‘8’ THEN ‘Portable’
    WHEN ‘9’ THEN ‘Laptop’
    WHEN ’10’ THEN ‘Notebook’
    WHEN ’11’ THEN ‘Hand Held’
    WHEN ’12’ THEN ‘Docking Station’
    WHEN ’13’ THEN ‘All in One’
    WHEN ’14’ THEN ‘Sub Notebook’
    WHEN ’15’ THEN ‘Space-Saving’
    WHEN ’16’ THEN ‘Lunch Box’
    WHEN ’17’ THEN ‘Main System Chassis’
    WHEN ’18’ THEN ‘Expansion Chassis’
    WHEN ’19’ THEN ‘SubChassis’
    WHEN ’20’ THEN ‘Bus Expansion Chassis’
    WHEN ’21’ THEN ‘Peripheral Chassis’
    WHEN ’22’ THEN ‘Storage Chassis’
    WHEN ’23’ THEN ‘Rack Mount Chassis’
    WHEN ’24’ THEN ‘Sealed-Case PC’
    ELSE ‘Undefinded’
    END AS ‘PC Type’
    FROM
    v_R_System SYS
    INNER JOIN (
    SELECT
    Name0,
    MAX(Creation_Date0) AS Creation_Date
    FROM
    dbo.v_R_System
    GROUP BY
    Name0
    ) AS CleanSystem
    ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
    LEFT JOIN v_GS_COMPUTER_SYSTEM CS
    ON SYS.ResourceID=cs.ResourceID
    LEFT JOIN v_GS_PC_BIOS BIOS
    ON SYS.ResourceID=bios.ResourceID
    LEFT JOIN (
    SELECT
    A.ResourceID,
    MAX(A.[InstallDate0]) AS [InstallDate0]
    FROM
    v_GS_OPERATING_SYSTEM A
    GROUP BY
    A.ResourceID
    ) AS X
    ON SYS.ResourceID = X.ResourceID
    INNER JOIN v_GS_OPERATING_SYSTEM OS
    ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
    LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
    ON SYS.ResourceID=ram.ResourceID
    LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
    ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
    LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
    ON SYS.ResourceID = U.ResourceID
    LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
    LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
    LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
    LEFT JOIN v_CH_ClientSummary CH
    ON SYS.ResourceID = CH.ResourceID
    LEFT JOIN (
    SELECT
    DISTINCT(CPU.SystemName0) AS [System Name],
    CPU.Manufacturer0 AS Manufacturer,
    CPU.ResourceID,
    CPU.Name0 AS Name,
    COUNT(CPU.ResourceID) AS [Number of CPUs],
    CPU.NumberOfCores0 AS [Number of Cores per CPU],
    CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
    FROM [dbo].[v_GS_PROCESSOR] CPU
    GROUP BY
    CPU.SystemName0,
    CPU.Manufacturer0,
    CPU.Name0,
    CPU.NumberOfCores0,
    CPU.NumberOfLogicalProcessors0,
    CPU.ResourceID
    ) CPU
    ON CPU.ResourceID = SYS.ResourceID
    LEFT JOIN v_UpdateScanStatus US
    ON US.ResourceID = SYS.ResourceID
    –start
    LEFT JOIN v_RA_System_MACAddresses MACADDRESS
    ON MACADDRESS.ResourceID = SYS.ResourceID
    LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION IPADDRESS
    ON IPADDRESS.ResourceID = SYS.ResourceID
    –end
    WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
    CH.LastOnline BETWEEN @BackInTime AND GETDATE()
    GROUP BY
    SYS.Creation_Date0 ,
    SYS.Name0 ,
    MACADDRESS.MAC_Addresses0,
    IPADDRESS.IPAddress0,
    SYS.ResourceID ,
    SYS.AD_Site_Name0 ,
    CS.UserName0 ,
    REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, ‘Microsoft Windows’,’Win’)),’Enterprise’,’EE’) ),’Standard’,’ST’)),’Microsoft®’,”)),’Server’,’SRV’)),’Windows’,’Win’),
    REPLACE (OS.CSDVersion0,’Service Pack’,’SP’),
    CS.Manufacturer0 ,
    CS.Model0 ,
    BIOS.SerialNumber0 ,
    REPLACE (cs.SystemType0,’-based PC’,”) ,
    CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
    CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
    CONVERT(VARCHAR(26), WS.LastHWScan, 101),
    CASE
    WHEN U.TopConsoleUser0 = ‘-1’ OR U.TopConsoleUser0 IS NULL THEN ‘N/A’
    ELSE U.TopConsoleUser0
    END,
    CPU.Manufacturer,
    CPU.[Number of CPUs] ,
    CPU.[Number of Cores per CPU],
    CPU.[Logical CPU Count],
    US.ScanTime ,
    US.LastErrorCode ,
    US.LastScanPackageLocation ,
    CASE SE.ChassisTypes0
    WHEN ‘1’ THEN ‘Other’
    WHEN ‘2’ THEN ‘Unknown’
    WHEN ‘3’ THEN ‘Desktop’
    WHEN ‘4’ THEN ‘Low Profile Desktop’
    WHEN ‘5’ THEN ‘Pizza Box’
    WHEN ‘6’ THEN ‘Mini Tower’
    WHEN ‘7’ THEN ‘Tower’
    WHEN ‘8’ THEN ‘Portable’
    WHEN ‘9’ THEN ‘Laptop’
    WHEN ’10’ THEN ‘Notebook’
    WHEN ’11’ THEN ‘Hand Held’
    WHEN ’12’ THEN ‘Docking Station’
    WHEN ’13’ THEN ‘All in One’
    WHEN ’14’ THEN ‘Sub Notebook’
    WHEN ’15’ THEN ‘Space-Saving’
    WHEN ’16’ THEN ‘Lunch Box’
    WHEN ’17’ THEN ‘Main System Chassis’
    WHEN ’18’ THEN ‘Expansion Chassis’
    WHEN ’19’ THEN ‘SubChassis’
    WHEN ’20’ THEN ‘Bus Expansion Chassis’
    WHEN ’21’ THEN ‘Peripheral Chassis’
    WHEN ’22’ THEN ‘Storage Chassis’
    WHEN ’23’ THEN ‘Rack Mount Chassis’
    WHEN ’24’ THEN ‘Sealed-Case PC’
    ELSE ‘Undefinded’
    END ,
    CONVERT (DATE,BIOS.ReleaseDate0) ,
    BIOS.SMBIOSBIOSVersion0 ,
    SYS.Client_Version0 ,
    CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
    ORDER BY SYS.Name0

    • Getting:

      “An error occurred while reading data from the query result set.
      Arithmetic overflow error converting expression to data type int.

      Arithmetic overflow error converting expression to data type int. (Microsoft SQL Server, Error: 8115)”

      (after replacing all single open and end quote marks, and double-quote marks, with single quote characters (ASCII 39) and removing “-start” and “-end”)

  17. Pingback: SCCM Hardware Inventory Report | SCCMIZER

  18. Hi, this is a fantastic report and is everything I need. I am trying to get it to work in my lab environment at the moment, but the User Name column doesn’t return anything, it’s all blank. Any idea what I might be missing? I’m guessing I’m not collecting some specific information from the machines?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s