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
|SELECT||What to show from the view or table. The wildcard character is ‘*’|
|FROM||The table or the view to query|
|JOIN||Used to combine rows from two or more tables|
|WHERE||Conditions for the query – OPTIONAL – The wildcard character is ‘%’|
|GROUP BY||Group results – OPTIONAL|
|ORDER BY||Order results – OPTIONAL|
SQL Comparison Operators
You can add comparison operator to filter your results when you query SCCM SQL Database.
|=||Checks if the values of two operands are equal or not, if yes then condition becomes true.||(a = b) is not true.|
|!=||Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.||(a != b) is true.|
|<>||Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.||(a <> b) is true.|
|>||Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.||(a > b) is not true.|
|<||Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.||(a < b) is true.|
|>=||Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.||(a >= b) is not true.|
|<=||Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.||(a <= b) is true.|
|!<||Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.||(a !< b) is false.|
|!>||Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.||(a !> b) is true.|
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
|AND||The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.|
|ANY||The ANY operator is used to compare a value to any applicable value in the list as per the condition.|
|IN||The IN operator is used to compare a value to a list of literal values that have been specified.|
|LIKE||The LIKE operator is used to compare a value to similar values using wildcard operators.|
|NOT||The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.|
|OR||The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.|
|IS NULL||The NULL operator is used to compare a value with a NULL value.|
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
- Hit the Execute button or hit the F5 key
- Your results show at the bottom
- 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.
|v_R_ or v_RA||Discovery Class Views|
|v_GS||Hardware Inventory Class views|
|v_HS||Historical Hardware Inventory Class views|
|_RES_COL||Specific Collection members information|
Most used SQL views
|v_R_System||Default System Discovery Views|
|v_R_User||Default Users Discovery Views|
|v_GS_ADD_REMOVE_PROGRAMS||Software installed on a computer|
|v_GS_DISK||Specific Collection members information|
|v_FullCollectionMembership||Collection Membership Information|
|v_DistributionPoints||Distribution Point Info|
|v_Site||Site version information|
|v_UpdateDeploymentSummary||Software Update information|
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 :
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
SELECTstatements. 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.