myITforum and Windows IT Pro Forums

 Script to Bulk Find/Replace in Collection Membership Query Statements

Author Message
TickTarry37

  • Total Posts : 2
  • Scores: 0
  • Reward points : 100
  • Joined: 11/4/2010
  • Status: offline
Script to Bulk Find/Replace in Collection Membership Query Statements Thursday, November 04, 2010 9:28 AM (permalink)
0
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.
<message edited by TickTarry37 on Thursday, November 04, 2010 9:32 AM>
 
#1
    TickTarry37

    • Total Posts : 2
    • Scores: 0
    • Reward points : 100
    • Joined: 11/4/2010
    • Status: offline
    Re:Script to Bulk Find/Replace in Collection Membership Query Statements Thursday, November 04, 2010 10:27 AM (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

      • Total Posts : 529
      • Scores: -28
      • Reward points : 52930
      • Joined: 9/17/2009
      • Location: San Antonio,TX
      • Status: offline
      Re:Script to Bulk Find/Replace in Collection Membership Query Statements Thursday, November 04, 2010 10:29 AM (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

        • Total Posts : 272
        • Scores: 17
        • Reward points : 27520
        • Joined: 7/21/2008
        • Location: Sydney, Australia
        • Status: offline
        Re:Script to Bulk Find/Replace in Collection Membership Query Statements Sunday, November 07, 2010 1:33 AM (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    
         

        <message edited by fault on Sunday, November 07, 2010 1:59 AM>
         
        #4
          Online Bookmarks Sharing: Share/Bookmark

          Jump to:

          Current active users

          There are 0 members and 1 guests.

          Icon Legend and Permission

          • New Messages
          • No New Messages
          • Hot Topic w/ New Messages
          • Hot Topic w/o New Messages
          • Locked w/ New Messages
          • Locked w/o New Messages
          • Read Message
          • Post New Thread
          • Reply to message
          • Post New Poll
          • Submit Vote
          • Post reward post
          • Delete my own posts
          • Delete my own threads
          • Rate post

          2000-2014 ASPPlayground.NET Forum Version 3.9