Find SQL Server MS Cluster Computer Objects In AD

In this post I’ll show a small PowerShell function to identify SQL Server MS Cluster computer objects in AD.

The situation I have is that there is no consistent naming standard used when creating MS Clusters in one of my AD environments. Therefore, I needed some other way of filtering out these objects in scripts, without using SQL. After examining some of the objects I found that the ServicePrincipalName values could be used.

Prerequisites

PowerShell Modules – The Active Directory module installed via the RSAT tools.

Privileges – the account that runs the function must be able to read AD data.

Solution

This approach works in my environment. It may not work in yours. The ServicePrincipalName of a MS Cluster computer object has two components of the string that can be used to identify cluster computer objects in my environment.

• RestrictedKrbHost
  This string exists only for cluster member servers.
• MSServerCluster
  This string exists in Cluster computer objects and Cluster member server computer objects in AD.

That’s all the info I needed to effectively identify the cluster computer objects in my AD.

Here is the simple function I use. I validated the results with one of the DBA team members.

function test-IsClusterName($server) {
   try {
      $thisComputerObjectSPN = (get-adcomputer $server -Properties * | Select-Object servicePrincipalName -ExpandProperty servicePrincipalName)
      $cnt1 = 0
      $cnt2 = 0
      $thisComputerObjectSPN | ForEach-Object { if ($_.contains('RestrictedKrbHost')) { $cnt1 = $cnt1 + 1 } }
      $thisComputerObjectSPN | ForEach-Object { if ($_.contains("MSServerCluster")) { $cnt2 = $cnt2 + 1 } }
      if (($cnt1 -eq 0) -and ($cnt2 -gt 0)) { $true }else { $false }
   }
   Catch {
      Write-Error "Could not get the SPN for $server"
   }   
}

I’ve found this little function very useful when writing scripts that interact with computer objects in AD.

Example:

$serversInAD = Get-ADComputer -Filter { OperatingSystem -like "*windows*server*" } -Properties * | Sort-Object DNSHostname | Select-Object Name, DNSHostName, OperatingSystem, DistinguishedName, LastLogonDate, Enabled, @{name = 'IsClusterIP'; expression = { test-IsClusterName($($_.Name)) } }