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