Part 1 – Building a SCCM Query
Part 2 – Fleshing out a machine information SQL Query
Part 3 – Adding SCCM SQL Queries to PowerBi
Hi, in this blog post I will attempt to walk you through building up a basic SQL query to query your SCCM DB. I’ll talk you through a select few techniques and I may expand on this as I too learn more. I’m not a DBA by nature, however with a few tricks you’ll soon be on your way to generating good queries & getting the results you need a lot quicker.
Requirements:
1. SQL Server Management Studio (SSMS)
2. Access to the SCCM DB
Structure of a query:
There are many websites & books that are perhaps better suited to training you on structuring a SQL query and trust me on this, the way you start to commit it to memory is just to use it daily if you can, similar to PowerShell (which I also love). I recently started getting into creating SQL queries and querying the DB directly in order to get information quicker than navigating through the console and it works too. Sure the output isn’t always as pretty as you’d like it to be however once you get the idea, the possibilities are endless.
Essentially there are 5 main sections on your query SELECT, FROM, WHERE, GROUP BY and ORDER BY (In that order – the last three are optional)
Format your code (BASIC):

SELECT Properties to select from the view or table (see below)
FROM Select the table or the view that you want to use for the query
WHERE Conditions for the query {OPTIONAL}
GROUP BY Group results {OPTIONAL}
ORDER BY Order results {OPTIONAL}

Example
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
ResourceID,
Name0,
Full_Domain_Name0
FROM
v_R_System
WHERE
Full_Domain_Name0 <> ‘NULL’
[/css]
SQL00
Results…
SQL0
Ok, so this query ran successfully I got quite a few results and in this particular query I have omitted any device in the SCCM database that hasn’t got a domain name (shown as NULL if you remove the WHERE statement)
Points to note from above:

  • Notice I have my code sections written in CAPITAL letters
  • Notice the comma at the end of two lines within the SELECT statement to denote more options
  • Notice the ‘<>’ on the WHERE statement which is the operator (this one essentially means NOT so; Property NOT EQUAL ‘Condition’
  • Notice the single quotations to denote the ‘Condition’ (important)
  • Notice the condition in single quotation marks is turned to red text, the sections are blue text and the code is black.

“…But how do you know what columns are stored in what tables? The SCCM DB is vast
The answer to this is to simply explore, I’ll explain that next. And you are right, it is HUGE, but if you use it, it’ll start to become familiar to you, bit by bit. Practice with it and it will start to become familiar to you pretty quickly.
In a nutshell, the SCCM database (like every other SQL Database) will contain tables, the tables contain rows, columns and data (like every table you’ve ever seen in your life). SQL Supports ‘Views’ where you can pull data from various tables and present it in a ‘View’. Views are particularly useful and the majority of things you’ll access in your queries will probably be views. You can create your own views if you want, once you start to become familiar with the location of data and the bits you are interested in.
Exploring SCCM DB in SSMS
Often in my line of work someone will to you and ask “Can SCCM tell me X about computers?” Where X is some condition they need for planning a project or an action to fix something. Ones I’ve had in the past “How many HP laptops do we have at a particular site?” or “How many HP Elitebook 840 G3’s do we have on a particular domain”. When you start to think about the query in a logical fashion it can soon be broken down into sections and then you can link tables together to get all the information.
Let’s take the second (and easiest) query “How many HP Elitebook 840 G3’s do we have on a particular domain?” and work with that through this example.
So firstly, we’ll need to find information about system. I know from experience that we need to look at the view “v_GS_COMPUTER_SYSTEM” It makes sense, right? It’s a system so information about it will be in the a system view (or table). In order to find this I might search in SSMS for a particular key word. So I can select either a table or a view to search, in this example I’m searching the views. Under my SCCM DB I would right click ‘Views’ and the select ‘Filter’ and ‘Filter Settings’ (You can filter tables in the same way)
SQL1
In the dialog box that appears I might chose to search for the keyword “system”, like so…
SQL2
If I now look at ‘Views’ (we can see here it’s now filtered – Watch out for this it might catch you out if you don’t remove your filters!)
SQL3
I will get a long list of tables containing the word “system” from this filter if I expand Views, luckily there is a naming convention you can follow to be able to determine which ones to choose from.
Naming Convention

  • Views that start with ‘v_R_’ or ‘v_RA_’ contain Discovery Class Views, so anything discovered by SCCM discovery set up within the console will be populated into tables, and these views show that data.

,li>Views that start with ‘v_GS_’ show us Current Hardware Inventory Class views

  • Views that start with ‘v_HS_’ show us Historical Hardware Inventory Class views

 
There are some exceptions to the hard set rule, but not too many. I intend to start a follow up blog post that will hopefully contain some information about the different tables and views and what information is contained within them. I will continue to update that as I continue to use SCCM and I may open that up to suggestions / comments moving forward as I’d like a one-stop-shop for exploring the data within the DB. This type of resource would have helped me loads.
Back to our particular problem, now that we’ve used filters and followed the naming convention to logically select the table we want to look at we can now examine it to see what data it contains. In this instance the first thig I so is select all data in a “show me what you got!” query, that looks like this:
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
*
FROM
v_GS_COMPUTER_SYSTEM
[/css]
SQL4
(37216 results)
The results from this particular query show me too many columns to snap however I know that I’m interested in however looking at them and knowing what I’m interested in, I select the columns Name0, Domain0, Manufacturer0 and Model0 which should give us what we need.
Lets start by adjusting our query to only select those three columns, that will look like this:
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
[/css]
SQL5
(37125 results)
Now you will see that we’ve selected only those columns, next lets narrow by HP. I can see even by the above query that I have two results to be wary of here, one is “Hewlett-Packard’ and one is “HP”, watch out for this sort of stuff in your own environment. In order to show both of these I will edit the query like this;
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘Hewlett-Packard’ or Manufacturer0 = ‘HP’)
[/css]
SQL6
(27752 results) – getting smaller!
OK, now we have brought in a WHERE condition. The above statement should read and make logical sense to you. Don’t forget there are lots of operators you could use to retrieve specific data. I have placed this in brackets to class the line as one statement, and to give me and option to add an additional statement (you’ll see why in the next few sections).
Next I’m going to narrow by model. There are a lot to pick though so at first I try this:
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘Hewlett-Packard’ or Manufacturer0 = ‘HP’)
and
Model0 like ‘%Elitebook%’
[/css]
SQL7
(16285 results)
This time I added an additional query to the WHERE statement to look for text that contains the word “Elitebook”. The % symbol represents the wildcard so SQL will read that as ‘anything can go here’… from these results I can see that the actual text I need is indeed “HP EliteBook 840 G3” I also see that of the two manufacturer types, the one I need is “HP” and not “Hewlett-Packard” which seems to be related to older models so I’ll edit the query like this…
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘HP’ and Model0 like ‘HP EliteBook 840 G3’)
[/css]
SQL8
(7806 results)
Now we’re getting somewhere…. The final step in this would be to add the domain on the query….. This makes the final statement like this:
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘HP’ and Model0 like ‘HP EliteBook 840 G3’)
and
(Domain0 = ‘YOUR-DOMAIN-HERE’)
[/css]
SQL9
(610 results when I placed in my correct domain)
OK, so now I have a list of 600+ machines, and the details I want so lets look at making this a little neater. Firstly, lets sort by machine name.. Like this…
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0,
Domain0,
Manufacturer0,
Model0
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘HP’ and Model0 like ‘HP EliteBook 840 G3’)
and
(Domain0 = ‘YOUR-DOMAIN-HERE’)
ORDER BY
Name0
[/css]
SQL10
Pretty straight forward right? We added a ‘ORDER BY’ section and selected the column to sort by. Next lets change the column headers because Name0, Domain0, Manufacturer0 and Model0 are OK, but they can be neater also.
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
Name0 as ‘Computer Name’,
Domain0 as ‘Domain’,
Manufacturer0 as ‘Make’,
Model0 as ‘Model’
FROM
v_GS_COMPUTER_SYSTEM
WHERE
(Manufacturer0 = ‘HP’ and Model0 like ‘HP EliteBook 840 G3’)
and
(Domain0 = ‘YOUR-DOMAIN-HERE’)
ORDER BY
‘Computer Name’
[/css]
SQL11
Ok, this time notice we are selecting columns as ‘something’ and also notice I changed the ORDER BY at the bottom to reflect that. This gives the results neater…
SQL12
This can now be copied and pasted out, much neater.
I’m aware that, yes, you can use SELECT COUNT to bring in a total count of machines based on parameters, so that you can get results (probably quicker) rather than relying on looking at the results output so see how many rows are returned, and that’s 100% correct, there are many ways to skin a cat. If you want to learn more about SQL then there are lots of resources available to you. My aim with this post is not only to get you to think about SQL code for the query, but also to think about exploring the SCCM database.
Let’s see if we can bring in some other data from another table to flesh this out. This time we’ll link another view in, based on relational data (important) and pull columns from two separate tables. Let’s start by adding something to this… what shall we add? How about we add the Operating System too? The view “v_GS_COMPUTER_SYSTEM” doesn’t contain that information.
I’m going to go back to the Object explorer in SQL, clear the filter on the views and try to find a view with ‘Operating System’ in the name.
SQL13
(This didn’t work, use “operating_system” – with an underscore, you’re welcome!)
SQL14
Here are my results, I know from the logic above that “v_HS…” is historical data, “v_GS…” is current data so I’m going to use “v_GS_OPERATING_SYSTEM”. Let me look at that table and see what columns are in there:
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
*
FROM
v_GS_OPERATING_SYSTEM
[/css]
SQL15
From the results I can see that there’s a row for every device on my estate and that there’s a column called “ResourceID” which also exists in the “v_GS_COMPUTER_SYSTEM” table so this is a relationship I can use. I will need to link the views on this property and bring in additional data that way. This is how I achieved that.
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
CS.Name0 as ‘Computer Name’,
CS.Domain0 as ‘Domain’,
CS.Manufacturer0 as ‘Make’,
CS.Model0 as ‘Model’,
OS.Caption0 as ‘Operating System’
FROM
v_GS_COMPUTER_SYSTEM as CS
INNER JOIN v_GS_OPERATING_SYSTEM as OS on CS.ResourceID = OS.ResourceID
WHERE
(CS.Manufacturer0 = ‘HP’ and CS.Model0 like ‘HP EliteBook 840 G3’)
and
(CS.Domain0 = ‘YOUR-DOMAIN-HERE’)
ORDER BY
‘Computer Name’
[/css]
SQL16
Things to notice from the code:
1. Under the FROM statement I have done an INNER JOIN to join the two tables. I also used “as” to shorten the name of the two tables to “OS” (representing Operating System) and “CS” (representing Computer System)
2. Notice that in the SELECT section I’ve had to prefix the values with the table they came from, so now I’m pulling properties from two tables and outputting to one result.
In this one I also asked for the OS Architecture to be brought back in so that we can tell if its 32 or 64 bit.
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
SELECT
CS.Name0 as ‘Computer Name’,
CS.Domain0 as ‘Domain’,
CS.Manufacturer0 as ‘Make’,
CS.Model0 as ‘Model’,
OS.Caption0 as ‘Operating System’,
OS.OSArchitecture0 as ‘Architecture’
FROM
v_GS_COMPUTER_SYSTEM as CS
INNER JOIN v_GS_OPERATING_SYSTEM as OS on CS.ResourceID = OS.ResourceID
WHERE
(CS.Manufacturer0 = ‘HP’ and CS.Model0 like ‘HP EliteBook 840 G3’)
and
(CS.Domain0 = ‘YOUR-DOMAIN-HERE’)
ORDER BY
‘Computer Name’
[/css]
SQL17
I hope by now you can see how to build up a knowledge base of tables or views to use for your queries (by using filters and searching for logical names) and in a basic manner, how to structure your code so its readable. In addition, how to join tables together in a basic manner. You can read more about the different types of joins here.
I can HIGHLY recommend a book called “System Centre Configuration Manager Reporting Unleashed, by Garth Jones, Dan Toll & Kerrie Meyler” If you want to delve further. I’m going to try and document all the tables I can find & what data could be used for I’l try to keep adding to this and link into this post to assist with any of your queries.
I hope that you enjoyed reading this
Jonathan

4 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *