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