List members of SCCM collection - SCCM

If you use SCCM to deploy packages frequently or just handle computers you will need to handle SCCM Collections as well. You can list collections and their attributes easily, but one of the things you probably need the most is to see the members of your collections and then potentially do something else with the list. E.g. get the list of hosts from a collection and run a ping on all to see which one is pingable, or read the OS version via WMI...etc. You can do this if you save the list of members to a text file and run a loop on them...etc.
How can you do it in one go, well, you need a PS script to list the members, which is not that difficult.
First you need to know the ID of the collection from the v_Collection SQL view (I assume you know the name of the collection), this is what the lookupCollID function does in the below script:
$queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select CollectionID from v_Collection where name like '$collectionName'"

Then you can query the members of the collection from the view of the collection (v_CM_RES_COLL_collectionID):
function getMembers([string]$fsccmSQLServer, [string]$site, [string]$fcollID){
$memberQuery = "SELECT Name "
$memberQuery += "FROM dbo.v_CM_RES_COLL_$fcollID "
$memberQuery += "ORDER BY Name"
$membership = execSQLQuery $fsccmSQLServer "SMS_$site" $memberQuery
return $membership
}
Obviously, you need some parameters for the script and the function which reads data from the SCCM database, all explained in previous posts.

The full script:

 param (   
 [string] $sccmsrv = "",   
 [string] $collname = "",   
 [string] $collID = "",   
 [switch] $advert = $false)   
   

 #### 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  
 }  
   
 #### Function for enumerating ID of an SCCM collection  
 function lookupCollID ([string]$fsccmSQLServer, [string]$site, [string] $collectionName){  
      writelog 0 "Reading collection ID for name $collectionName...." "nonew"  
      $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){  
                writelog 2 "Could not find collection $collectionName" "extend"  
                exit  
           }  
           else{  
                writelog 0 $fcollectionID "extend"  
                return $fcollectionID  
           }  
      }  
      elseif($fcount -gt 1){  
           writelog 2 "More than 1 collection found" "extend"  
           exit  
      }  
      else{  
           writelog 2 "Could not find collection $collectionName" "extend"  
      }  
   
 }  
   
 #### Function for enumerating all SCCM collection memberships of a server  
 function getMembers([string]$fsccmSQLServer, [string]$site, [string]$fcollID){  
      $memberQuery = "SELECT Name "  
      $memberQuery += "FROM dbo.v_CM_RES_COLL_$fcollID "  
      $memberQuery += "ORDER BY Name"  
   
      $membership = execSQLQuery $fsccmSQLServer "SMS_$site" $memberQuery  
      return $membership  
 }  

  
 # read the SCCM site name of the SCCM site server  
 $site = (gwmi -ComputerName $sccmsrv -Namespace root\sms -Class SMS_ProviderLocation).sitecode  
   
 # enumerating SQL server name for the given SCCM site server  
 $sccmCompquery = gwmi -q "Select distinct SiteSystem, Role, SiteCode FROM SMS_SiteSystemSummarizer where role = 'SMS SQL Server' and siteCode = '$site' ORDER BY SiteCode" -namespace "root\sms\site_$site" -ComputerName $sccmsrv  
 [string]$tmpstr = [regex]::Match($sccmCompquery.sitesystem, "\\\\\w+\\$")  

 $sccmSQLServer = $tmpstr.replace("\", "")  

 $objColl = @()  
   
 ### -getmembers  
 write-host "Listing members of collection...."  
 $allmembers = @()  
 if(!$collID -and $collName){  
      $collID = lookupCollID $sccmSQLServer $site $collName  
      if(!$collID){  
           write-host "Could not find ID for $collName"  
           exit  
      }  
 }  
   
 $members = getMembers $sccmSQLServer $site $collID  
 $members  
   
   

May the Force...
t