Use PowerShell to Discover and Create SCCM Collection Queries

A customer wanted a collection with all their HP EliteBook 840 G3 computers. I could always search the Intenet and find some queries that works, but I wanted to find out myself what fields to use and create the collection query myself. This blog post explain the process of creating your own WQL queries for SCCM collections from scratch using PowerShell.

SCCM Collection queries use WQL (WMI Query Language) queries, which are queries to the site server’s WMI and not the SQL database. A SQL query and WQL query are not the same thing, but they look almost the same. If you know how you write SQL queries, then you find it easy to write WQL queries as well.

My first task to create the collection query for all HP EliteBook 840 G3 computers is to discover where in WMI this information is stored. SCCM stores its information in the “ROOT\SMS\site_xxx” namespace, where xxx is your sitecode.

The following PowerShell code will list all the WMI classes where the SCCM server’s sitecode is P01.

Get-WmiObject -Namespace "ROOT\SMS\site_p01" -List

In this list I see a class named “SMS_G_System_COMPUTER_SYSTEM” that seems very familiar. I remember that driver packages in task sequences perform a local WMI query on “Win32_ComputerSystem” to find the Model. Could this be the class in SCCM that holds the information I’m looking for?

I perform another WMI query on the SMS_G_System_COMPUTER_SYSTEM class to see what properties it holds. This PowerShell command selects the first object in this class.

Get-WmiObject -Namespace "ROOT\SMS\site_p01" -Class "SMS_G_System_COMPUTER_SYSTEM" | Select-Object -First 1

I see from the result that the SMS_G_System_COMPUTER_SYSTEM class do indeed have the Model property that I’m looking for, and it contains exactly what I want, the model name of the computers. It is from this property that we will build our WQL query to use in our collection.

It’s time to build the WQL query. I want the name of all systems that are a HP EliteBook 840 G3.

select SMS_R_System.Name FROM SMS_R_System
INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID
WHERE SMS_G_System_COMPUTER_SYSTEM.Model = "HP EliteBook 840 G3"

And I want to test the query before I create a collection on it.

$query = @"
select SMS_R_System.Name FROM SMS_R_System
INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID
WHERE SMS_G_System_COMPUTER_SYSTEM.Model = "HP EliteBook 840 G3"
"@

Get-WmiObject -Namespace "ROOT\SMS\site_p01" -Query $query

Lets use PowerShell to create our collection with this WQL query. These SCCM PowerShell cmdlets requires you to load ConfigurationManager.psd1 module, and the easiest way to to this is through the SCCM console.

I want the collection to update every 4 hours. You can change this using the -RecurInterval and -RecurCount at the New-CMSchedule cmdlet. I also want incremental updates off, so I set -RefreshType to Periodic.

$query = @"
select SMS_R_System.Name FROM SMS_R_System
INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID
WHERE SMS_G_System_COMPUTER_SYSTEM.Model = "HP EliteBook 840 G3"
"@

$Schedule = New-CMSchedule -Start (Get-Date).DateTime -RecurInterval Hours -RecurCount 4
New-CMDeviceCollection -Name "All HP EliteBook 840 G3" -LimitingCollectionName "All Systems" -RefreshSchedule $Schedule -RefreshType Periodic
Add-CMDeviceCollectionQueryMembershipRule -RuleName "Query All HP EliteBook 840 G3" -CollectionName "All HP EliteBook 840 G3" -QueryExpression $query

Note on query performance.

Try to avoid using LIKE when writing queries, as they are slow to execute and degrades performance of your system. Now some queries requires like due to the nature of the desired result. What you should avoid at all costs are queries with LIKE “%criteria%” as they are very slow to execute. A similar query using LIKE “criteria%” is much quicker to execute than LIKE “%criteria%”.