Checking MPS and AD Integrity... Part #4: OU Container to Organization - MPS to AD

In Part #3, I provided a piece of code that will retrieve from Active Directory all the OUs and then check against the MPS database to see if they exist. That helps you identify any mismatch. It is useful in situations like if you have to restore a MPS database that is like 2-3 weeks old and you do not know which OU that you created has a record in the database and etc. Now what about the other way round, say you need to restore an Active Directory that is 3 weeks old (I pray that you guys don't need to do that), then you will need to find out what OU has been created for the past 3 weeks. Running the code in Part #3, will probably tell you what OU has been deleted in the past 3 weeks but it won't be able to tell you what has been created within that 3 weeks.

So, here I have a piece of code for you guys that should do the job. Check it out (and of course, as usual, all those disclaimer about this code applies. Please test it first before running this against the production environment.),

What this code will do is to read all the OUs from your PlanManager database and then retrieve each of the GUID and attempt to retrieve it from the Active Directory and then report any failure. Simple enough, yeah?

I also received some feedback on my previous blog entries asking if there are ways to fix this should you see the problem. Well, yes, of course there is. I will definitely find some time to write those too.

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

 
$sqlserver = "MPSSQL01"
$sqldb = "PlanManager"

$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 REPLACE(CustomerID,'-','') AS CustomerID, CommonName FROM [PlanManager].[dbo].[Customers] WHERE StatusTypeCode = 'EN' and (CustomerTypeCode = 'BZ' or CustomerTypeCode = 'RO' or CustomerTypeCode = 'IO' or CustomerTypeCode = 'IR' or CustomerTypeCode = 'UM' or CustomerTypeCode = 'CO')"

 $sqlcmd.CommandText = $sqlquery

 $sqladapter.SelectCommand = $sqlcmd

 $dataset.clear() 
 $justfill = $sqladapter.fill($dataset)
 $items = $dataset.tables[0]
 
 $query = $items | Select CustomerID, CommonName

# Read the SQL table and match the AD entry

$dumpfile = "CommonName;MPSGUID;Match;DN`n"

$found = ""

Foreach($custID in $query) {
 $guid = $custID.CustomerID.ToString()
 $converted = ConvertGUID $guid
 $LDAP = "LDAP://<GUID=" + $converted  + ">"
 $commonname = $custID.CommonName.ToString()

 "Common Name:  " + $commonname 
 "MPS GUID:     " + $guid
 
 $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 + $commonname + ";" + $guid + ";" + $found + ";"+ $directoryEntry.distinguishedName  + "`n"

" "

}

Set-Content "CheckOUMPStoAD.csv" $dumpfile

$sqlconnection.close()

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