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()