SCCM - Inventory SQL Query (Server Info)

The guy who manages CMDB wanted to update some CMDB fields, like Total Memory, CPU, Storage and OS Information on every registered server.

So, since SCCM is our configuration and also our "inventory" application, perhaps we could do some SQL-Query to save the day.

 SELECT DISTINCT   
    [RSYS].Netbios_Name0 AS [CI Name],  
      ROUND([OS].TotalVirtualMemorySize0 / CAST(1024 AS FLOAT),0) AS [Total RAM (GB)],  
      CASE WHEN ISNULL(SUM(CAST([LDISK].Size0 AS INT)),0) / 1024 > 0  
      THEN ISNULL(SUM(CAST([LDISK].Size0 AS INT)),0) / 1024  
      ELSE 1  
      END AS [Total Storage (GB)],  
      ROUND(SUM(CAST([LDISK].FreeSpace0 AS FLOAT) ) / CAST(1024 AS FLOAT),1) AS [Total Free Space (GB)],  
      [CPU].Name0 AS [CPU Info],  
      CASE WHEN [CPU].NumberOfCores0 IS NULL THEN '0' ELSE [CPU].NumberOfCores0 END AS Cores,  
      CASE WHEN [CPU].NumberOfLogicalProcessors0 IS NULL THEN '0' ELSE [CPU].NumberOfLogicalProcessors0 END AS [Logical Processors],  
      COUNT([CPU].ResourceID) AS [Number of CPUs],  
      [OS].Caption0 AS [OS]  
  FROM v_R_System RSYS  
      JOIN v_GS_PROCESSOR CPU on RSYS.ResourceID = CPU.ResourceID  
    JOIN v_GS_LOGICAL_DISK LDISK on RSYS.ResourceID = LDISK.ResourceID   
      JOIN v_GS_OPERATING_SYSTEM OS ON RSYS.ResourceID = OS.ResourceID  
 WHERE ISNULL([RSYS].Obsolete0, 0) <> 1  
 AND [LDISK].Size0 IS NOT NULL  
 AND ( [LDISK].DeviceID0 = 'C:' OR [LDISK].VolumeName0 = '/' )  
 GROUP BY [RSYS].Netbios_Name0, [OS].Caption0, [OS].TotalVirtualMemorySize0, [CPU].Name0, [LDISK].DeviceID0, [LDISK].VolumeName0, [LDISK].Size0,[CPU].NumberOfCores0,[CPU].NumberOfLogicalProcessors0  
 ORDER BY [RSYS].Netbios_Name0  

This will give you something like this :

See you soon!