Thought I’d throw a quick piece of advice (and some background, of course) for you to help reduce the time it takes for overall collection evaluation in your Configuration Manager (ConfigMgr) environment.
One of the best practices in ConfigMgr environments is to monitor the time it’s taking your processes to evaluate collection membership rules. A great thing about ConfigMgr is the ability to very clearly target devices and users but with that flexibility can also come lazy ConfigMgr administrators who don’t want to take the time (or who are so swamped with action items) to think about the effects of bad query writing in ConfigMgr.
We are a large organization with a very well defined Active Directory broken down by physical locations and sometimes we’ll want to target software or policy to a subset of our environment based on their location. You may already know about the distinguished name field in SMS_R_SYSTEM but as you’ve also probably found out, the fully qualified distinguished name also ENDS with the domain name rather than starting with it. This means that you’d have to insert your wildcard for all systems at a site with ‘%/Computers/<SiteName>/Domain.com’. Unfortunately, using wildcards at the BEGINNING of your compare is the worst (ok maybe not the worst — but definitely not efficient) way to do comparisons using ‘like’. Instead, you either want to use an ending wildcard or, better yet, you can use an ‘=’ operator and this GREATLY reduces evaluation time over using any like statement. You may be thinking — “Chris, you’re out of your mind… I need an OU and the distinguished names aren’t the same at every site…”
Have no fear, here’s the answer… ConfigMgr actually did an interesting thing in the DB… they split every distinguished name into just the OU structure… so here’s an example — if the distinguished name of COMPUTERA is actually CN=COMPUTERA,OU=IT,OU=Computers,OU=MYSITE,OU=NORTHAMERICA,DC=MYDOMAIN,DC=COM, there will be individual entries in the DB as follows that will apply to this resource:
This means that if you want to target all Computers in the IT OU, you don’t need a wildcard at all… here’s the collection query:
SELECT * FROM SMS_R_System WHERE SystemOUName = ‘MYDOMAIN.COM/NORTHAMERICA/MYSITE/COMPUTERS/IT‘
And your query will evaluate super fast because it’s an exact match query rather than a wildcard comparison. Voila… hope this helps some of you do great things to maximize efficiency of your environment with respect to collection evaluation.
Another benefit to using this is that it requires no inner joins (which are part of the process that will slow down query evaluation). I only mention this because inner joins shouldn’t necessarily be avoided but it helps to try to use what’s in SMS_R_SYSTEM when you can.