How to Create ConfigMgr Query Collections

I remember when I first started in ConfigMgr, I wasn’t sure how to easily create ConfigMgr query collections.  Being able to create the collections for yourself, your team or management needs comes in very helpful.

When reading the forums and other feeds I see people ask, “how to make a query for X?”

The quick answer is a device’s Hardware Inventory is your best friend. If you know where to get the data from in the Hardware Inventory, you will easily be able to create a Query Collection.

What are ConfigMgr Query Collections?

As a ConfigMgr admin, collections will be a big part of your role.  Collections are used to create logical groupings of users or devices.  With collections, you will be creating them to help you manage your environment in a structured manner.  Here are a few use cases for collections:

• Deployments, for example:
  • Applications
  • Operating system deployments (Task Sequences)
  • Software Updates
  • Configuration Baselines
• Client Setting
• Maintenace Windows
• Power Settings
• Role-Based administration

Using the Hardware Inventory to build collections

With this tip, the mystery behind creating query collections in ConfigMgr will be solved.  When I see administrators starting to learn query collections, a common struggle is building them. This often leads to our best friend, Google. Instead, I like to teach creating query collections as a two-step process.

1. Work out from a device’s hardware inventory the criteria for your collection
2. Create the collection based on hardware inventory criteria discovered

Creating an example collection

Let’s walk through building a simple collection query.  We are going to build a collection for All Windows 10 Enterprise Devices.

Step 1: Data Discovery

First up is how to use Hardware Inventory to find where data you need is stored.

• From your ConfigMgr console, navigate to Assets and Compliance / Devices
• Find a device that contains the data you require.  As the goal here is to create a collection with All Windows 10 devices, you want to choose a Windows 10 Enterprise machine. Right-click your chosen device and click Start > Resource Explorer
• On the left-hand side of the Resource Explorer, expand the node Hardware
• Under the Hardware node, you will be presented with the device’s Attribute Classes. Here you can scroll down until you find something that sounds logical.  For this example, it was nice and easy. Select the attribute class Operating System
• With the Attribute Class located, we can now find the correct attribute which in this case is Caption

• You now have the criteria needed to create a query Collection.  I recommend writing it down for easy reference later.

Step 2: Create The Query Collection

We have set ourselves up for success in step 1 with our preparation.  You’ll now learn how to create the collection.

• Open up your ConfigMgr Console and navigate to Assets and Compliance / Collections / Device Collections
• Right-Click Device Collections and select Create Device Collection
• From the Create Device Collection Wizard Supply a Name (All Windows 10 Enterprise Devices) and choose a limiting collection.

Note: Please do not use All System as your limiting collection. Choose something like All Workstation Clients for example. More on this topic later but for now just know that this tip is to try to keep your ConfigMgr environment healthy and fast.

• On the Membership Rules page of the Create Device Collection Wizard, in the Add Rule list, select the Query membership rule
• On the Query Rule Properties Window that pops up provide a Name (Windows 10 Enterprise Query) and then select Edit Query Statement…
• From the Query Statement Properties window select Criteria and then select the Starburst.

• On the Criterion Properties Window under Where click Select 

• On the select Attribute window, you now can reference your notepad from step 1
  • From Attribute Class choose Operating System
  • From Attribute choose Caption 
  • Now click OK
    • Now that was simple 🙂

• Leave operator as is equal to
  • You can have a read of the following link if you are interested in reading more about operators.  Note ConfigMgr queries use the language WQL https://docs.microsoft.com/en-us/windows/desktop/wmisdk/wql-operators
• Select Values and then choose Windows 10 Enterprise.  The great thing about the Query wizard is that in Values it references all the options in your environment.  Once selected, click OK
• Awesome, your Query is now complete you can click Show Query Language on the bottom left to see behind the scenes WQL Query.  Here is the Query you have just created:

select * from SMS_R_System
inner join
SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 10 Enterprise”

via GIPHY

Using the ConfigMgr Query Node

When you are starting out with Query collections you can practice creating queries without building a collection.  This is done through the monitoring node inside ConfigMgr.

• In the Configuration Manager console, choose Monitoring.
• In the Monitoring workspace, choose Queries. Then, on the Home tab, in the Create group, choose Create Query

The other great thing about the Queries node is you can import your query while creating a collection.  This saves you a lot of time by not having to rebuild the query.

ConfigMgr Query Collection Best Practises

I want to share some collection best practices. These tips are just to help you be mindful when creating collections.

• Do not use too many Incremental Updates Collections
• Stay clear from using All Systems as your limiting collection
• Be careful with your query operators
• Naming convention

Now let’s see why these points are important.

Incremental Updates in Collections

Limiting your use of Incremental update collections is important. If you have too many collections set this way, your ConfigMgr evaluation time is going to slow down.

What I see in a lot of environments is that the option for incremental updates is used too often.  Microsoft’s guidance is that your ConfigMgr site should be below 200 incremental update collections.

Here are the most common questions I get around this topic:

• How do I see which of my Collections have incremental updates enabled?
• How is this impacting my environment?

Before answering these questions, I recommend you install a tool called CEViewer. CEViewer is part of the System Center 2012 R2 Configuration Manager Toolkit. CEViewer will help you get an understanding of the collections within your environment.

Download link: https://www.microsoft.com/en-us/download/details.aspx?id=50012

How do I see which of my Collections have incremental updates enabled?

There are three main ways I look this information up:

• Using CEViewer
  • Make sure you have installed it using the link above and loaded the application
  • On the Connect Screen type in your Primary site Server Name and click Connect
  • Navigate to the Incremental Evaluation tab
  • You will now be able to see rich data, for example:
    • Total run time of evaluation
    • Total Collections
    • Information about the performance of each Collection
  • The screenshot below gives an example of an environment that needs cleaning.  You can see there are over 400 incremental collections and it takes nearly five minutes to evaluate all of those collections.

• Using PowerShell
  • You could use Powershell to gather the information.  Then from Powershell, you could create code to either remove the option for Incremental Updates or delete unused collections from the environment.  Here is one line of PowerShell just to gather the information:

Get-CMDeviceCollection | Select -Property Name, CollectionID,RefreshType | Where-Object {$_.Refreshtype -eq 6}

• Using PowerBI (our personal favorite)
  • With the ConfigMgr PowerBI dashboards you get access to this data and much more.  I recommend for free going and downloading our Dashboard. Trust me you won’t regret it. https://gallery.technet.microsoft.com/PowerON-Power-BI-ConfigMgr-0cd61c5f

Limiting Collections

Every time you create a collection you have to assign a limiting collection.  When starting out, you may end up scoping all new collections to All Systems. The way that collections in ConfigMgr are evaluated could cause different results than you expect such as long evaluation times.  The best rule of thumb is to scope the limiting collection down as much as possible.  For example, if you are deploying an application to only Windows 10 devices, limit to a collection such as All Windows 10 Devices instead of All Systems.

I recommend reading the following post which gives an overview of how ConfigMgr evaluates collections. https://blogs.technet.microsoft.com/scott/2017/09/13/collection-evaluation-overview-configuration-manager/

Careful with your query operator

If you have used our PowerBI Dashboard or CEViewer, at this point you may have identified some long-running queries.  If you have not checked for slow running collections I recommend doing it for the health of your ConfigMgr environment. From experience, slow running query collections are often caused by using the operator LIKE or NOT LIKE.  When you use these operators, it can cause a lot more load on SQL.  The recommendation here is to remove this operator wherever possible.

Naming conventions

One thing that is often overlooked in ConfigMgr is a naming convention. This may seem trivial but when your environment gets larger or if you are already working in a large environment, collections can easily get out of control.

I recommend that you work with your team to start writing up a naming convention document.  With the correct naming convention, it will help you admins easily identifying deployments and also searching for the collection they need.

Here are some examples of naming conventions.

Conclusion

Cheers,

Harry