Posts

Part 1 – Building a SCCM Query
Part 2 – Fleshing out a machine information SQL Query
Part 3 – Adding SCCM SQL Queries to PowerBi
 
Now that I have put together the basics of a SQL report I wanted to demo how to piece together a machine audit report that could be much more useful than some of the standard reports in SCCM.
When we think about machines, there are certain elements that I want to know that might be on different tables within the SCCM SQL data base and as such, we’re probably going to need to join tables together to bring information in to one place.
As a basic starting block…. Lets look at the view v_GS_COMPUTER_SYSTEM  to see what goodness I might wanna pick out.
[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 CS
[/css]

• Notice I added ‘CS’ at the end of the FROM statement. This is just a short name so I don’t have to type the full name of the table/view in future. It could be written as “v_GS_COMPUTER_SYSTEM as CS” if you wanted to be more human-readable
From the results of this query I can see that my relational data column is going to be “ResourceID”, that’s unique to each device, then from the headings I want to take Domain0, Manufacturer0, Model0, Name0 (of course!), and finally SystemType0. So lets pull just those columns…
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
Select
CS.Domain0
,CS.Manufacturer0
,CS.Model0
,CS.Name0
,CS.SystemType0
FROM
v_GS_COMPUTER_SYSTEM CS
[/css]

• Notice I put my comma at the beginning of the line, this is something I do, only because it makes commenting out a line with “–” easier, you don’t have to do this.
Let’s neaten up those columns headers before we go any further…
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
Select
CS.Domain0 as ‘Domain’
,CS.Manufacturer0 as ‘Make’
,CS.Model0 as ‘Model’
,CS.Name0 as ‘Name’
,CS.SystemType0 as ‘Architecture’
FROM
v_GS_COMPUTER_SYSTEM as CS
[/css]


• Notice the “as ‘Something else’ ” statement to rename the columns.
Next, I can see from the results that we have both “HP” and “Hewlett-Packard” in the results. I also don’t like the output from SystemType0, I want to make the simply “x86” or “x64″. In this instance I’m going to use a CASE statement. This type of statement will present data differently so I can say, for example, if text is X then show as Y. Like so…
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
Select
CS.Domain0 as ‘Domain’
,’Make’ = CASE
WHEN CS.Manufacturer0 = ‘Hewlett-Packard’ THEN ‘HP’
ELSE CS.Manufacturer0
END
,CS.Model0 as ‘Model’
,CS.Name0 as ‘Name’
,’Architecture’ = CASE
WHEN CS.SystemType0 = ‘x64-based PC’ THEN ‘x64’
WHEN CS.SystemType0 = ‘X86-based PC’ THEN ‘x86’
ELSE CS.SystemType0
END
FROM
v_GS_COMPUTER_SYSTEM as CS
[/css]


• Notice how the CASE statements are structured above. We’re replacing text with certain situations and then adding a ‘catch-all’ clause ELSE display x
• Notice how the results have changed. We have what we want now. Great stuff, lets start bringing in other data. Let’s bring in Operating System & version
I can see that from v_GS_OPERATING_SYSTEM view I will need the columns Caption0 and I can either use BuildNumber0 or Version0, I’ll go for BuildNumber0
[css autolinks=”false” collapse=”true” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true”]
Select
CS.Domain0 as ‘Domain’
,’Make’ = CASE
WHEN CS.Manufacturer0 = ‘Hewlett-Packard’ THEN ‘HP’
ELSE CS.Manufacturer0
END
,CS.Model0 as ‘Model’
,CS.Name0 as ‘Name’
,’Architecture’ = CASE
WHEN CS.SystemType0 = ‘x64-based PC’ THEN ‘x64’
WHEN CS.SystemType0 = ‘X86-based PC’ THEN ‘x86’
ELSE CS.SystemType0
END
,OS.Caption0 as ‘Operating System’
,’OS Build Number’ = CASE
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10240’) THEN ‘1507’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10586’) THEN ‘1511’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘14393’) THEN ‘1607’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘15063’) THEN ‘1703’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘16299’) THEN ‘1709’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17134’) THEN ‘1803’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17763’) THEN ‘1809’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18362’) THEN ‘1903’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18912’) THEN ‘1909’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7601’) THEN ‘With SP1’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7600’) THEN ‘Without SP1’
ELSE OS.BuildNumber0
END
FROM
v_GS_COMPUTER_SYSTEM as CS
INNER JOIN v_GS_OPERATING_SYSTEM OS on OS.ResourceID=CS.ResourceID
[/css]

• Notice the INNER JOIN statement, linking the two views together on ResourceID (which is present in both tables.)
• Notice that I have used a CASE statement for the OS Build Number, this is mainly to differentiate between different Windows 10 versions on my estate but I also added logic to tell me which Windows 7 devices don’t even have SP1 (shocker!)
NOTE: The code examples are getting a little long now so from here on in I will use collapsible code sections instead of images
I’ve actually spotted two other columns from the Operating System view that I want to bring in, there are LastBootUpTime0 and InstallDate0. I firstly want to change the name of these columns (to remove the Zero) and also to convert the format. I want LastRebootTime0 to show the last time it was rebooted, this might be handy if you’re tracing patching issues, for example, and I want InstallDate0 to show me when the OS was put on, this might be handy and it might indicate the age of the machine i.e. when it was last re-imaged.
[css autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” highlight=”29-30″]
Select
CS.Domain0 as ‘Domain’
,’Make’ = CASE
WHEN CS.Manufacturer0 = ‘Hewlett-Packard’ THEN ‘HP’
ELSE CS.Manufacturer0
END
,CS.Model0 as ‘Model’
,CS.Name0 as ‘Name’
,’Architecture’ = CASE
WHEN CS.SystemType0 = ‘x64-based PC’ THEN ‘x64’
WHEN CS.SystemType0 = ‘X86-based PC’ THEN ‘x86’
ELSE CS.SystemType0
END
,OS.Caption0 as ‘Operating System’
,’OS Build Number’ = CASE
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10240’) THEN ‘1507’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10586’) THEN ‘1511’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘14393’) THEN ‘1607’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘15063’) THEN ‘1703’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘16299’) THEN ‘1709’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17134’) THEN ‘1803’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17763’) THEN ‘1809’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18362’) THEN ‘1903’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18912’) THEN ‘1909’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7601’) THEN ‘With SP1’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7600’) THEN ‘Without SP1′
ELSE OS.BuildNumber0
END
,’Last Reboot’ = CONVERT(VARCHAR, OS.LastBootUpTime0, 103)
,’OS Install Date’ = CONVERT(VARCHAR, OS.InstallDate0, 103)
FROM
v_GS_COMPUTER_SYSTEM as CS
INNER JOIN v_GS_OPERATING_SYSTEM OS on OS.ResourceID=CS.ResourceID
[/css]
I’ll let you do your own research on CONVERT statements, there a few options you can chose from and many websites to help you, it’s not massively difficult. Try to get yourself a cheat-sheet together of the options you can use.
CURVE BALL!! I use ConfigMgr ClientHealth by Anders Rodland, this script has a table which I’m going to use to pull in other details. I’m going to pull in PowerShell version number and SCCM Client version number.
[css autolinks=”false” collapse=”false” firstline=”1″ gutter=”true” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” highlight=”31-32, 37″]
SELECT
CS.Domain0 as ‘Domain’
,’Make’ = CASE
WHEN CS.Manufacturer0 = ‘Hewlett-Packard’ THEN ‘HP’
ELSE CS.Manufacturer0
END
,CS.Model0 as ‘Model’
,CS.Name0 as ‘Name’
,’Architecture’ = CASE
WHEN CS.SystemType0 = ‘x64-based PC’ THEN ‘x64’
WHEN CS.SystemType0 = ‘X86-based PC’ THEN ‘x86’
ELSE CS.SystemType0
END
,OS.Caption0 as ‘Operating System’
,’OS Build Number’ = CASE
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10240’) THEN ‘1507’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘10586’) THEN ‘1511’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘14393’) THEN ‘1607’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘15063’) THEN ‘1703’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘16299’) THEN ‘1709’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17134’) THEN ‘1803’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘17763’) THEN ‘1809’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18362’) THEN ‘1903’
WHEN (OS.Caption0 = ‘Microsoft Windows 10 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 10 Pro’) and (OS.BuildNumber0 = ‘18912’) THEN ‘1909’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7601’) THEN ‘With SP1’
WHEN (OS.Caption0 = ‘Microsoft Windows 7 Enterprise’ or OS.Caption0 = ‘Microsoft Windows 7 Professional’) and (OS.BuildNumber0 = ‘7600’) THEN ‘Without SP1′
ELSE OS.BuildNumber0
END
,’Last Reboot’ = CONVERT(VARCHAR, OS.LastBootUpTime0, 103)
,’OS Install Date’ = CONVERT(VARCHAR, OS.InstallDate0, 103)
,’PowerShell Version’ = CH.PSVersion
,’SCCM Client Version’ = CH.ClientVersion
FROM
v_GS_COMPUTER_SYSTEM as CS
INNER JOIN v_GS_OPERATING_SYSTEM OS on OS.ResourceID=CS.ResourceID
INNER JOIN [ClientHealth].dbo.Clients as CH on CS.Name0=CH.Hostname
[/css]
The table create by the ClientHealth script also contains similar data to what I have pulled in using the INNER JOIN, such as make, model, operating system etc. So in theory I could use this one and neaten this up, without using joins. The current SQL query we have created takes 3 seconds to run, however if I was querying just one table, it may shave time even off that.
The idea behind this particular post is to hopefully get you started with building up SQL queries and searching the SCCM database for specific data. The queries inside SQL run a LOT faster that the reports built in, granted they’re not as pretty but in my next post I’ll drop this query into PowerBi and show you how you can add some beauty to the data you’ve collected.
Thanks for reading,
Jonathan