SQL Query - Get Custom Inventory report from SCCM Database

You can use the below query to pull up custom inventory details, including device name, model, serial number, processor name, no of processors, no of sockets, total memory, hardik size, and free space.

Query:

SELECT DISTINCT 

                        CPU.SystemName0 AS [System Name], GSCS.Model0 as Model, 

sed.SerialNumber0 as Serial,

CPU.Name0 AS Name, COUNT(CPU.ResourceID) AS [Number of Sockets], 

                         CPU.NumberOfCores0 AS [Number Cores Per Socket], 

GSS.Caption0 AS OS, 

RAM.TotalPhysicalMemory0 / 1000 / 1000 AS [Total Memory],

  sum(isnull(LDisk.Size0,'0'))  / 1000 as 'Hardrive Size', 

  sum(isnull(LDisk.FreeSpace0,'0')) / 1000 AS 'Free Space',

--GSS.InstallDate0 AS [SCCM Agent Install Date], 

v_GS_OPERATING_SYSTEM.InstallDate0 AS OSInstall, 

                         --WSS.LastPolicyRequest AS LastContact, 

DATEDIFF(DAY, WSS.LastPolicyRequest, GETDATE()) AS 'DaysSinceLastContact'

--,CASE WHEN GSY.DisplayName0 = 'Cluster Service' and GSY.StartMode0 = 'Auto' then 'CLuster' Else 'Non Cluster' END As 'Cluster'

 

FROM            v_GS_PROCESSOR AS CPU INNER JOIN

                         v_GS_OPERATING_SYSTEM AS GSS ON GSS.ResourceID = CPU.ResourceID INNER JOIN

                         vWorkstationStatus AS WSS ON GSS.ResourceID = WSS.ResourceID INNER JOIN

                         v_GS_OPERATING_SYSTEM ON GSS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN

                         v_CH_ClientSummary AS CS ON CPU.ResourceID = CS.ResourceID INNER JOIN

--v_GS_SERVICE AS GSY ON CPU.ResourceID = GSY.ResourceID INNEr JOIN

v_GS_COMPUTER_SYSTEM as GSCS on   CPU.ResourceID = GSCS.ResourceID INNER JOIN

v_GS_SYSTEM_ENCLOSURE as SED on CPU.ResourceID = SED.ResourceID INNER JOIN

 V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID INNER JOIN

 

 v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 

 where

  LDisk.DriveType0 =3

  and

  (GSS.Caption0 LIKE '%10%'

  or

  GSS.Caption0 LIKE '%7%'

  or

  GSS.Caption0 LIKE '%8%'

  or

  GSS.Caption0 LIKE '%work%')

  and

  GSCS.Model0 NOt LIKE 'Vmwa%'

GROUP BY  RAM.TotalPhysicalMemory0, CPU.SystemName0, CPU.Manufacturer0, CPU.Name0, CPU.NumberOfCores0, CPU.NumberOfLogicalProcessors0, GSS.Caption0, GSS.InstallDate0, WSS.LastPolicyRequest, 

                         v_GS_OPERATING_SYSTEM.InstallDate0, CS.ClientActiveStatus, GSCS.Model0, sed.SerialNumber0

Result:

Thank You!

Share