29 April, 2013

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

5 comments:

  1. Here's what I came up with. It does require you to use the ConfigMgr 2012 SP1 module which you need to run in (x86) mode:

    function Get-CollectionMembers{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory=$True)]
    [String]$CollectionName
    )

    $CollectionID = Get-CMDeviceCollection -Name $CollectionName | Select CollectionID
    $ComputerList = Get-CMDevice -CollectionId $CollectionID.CollectionID | Select -Property Name

    ForEach ($ComputerObject In $ComputerList){

    Get-ADComputer -Identity $ComputerObject.Name -Properties CanonicalName | Select Name,CanonicalName

    }
    }

    ReplyDelete
  2. There is a significantly easier way to do this.
    Get-CMDevice -CollectionName "My Collection" | Select Name

    ReplyDelete
    Replies
    1. Indeed, in SCCM 2012 things are getting easier with the built-in cmdlets. This article was originally written for SCCM 2007.:
      http://technet.microsoft.com/en-us/library/jj821831(v=sc.20).aspx

      Delete
  3. Is there a WMI way to do this? To make a tool for people who don't have access to the SCCM 2012 Console?

    ReplyDelete
    Replies
    1. WMI will have the same delegation model as the console. However, you can grant read access on the SQL instance and the DB for a user running this, you can strip out the WMI queries for the site and the server name enumeration and that would mean you only do SQL queries.

      Delete