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