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
