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
