How to Query the SQL SCCM Database

In this blog post, we will show you a beginner’s guide on how to Query the SQL SCCM Database. The goal is not to make you an SQL DBA or expert. This is a guide to help you start to find the information you need in the SCCM Database.

As you may know, all the information gathered by the SCCM agent is sent to an SQL Database. If you need to find a piece of specific information, you need to know in which SQL view to look for and most importantly the SQL language syntax.

After reading this post, you’ll be able to use SQL to query the SCCM database and if needed, build some nice reports.

Query SCCM SQL Database Requirements

• SQL Server Management Studio (SSMS)
• Access to the SCCM Database

Be careful when playing with SQL Server Management Studio. Doing Query make no harm but can have a negative impact on the server performance. Do not alter or delete anything.

SQL Query Structure

I will start by saying that this is a basic SQL example and that I’m not a SQL expert. Yes, you can build very complex solutions with SQL but we won’t go deep here. The goal is to show you how to begin. After, like any other programming language, it will be up to you to improve your skills by experimenting. I’ll give you some good resources at the end of this post if you’re interested to go deeper.

There are 5 main sections in queries: SELECT, FROM, WHERE, GROUP BY, ORDER BY and JOIN

SQL Main Statement

SQL Comparison Operators

You can add comparison operator to filter your results when you query SCCM SQL Database.

SQL Logical Operators

Here’s some logical operator that you can use in your queries. There’s more but this is the one I mostly use

Basic Example

Let’s see this in action and try our first query :

• Open SQL Server Management Studio
• Logon using an account that has rights
• On the left, expand Database and select your SCCM Database. The SCCM database is the one named CM_XXX
• Right-click the database and select New Query

• In the Query pane, let’s enter the following query

SELECT * 
FROM v_r_system

• In the results, see the column name. This is all available data in the V_R_SYSTEM view. The v_r_system view is the “default” view of the SCCM database. When you double click a device in the console, see the information there ? It is stored in the V_R_SYSTEM view. You’ll be using this view a lot.

• Let’s modify our query and compare the results. We will specify what to show in the SELECT clause and add the WHERE clause just to show the device having the desired name.

SELECT Name0,Operating_System_Name_and0
FROM v_r_system
WHERE Name0 = 'WIN10-01'

• Hit the Execute button or hit the F5 key
  • Don’t worry if the query fails on your side. This is probably because you don’t have a system named Win10-01. Change it to a device name existing on your server.
• Your results show at the bottom

• So what’s the difference with the first one? The query results is now showing only the Win10-01 system since it was specified in the WHERE clause. The results show only the Name and OS of the system because of the SELECT clause. Make sense? Experiment with different possibilities and syntax.

Here are some ideas :

SELECT * FROM v_r_system WHERE Name0 like 'XYZ%'
-- This will show all system with name beginning with XYZ

SELECT * FROM v_r_system WHERE Operating_System_Name_and0 like '%Workstation 10%'
-- Shows only Windows 10 workstations

SELECT Name0,Operating_System_Name_and0,Build01 FROM v_r_system WHERE Operating_System_Name_and0 like '%Workstation 10%'
-- Shows only Name and OS relevant info from Windows 10 workstations

Exploring SCCM DB in SSMS

This is a good start, you can now query the V_R_SYSTEM view but it does not contain much relevant information. What if you need to query for hardware information or any other information? How do you find what you need?

There are no easy answers here. It will come with time as you explore the available views. Microsoft provides good documentation about the available view but still can be challenging to find what you’re looking for.

• Expand the View node in SQL MS

• As you can see there’s a lot. One trick you can use is to use filters to show views for a specific topic. Let’s say you want to have information about Collections. You can filter view name with “Collection” only

• Right-Click the Views node
• Select Filter / Filter Settings

• In the Filter setting window enter “Collection” in the name and click OK

• Only views with Collection are shown

• Using the Microsoft documentation you can narrow down which view can be relevant for you. If you want to quickly see the information contained in a particular view, just right-click on it and click on Select Top 1000 Rows

• In the result pane, all our collections data is displayed

• You can also use these 2 queries which display information about the Database schema which could be helpful to see in which view the information resides.

SELECT ViewName, Type from v_SchemaViews order by 2
SELECT * from v_GroupMap

SQL Database Naming Convention

You may also wonder why some view has a “V_”, “V_GS” or “V_HS” in their name. Here’s the meaning.

Most used SQL views

Query more than 1 view in the same query

Ok, this is where the fun starts. Let’s say you want to build a query that needs information about PC Name, CPU, Model and OS information.

You find the relevant information in 4 different views :

• v_R_SYSTEM
• v_GS_OPERATING_SYSTEM
• v_GS_COMPUTER_SYSTEM
• v_GS_PROCESSOR

How do you query 4 view? Using a JOIN clause. The Join clause is used to combine rows from two or more views, based on a related column between them. I won’t describe all the differences between all the JOIN (LEFT, INNER, OUTER…). There are already many good resources for that.

I still use the SELECT and FROM and at the end of the query we use the JOIN clause. I’m basically telling my query that the relationship between the two views is the ResourceID column. In the vast majority of your first queries, the ResourceID can be used to link 2 views.

SELECT
		SYS.Name0 AS Name,
		OS.Caption0 AS OS,
		CS.Manufacturer0 AS Manufacturer,
		CS.Model0 AS Model,
		CPU.Name0 AS CPUName
	
FROM
		v_R_System AS SYS

JOIN    v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID
JOIN	v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = SYS.ResourceID
JOIN	v_GS_PROCESSOR CPU ON CPU.ResourceID = SYS.ResourceID

Data Display, Order and Group

On any query, you can display it the way you want it. The placement after the SELECT decide the column position.

Taking the above example :

• Name is #1
• OS is #2
• Manufacturer is #3
• Model is #4
• CPUName is #5

Let’s say I want to change the column to display the CPU first, I would just need to move the CPU.Name0 to the first place after SELECT. The rest of the SQL query doesn’t change.

SELECT
		CPU.Name0 AS CPUName
		SYS.Name0 AS Name,
		OS.Caption0 AS OS,
		CS.Manufacturer0 AS Manufacturer,
		CS.Model0 AS Model,


FROM
		v_R_System AS SYS

JOIN    v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID
JOIN	v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = SYS.ResourceID
JOIN	v_GS_PROCESSOR CPU ON CPU.ResourceID = SYS.ResourceID

If you want to order data using alphabetic order or numerical order, you need to use the ORDER BY clause. This will sort your data.

When using ORDER BY, you can use the column name or number to designate the desired column. Using the same example, both ORDER BY syntax would return the same results.

SELECT
		CPU.Name0 AS CPUName,
		SYS.Name0 AS Name,
		OS.Caption0 AS OS,
		CS.Manufacturer0 AS Manufacturer,
		CS.Model0 AS Model


FROM
		v_R_System AS SYS

JOIN    v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID
JOIN	v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = SYS.ResourceID
JOIN	v_GS_PROCESSOR CPU ON CPU.ResourceID = SYS.ResourceID

--ORDER BY 1
ORDER BY CPU.Name0

You can also use the GROUP BY statement to groups rows that have the same values into summary rows.

Query SCCM SQL Database Tips

• To increase performance, do not overuse the * operator in your SELECT statements. Instead, use column names as much as possible.
• You can comment any line using the hyphen character “–“
• You can comment a big section using /* and */
• Hit the F5 key to quickly Execute a query
• SQL clauses aren’t case sensitive, but the general practice is to write them in caps

SQL Resources and Links

• SQL Tutorial (w3schools.com)
• Visual Representation of SQL Joins – CodeProject
• SCCM – SQL Queries – Lab Core | the lab of MrNetTek (eddiejackson.net)
• Configuration Manager Report (Most Amazing Hardware Inventory SQL Report) | Ammar Hasayen (ahasayen.com)
• Amazon.com: System Center Configuration Manager Reporting Unleashed eBook: Jones, Garth, Toll, Dan, Meyler, Kerrie: Books

Once you’ve built a good comprehension of building SQL queries, you can read our blog post on How to create great-looking SCCM Reports.