Share via

Checking MPS and AD Integrity... Part #2: User Objects - AD to MPS

  • Article

Alright, first I provided the Powershell script to do MPS to AD check and now we are going to do the other way round, which is from AD to MPS. In this script, with the specified base DN, say OU=Hosting, DC=Fabrikam, DC=COM, it will start searching for all the user objects underneath that OU and then for each user object, it will extract the object GUID to see if it can mapped to an active customer ID in the MPS database. To run this, make the changes in the highlighted area, save this file to say ADtoMPS.ps1 and then run it. As with the previous script, it should also dump out a CSV file for your review.

So, have fun with it. :)

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

if ($args[0] -eq $null) {
 $LDAP = "ou=hosting, dc=fabrikam, 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=user)")
$users = $query.findAll()

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

$dumpfile = "Username,ObjectGUID,MailboxGUID,PlanManagerMatch,MailboxGUIDMatch,Comment`n"

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

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

 "Username:    " + $UserName

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

 $userMailboxGUID = $user.msExchMailboxGuid.ToString()
 $mailboxguid = GUID $userMailboxGUID 1
 $mailboxguid = $mailboxguid.ToUpper()
 if ($mailboxguid -ne "00000000000000000000000000000000") {
  "MailboxGUID: " + $mailboxguid
 } else {
  "MailboxGUID: NULL"   
 }

# 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

# Match Mailbox GUID

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

 $sqladapter.SelectCommand = $sqlcmd

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

 if ($query.Asset -ne $null) {
  $Asset = $query.Asset.ToString().ToUpper()

  If($Asset -eq $mailboxguid) {
   $result = "Yes"
   $Asset = ""
  } else {
   $result = "No"
  }

 } else {
  $result = "No Asset Found"
 }

 $FoundA = $result

 "Match in Asset Table:  " + $FoundA

# Output into CSV file

 $comment = $Asset

 $dumpfile = $dumpfile + $UserName + "," + $objectguid + "," + $mailboxguid + "," + $FoundPM + "," + $FoundA + "," + $comment + "`n"
 " "
}

Set-Content "CheckUserADtoMPS.csv" $dumpfile

$sqlconnection.close()

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

Comments