Automation tails from the fox hole

Recently at work, we had a task come up which saw us needing to move tens of thousands of devices between collections in CM. We decided to run some tests to find the fastest way! We compared:

The SCCM 1511 Era Collection Cmdlets

The newly released speedier Collection Cmdlets which shipped with Tech Preview 1803

Using Keith Garner’s super powerful CMPSLib Module

Query Based Membership

AD Group Query Membership

Direct SQL Membership Tampering ☠

I’d always kind of wondered myself, so it was a fun challenge to come up with some hard numbers. And for the last item in the list…this is just for fun, I do not recommend using this in your production…or your testlab. Or anywhere.

The test lab

All testing occurred in my VM Testlab, a Ryzen 7 1700 with 64 GB of RAM, with storage served on NVMe m.2 SSD drives. A beastly machine (also hello to viewers from the year 2025 where we have 6TBs of storage on our phones and this is laughably quaint. Here in 2018, we believed more RBG = more better, and we were happy, damn it!)

My ConfigMgr VM runs on Server 2016, 32 GB of RAM, SQL gets 16GB of that, and the SQL database and log files live on a separate NVMe drive for maximum performance.

The testing methodology

In this test, we’ll test two scenarios: adding 10,000 devices to a collection, and adding 30,000 devices to a collection. In our experience we start to see collection slow down at around 30K, and this amount isn’t too big as to exclude the majority of CM Environments in the world. Let me know if you think of something I forgot to test though!

We will resolve our input devices using DBATools Invoke-DBASqlQuery, with the following Syntax:

I used this method because I found it more performant than using the built-in command, and gave me just the two columns I needed, Name and ResourceID.

We will conduct add the users (resetting the collection count between tests) and measure both the time it takes to complete the Membership alter command with a refresh at the end of the process, then monitor CollEval.log for the following line items:

Specifically the final line indicates that Collection Rules have finished processing and the devices will now be visible in CM. Now let’s dive in!

The SCCM 1511 Era Collection Cmdlets

These cmdlets have something of a bad wrap, I feel, for being slow. Without digging into the code, I couldn’t tell you specifically how they’re written, but I’ve heard it described that when you use them to add multiple devices to a collection, rather than adding all of the rules and saving the changes once, it would add each rule one at a time.

When I tried to directly add 10,000 rules at once, I ran into out-of-memory errors!

This would continue no matter what, until I found a stable amount of devices to add at a time. 527 was the max I could ever add with one step, so for consistencies sake, I added just 500 rules at a time.

10,000 Devices took a leisurely 05:02 minutes to process, while 30,000 took a snooze inducing 50:51 to process! Nearly FIFTY ONE Minutes. Clearly something slow is happening under the covers here.

Super Speed Collection Moves CMPslib

Keith Garner wrote his own set of PowerShell cmdlets to deal with collection rules, after we experienced some frustration with the options that ship in the box. You can download them here, and to use the them you pass in a collection of devices with Name and ResourceID properties to the -System parameter.

10,000 rules are applied in only 1:54 seconds, and CollEval processed the devices in practically no time at all:

This is a very nice improvement over the built-in cmdlets, and I was eager to see what happened with 30K rules.

It turns out that when we applied 30,000 rules, we saw performance scale linearly, taking 4:44 to create and apply the rules, with processing taking just a bit longer.

The total processing time for 30K devices is 4 minutes, 52 seconds using this method of adding direct rules. By far the fastest!

1806 Cmdlets

The 1806 CM Cmdlets bring some nice features, and bug fixes. On top of that, something has changed under the covers of the Collection Direct Membership Add cmdlet, giving us a HUGE speed improvement too!

One Caveat the syntax has changed quite a bit, and you need to use the new cmdlets in a specific manner to ensure that you’ll experience the SUPER Speed!

First, don’t batch your collection addition rules, like we did previously. Or, if you do batch them, do it in batches of 10K. Next, the parameters have changed. If you use the cmdlet in stand-alone mode, like soAdd-CMDeviceCollectionDirectMembershipRule -CollectionID <SomeCollection> -ResourceID $arrayOfResourceIDs

You will end up with the previous cmdlet performance. From what I can tell, it looks like there may be an internal branch in the logic and the old code is alive and kicking down there! What, I told you it was weird!

and bundle them up in batches of 1k devices at a time. Here’s the code I used , edited for your viewing pleasure. You will need to make sure your own WQL query is on one-line, SCCM doesn’t like a multi-line string:

CollEval fired up a few seconds later, and interestingly it does take a longer time to crunch the Query rules than it did the Direct Rules, but we’re talking 10K devices added to a collection in under 20 seconds.

At this point, I knew 30K would be equally fast.

Wow. Only 26 seconds to apply the rules, and a total crunch time of 45 seconds to calculate membership. Just one minute…let’s see what happens if we…

Let’s just add every device using a query rule

This was required of me at this point, all 115K machines in my testlab would be added with massive IN queries to really test performance.

A weird screen shot. The PowerShell line reflects the total time to run the command (2:18 for 115K rules), while the bottom half is the relevant lines from Collection Evaluation

Only 2 minutes, 18 seconds to apply the rules, and two minutes to run the query! Incredible! This is a huge improvement compared to adding devices with direct rules, in which case using CMPSLib took 1 hour, 15 minutes to add 115K rules.

Using AD Membership Queries

Using AD Group Membership queries is super super fast. If your AD Replication is good and healthy.

If you begin to use AD Groups for membership in CM, keep in mind that if you make group changes at the periphery of your network, it will take some time to replicate from your remote site, to a global catalog, and then have to wait for CM to requery the Active Directory to see the change.
Unfortunately I don’t have a giant AD environment to play with Group Membership, but from what I’ve seen I would expect very good speed here too. Sorry this section is lame.

Direct Membership Control using SQL

I’ve always kind of wondered what Collection Evaluation was doing under the covers. We know that when you add rules to a collection, they’re processed in this order:

How it really works

In digging around under the covers, I spent a lot of time watching arcane log files and trying to make sense of strange views in SQL trying to uncover where certain info was stored. I had to grant myself super admin rights, break all of the warranty labels and in the end, took the CM Database out to dinner and then dug around with my flashlight under the covers, looking for goodies. And I found a totally unsupported method to directly manipulate collections with shocking speed.

How do I do it?

Well, a gentleman never tells. What I will share though is the impressive speed. Using this method to directly control collection membership, I was able to place 30K devices in a collection in 0:00:01. One second.

Code pixelated to protect you from yourself. Seriously, I’m not going to be the one arming you wild monkeys with razor bladed nunchuks

But at what cost?

Well, if we don’t actually add rules, but instead manipulate the collection via getting fresh with the database, we lost a lot. We lose RBAC. We don’t have include rules, we don’t have exclude rules, the collection membership would just be what we said it should be.

Oh, and since we skipped CollEval, CollEval is going to have something to say about the weird ass stuff we’ve done to this poor innocent collection. For instance, if we ever forget about the wonky, dark-magic joojoo we have performed on this poor collection and click ‘Update Membership’, CollEval will have its revenge.

CollEval Checked, found no rules, then deleted everyone

CM will helpfully look at the collection, look at its rules and say ‘WTF are you doing bro, are you drunk?’ and then delete everyone from the collection. Not a member via a valid rule? You’re not gonna stay in the collection.

I would not recommend using this approach.

The speed of direct query rules is mindblowing enough, and the new CM Cmdlet aren’t far behind them, so we have plenty of performance options. Seriously, don’t explore this route, if you do, the air conditioner will catch on fire with spiders coming out of it.

Don’t do this to your CM. And if you DO, don’t ask MSFT for support.

In Conclusion

So, to summarize our data in a chart

Basically any method is much, much better than the Old CM Cmdlets!

Basically anything is faster than using the old Cmdlets

If you’re considering your options outside of the old cmdlets, I’d recommend giving CMPSLib a try. Lovingly written by Keith Garner, with help from yours truly, we believe this is a very resilient method of adding devices to a collection, without the wonky-ness of the new Add-DeviceDirectRule cmdlets kind of odd syntax.

Want the new ones? It’s easy, just download the media for the tech preview, and use it to Install the CM Console on your machine. The CM Console will give you the new cmdlets and they’ll work on an old environment, super easy! Just be mindful of the syntax!

Of course, for true performance, if you’re looking to manage your collections from outside of CM, I would only recommend maintaining membership using query rules, it’s just too fast not to mention.