Some Dimented Dimensions of ConfigMgr Data

So, over the past few weeks, like most of the rest of the incredible people on our team, I’ve been working multiple projects for different customers around SCCM and other things. My biggest struggle is not only keeping names and details aligned with whomever I’m speaking with online, but keeping each of their respective constraints and obstacles aligned as well.

Disclaimer: This article just might almost possibly kind of make sense, so please: read to the end, before you print it out, ball it up, and light it on fire. In all seriousness, this is just ONE way to approach one challenge. I’m sure you may have a better way.

In my case, for example, in customer environment “A”, the SCCM admins have full access to the SQL instance which underpins their site, while customer environment “B” has to rely on DBA generosity to get anything, unless it’s available from the SSRS reports, or SCCM Queries.

For customer A (the first one), my tried-and-true cheezy-wheezy SQL-via-PowerShell pipeline happy meal kit works great. This is basically where I drop a library of deep-fried .sql files with a small fry, sugary drink and kids toy all in a nice box, then shoot those through a dbatools PowerShell paintball gun to get a pipeline-capable data pump, which they can do things with (see stupid diagram number 1 below).

Stupid Diagram 1

For customer B (the latter), this happy meal doesn’t work because it’s not gluten-free and doesn’t have the vegan solar-powered kids toy in the box. So that requires a somewhat less robust approach: WQL. SQL (T-SQL actually) is much much faster to process (in general) than squeezing a data cow through a WMI provider wormhole, but you work with what you have.

Stupid Diagram 2

So, to pull the same logical dataset “join” with WQL that the SQL option allows, requires some really arcane backflips with two wooden legs and a pogo stick. Why? I don’t know, but it seems like they didn’t have lunch with the SQL team and instead got high on gasoline fumes or something.

Maybe I’m being short-sighted, but to me, WQL is the JRE of SQL. Actually, that’s not fair, because WQL is at least useful. I know you can make WQL do joins by smoking MOF files in a kiln, but I’m too lazy for that. But I digress.

Anyhow, why do I prefer SQL over WQL for PowerShell automation? Mainly because SQL is easier to do joins, and it provides better performance (CPU, memory, etc.) on average than the WMI provider. It also requires more of the data shaping to be done by PowerShell after the source data is returned, and PowerShell isn’t as fast at performing dataset operations as a relational database. The more shaping you can do within SQL the better.

PowerShell SQL Query – Example 1

#forest gump example...

$sys = Invoke-DbaQuery -SqlInstance "CM01" -Database "CM_P01" -Query "select distinct name0 from v_r_system"

#forest gump the ping pong player example, or using a native join...

$clients = Invoke-DbaQuery -SqlInstance "CM01" -Database "CM_P01" -Query "select distinct sys.name0, cs.model0 from v_r_system sys inner join v_gs_computer_system cs on sys.ResourceID = cs.ResourceID"
# results example...
name0 model0
----- ------
CM01 Virtual Machine
DC01 Virtual Machine
FS01 Virtual Machine
WEB01 Virtual Machine
WS01 Virtual Machine
WS02 Virtual Machine

The downside to this is that the PowerShell code can easily (and quickly) get bloated, and you end up with two languages in one file, which can become a pain to test and troubleshoot over time.

Better yet, separate the two so that the PowerShell script and SQL query statements are in separate files. The SQL queries can be easily opened and tested in SQL Server Management Studio, making things much less painful.

PowerShell SQL Query – Example 2

First, the super-bloated SQL query file example…

dbo.v_R_System.Name0 AS ComputerName,
dbo.v_R_System.AD_Site_Name0 AS ADSiteName,
dbo.v_R_System.Client_Version0 AS ClientVersion,
dbo.v_GS_LOGICAL_DISK.DeviceID0 AS DiskID,
(dbo.v_GS_LOGICAL_DISK.Size0 / 1024.0) AS DiskSize,
(dbo.v_GS_LOGICAL_DISK.FreeSpace0 / 1024.0) AS DiskFree,
ROUND( (dbo.v_GS_LOGICAL_DISK.FreeSpace0 * 1.0 / dbo.v_GS_LOGICAL_DISK.Size0), 2) AS DiskUsed,
dbo.v_GS_PROCESSOR.Name0 AS Processor,
dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 as Memory,
dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 as OSBuild,
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as SerialNum,
dbo.v_R_System INNER JOIN
dbo.vWorkstationStatus ON
dbo.v_R_System.ResourceID = dbo.vWorkstationStatus.ResourceID LEFT OUTER JOIN
dbo.v_R_System.ResourceID = dbo.v_GS_PROCESSOR.ResourceID LEFT OUTER JOIN
dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID LEFT OUTER JOIN
dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_R_System.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID LEFT OUTER JOIN
dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:')
ORDER BY ComputerName

I told you it was bloated. Almost dead-fish-in-a-hot-summer-dumpster bloated. So, I save this carcass into a file named “cm_clients.sql”. This example was actually created in SSMS and saved, but you could use Visual Studio or whatever you prefer to SQL development and testing.

Now let’s hook up the electrodes to the PowerShell beast and get moving…

$rows = Invoke-DbaQuery -SqlInstance "CM01" -Database "CM_P01" -File "c:\queries\cm_clients.sql"

That’s it. One line of PowerShell, while the SQL logic is hiding in separate files. The output looks something like this…

Sidenote: I blogged about this already, but you can populate a folder with a library of .sql files, and use (get-childitem) and pipe it to Out-GrideView to make a handy and cheap GUI menu selector to impress your non-scripting coworkers. Something like this…

$QPath = "\\servername\sharename\folder"
$qfiles = Get-ChildItem -Path $QPath -Filter "*.sql" | Sort-Object Name
if ($qfiles.count -lt 1) {
Write-Warning "$qpath contains no .sql files"
$qfile = $qfiles | Select -ExpandProperty Name | Out-GridView -Title "Select Query to Run" -OutputMode Single
if ($qfile) {
$filepath = Join-Path $QPath $qfile
Invoke-DbaQuery ... you get the idea...

PowerShell WMI Query – Example 3

function Get-CmObjectCollection {
param (
[parameter(Mandatory=$True, HelpMessage="SMS Provider Name")]
[string] $Computer,
[parameter(Mandatory=$True, HelpMessage="Site Code")]
[string] $SiteCode,
[parameter(Mandatory=$True, HelpMessage="WMI Class Name")]
[string] $ClassName,
[parameter(Mandatory=$False, HelpMessage="Credentials")]
[System.Management.Automation.PSCredential] $Credential
$Namespace = "ROOT\SMS\site_$SiteCode"
try {
if ($Credential) {
$result = @(Get-WmiObject -Class $ClassName -ComputerName $Computer -Namespace $Namespace -Credential $Credential -ErrorAction SilentlyContinue)
else {
$result = @(Get-WmiObject -Class $ClassName -ComputerName $Computer -Namespace $Namespace -ErrorAction SilentlyContinue)
return $result | Select-Object * -ExcludeProperty PSComputerName, Scope, Path, Options, ClassPath,
Properties, SystemProperties, Qualifiers, Site, Container, __GENUS, __CLASS, __SUPERCLASS,
catch {
Write-Error $Error[0].Exception.Message

Without trying to shoehorn the entire set of SQL joins above into a WQL variation, which would look horrific without a ton of refactoring on Adderal and an ice bath, you can see with just two (2) datasets how this doesn’t scale very well…

$sys = Get-CmObjectCollection -Computer "CM01" -SiteCode "P01" -ClassName "SMS_R_System"
$cs = Get-CmObjectCollection -Computer "CM01" -SiteCode "P01" -ClassName "SMS_G_System_Computer_System"
$sys | % {
$resID = $_.ResourceID
$name = $_.Name
$model = $cs | ? {$_.ResourceID -eq $resID} | Select -ExpandProperty Model
$props = [ordered]@{
Name = $name
Model = $model
New-Object PSObject -Property $props

I suppose with enough brain juice, you could separate the query and processing logic with the WQL approach as well, but I’ll let you do that if you want.

Regardless of which approach you choose (or have chosen for you), the main thing to focus on is the data. With PowerShell you get the capability of pulling objects, which means you can filter and sort and do almost anything to shape the data you want to do what you need.


If you have the option, always use SQL. If you don’t, WQL can work, it’s just not as fun. Kind of like comparing Motley Crue with Lawrence Welk.

Brain is fried. Kirk out. Bed time.


One thought on “Some Dimented Dimensions of ConfigMgr Data

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s