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

Alright, here is a piece of code like the earlier. Instead of checking the user object, this checks the OU in the AD and match it with the Organization in the MPS database. This piece of code will help 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. We can talk about how you can recreate those entries in future blog (is it possible, you may ask, yes, of course it is... just need to take a bit of time and craft out some clever procedure to do the job).

Again, like any script, please use it at your own risk, however, having to say that, it should be noted that this Powershell script does not change anything. Enjoy!

PS: You may see in the code, I used some variable $Username instead of $OU or something, that's because I just re-use the code from my previous script but it should work without issue. Let me know if it isn't working.

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

if ($args[0] -eq $null) {
 $LDAP = "OU=Hosting,dc=hmc45,dc=com"
} else {
 $LDAP = $args[0]
}

$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 f([int]$a)
{

 if ($a -lt 16) {
      $r = "0" + [Convert]::ToString($a, 16)
 } else {
  $r = [Convert]::ToString($a, 16)
 }
 return $r
}

function GUID([String]$guid, [int]$dash)
{

 $p0,$p1,$p2,$p3,$p4,$p5,$p6,$p7,$p8,$p9,$p10,$p11,$p12,$p13,$p14,$p15 = $guid.split(" ")

 $p0 = f($p0)
 $p1 = f($p1)
 $p2 = f($p2)
 $p3 = f($p3)
 $p4 = f($p4)
 $p5 = f($p5)
 $p6 = f($p6)
 $p7 = f($p7)
 $p8 = f($p8)
 $p9 = f($p9)
 $p10 = f($p10)
 $p11 = f($p11)
 $p12 = f($p12)
 $p13 = f($p13)
 $p14 = f($p14)
 $p15 = f($p15)

 if($dash -eq "0") {
  $guid = $p0 + $p1 + $p2 + $p3   + "-" + $p4 +$p5 + "-" + $p6 + $p7 + "-" + $p8 + $p9 + "-" + $p10 + $p11 + $p12 + $p13 + $p14 + $p15
 } else {
  $guid = $p0 + $p1 + $p2 + $p3 + $p4 + $p5 + $p6 + $p7 + $p8 + $p9 + $p10 + $p11 + $p12 + $p13 + $p14 + $p15
 }

 return $guid
}

$domain = "LDAP://" + $LDAP
$directoryEntry = New-Object System.DirectoryServices.DirectoryEntry $domain

$query = new-object system.directoryservices.directorysearcher
$query.SearchRoot = $directoryEntry
$query.PageSize = 1000
$query.filter = ("(objectClass=organizationalUnit)")
$users = $query.findAll()

$count = 0
$errorcount = 0
$select = ""

$dumpfile = "organizationalUnit,ObjectGUID,PlanManagerMatch`n"

Foreach($user in $users)
{
 $user = $user.GetDirectoryEntry()

 $userDN = $user.distinguishedName
 $userName = $user.name

 

 "Organization Unit:    " + $UserName

 $userGUID = $user.objectGUID.ToString()
 $objectguid = GUID $userGUID 0
 $objectguid = $objectguid.ToLower()
 "objectGUID:  " + $objectguid

# Match Customer Table

 $sqlquery = "Select CustomerID FROM [PlanManager].[dbo].[Customers] WHERE StatusTypeCode = 'EN' and lower(CustomerID) = '" + $objectguid + "'"
 $sqlcmd.CommandText = $sqlquery

 $sqladapter.SelectCommand = $sqlcmd

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

 if ($query.CustomerID -ne $null) {
  $CustomerID = $query.CustomerID.ToString()
  # $CustomerID
  $result = "Yes"
 } else {
  $result = "No"
 }

 $FoundPM = $result

 "Found In PlanManager:  " + $FoundPM

# Output into CSV file

 $dumpfile = $dumpfile + $UserName + "," + $objectguid + "," + $FoundPM + "`n"

" "

}

Set-Content "CheckOUAdtoMPS.csv" $dumpfile

$sqlconnection.close()