List members of SCCM collection (with subcollections) - SCCM

This is a bit of an addition to the List members of SCCM collection post. Sometimes a collection has a sub collection which can also contain computers.

To list the members of subcollections, we need to look up the IDs of those collections:
$myQuery = "SELECT coll.name, coll.CollectionID "
$myQuery += "FROM dbo.v_Collection AS coll INNER JOIN dbo.v_CollectToSubCollect AS assoc "
$myQuery += "ON coll.CollectionID = assoc.subCollectionID "
$myQuery += "WHERE (assoc.parentCollectionID = '$myCollectionID')"
$subColls = execSQLQuery.NET $fsccmSQLServer "SMS_$site" $myQuery

If we find subcollections, we can go through each and enumerate the members:
foreach($coll in $fcollections){
   $collname = $coll.name
   $collID = $coll.CollectionID
   Write-Progress -id 1 -parentId 0 -activity " " -Status "Processing Collection $j of $fcollectionsLength : $collname " -PercentComplete ($j/$fcollectionsLength * 100) -currentoperation "Checking collection Maintenace Window criteria."
   $members = getMembers $sccmSQLServer $site $collID
   foreach($mbr in $members){$allmembers += $mbr.Name}
   $j++
}

The full script with parameters looks like this:

 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){   
    write-host "Reading collection ID for name $collectionName...."    
    #$fcollectionID = (gwmi -computer $sccmServer -namespace "root\sms\site_$site" -class "SMS_Collection" | where{$_.Name -eq $collectionName}).CollectionID   
    $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){   
         write-host "Could not find collection $collectionName"    
         exit   
       }   
       else{   
         write-host $fcollectionID    
         return $fcollectionID   
       }   
    }   
    elseif($fcount -gt 1){   
       write-host "More than 1 collection found"    
       exit   
    }   
    else{   
       write-host "Could not find collection $collectionName"    
       #exit   
    }   
     
  }   
     
  #### 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   
  }   
     
  #### Function for enumerating subcollections of a collection   
  function getSubCollections ([string]$fsccmSQLServer, [string]$site, [string] $myCollectionID){   
    write-host "Reading subcollections of collection $myCollectionID...."    
    $myQuery = "SELECT coll.name, coll.CollectionID "   
    $myQuery += "FROM dbo.v_Collection AS coll INNER JOIN dbo.v_CollectToSubCollect AS assoc "   
    $myQuery += "ON coll.CollectionID = assoc.subCollectionID "   
    $myQuery += "WHERE (assoc.parentCollectionID = '$myCollectionID')"   
    $subColls = execSQLQuery $fsccmSQLServer "SMS_$site" $myQuery   
     
    if(!$subColls){   
       write-host "Could not find subcollection of collection $myCollectionID"    
    }   
    else{   
       write-host "OK"    
    }   
    return $subColls   
  }   
     
     
     
     
  # 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   
    foreach($mbr in $members){$allmembers += $mbr.Name}   
     
    # going through all the subcollections of collection   
    $fcollections = getSubCollections $sccmSQLServer $site $collID   
    $j = 1   
    $fcollectionsLength = $fcollections.length   
    foreach($coll in $fcollections){   
       $collname = $coll.name   
       $collID = $coll.CollectionID    
       Write-Progress -id 1 -parentId 0 -activity " " -Status "Processing Collection $j of $fcollectionsLength : $collname " -PercentComplete ($j/$fcollectionsLength * 100) -currentoperation "Checking collection Maintenace Window criteria."   
       $members = getMembers $sccmSQLServer $site $collID   
       foreach($mbr in $members){$allmembers += $mbr.Name}   
       $j++   
    }   
    $allmembers = $allmembers | sort   
    $allmembersUnique = $allmembers | Get-Unique   
    $objColl = $allmembersUnique   
     
  $objColl   

t

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

List SCCM collections and their details - SCCM

When you deal with SCCM deployments, you can quickly realise that the MMC console is no use when large number of objects need to be handled. Collections are a pain when you have loads of them and you just want to create a new one with similar name, or remove a computer from a collection or see if that collection has any advertisement assigned to it...etc. Of course you can use the MMC console, but imagine you have 800+ collections and a collection can have 10000+ computers. Even opening the list of collections takes up to a minute on the console. Handling large number of objects is a burden, but if it was easy, why would we - IT guys - get paid?

Here is what I think is a better solution for listing details of collections:

Function for running SQL query to get the data, same function that you can find one of my previous post List Collection membership of computers - SCCM:
function execSQLQuery

The SQL query, this may need a bit of explanation. We obviously want to query data from the built-in view v_Collection. We need the

• Collection Name - (especially when we search for partial name) 
• Collection ID 
• LastChangeTime - to see when it was changed
• LastRefreshTime - if it's auto updated collection, see when it was refreshed last time
• LastMemberChangeTime - to see when someone added/removed member last time

If -advert switch is used, we need to add a bit more to the query, the Advertisement Name and ID, and we need to join the v_Advertisement view to the v_Collection view based on the CollectionID:

# creating SQL query string based on parameters
$collQuery = "SELECT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_Collection.Comment, "
$collQuery += "dbo.v_Collection.LastChangeTime, dbo.v_Collection.LastRefreshTime, dbo.v_Collection.LastMemberChangeTime "
if($advert){$collQuery += ", dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.AdvertisementID "}
$collQuery += "FROM dbo.v_Collection "
if($advert){$collQuery += "LEFT OUTER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.CollectionID "}

If the collection name is specified, we need a WHERE filer in the sql query which has a LIKE $collname. In this case you can just run the script with either querying full or partial name of a collection (e.g. -collname %servers%):
if($collname){
   $collQuery += " WHERE dbo.v_Collection.Name LIKE '$collname'"

If the collection ID is sepcified with -collid, then the WHERE filter needs to filter on the v_Collection.CollectionID:
elseif($collID){
   $collQuery += " WHERE dbo.v_Collection.CollectionID LIKE '$collID'"

Then we need to run the query and just post the result to the stdout:
# list collections
$queryResult = execSQLQuery.NET $sccmSQLServer "SMS_$site" $collQuery
$queryResult

The full script:

 param (   
    [string] $sccmsrv = "",   # sccm site server name
    [string] $collname = "",  # partial name of the collection with % wild card, e.g. %servers% 
    [string] $collID = "",    # or collection ID
    [switch] $advert = $false)   # if you want to see the details of assigned advertisements, use -advert
     
     
  #### 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 = @()    
   
    
  # creating SQL query string based on parameters   
  $collQuery = "SELECT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_Collection.Comment, "   
  $collQuery += "dbo.v_Collection.LastChangeTime, dbo.v_Collection.LastRefreshTime, dbo.v_Collection.LastMemberChangeTime "   
  if($advert){$collQuery += ", dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.AdvertisementID "}   
  $collQuery += "FROM dbo.v_Collection "   
  if($advert){$collQuery += "LEFT OUTER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.CollectionID "}   
     
  if($collname){   
    Write-Host "Enumerating Collection with criteria: Collection name $collname"   
    $collQuery += " WHERE dbo.v_Collection.Name LIKE '$collname'"   
  }   
  elseif($collID){   
    $collQuery += " WHERE dbo.v_Collection.CollectionID LIKE '$collID'"   
    Write-Host "Enumerating Collection with criteria: Collection ID $collID"   
  }   
     
     
  # list collections   
  $queryResult = execSQLQuery $sccmSQLServer "SMS_$site" $collQuery   
     
  $queryResult   
     
     
   

Hope this is useful. May the Force...
t