Microsoft improved the SCM WQL Query Results Preview Tool with the latest version of ConfigMgr. Collection WQL query preview is useful to get your WQL query tested before building a dynamic collection query. This tool can also help to avoid accidental deployments.
I have highlighted SCCM WQL Query Results Preview Tool in the Top 5 New Features Of ConfigMgr 2103 | SCCM HTMD Blog. The dynamic collections are helpful to automate many tasks. However, you will need to be very careful when you create dynamic collections.
The SCCM collection WQL query results preview tool can help identify the problematic or intensive WQL queries that can impact the entire infrastructure’s performance. Let’s also learn What is Collection, How to Create SCCM Direct Membership Collections, and How to create dynamic collections?
SCCM WQL Query Results Preview Tool is the Best tool to Analyze the Performance of Collections. You can learn more details about best practices and the total time taken to evaluate collections queries in this post.
What is SCCM Collection WQL Query Results Preview Tool?
SCCM collection query previews the new feature that can help Configuration Manager admins analyze the WQL queries before putting this WQL query into production. You can get the performance of WQL query statistics options from the collection preview tool.
How to Launch SCCM Collection WQL Query Results Preview?
It would be best if you navigated to collection properties -> Add dynamic query -> Edit query statement -> Click on the green triangle to show the Query Results Preview window.
Verify SCCM Collection with WQL Query Results Preview Tool
Let’s check the performance of the SCCM dynamic collections with SCCM WQL Query Results Preview Tool. You can Set Limit Rows Returned for the collection results preview. You can limit preview rows to be returned between 1 to 10,000 rows. The default is 5000 rows.
You have an Omit option to avoid duplicate row issues. I normally recommend using this omit option in results preview and distinct in the WQL query.
- Click on the Run button to get the performance details of the WQL query.
Now, SCCM shall give you the Preview results of the query’s WQL query and performance details.
- Let’s check the default query that fetches all the devices from the hierarchy.
- Select * from SMS_R_System
- Statistics or performance details of this query are also given in the preview results.
- Rows Returned = 14
- Query Execution Elapsed Time – 00:00:00:257
- Displaying results elapsed time – 00:00:01:796
NOTE! – You might see different results and performance based on the number of managed clients in your environment.
I used another WQL query to test the performance of the query via the results preview option. As you can see this is a more complex query than the above one. You can check performance-related details below.
- Rows Returned = 2
- Query Execution Elapsed Time – 00:00:00:194
- Displaying results elapsed time – 00:00:01:078
select sms_r_system.Name, sms_r_system.SMSAssignedSites, sms_r_system.OperatingSystemNameandVersion, sms_r_system.ResourceDomainORWorkgroup, sms_r_system.LastLogonUserName, sms_r_system.IPAddresses, sms_r_system.IPSubnets, sms_r_system.ResourceId, sms_r_system.ResourceType, sms_r_system.ClientType from sms_r_system AS sms_r_system inner join SMS_G_System_SYSTEM as c on c.resourceid=sms_r_system.resourceid where datepart(yy,c.timestamp) >= 1997 AND datepart(mm,c.timestamp) >= 01 AND datepart(dd,c.timestamp) >= 01Launch Results preview from Queries Node – SCCM WQL Query Results Preview Tool
Let’s see whether there is an option to launch the collection result preview tool from the queries node in the Monitoring workspace.
I have a blog post about SCCM Query All Active Directory Security Groups Dynamic Collection HTMD Blog (anoopcnair.com).
- Launch the console.
- Navigate to \Monitoring\Overview\Queries.
- Select a query and go to properties.
- Click on Edit Query Statement.
- Click on the green triangle to show the Query Results Preview window.
Query Results Preview Tool Best Tool to Analyze Performance of Collections
SCCM WQL Query Results Preview Tool can be used to check the performance of the dynamic collections in SCCM. I have noted down some of the points in the following post on best practices of collection creation.
Query Results Preview Tool Best Tool to Analyze Performance of Collections helps to analyze the end-to-end performance of the WQL query similar to that of SQL server management studio.
Resources
Author
Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. 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.