Hello,
We have updated the script for you. Find it below.
$csvFilePath = "\\server.com\D$\CSV\bumpfile.csv" # TODO: modify me
$identityColumnName = "sAMAccountName" # TODO: modify me
$identityPropertyName = "sAMAccountName" # TODO: modify me
$propertiesToCheck = @{
"Job Code" = "employeeType"
"Title" = "title"
"EmployeeID" = "employeeID"
"Manager" = "manager"
"DepartmentNumber" = "departmentNumber"
} # TODO: modify me. Example: $propertiesToCheck = @{"<column name>" = "<property LDAP name>"}
# Email settings
$to = "jay@server.com" # TODO: modify me
$subject = "Discrepancies between Active Directory and CSV Data" # TODO: modify me
$reportHeader = "<h2><b>Discrepancies between Active Directory and CSV Data</b></h2>" # TODO: modify me
$reportFooter = "<hr /><p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>" # TODO: modify me
# Import CSV
if (-not (Test-Path -Path $csvFilePath))
{
$Context.LogMessage("File '$csvFilePath' not found", "Warning")
return
}
$importedUsers = Import-Csv -Path $csvFilePath | Where {(-not([System.String]::IsNullOrEmpty($_.$identityColumnName)))}
# Specify settings for user search
$searcher = $Context.BindToObject("Adaxes://RootDSE")
$searcher.PageSize = 500
$propertiesToLoad = @($propertiesToCheck.Values)
$propertiesToLoad += $identityPropertyName
$searcher.SetPropertiesToLoad($propertiesToLoad)
$searcher.VirtualRoot = $True
$filter = New-Object "System.Text.StringBuilder"
$usersInCSV = @{}
for ($i = 0; $i -lt $importedUsers.Length; $i++)
{
# Get user identity
$userFromCSV = $importedUsers[$i]
$identity = $userFromCSV.$identityColumnName
if ($usersInCSV.ContainsKey($identity))
{
$usersInCSV[$identity] = $NULL
continue
}
else
{
$usersInCSV.Add($identity, @{
"UserDataFromCSV" = $userFromCSV
"UserDataFromAD" = $NULL
})
}
# Build search filter
[void]$filter.Append([Softerra.Adaxes.Ldap.FilterBuilder]::Create($identityPropertyName, $identity))
$remainder = 0
[void][System.Math]::DivRem($i, 500, [ref]$remainder)
if ((($i -ne 0) -and ($remainder -eq 0)) -or ($i -eq $importedUsers.Length - 1))
{
# Search users
$searcher.SearchFilter = "(&(sAMAccountType=805306368)(|" + $filter.ToString() + "))"
try
{
$searchResultIterator = $searcher.ExecuteSearch()
$searchResults = $searchResultIterator.FetchAll()
foreach ($searchResult in $searchResults)
{
$userIdentity = $searchResult.Properties[$identityPropertyName].Value
$userInfo = $usersInCSV[$userIdentity]
if ($userInfo -eq $NULL)
{
continue
}
elseif ($userInfo.UserDataFromAD -ne $NULL)
{
$usersInCSV[$userIdentity] = $NULL
continue
}
$userInfo.UserDataFromAD = $searchResult
}
}
finally
{
# Release resources
if ($searchResultIterator) { $searchResultIterator.Dispose() }
}
# Clear filter
$filter.Length = 0
$filter.Capacity = 0
}
}
# Update user information
$reports = @{
"NotFound" = New-Object "System.Text.StringBuilder"
"FoundMoreThanOne" = New-Object "System.Text.StringBuilder"
"ValuesDontMatch" = New-Object "System.Text.StringBuilder"
}
foreach ($item in $usersInCSV.GetEnumerator())
{
if ($item.Value -eq $NULL)
{
[void]$reports["FoundMoreThanOne"].Append("<li>")
[void]$reports["FoundMoreThanOne"].Append($item.Key)
[void]$reports["FoundMoreThanOne"].Append("</li>")
continue
}
elseif ($item.Value.UserDataFromAD -eq $NULL)
{
[void]$reports["NotFound"].Append("<li>")
[void]$reports["NotFound"].Append($item.Key)
[void]$reports["NotFound"].Append("</li>")
continue
}
# Compare properties
$userDataFromCSV = $item.Value.UserDataFromCSV
$userDataFromAD = $item.Value.UserDataFromAD
$tableRecords = New-Object "System.Text.StringBuilder"
foreach($columnName in $propertiesToCheck.Keys)
{
$valueInCSV = $userDataFromCSV.$columnName
$propertyName = $propertiesToCheck[$columnName]
$valueInAD = $userDataFromAD.Properties[$propertyName].Value
if ($valueInAD -eq $NULL)
{
$valueInAD = [System.String]::Empty
}
if ($valueInCSV -eq $valueInAD)
{
continue
}
[void]$tableRecords.Append("<tr><td>$propertyName</td><td>$valueInCSV</td><td>$valueInAD</td></tr>")
}
if ($tableRecords.Length -ne 0)
{
$userName = $Context.GetDisplayNameFromAdsPath($userDataFromAD.AdsPath)
[void]$reports["ValuesDontMatch"].Append("<li>")
[void]$reports["ValuesDontMatch"].Append($userName)
[void]$reports["ValuesDontMatch"].Append("<table border='1' width='100%%'>")
[void]$reports["ValuesDontMatch"].Append("<tr><th width='30%%'>Property name</th><th width='30%%'>Value in CSV</th><th width='30%%'>Value in AD</th></tr>")
[void]$reports["ValuesDontMatch"].Append($tableRecords.ToString())
[void]$reports["ValuesDontMatch"].Append("</table>")
[void]$reports["ValuesDontMatch"].Append("</li>")
}
}
# Build report
$report = New-Object "System.Text.StringBuilder"
if ($reports["FoundMoreThanOne"].Length -eq 0 -and
$reports["NotFound"].Length -eq 0 -and
$reports["ValuesDontMatch"].Length -eq 0)
{
[void]$report.Append("<b>Found no discrepancies between the CSV file and Active Directory</b>")
}
else
{
if ($reports["NotFound"].Length -ne 0)
{
[void]$report.Append("<h3>The following users were not found in Active Directory: </h3>")
[void]$report.Append("<ul>")
[void]$report.Append($reports["NotFound"].ToString())
[void]$report.Append("</ul>")
}
if ($reports["FoundMoreThanOne"].Length -ne 0)
{
[void]$report.Append("<h3>Each of the following names were found in more than one user account: </h3>")
[void]$report.Append("<ul>")
[void]$report.Append($reports["FoundMoreThanOne"].ToString())
[void]$report.Append("</ul>")
}
if ($reports["ValuesDontMatch"].Length -ne 0)
{
[void]$report.Append("<h3>The following properties of the following users did not match:</h3>")
[void]$report.Append("<ul>")
[void]$report.Append($reports["ValuesDontMatch"].ToString())
[void]$report.Append("</ul>")
}
}
# Build report
$html = "$reportHeader$($report.ToString())$reportFooter"
# Send mail
$Context.SendMail($to, $subject, $NULL, $html)