Collection Query findings

Collection Queries can be created easily from the Configuration Manager Console without deep knowledge of WQL or SQL… But have you ever checked how long the Collection Evaluator requires to execute the Query ?

An example: the following Rule should get all Machines with a specific Software installed where the Software itself can be x86 or x64:

image

CollEval took 153s ( 2min 33s ) to evaluate the rule ( in an environment with ~15’000 Devices ).

The Problem with this Rule is that two big tables with a lot of records per device are joined.

In this case it would make sense to create two individual queries on the Collection, one for x86 and one for x64.  A Query that references only one of the two big tables takes about 2s in this environment. So for two queries you will end up in ~4s.

Another Option would be to use “Installed Software” instead of “Installed Applications” because “Installed Software” contains x86 and x64 Software in one Table:

image

this Rule took only 2s

“Installed Software” is not enabled by default, you have to enable this inventory class in the AssetIntelligence section.
If you want to have a single Query Rule for x86 and x64 “Installed Applications” then you can use  a sub-select statement:

image


select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in
(select  SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId FROM SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “SoftwareA”) or SMS_R_System.ResourceId in (select  SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId FROM SMS_G_System_ADD_REMOVE_PROGRAMS_64 where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = “SoftwareA”)

This Query took also 2s

Conclusion:
There are many ways to get the Result, but the time that CollEval requires to execute a Query can vary (in the scenario here from 153s to 2s ).

email

Written by , Posted .