Microsoft improved the SCCM collection query preview tool with the latest version of ConfigMgr. Collection 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 the collection query 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 query 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?
What is SCCM Collection Query 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 Query 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.
- 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 rows 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 results preview option. As you can see this is 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) >= 01
Launch Results preview from Queries Node
Let’s see whether there is any option to launch collection result preview tool from queries node in 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.
- Create collections – Configuration Manager | Microsoft Docs
- Sample queries for collections in Configuration Manager
- Create queries in Configuration Manager
- How to Create SCCM Dynamic Collection – Part 2