System Center Configuration Manager 2012 Database Queries

System Center Configuration Manager 2012 have a lot of reports out of the box for different purposes, but for people who want to build their own reports to combine and consolidate information, the best way is knowing the relationship between tables and views direct from the database.

Since Microsoft decided to use SQL Server like the platform to store configurations from more of their products, they gave us the dbo.vCollectionspossibility to extend the results using Reporting Services or just querying the Database. In this article you’ll see some basic and important queries, just to open the door to the  all possibilities.

Accessing Database

Basic Catalogs:

The relationship between both views are with CollectionID column

Almost in all queries you can reduce the result set just adding the condition:

AND vCollectionMembers.Name = ''

Equipment inventory:

SELECT * FROM dbo.vCollectionMembers CM WHERE CM.CollectionID = 2  -- It's the ID for Collection "All System"

Software Inventory:

SELECT CM.name,

       SP.ProductName,

       SF.FileName,

       SF.FileDescription,

       SF.FileSize,

       SF.FileVersion,

       SFP.FilePath

FROM  dbo.SoftwareInventory             SI

INNER JOIN    dbo.vCollectionMembers     CM     ON CM.MachineID      = SI.ClientId

INNER JOIN    dbo.v_SoftwareProduct      SP     ON SP.ProductId      = SI.ProductId

INNER JOIN    dbo.v_SoftwareFile         SF     ON SF.FileID         = SI.FileId

AND SF.ProductID     = SI.ProductId

INNER JOIN    dbo.SoftwareFilePath       SFP    ON SFP.FilePathId    = SI.FilePathId

WHERE  CM.CollectionID      = 2

Historic Installed Software:

SELECT ARP.*

FROM   dbo.v_GS_ADD_REMOVE_PROGRAMS      ARP

INNER JOIN dbo.vCollectionMembers        CM     ON CM.MachineID = ARP.ResourceID

WHERE  CM.CollectionID      = 2

ORDER BY InstallDate0 DESC

Memory:

SELECT SUM(M.Capacity0)/1024 AS Mem_GB,

       CM.Name

FROM   dbo.v_GS_PHYSICAL_MEMORY   M

INNERJ OIN dbo.vCollectionMembers CM ON CM.MachineID   = M.ResourceID

WHERE  CM.CollectionID = 2

GROUP BY CM.Name

Logical Disk:

SELECT CM.Name,

       D.Description0,

       D.FileSystem0,

       D.name0,

       D.Size0/1024 AS GB,

       D.SystemName0

FROM   dbo.v_GS_LOGICAL_DISK D

INNER JOIN dbo.vCollectionMembers CM ON CM.MachineID   = D.ResourceID

WHERE  CM.CollectionID = 2

Let me give you some extra information, if you are looking for Operating System then you need to know the internal name how SCCM recognize the OS, then you can query easily. Here are some of the most important OS by Microsoft:

Hostname with Operating System

SELECT CM.Name,

       CASE

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 6.1',

                                         'Microsoft Windows NT Workstation 6.1 (Embedded)',

                                         'Windows Embedded Standard 6.1',

                                         'Microsoft Windows NT Workstation 6.1 (Tablet Edition)' )       THEN 'Windows 7'

       WHEN Operating_System_Name_and0 IN('Windows 8 Pro 6.2',

                                         'Windows 8 Enterprise 6.2',

                                         'Microsoft Windows NT Workstation 6.2')                       THEN 'Windows 8'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 6.2 (Tablet Edition)')       THEN 'Windows 8 Tablet'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 6.3',

                                         'Windows 8.1 Pro Preview 6.3')                  THEN 'Windows 8.1'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Server 5.0',

                                         'Microsoft Windows NT Advanced Server 5.0' )    THEN 'Windows Server 2000 Adv'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Server 5.2',

                                         'Microsoft Windows NT Advanced Server 5.2' )    THEN 'Windows Server 2003, R2, Adv'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Server 6.0',

                                         'Microsoft Windows NT Advanced Server 6.0' )    THEN 'Windows Server 2008'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Server 6.1',

                                         'Microsoft Windows NT Advanced Server 6.1' )    THEN 'Windows Server 2008 R2'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Server 6.2',

                                         'Microsoft Windows NT Advanced Server 6.2' )    THEN 'Windows Server 2012'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 6.0')       THEN 'Windows Vista'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 5.1',

                                         'Microsoft Windows NT Workstation 5.2' )        THEN 'Windows XP'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 5.1 (Tablet Edition)') THEN 'Windows XP Tablet Edition'

       WHEN Operating_System_Name_and0 IN('Microsoft Windows NT Workstation 5.0')       THEN 'Windows 2000'

       END

FROM   dbo.v_R_System              S

INNER JOIN dbo.vCollectionMembers CM ON CM.MachineID   = S.ResourceID

WHERE  CM.CollectionID = 2

It’s just the beginning, please check all the columns available in each table or view and extend your reports.

Thanks.