Script to Bulk Find/Replace in Collection Membership Query Statements

Author
TickTarry37
New Member
  • Total Posts : 2
  • Scores: 0
  • Reward points: 100
  • Joined: 2010/11/04 09:25:40
  • Status: offline
2010/11/04 09:28:24 (permalink)
0

Script to Bulk Find/Replace in Collection Membership Query Statements

Hi All,
 
I've spent a few hours on this and haven't made any progress so this is a cry for help!
 
I have a client who for the most part bases their collections on OU's. They're going through some rebranding which means that the OU structure is dropping a level with some uniform name changes. I have been looking for a way to perform a bulk find and replace on the query statements for the collection membership rules to no avail. Ideally I just need a way to bulk replace ...SystemOUName = "FQDN/OLD OU/.../..." with ...SystemOUName = "FQDN/NEW OU/OLD OU/.../...".
 
I found this: http://www.snowland.se/2009/03/19/bulk-update-commandlines-in-sccm-programs/ which does something similar for programs, however I browsed WMI for a while looking for the data I need to modify but couldn't find it.
 
Hopefully this will be pretty straight forward for someone in the know! As you can probably tell I'm reasonably new to trying to do things programmatically so go easy on me! :)
 
Thanks.
post edited by TickTarry37 - 2010/11/04 09:32:13
#1

3 Replies Related Threads

    TickTarry37
    New Member
    • Total Posts : 2
    • Scores: 0
    • Reward points: 100
    • Joined: 2010/11/04 09:25:40
    • Status: offline
    Re:Script to Bulk Find/Replace in Collection Membership Query Statements 2010/11/04 10:27:46 (permalink)
    0
    This is as far as I have been able to get by messing around with the sample code, needless to say it doesn't work. Hopefully the issue will be obvious to someone who knows what they're doing!
     
    Set oLocator = CreateObject("WbemScripting.SWbemLocator")
    Set oSccmWmi = oLocator.ConnectServer(".", "root\sms\site_XXX", "", "")
    Set oCollectionRuleQueries = oSccmWmi.ExecQuery("select * from SMS_CollectionRuleQuery where QueryExpression LIKE '%OLD_TEXT%'")
    For Each oCollectionRuleQuery In oCollectionRuleQueries
            WScript.Echo "Rule: " & oCollectionRuleQuery.RuleName
             WScript.Echo "Orginal: " & oCollectionRuleQuery.QueryExpression
             sNewQuery = Replace(oCollectionRuleQuery.QueryExpression, "OLD_TEXT", "NEW_TEXT")
             WScript.Echo "    New: " & sNewQuery
             Set oModQuery = oSccmWmi.Get("SMS_CollectionRuleQuery.QueryID='" & oCollectionRuleQuery.QueryID & "'"& ",RuleName='" & oCollectionRuleQuery.RuleName & "'")
             oModQuery.QueryExpression = sNewQuery
             oModQuery.Put_ ' Comment out this line if you want to test
             Set oModQuery = Nothing
    Next
    #2
    cp07451
    Expert Member
    • Total Posts : 530
    • Scores: -28
    • Reward points: 69070
    • Joined: 2009/09/17 23:15:33
    • Location: San Antonio,TX
    • Status: offline
    Re:Script to Bulk Find/Replace in Collection Membership Query Statements 2010/11/04 10:29:42 (permalink)
    0
    Not sure if there is a script but I know the rules are kept in the Collection_Rules_SQL table. You could try using the SQL 'replace' command for the string.. I would test this first or do a backup though first

    Carl Polk
    SCCM, AV,AD "and other duties as assigned"
    #3
    fault
    Expert Member
    • Total Posts : 272
    • Scores: 17
    • Reward points: 27520
    • Joined: 2008/07/21 03:14:55
    • Location: Sydney, Australia
    • Status: offline
    Re:Script to Bulk Find/Replace in Collection Membership Query Statements 2010/11/07 01:33:30 (permalink)
    0
    Just a note that editing the database is not generally supported ;(

    What you need to do is enumerate all instances of SMS_Collection, get the CollectionRules[] array of SMS_CollectionRule objects, enumerate each SMS_CollectionRule object, test if it is of type SMS_CollectionRuleQuery (it could be SMS_CollectionRuleDirect and you don't want those), then you can work your magic... I'm sure you can figure out the editing part? :) Here's some sample code to get you going on a single collection after you've connected to the WMI provider:

        
    ' Get SMS_Collection
    Set objCollection = objSWbemServices.Get("SMS_Collection.CollectionID='XXX12345'")

    ' Enumerate array of SMS_CollectionRule objects
    For Each objCollectionRule In objCollection.CollectionRules
       
        If objCollectionRule.Path_.Class = "SMS_CollectionRuleQuery" Then
            ' Output the query rule attributes
            WScript.Echo objCollectionRule.QueryID
            WScript.Echo objCollectionRule.RuleName
            WScript.Echo objCollectionRule.QueryExpression
        End If
       
    Next

    post edited by fault - 2010/11/07 01:59:41
    #4
    Jump to:
    © 2018 APG vNext Commercial Version 5.5