List SCCM collections and their details - SCCM

When you deal with SCCM deployments, you can quickly realise that the MMC console is no use when large number of objects need to be handled. Collections are a pain when you have loads of them and you just want to create a new one with similar name, or remove a computer from a collection or see if that collection has any advertisement assigned to it...etc. Of course you can use the MMC console, but imagine you have 800+ collections and a collection can have 10000+ computers. Even opening the list of collections takes up to a minute on the console. Handling large number of objects is a burden, but if it was easy, why would we - IT guys - get paid?

Here is what I think is a better solution for listing details of collections:

Function for running SQL query to get the data, same function that you can find one of my previous post List Collection membership of computers - SCCM:
function execSQLQuery

The SQL query, this may need a bit of explanation. We obviously want to query data from the built-in view v_Collection. We need the

• Collection Name - (especially when we search for partial name) 
• Collection ID 
• LastChangeTime - to see when it was changed
• LastRefreshTime - if it's auto updated collection, see when it was refreshed last time
• LastMemberChangeTime - to see when someone added/removed member last time

If -advert switch is used, we need to add a bit more to the query, the Advertisement Name and ID, and we need to join the v_Advertisement view to the v_Collection view based on the CollectionID:

# creating SQL query string based on parameters
$collQuery = "SELECT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_Collection.Comment, "
$collQuery += "dbo.v_Collection.LastChangeTime, dbo.v_Collection.LastRefreshTime, dbo.v_Collection.LastMemberChangeTime "
if($advert){$collQuery += ", dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.AdvertisementID "}
$collQuery += "FROM dbo.v_Collection "
if($advert){$collQuery += "LEFT OUTER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.CollectionID "}

If the collection name is specified, we need a WHERE filer in the sql query which has a LIKE $collname. In this case you can just run the script with either querying full or partial name of a collection (e.g. -collname %servers%):
if($collname){
   $collQuery += " WHERE dbo.v_Collection.Name LIKE '$collname'"

If the collection ID is sepcified with -collid, then the WHERE filter needs to filter on the v_Collection.CollectionID:
elseif($collID){
   $collQuery += " WHERE dbo.v_Collection.CollectionID LIKE '$collID'"

Then we need to run the query and just post the result to the stdout:
# list collections
$queryResult = execSQLQuery.NET $sccmSQLServer "SMS_$site" $collQuery
$queryResult

The full script:

 param (   
    [string] $sccmsrv = "",   # sccm site server name
    [string] $collname = "",  # partial name of the collection with % wild card, e.g. %servers% 
    [string] $collID = "",    # or collection ID
    [switch] $advert = $false)   # if you want to see the details of assigned advertisements, use -advert
     
     
  #### 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 = @()    
   
    
  # creating SQL query string based on parameters   
  $collQuery = "SELECT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_Collection.Comment, "   
  $collQuery += "dbo.v_Collection.LastChangeTime, dbo.v_Collection.LastRefreshTime, dbo.v_Collection.LastMemberChangeTime "   
  if($advert){$collQuery += ", dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.AdvertisementID "}   
  $collQuery += "FROM dbo.v_Collection "   
  if($advert){$collQuery += "LEFT OUTER JOIN dbo.v_Advertisement ON dbo.v_Collection.CollectionID = dbo.v_Advertisement.CollectionID "}   
     
  if($collname){   
    Write-Host "Enumerating Collection with criteria: Collection name $collname"   
    $collQuery += " WHERE dbo.v_Collection.Name LIKE '$collname'"   
  }   
  elseif($collID){   
    $collQuery += " WHERE dbo.v_Collection.CollectionID LIKE '$collID'"   
    Write-Host "Enumerating Collection with criteria: Collection ID $collID"   
  }   
     
     
  # list collections   
  $queryResult = execSQLQuery $sccmSQLServer "SMS_$site" $collQuery   
     
  $queryResult   
     
     
   

Hope this is useful. May the Force...
t

List Collection membership of computers - SCCM

Continuing from the previous article - where I wrote about SCCM Resource Explorer and how to handle multiple machines when you want to see the inventoried data about them in SCCM - there's another important parameter of computers in SCCM which can be listed: the SCCM Collections computer(s) are member of.

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