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.

(2163)

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 to Ricsen
    Cancel Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.