Checking MPS and AD Integrity... Part #7: OAB Servers

Yes, while the whole world is talking and about to planning to move towards the next release of Hosted Exchange (Exchange Server 2010), I haven't forgotten that we still have folks in HMC world. The good news is that the migration guide is on the way. I have read through it, looks reasonable so far. That's topic for a different post.

Now, what I have here is a PowerShell script that retrieves from the SQL server all the OAB server and check that against the Active Directory to see if we have any OAB servers registered to the MPS but has been removed from the environment. Here you go,

########################################################################

$sqlserver = "MPSSQL01"
$sqldb = "ResourceManager"

$sqlconnection = New-Object System.Data.SqlClient.SqlConnection
$sqlconnection.ConnectionString = "Server=$sqlserver;Database=$sqldb;Integrated Security = True"

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.connection = $sqlconnection

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$dataset = New-Object System.Data.DataSet

function ConvertGUID([String]$guid)
{

  $p0 = $guid.substring(6,2)
$p1 = $guid.substring(4,2)
$p2 = $guid.substring(2,2)
$p3 = $guid.substring(0,2)
$p4 = $guid.substring(10,2)
$p5 = $guid.substring(8,2)
$p6 = $guid.substring(14,2)
$p7 = $guid.substring(12,2)
$p8 = $guid.substring(16,2)
$p9 = $guid.substring(18,2)
$p10 = $guid.substring(20,2)
$p11 = $guid.substring(22,2)
$p12 = $guid.substring(24,2)
$p13 = $guid.substring(26,2)
$p14 = $guid.substring(28,2)
$p15 = $guid.substring(30,2)

 
$guid = $p0 + $p1 + $p2 + $p3 + "-" + $p4 +$p5 + "-" + $p6 + $p7 + "-" + $p8 + $p9 + "-" + $p10 + $p11 + $p12 + $p13 + $p14 + $p15

return $guid
}

# Main Module

$sqlquery = "select a.instance_id, a.instance_name, a.instance_description, a.instance_location, c.actual_max, c.actual_free from rmcore_ResourceInstance a, rmcore_ResourceType b, blockmodel_ResourceCapacity c where b.type_name = 'ExchOABPublicStore07' and a.type_id = b.type_id and a.instance_id = c.instance_id"

  $sqlcmd.CommandText = $sqlquery

  $sqladapter.SelectCommand = $sqlcmd

  $dataset.clear()
$justfill = $sqladapter.fill($dataset)
$items = $dataset.tables[0]

$query = $items | Select instance_name, instance_description, instance_location, actual_max, actual_free

# Read the SQL table and match the AD entry

$found = ""

$dumpfile = "Server;GUID;Actual Free;Actual Max;Found;DN`n"

Foreach($storeGUID in $query) {
$guid = $storeGUID.instance_name.ToString()
$converted = ConvertGUID $guid
$LDAP = "
LDAP://<GUID =" + $converted + ">"
$location = $storeGUID.instance_location.ToString() + "\" + $storeGUID.instance_description.ToString()
$actualmax = $storeGUID.actual_max.ToString()
$actualfree = $storeGUID.actual_free.ToString()

  "Location: " + $location
"MPS GUID: " + $guid
"Actual Free/Max: " + $actualfree + "/" + $actualmax

$directoryEntry = New-Object System.DirectoryServices.DirectoryEntry $LDAP

 if ($directoryEntry.distinguishedName -ne $null) {
"DN Match: " + $directoryEntry.distinguishedName
$found = "Yes"

  } else {
"DN Match: " + "ERROR!"
$found = "No"
}

# Output into CSV file

  $dumpfile = $dumpfile + $location + ";" + $guid + ";" + $actualfree + ";" + $actualmax + ";"+ $found + ";"+ $directoryEntry.distinguishedName + "`n"

" "

}

Set-Content "CheckOABServer.csv" $dumpfile

$sqlconnection.close()