Update a Collection Membership Query Rule with PowerShell

A member of the Swedish System Center User Group, also known as SCUG, asked for some help with updating all the collections that had membership queries matching a specific pattern/text. I figured that I’d give it a try in my lab environment and see if I was able to assist with this request. The problem the member was facing concentrated about a migration project they were working on where part of the WQL query statement would have to be amended, e.g. you would like to replace a Domain name in the query or perhaps a group name. I ended up with a script that I sent to the member and thought that I’d share here as well.

Download script

You can get your hands on the script from this TechNet Gallery page.

Usage

I recommend that you take caution when you’re execution this script. As for a demonstration of the script, I’ve come up with this scenario where I have two collections that each have a single membership query rule that has the following WQL query statements:

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.SystemGroupName like "CONTOSO\\<AD group name>"

I want to change the Domain name from CONTOSO to FABRIKAM. Since both of the collections have this string in common, I can simply execute my Update-CMCollectionQuery.ps1 script to locate CONTOSO and replace that with FABRIKAM. What happens in the background is that the script will enumerate all collections for those that contains a membership query rule and evaluate that rule checking for a match in the SQL query statement of what’s specified in the Locate parameter.
1. Download the script and unpack to to C:\Scripts.
2. Open an elevated PowerShell console and browse to C:\Script, or where else you have stored the script.
3. Run the following command:

.\Update-CMCollectionQueryRule.ps1 -SiteServer CAS01 -Locate "CONTOSO" -Replace "FABRIKAM" -UpdateMembership -Verbose

Since we’ve included the -Verbose parameter in the command above, we’ll be presented with lot’s of verbose output. You can also specify the -WhatIf or -Confirm parameters if you’d like. This is how the output looks like when I executed the script in my lab environment:

Here’s a short video clip of the scenario that is described in this post: