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.
Chief Technical Architect and Enterprise Mobility MVP since 2016. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows devices and deployments including automation. Awarded as PowerShell Hero in 2015 by the community for his script and tools contributions. Creator of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService to name a few. Frequent speaker at conferences and user groups.