SCCM Collection Best Practices while Creating a Dynamic Collection

Let’s quickly check the SCCM Collection best practices while creating a dynamic device collection for all Windows 10 Devices.

A collection of all Windows 10 devices would be useful for segregating them from Windows 11 devices. In the coming days, you might need to deploy specific policies or applications only for Windows 11 devices.

We have many posts related to collections with the HTMD community. There are two different types of collections: user collections and device collections. The other two segregations of collections are based on their static vs. dynamic nature.

In this scenario, I will share the details of creating a dynamic device collection to group all Windows 10 devices. Dynamic collections are helpful because they automatically add new devices. However, you need to ensure that the WQL queries are optimized.

Patch My PC

SCCM Collection Best Practices

Now, let’s quickly understand the logic you want to use to create a dynamic collection before creating any collections. The collection logic should be selected carefully because nonoptimized WQL queries can impact the performance of the entire SCCM infrastructure.

SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.1
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.1

You must understand the best way to analyze the WQL query to build a collection. There could be multiple options to create dynamic collections using different types of query logic. We will see the example of creating a dynamic collection for Windows 10 devices. Following is a set of generic guidelines/best practices that you need to keep in mind when you make a collection:

  • Avoid wildcard (%) in WQL Queries used in dynamic collections wherever possible.
  • Avoid OR conditions in WQL Queries used in dynamic collections wherever possible.
  • Avoid scheduled updates for Direct Collection Rules.
  • Reduce the number of Incremental collection updates.
  • Avoid using All systems/All users as limiting collection.

Analyse WQL Query Performance

Let’s create a collection and analyze the WQL query load during dynamic collection creation for All Windows 10 devices. The details of the Windows 7, 8, and 10 build numbers can help you identify the best choice to identify all Windows 10 devices. The OS build numbers and captions are two properties we compared in this query performance analysis.

  • Windows 11 build number starts with 20000
  • Windows 10 build number starts with 10000
  • Windows 8.1 build numbers start with 9000
  • Windows 7 build numbers start with 7000
  • Windows XP build numbers start with 3000

Create a Dynamic Device Collection for All Windows 10 Devices

Let’s create a dynamic collection for all Windows 10 devices. I plan to analyse the queries’ performance using different SMS_G_System_OPERATING_SYSTEM properties.

  • Navigate to the SCCM console – Assets and Compliance – Device Collections.
  • Right-click and select “Create Device Collection” from the Device Collections node.
  • On the General page, provide a Name and a Comment.
  • You can limit the collection; choose Browse to select a limiting collection. The collection contains members from the limiting collection.
  • Click on the NEXT button.
SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.2
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.2

Now, it’s time to create the rule to collect devices based on the operating system properties of the device. You need to select the type of query rule shown in the screenshot below.

  • Enter the name of the Dynamic query – All Windows 10 Devices.
  • Click on Edit Query Statement.
  • Click on Criteria and Click on the Show Query Language option.
SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.3
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.3

Test #1

Copy the following WQL query based on Windows 10 build numbers and paste it into the Show Query Language window. The criteria for building the All Windows 10 collection are based on build numbers (SMS_G_System_OPERATING_SYSTEM.BuildNumber).

  • Greater than and equal to 1000 and less than 2000.
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.BuildNumber >= "10000" and SMS_G_System_OPERATING_SYSTEM.BuildNumber < "20000"

Now, let’s try evaluating the WQL query you wrote to analyze performance. You can click on the Green Play button to start the query evaluation.

SCCM Collection Best Practices while Creating a Dynamic Collection - Fig..4
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.4

Let’s quickly look at the Collection query evaluation results to understand the performance impact of the WQL query. It seems this is a complex query, and it took 00.00.05.885 milliseconds.

  • Query Execution Elapsed time = 00.00.00.331 Milliseconds.
  • Displaying results elapsed time = 00.00.00.885 Milliseconds
SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.5
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.5

Test #2

Now, let’s check whether there is an opportunity to optimize the query to improve the performance. Use the following query based on SMS_G_System_OPERATING_SYSTEM.Caption. Copy the following query to the Show Query Language option, as discussed in test #1.

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"

Now, let’s try evaluating the WQL query you wrote to analyze performance. You can click on the Green Play button to start the query evaluation.

SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.6
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.6

Let’s quickly look at the Collection query evaluation results to understand the performance impact of the WQL query. The results were displayed in 00.00.01.498 milliseconds, which was faster than Test #1.

  • Query Execution Elapsed time = 00.00.00.242 Milliseconds.
  • Displaying results elapsed time = 00.00.01.498 Milliseconds

Results

The results of the best query to create a dynamic collection of all Windows 10 devices are given below. As you can see in the results, you can’t come to any particular conclusion with query execution and the elapsed time displayed in the Collection Query Preview Tool.

I don’t think the primary purpose of the collection query preview tool is not to analyze the best or optimized WQL query. Microsoft documentation also provides some hints towards this conclusion, and the notes from Microsoft are:

  • Elapsed times shown for the query preview may not be the same as the actual execution of the target query.
  • Query execution elapsed time and Displaying results elapsed time shouldn’t be added to a total elapsed time since these processes run in parallel.

Does this mean it is better to rely on ConfigMgr Collection Full & Incremental Evaluation Status Summary from the admin console? The best option is to compare the results of all these tools and decide what is best for your SCCM infrastructure.

SCCM Collection Best Practices while Creating a Dynamic Collection - Fig.7
SCCM Collection Best Practices while Creating a Dynamic Collection – Fig.7

Resources

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Anoop C Nair has been Microsoft MVP for 10 consecutive years from 2015 onwards. He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is a Blogger, Speaker, and Local User Group Community leader. His primary focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc.

2 thoughts on “SCCM Collection Best Practices while Creating a Dynamic Collection”

  1. Hi anoop is there a way to optimise the collection for non-compliant devices for patching which can later be targeted for remediation ?

    Reply
  2. Hi there,

    Is there a way to build a “dynamic” dynamic query. say you want to build a dynamic collection for ADR. but you want to use a generic query of say all 7zip installs (easy), find the latest version (easy) and then remove those from the collection. so you will always have a dynamic 7zip collection of all devices that need 7zip updated?

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.