29 April, 2013

List members of SCCM collection - SCCM

If you use SCCM to deploy packages frequently or just handle computers you will need to handle SCCM Collections as well. You can list collections and their attributes easily, but one of the things you probably need the most is to see the members of your collections and then potentially do something else with the list. E.g. get the list of hosts from a collection and run a ping on all to see which one is pingable, or read the OS version via WMI...etc. You can do this if you save the list of members to a text file and run a loop on them...etc.
How can you do it in one go, well, you need a PS script to list the members, which is not that difficult.
First you need to know the ID of the collection from the v_Collection SQL view (I assume you know the name of the collection), this is what the lookupCollID function does in the below script:
$queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select CollectionID from v_Collection where name like '$collectionName'"

Then you can query the members of the collection from the view of the collection (v_CM_RES_COLL_collectionID):
function getMembers([string]$fsccmSQLServer, [string]$site, [string]$fcollID){
$memberQuery = "SELECT Name "
$memberQuery += "FROM dbo.v_CM_RES_COLL_$fcollID "
$memberQuery += "ORDER BY Name"
$membership = execSQLQuery $fsccmSQLServer "SMS_$site" $memberQuery
return $membership
}
Obviously, you need some parameters for the script and the function which reads data from the SCCM database, all explained in previous posts.

The full script:
 param (   
 [string] $sccmsrv = "",   
 [string] $collname = "",   
 [string] $collID = "",   
 [switch] $advert = $false)   
   

 #### 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  
 }  
   
 #### Function for enumerating ID of an SCCM collection  
 function lookupCollID ([string]$fsccmSQLServer, [string]$site, [string] $collectionName){  
      writelog 0 "Reading collection ID for name $collectionName...." "nonew"  
      $queryResult = execSQLQuery $fsccmSQLServer "SMS_$site" "select CollectionID from v_Collection where name like '$collectionName'"  
      $fcount = ($queryResult | Group-Object -Property CollectionID).count  
   
      if($fcount -eq 1){  
           $fcollectionID = $queryResult.CollectionID  
   
           if(!$fcollectionID){  
                writelog 2 "Could not find collection $collectionName" "extend"  
                exit  
           }  
           else{  
                writelog 0 $fcollectionID "extend"  
                return $fcollectionID  
           }  
      }  
      elseif($fcount -gt 1){  
           writelog 2 "More than 1 collection found" "extend"  
           exit  
      }  
      else{  
           writelog 2 "Could not find collection $collectionName" "extend"  
      }  
   
 }  
   
 #### Function for enumerating all SCCM collection memberships of a server  
 function getMembers([string]$fsccmSQLServer, [string]$site, [string]$fcollID){  
      $memberQuery = "SELECT Name "  
      $memberQuery += "FROM dbo.v_CM_RES_COLL_$fcollID "  
      $memberQuery += "ORDER BY Name"  
   
      $membership = execSQLQuery $fsccmSQLServer "SMS_$site" $memberQuery  
      return $membership  
 }  

  
 # 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 = @()  
   
 ### -getmembers  
 write-host "Listing members of collection...."  
 $allmembers = @()  
 if(!$collID -and $collName){  
      $collID = lookupCollID $sccmSQLServer $site $collName  
      if(!$collID){  
           write-host "Could not find ID for $collName"  
           exit  
      }  
 }  
   
 $members = getMembers $sccmSQLServer $site $collID  
 $members  
   
   

May the Force...
t

27 April, 2013

List details of installed hotfixes remotely - OS

Hotfixing again. In one of the previous articles, I wrote about how to enumerate the list of installed patches on remote hosts and then find out the differences. The only caveat is that Get-Hotfix cmdlet (which basically uses WMI class Win32_QuickFixEngineering) doesn't contain too much information on the particular hotfix itself apart from the KB number.

However, the Windows Update Agent (WUA) has an API which can be access from PowerShell via COM (Microsoft.Update.Session) and can be called remotely to get almost all fields of an installed patch that you can see on the GUI:

An installed hotfix shown on the GUI of WUA

















But then it doesn't give you some useful data, e.g. who installed that fix, which can be found in the data read from WMI:

An installed hotfix listed by Get-Hotfix or Win32_QuickFixEngineering














Fortunately, you can get the data from the WUA API and WMI as well and merge them easily in PowerShell.
First things first, getting the list of fixes from WUA and store it in $HFDetails:
$HFobj = [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$srv))
$objSearcher= $HFobj.CreateUpdateSearcher()
$allupdates = $objSearcher.GetTotalHistoryCount()
$HFDetails = $objSearcher.QueryHistory(0,$allupdates)

Then get the list of fixes from WMI:
Get-HotFix -ComputerName $srv | %{

Create an object with properties we are interested in and want to record from the two data sets:
$obj = "" | select ComputerName,HotfixID,InstalledOn,InstalledBy,Title,Description

The HotfixID, InstalledOn, InstalledBy fields come from the WMI data so I can add them to my object straight away:
$obj.HotfixID = $_.Hotfixid
$obj.InstalledOn = $_.Installedon
$obj.InstalledBy = $_.Installedby

Let's stop here a bit. There's a way to define a psobject with the list of properties and give them value at the same time, so why would I define it in one line and then add value to each attribute afterwards. The only reason is the order of attributes. When you define a psobject with a hash table, the order it displays the properties afterwards is random:
$obj = New-Object PSObject -Property @{ComputerName=$srv;HotfixID=$_.HotfixID...

Anyway, so we have 2 things we want from the WUA API. The title of the patch and the Description of it, which basically means finding the KB number in the WUA dataset and read the title and the Description:
$obj.Description = ($HFDetails | ?{$_.Title -imatch $obj.HotfixID}).description
$obj.Title = ($HFDetails | ?{$_.Title -imatch $obj.HotfixID}).Title

The full script is below (can be combined with the hotfix comparing script), taking the list of hosts from the pipe and recording the ComputerName as well. Sample output:










Full script:
 

 
 $hostlist = @($Input)

    
 foreach($srv in $hostlist){  
      $HFobj = $HFDetails = $allupdates = $objSearcher = $null  
      $HFobj = [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$srv))  
      $objSearcher= $HFobj.CreateUpdateSearcher()  
      $allupdates = $objSearcher.GetTotalHistoryCount()  
      $HFDetails = $objSearcher.QueryHistory(0,$allupdates)  
   
      Get-HotFix -ComputerName $srv | %{  
           $obj = "" | select ComputerName,HotfixID,InstalledOn,InstalledBy,Title,Description  
           $obj.ComputerName=$srv  
           $obj.HotfixID = $_.Hotfixid  
           $obj.InstalledOn = $_.Installedon  
           $obj.InstalledBy = $_.Installedby  
           $obj.Description = ($HFDetails | ?{$_.Title -imatch $obj.HotfixID}).description  
           $obj.Title = ($HFDetails | ?{$_.Title -imatch $obj.HotfixID}).Title  
           $obj  
      }   
 }  

May the Force...
t

07 April, 2013

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