How to Query the SQL SCCM Database

Benoit LecoursSCCM2 Comments

4.2
(5)

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

SELECTWhat to show from the view or table. The wildcard character is ‘*’
FROMThe table or the view to query
JOINUsed to combine rows from two or more tables
WHEREConditions for the query – OPTIONAL – The wildcard character is ‘%’
GROUP BYGroup results – OPTIONAL
ORDER BYOrder results – OPTIONAL

SQL Comparison Operators

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

OperatorDescriptionExample
=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

OperatorDescription
ANDThe AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.
ANYThe ANY operator is used to compare a value to any applicable value in the list as per the condition.
INThe IN operator is used to compare a value to a list of literal values that have been specified.
LIKEThe LIKE operator is used to compare a value to similar values using wildcard operators.
NOTThe 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.
ORThe OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.
IS NULLThe NULL operator is used to compare a value with a NULL value.

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
Query SCCM SQL Database
  • 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
Query SCCM SQL Database
  • 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.
Query SCCM SQL Database
  • 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
Query SCCM SQL Database
  • 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
Query SCCM SQL Database
  • 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
Query SCCM SQL Database
  • 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
Query SCCM SQL Database

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_RADiscovery Class Views
v_GSHardware Inventory Class views
v_HSHistorical Hardware Inventory Class views
v_CHClient Health
_RES_COLSpecific Collection members information
v_All others

Most used SQL views

v_R_SystemDefault System Discovery Views
v_R_UserDefault Users Discovery Views
v_GS_PROCESSORCPU Information
v_GS_ADD_REMOVE_PROGRAMSSoftware installed on a computer
v_GS_COMPUTER_SYSTEMComputer Model
v_GS_VIDEO_CONTROLLERClient Health
v_GS_DISKSpecific Collection members information
v_GS_OPERATING_SYSTEMAll others
v_FullCollectionMembershipCollection Membership Information
v_CollectionCollections information
v_DistributionPointsDistribution Point Info
v_RA_System_IPAddressesIP Addresses
v_SiteSite version information
v_UpdateDeploymentSummarySoftware 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 :

  • 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
Query SCCM SQL Database

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

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.

How useful was this post?

Click on a star to rate it!

Average rating 4.2 / 5. Vote count: 5

No votes so far! Be the first to rate this post.