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

114_1

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:

114_2

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.

Nickolaj Andersen
Principal Consultant and Enterprise Mobility MVP. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows deployments and Automation. In 2015 Nickolaj was awarded as PowerShell Hero by the community for his script and tools contributions. Author of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService and a frequent speaker at user groups.

(343)

comments
  • Ricsen
    Posted at 03:46 August 20, 2015
    Ricsen
    Reply
    Author

    HI,

    It seems like does not work with special character. I have a query string that contain \\ and it does not seems detecting it. Thank you.

    Regards,

    Ricsen

    • AF
      Posted at 16:31 August 22, 2016
      AF
      Reply
      Author

      That’s probably because backslash is an escape character in powershell.

  • Leave a Reply