List Collection membership of computers - SCCM

Continuing from the previous article - where I wrote about SCCM Resource Explorer and how to handle multiple machines when you want to see the inventoried data about them in SCCM - there's another important parameter of computers in SCCM which can be listed: the SCCM Collections computer(s) are member of.

Why is this needed? When you advertise a package or a baseline in Desired Configuration Management, you target collections. You do this for years on a couple of 1000s computers and then one day you get 100 computer names from someone saying, I want to make sure all automated deployments and baselinings stop on these for the next 6 months. Then you would need to look through all 750 collections to see which of them have some of those 100 computers as members. So you want a way to generate a list of collections for each computer.

To do it on the SCCM console, it's not really possible, so let's see a PowerShell way. We need to put some functions and supporting actions into the script, let's have a look at them.

Function for executing SQL query to get data out of SCCM DB:

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

}

Read SCCM site name from SCCM site server remotely:

$site = (gwmi -ComputerName $sccmsrv -Namespace root\sms -Class SMS_ProviderLocation).sitecode

Read SQL server name from SCCM:

$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("\", "")

SQL query to list collection memberships of computers

$memberQuery = "SELECT dbo.v_Collection.Name "

$memberQuery += "FROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID "

$memberQuery += "WHERE (LOWER(dbo.v_FullCollectionMembership.Name) = LOWER('$srv'))"

At the end the script will list the names of computers and list their collection memberships.

The full script looks like:

 param (   [string] $hosts = "",  
       [string] $sccmsrv = "")  
    
  #### 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  
  }  
    
  # 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 = @()  
    
  #### Collate the host list.  
  $hostlist = @($Input)  
  if ($hosts) {  
    if($hosts -imatch " "){  
       $hostsArr = @($hosts.split(" "))  
       $hostlist += $hostsArr  
    }  
    else{  
       $hostlist += $hosts  
    }  
  }  
    
  # going through the list of hosts  
  foreach($srv in $hostlist){  
    $memberQuery = "SELECT dbo.v_Collection.Name "  
    $memberQuery += "FROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID "  
    $memberQuery += "WHERE (LOWER(dbo.v_FullCollectionMembership.Name) = LOWER('$srv'))"  
    
    # running sql query to enumerate list of collections the computer is member of  
    $membership = execSQLQuery $sccmSQLServer "SMS_$site" $memberQuery  
    
    # if we have a result, go through it and build an object collection with the computer name and the collection(s) it is member of  
    if($membership){  
       foreach($enumColl in $membership){  
         $sObject ="" | select ComputerName,Result  
         $sObject.ComputerName = $srv  
         $sObject.Result = $enumColl.name  
         $objColl += $sObject  
       }  
    }  
  }  
    
  $objColl 

May the Force...
t