24 February, 2013

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


10 comments:

  1. could you please help me how to excute the script....

    is there any editing required or any parameters to be passed to the script??

    ReplyDelete
  2. Hi Vimal, you can just save it with name e.g. listCollections.ps1
    then run it like this:

    PS C:\> listCollections.ps1 -sccmsrv MYSCCMSERVER -hosts host1,host2,host3

    or

    PS C:\> gc hostlist.txt | listCollections.ps1 -sccmsrv MYSCCMSERVER

    ReplyDelete
  3. Hello

    When running the script, I get an (/SMS_'sitecode' not accessible) error.
    Any idea ?

    ReplyDelete
  4. It can be loads of things:
    - WMI issue on the server
    - insufficient permissions (your user account doesn't have rights in SCCM)
    -the given sccm server is not a site server...
    etc.

    verify all these things and see if there's anything missing. You can also run this line in PS to see if the sitecode can be read:
    (gwmi -ComputerName YOURSCCMSERVER -Namespace root\sms -Class SMS_ProviderLocation).sitecode

    ReplyDelete
  5. I have no wmi issues.
    I have full rights on the server, SQL and in SCCM
    The given server is correct.

    This script does not work. I run the last line you stated above to try and al I get from the machine is a blinking prompt.

    ReplyDelete
  6. If this line does not return the site code of the client, then you have WMI issue:
    (gwmi -ComputerName YOURSCCMSERVER -Namespace root\sms -Class SMS_ProviderLocation).sitecode

    This could mean issue with WMI reporsitory or could be only with the root\sms namespace. I'd try to reinstall the client and see how it goes after that.

    ReplyDelete
  7. I tried above, including the WMI test, which correctly responds with my site code. Still getting (/SMS_HLP not accessible). Any other thoughts?

    ReplyDelete
    Replies
    1. Could you send me the full error message?

      Delete
    2. I was having the same problem. I discovered that the regex in the execSQLQuery would not return the name of my SQL server. I modified it to [string]$tmpstr = [regex]::Match($sccmCompquery.sitesystem, "\\\\[\w\.\-]+\\$") . Also, in my installation the database being queried in the foreach loop was not SMS_$sitecode but CM_$sitecode.

      Delete
    3. Worked like a charm -- thanks Rick

      Delete