07 December, 2013

Add / Remove members of collections - SCCM


Continuing on the managing collections subject in SCCM (List members of SCCM collection, List SCCM collections and their details - SCCM ) I think the next natural step people want to do with a collection - in case you are thinking about using SCCM in a large environment - is to add/remove computers to/from a collection and at the same time, forget about the very colorful, but sluggish mmc console.
Obviously, this is only useful if you are not thinking about creating conditions/filters for a collection membership but you want to manually add loads of servers into a collection based on seemingly no commonalities between them or - the contrary - too many commonalities between them.

There's such a case when you have 8000 servers without a particular software component, but you don't want to install it on all of them at the same time, you want to do it in phases. Why? Because I think it's better to screw up 500 servers spread across the world than either 500 in one location (taking out the service in that location) or all 8000 of them.
You could argue, if you break 500 servers, you better update your CV so why not be brave and effective and target all 8000? I'll leave it with your capable decision making.

If I want to add/remove computers to/from a collection, I will need a couple of things:
  • The ID of the collection - if you want to be nice, you make the script to looks this up based on the collection name, but you can be rude if you want to and make people remember hex number - I think DNS should not have been invented, people should NOT be lazy and they should remember IP addresses!
    $queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select CollectionID from v_Collection where name like '$collectionName'"
  • The ID of the computer being added/removed:
    $queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select ResourceID from v_R_System where name0 = '$srv'"
    $computerResID = $queryResult.ResourceID
  • Bind the WMI instance of the collection to be able to invoke methods (like AddMembershipRule, DeleteMembershipRule):
    $global:mc = [wmi]"\\$sccmsrv\root\sms\site_$($site):SMS_Collection.CollectionID='$collID'"

If you look at the two functions addComputerToCollection and removeComputerFromCollection  you can see how the wmi methods can be used. There's only one twist in the addComputerToCollection to make sure the given computer exists in SCCM before trying to add an empty membership. This is required because the AddMembershipRule method does not have a very good exception handling, so need to implement it in the script instead.

Needless to say, these are snippets from a much bigger script I use for managing collections which has additional logging, a bit more exception handling, more parameters to be able to use it against multiple SCCM sites...etc. to make it a complete tool. However, the published code snippets can be used as individual scripts.

A script to add/remove computers to/from SCCM collections could look like this:

 param (   
    [string] $hosts = "",  
    [switch] $addcomputer = $false,  
    [switch] $removecomputer = $false,  
    [string] $sccmsrv = "r2d2SCCM",  
    [string] $site = "SW1",  
    [string] $collName = "",  
    [string] $collID = "",  
    [string] $log = "")  
   
   
 #### Function for adding a computer to an SCCM collection  
 function addComputerToCollection ([string]$collectionID, [string]$SccmServer, $fsccmSQLServer, [string]$site, [string]$srv){  
    $found = $false  
   
    # checking if the direct membership for the computer exist or not  
    foreach($member in $global:mc.CollectionRules){  
       if($member.RuleName -ieq $srv){  
          $found = $true  
          break  
       }  
    }  
   
    if($found){  
       $retVal = "host has already got direct membership"  
    }  
    else{  
   
       # getting resource ID of the computer  
       $queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select ResourceID from v_R_System where name0 = '$srv'"  
       $computerResID = $queryResult.ResourceID  
   
       if($computerResID){  
   
       # creating DirectRule  
          $objColRuledirect = [WmiClass]"\\$SccmServer\ROOT\SMS\site_$($site):SMS_CollectionRuleDirect"  
          $objColRuleDirect.psbase.properties["ResourceClassName"].value = "SMS_R_System"  
          $objColRuleDirect.psbase.properties["ResourceID"].value = $computerResID  
   
          #target collection  
          $InParams = $global:mc.psbase.GetMethodParameters('AddMembershipRule')  
          $InParams.collectionRule = $objColRuleDirect  
          $R = $global:mc.PSBase.InvokeMethod('AddMembershipRule', $inParams, $Null)  
   
          if($r.ReturnValue -eq 0){$retVal = "OK" }  
          else   {$retVal = "Err"}  
       }  
       else{  
       $retVal = "Computer is not in SCCM DB"  
       }  
    }  
    return $retVal  
 }  
   
   
 #### Function for a computer from an SCCM collection  
 function removeComputerFromCollection ([string]$collectionID, [string]$srv){  
    $found = $false  
   
    foreach($member in $global:mc.CollectionRules){  
       if($member.RuleName -ieq $srv){  
          $res = $global:mc.deletemembershiprule($member)  
          $found = $true  
          break  
       }  
    }  
    if($res.ReturnValue -eq 0){$retVal = "OK" }  
    else   {$retVal = "Err"}  
   
    if(!$found){$retVal = "No direct membership of $srv in collection $collectionID"}  
    return $retVal  
 }  
   
   
   
 #### Function for enumerating ID of an SCCM collection  
 function lookupCollID ([string]$fsccmSQLServer, [string]$site, [string] $collectionName){  
    $queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select CollectionID from v_Collection where name like '$collectionName'"  
    $fcount = ($queryResult | Group-Object -Property CollectionID).count  
   
    if($fcount -eq 1){  
       $fcollectionID = $queryResult.CollectionID  
   
       if(!$fcollectionID){  
          exit  
       }  
       else{  
          return $fcollectionID  
       }  
    }  
    elseif($fcount -gt 1){  
       exit  
    }  
    else{  
       exit  
    }  
 }  
   
   
   
 #### Function for executing a SQL query with integrated authentication    
 function execSQLQuery ([string]$fSQLServer, [string]$db, [string]$query){    
    $objConnection = New-Object System.Data.SqlClient.SqlConnection    
    $objConnection.ConnectionString = "Server = $fSQLServer; Database = $db; trusted_connection=true;"    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $query, $objConnection    
    trap {Write-Host -ForegroundColor 'red' "($sqlsrv/$db not accessible)";continue}    
    $SqlCmd.Connection.Open()    
   
    if ($SqlCmd.Connection.State -ine 'Open') {    
       $SqlCmd.Connection.Close()    
       return    
    }    
    $dr = $SqlCmd.ExecuteReader()    
   
    #get the data    
    $dt = new-object "System.Data.DataTable"    
    $dt.Load($dr)    
    $SqlCmd.Connection.Close()    
    $dr.Close()    
    $dr.Dispose()    
    $objConnection.Close()    
    return $dt    
 }    
   
   
   
 ##################################################### Body #####################################################  
   
 # if site is not specified, let's get it from the SCCM server itself  
 if(!$site){  
    $site = (gwmi -ComputerName $sccmsrv -Namespace root\sms -Class SMS_ProviderLocation).sitecode  
 }  
   
   
 #### Collate the host list.  
 $hostlist = @($Input)  
 if ($hosts) {  
    if($hosts -imatch " "){  
       $hostsArr = @($hosts.split(" "))  
       $hostlist += $hostsArr  
    }  
    else{  
       $hostlist += $hosts  
    }  
 }  
   
 # if -collName, we need to enumerate the collection ID  
 if(!$collID -and $collName){  
    $collID = lookupCollID $sccmsrv $site $collName  
 }  
   
 if($($hostlist.length) -gt 0){  
    $global:mc = ""  
    #Binding collection $collID  
    $global:mc = [wmi]"\\$sccmsrv\root\sms\site_$($site):SMS_Collection.CollectionID='$collID'"  
   
    if($global:mc){  
   
       $hostlistlength = $hostlist.length  
       $k = 1  
       $objColl = @()  
   
       foreach ($srv in $hostlist) {  
          $result = $result2 = ""  
   
          if($srv -ne ""){       # if the hostname is not empty  
             Write-Progress -activity "Performing checks" -Status "Processing host $k of $hostlistlength : $srv " -PercentComplete ($k/$hostlistlength * 100) -currentoperation "checking Client state..."  
   
             # if -addcomputer, then we need to add computers to collections (direct membership)  
             if($addcomputer){  
                $sObject = new-Object -typename System.Object  
                $sObject | add-Member -memberType noteProperty -name Hostname -Value $srv  
   
                # adding host to collection $collName $collID  
                $result = addComputerToCollection $collID $sccmsrv $sccmsrv $site $srv  
   
                $sObject | add-Member -memberType noteProperty -name Result -Value $result  
                $objColl += $sObject  
             }  
   
             # if -removecomputer, then we need to remove computers from collections (direct membership)  
             if($removecomputer){  
                $sObject = new-Object -typename System.Object  
                $sObject | add-Member -memberType noteProperty -name Hostname -Value $srv  
   
                # removing host from collection $collName $collID  
                $result = removeComputerFromCollection $collID $srv  
   
                $sObject | add-Member -memberType noteProperty -name Result -Value $result  
                $objColl += $sObject  
             }  
          }  
          $k++  
       }  
    }  
    else{  
    "Could not bind collection"  
    }  
 }  
 else{  
    "No hostname or hostlist is specified."  
 }  
   
 $objColl  

t


13 comments:

  1. Can you help me to get the instructions, syntax to run this? Really great stuff

    1. Is it compatible with SCCM 2007
    2. Can I use it only to remove?

    ReplyDelete
  2. sure, you can run it remotely, save the script and run it like this:
    yourscriptname.ps1 -sccm YOURSITESERVER -site YOURSCCMSITE -collnam COLLECTIONAME -host SCCMCLIENTNAME

    it is compatible with sccm 2007 and should be working with 2012 as well.

    ReplyDelete
  3. HI, I tried to run your script, it said "Bad numeric constant: 1..
    At :line:1 char:3
    + 1.p <<<< s1 -sccm SCCMSRV -site SCM -collID SCM002F5 -host sltest4

    do you know how I fix it?

    Also, is this to add it to collection by default? or I have to change it in "$removecomputer" to $true?

    thanks so much!

    ReplyDelete
    Replies
    1. Hi Ray,

      the error above seems to be a powershell syntax error and seems to me that the script was named or called incorrectly from powerhell. What was the name of the script you ran and what was the exact syntax?

      The script doesn't do anything by default, you have to specify either the -addcomputer or -removecopmuter switch. Hope this helps.
      t

      Delete
  4. What is the complete syntax for deleting computers as # $env:computername # from a specific Collection (CEN00011) on a particular SCCM Site server (SCCM01) with a specific site Code (LAB)? Plz help.

    ReplyDelete
    Replies
    1. If you look at the code above, line 60-66 does this, for $env:computername, I'd do it like this:
      $global:mc = [wmi]"\\SCCM001\root\sms\site_LAB:SMS_Collection.CollectionID='CEN00011'"


      foreach($member in $global:mc.CollectionRules){
      if($member.RuleName -ieq $env:computername){
      $res = $global:mc.deletemembershiprule($member)
      $found = $true
      break
      }
      }

      Delete
  5. Does this accept wildcards? For example: can you remove all computers from a collection?

    Also can you declare $site, $collName, and $collID and avoid using these switches?

    ReplyDelete
    Replies
    1. It doesn't accept wildcards for the computer names in this form, but you can make it so by just changing the evaluation when checking $member.RuleName and use -match or -like.

      For the parameters, you can specify default value in the param section so you don't need to specify it at runtime.

      Delete
  6. Thank you for sharing. I was only in need of a script for removing a computer from a collection, so I modified the script and created a (to me: easier) function just for this:
    Function Remove-CMCollectionMembership {
    <#
    .SYNOPSIS
    Remove-CMCollectionMembership.ps1

    .DESCRIPTION
    Removes a member from a collection

    .NOTES
    Created : December, 2013 tompa
    Modified : August 2015 PowerShellGirl

    .LINK
    http://tompaps.blogspot.no/2013/12/add-remove-members-of-collections-sccm.html
    #>
    Param(
    $SccmServer,
    $Site,
    $collectionID,
    $ComputerName
    )

    #Find collection
    $global:mc = [wmi]"\\$SccmServer\root\sms\site_$($site):SMS_Collection.CollectionID='$collectionID'"

    #RuleName
    $ruleName = $ComputerName + " - SMSTS" #You can remove + " - SMSTS" if your rulenames only contains the computername

    #Find rule
    $member = $global:mc.CollectionRules.RuleName | Where-Object {$_ -eq $ruleName}

    #Remove rule from collection if rule is found
    If ($member) {

    #Remove rule from collection
    $res = $global:mc.deletemembershiprule($member)

    return $res.ReturnValue
    }
    #Rule not found
    Else {
    return 1168 #"No direct membership of $ComputerName in collection $collectionID"
    }
    }

    ReplyDelete
  7. you are thanked many, many times!

    I did have to modify this line:
    $global:mc = [wmi]"\\$sccmsrv\root\sms\site_$($site):SMS_Collection.CollectionID='$collID'"
    to be
    $global:mc = gwmi -namespace "root\sms\site_$site" -class "sms_collection" | where {$_.collectionid -eq $collID}

    It simply would not enumerate the property CollectionId.

    I'm running SCCM 2007 (4.00.6487.2000)
    and PowerShell v2. (No, I can't go higher in my environment.)

    Brad.

    ReplyDelete
  8. Do we have the powershell script to delete the collections in bulk from SCCM 2012.

    ReplyDelete
    Replies
    1. if you have a list of collection names in a text file, e.g.:
      collection1
      collection2
      collection3

      You can do it like this

      get-content c:\collectionlist.txt | %{addremovecollection.ps1 -collname $_}

      Delete