List SCCM servers in your infrastructure - SCCM

When you have a large infrastructure, you have lots of moving parts, so you might not want to keep track of your servers on  PostIt or in an xls file. Why? It's just overhead and someone always forgets to update it when pulling a server out or installing a new one.

Let's make the inventory dynamic and base it on the infrastructure component itself... meaning, if you have e.g. an SCCM infrastructure with many sites, many servers with different roles (Management points, Site Servers, Fallback status points...etc.) try to gather it from the SCCM infrastructure itself, instead of having a manual list in an xls file somewhere.
Happened one day that I wanted to get the list of SCCM servers in all the sites (sub-sites) with all their roles (SCCM 2007 and SCCM 2012 as well. If you start browsing in the WMI root of SCCM on the site server and look through some of the tables in the database, you will the way eventually, here is the SQL query which lists all SCCM servers and their roles (with some garbage):
Select distinct SiteSystem, SiteCode, Role FROM dbo.Summarizer_SiteSystem ORDER BY SiteCode

To get rid of the garbage and only see the server names, let's do a regex match and some replacing:
[string]$tmpstr = [regex]::Match($sccmSrvObj.sitesystem, \\\\(\w|\.)+\\$)
$sccmServerName = $tmpstr.replace("\", "")

If you want the roles listed as well:
$sccmQueryResult | ?{$_.SiteSystem -imatch $sccmServerName}| %{$sObject.Role += $_.role + ","}

If you have SCCM 2012, be aware that the database naming changed from SMS_sitecode to CM_sitecode, however they also made the DB name available via WMI:
$sccmDB = (gwmi -ComputerName $sccmsrv -Namespace ROOT\SMS\site_$site -Class SMS_SCI_SiteDefinition).SQLDatabaseName

Here is an example script which takes an SCCM server name and a Site code and lists all servers from that site (and sub-sites) with their SCCM roles - as always, you can add logging, error handling...etc.
If you want to make it more sophisticated, you could just get an Active Directory domain name, read the list of SCCM sites registered and then list all SCCM servers... I may put it into another article...

 

 param(     [string] $sccmSrv = "",  
           [string] $site = "")  
   
   
 #### 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    
 }    
   
 $objCollection = @()  
 $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("\", "")  
 $sccmDB = (gwmi -ComputerName $sccmsrv -Namespace ROOT\SMS\site_$site -Class SMS_SCI_SiteDefinition).SQLDatabaseName  
   
 if(!$sccmDB){  
      $sccmDB = "SMS_" + $site  
 }  
   
 $sccmCompquery = "Select distinct SiteSystem, SiteCode, Role FROM dbo.Summarizer_SiteSystem ORDER BY SiteCode"  
 $sccmQueryResult = execSQLQuery.NET $sccmSQLServer $sccmDB $sccmCompquery  
   
 foreach($sccmSrvObj in $sccmQueryResult){  
      [string]$tmpstr = [regex]::Match($sccmSrvObj.sitesystem, "\\\\(\w|\.)+\\$")  
      $sccmServerName = $tmpstr.replace("\", "")  
   
      # if we haven't recorded the given server  
      if(!($objCollection | ?{$_.SiteServer -ieq $sccmServerName})){  
   
           $sObject = new-Object -typename System.Object  
           $sObject | add-Member -memberType noteProperty -name SiteServer -Value $sccmServerName  
           $sObject | add-Member -memberType noteProperty -name SiteCode -Value $sccmSrvObj.SiteCode  
           $sObject | add-Member -memberType noteProperty -name Role -Value ""  
   
           # go through the rows in the query result and pick up each row where our server is listed  
           $sccmQueryResult | ?{$_.SiteSystem -imatch $sccmServerName}| %{$sObject.Role += $_.role + ","}  
           $sObject.Role = $sObject.Role -replace ",$",""  
        
           $objCollection += $sObject  
      }  
 }  
   
 $objCollection  
   

t