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. All Windows 10 devices collection would be useful to segregate these devices from Windows 11 devices. You might need to deploy some specific policies or applications only for Windows 11 devices in the coming days.

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

In this scenario, I will share the details of creating a dynamic device collection to group all Windows 10 devices. The dynamic collections are helpful because it automatically adds new devices into the collection. But, you need to ensure that the WQL queries are optimized.

• Easiest Method To Create SCCM WQL Queries For Automation Tasks Collection Creation

SCCM Collection Best Practices

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

You need to understand what is 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 are a set of generic guidelines/best practices that you need to keep in mind when you create 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 update for Direct Collection Rules.
• Reduce the number of Incrmental collection updates.
• Avoid using All systems/All users as limiting collection.

Analyse WQL Query Performance

Let’s create a collection and analyze 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 start with 20000
• Windows 10 build number start 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 Dynamic Device Collection for All Windows 10 devices

Let’s create a dynamic collection for all Windows 10 devices. I’m planning to analyze the performance of the queries using different SMS_G_System_OPERATING_SYSTEM properties.

• Navigate to 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 be limiting the collection; choose to Browse to select a limiting collection. The collection contains members from the limiting collection.
• Click on the NEXT button.

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 Query Rule as shown in the below screenshot.

• Enter the name of the Dynamic query – All Windows 10 Devices.
• Click on Edit Query Statement.
• Click on Criteria and Click on Show Query Language option.

Test #1

You can Copy the following WQL query based on Windows 10 build numbers and paste it into the Show Query Language window. The criteria to build the All Windows 10 collection is 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 to evaluate the WQL query that you have written to analyze the performance. You can click on the Green play button to start the query evaluation.

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 Exeution Elapsed time = 00.00.00.331 Milliseconds
• Displaying results elapsed time = 00.00.00.885 Milliseconds

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 to evaluate the WQL query that you have written to analyze the performance. You can click on the Green play button to start the query evaluation.

Let’s quickly look at the Collection query evaluation results to understand the performance impact of the WQL query. It took 00.00.01.498 milliseconds to display the results. And the query execution time was faster than Test #1.

• Query Exeution 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 all Windows 10 device dynamic collection is given below. As you can see in the results, you can’t come to any particular conclusion with query execution and displaying elapsed time shown in Collection Query Preview Tool.

I don’t think the main 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 actual execution of the target query.
• Query execution elapsed time and Displaying results elapsed time shouldn’t be added for a total elapsed time since these processes run in parallel.

Does this mean, 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 make a decision that is best for your SCCM infrastructure.

Resources

• What is Collection, How to Create SCCM Direct Membership Collections

Author

Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with over 17 years of experience (calculation done in 2018). He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…..…