Use PowerShell to Discover and Create SCCM Collection Queries

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.

 

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.

 

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.

 

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

 

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.

 

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%”.

About the Author

Anders Rodland
Anders Rødland started his carrer as an IT consultant in 2006 and now works as a Senior IT-Specialist and Service Owner for Atea, one of the major IT companies in Scandinavia. My main focus is on Microsoft System Center, and I currently have more than 13 Microsoft certifications. Certified on Windows Server, Windows Client, SQL, Exchange and System Center Configuration Manager. Anders Rodland also holds an ITIL Foundation certification. This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.