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
