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.
You can get your hands on the script from this TechNet Gallery page.
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:
If you have any questions or need any help with the script, write a comment or send me an email and I’ll try to assist.