Rather than connecting to each computer and extracting the information (slow) get it from the straight from the database....
[CmdletBinding()]
param (
[string] $hosts = "",
[string] $sccmsrv = "",
[Parameter(Mandatory=$False,Position=3)]
[string] $path = ""
)
$usage = "USAGE: List-AdvertCollMembershipSCCM.ps1 -sccmsrv SCCMSERVER -hosts 'host1 host2 host3' -path 'c:\temp\Outfile.csv'"
if ($host -and $sccmsrv){
Write-Host ""
Write-Host -ForegroundColor Yellow "SCCM Server: $sccmsrv"
Write-Host -ForegroundColor Yellow "Looking at hosts: $hosts"
#### 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_FullCollectionMembership.Name AS 'Hostname', dbo.v_GS_SYSTEM.ResourceID, dbo.v_Collection.Name AS 'Collection Name', dbo.v_Collection.CollectionID, dbo.v_FullCollectionMembership.IsDirect "
$memberQuery += "FROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN dbo.v_GS_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_SYSTEM.ResourceID "
$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 = $enumColl | select Hostname, ResourceID, "Collection Name", CollectionID, IsDirect
$objColl +=$sObject
}
}
}
if ($objColl){
if ($path){
$objColl | ft -AutoSize
Write-Host -ForegroundColor Yellow "Exporting to results to: $path"
$objColl | Export-Csv $path -NoTypeInformation
}
else{
$objColl | ft -AutoSize
Write-Host -ForegroundColor Green "Use the -path argument in the command line to export output to csv to display"
Write-Host -ForegroundColor Green "the 'IsDirect' Information"
Write-Host ""
}
}
Else {
foreach ($Hostname in $hostlist){
Write-Host ""
Write-Host -ForegroundColor Yellow "The host $hostname is not a member of any collection"
}
Write-Host -ForegroundColor Yellow "Check you have entered the correct hostname and try again"
}
}
else {
Write-Host ""
Write-Host -ForegroundColor Yellow $usage
}
Execution:-
PS C:\> ListSCCMCollections.ps1 -sccmsrv SCCMSERVER -hosts host1,host2,host3 -path "c:\temp\Outfile.csv"
or
PS C:\> Get-Content hostlist.txt | ListSCCMCollections.ps1 -sccmsrv SCCMSERVER -path c:\temp\Outfile.csv
Getting the requested info straight from SQL:-
SELECT dbo.v_FullCollectionMembership.Name AS 'Hostname', dbo.v_GS_SYSTEM.ResourceID, dbo.v_Collection.Name AS 'Collection Name', dbo.v_Collection.CollectionID,
dbo.v_FullCollectionMembership.IsDirect
FROM dbo.v_FullCollectionMembership INNER JOIN
dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_SYSTEM.ResourceID
WHERE (LOWER(dbo.v_FullCollectionMembership.Name) = LOWER('Hostname'))
This script can be used with any SQL query on the SCCM database. All you need to do is update the SQL query in the script. i.e. the $memberQuery array (if you spread the query over a couple of lines like below, be sure to leave a space at the end of each line with exception to the last).
For example; If you'd like the script to show the clients collections with live advertisements assigned to them change the SQL query in the $memberQuery array to:-
$memberQuery = "SELECT dbo.v_FullCollectionMembership.Name AS 'Hostname', dbo.v_GS_SYSTEM.ResourceID, dbo.v_Collection.Name AS 'Collection Name',dbo.v_Collection.CollectionID, dbo.v_FullCollectionMembership.IsDirect, dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName "
$memberQuery += "FROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN dbo.v_GS_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.CollectionID "
$memberQuery += "WHERE (LOWER(dbo.v_FullCollectionMembership.Name) = LOWER('$srv'))"
and the $sObject variable to:-
$sObject = $enumColl | select Hostname, ResourceID, "Collection Name", CollectionID, IsDirect, AdvertisementID, AdvertisementName
Complete script to view client collections with live advisements (execution the same as before):-
[CmdletBinding()]
param (
[string] $hosts = "",
[string] $sccmsrv = "",
[Parameter(Mandatory=$False,Position=3)]
[string] $path = ""
)
$usage = "USAGE: List-AdvertCollMembershipSCCM.ps1 -sccmsrv SCCMSERVER -hosts 'host1 host2 host3' -path 'c:\temp\Outfile.csv'"
if ($host -and $sccmsrv){
Write-Host ""
Write-Host -ForegroundColor Yellow "SCCM Server: $sccmsrv"
Write-Host -ForegroundColor Yellow "Looking at hosts: $hosts"
#### 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_FullCollectionMembership.Name AS 'Hostname', dbo.v_GS_SYSTEM.ResourceID, dbo.v_Collection.Name AS 'Collection Name',dbo.v_Collection.CollectionID, dbo.v_FullCollectionMembership.IsDirect, dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName "
$memberQuery += "FROM dbo.v_FullCollectionMembership INNER JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN dbo.v_GS_SYSTEM ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.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 = $enumColl | select Hostname, ResourceID, "Collection Name", CollectionID, IsDirect, AdvertisementID, AdvertisementName
$objColl +=$sObject
}
}
}
if ($objColl){
if ($path){
$objColl | ft -AutoSize
Write-Host -ForegroundColor Yellow "Exporting to results to: $path"
$objColl | Export-Csv $path -NoTypeInformation
}
else{
$objColl | ft -AutoSize
Write-Host -ForegroundColor Green "Use the -path argument in the command line to export output to csv to display"
Write-Host -ForegroundColor Green "the header 'AdvertisementName'"
Write-Host ""
}
}
Else {
foreach ($Hostname in $hostlist){
Write-Host ""
Write-Host -ForegroundColor Yellow "The host $hostname is not a member of any collection with live advertisements"
}
Write-Host -ForegroundColor Yellow "Check you have entered the correct hostname and try again"
}
}
else {
Write-Host ""
Write-Host -ForegroundColor Yellow $usage
}
