Script Repository

$csvFilePath = "\\Server\Share\Users.csv" # TODO: modify me
$identityColumnName = "sAMAccountName" # TODO: modify me
$identityPropertyName = "sAMAccountName" # TODO: modify me
$propertiesToCheck = @{
    "EmployeeType" = "employeeType"
    "Title" = "title"
    "EmployeeID" = "employeeID"
    "Manager" = "manager"
    "DepartmentNumber" = "departmentNumber"
} # TODO: modify me. Example: $propertiesToCheck = @{"<column name>" = "<property LDAP name>"}

# Email settings
$to = "recipient@example.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.TargetObject
$searcher.PageSize = 500
$propertiesToLoad = @($propertiesToCheck.Values)
$propertiesToLoad += $identityPropertyName
$searcher.SetPropertiesToLoad($propertiesToLoad)
$searcher.VirtualRoot = $True

$usersInCSV = @{}
for ($i = 0; $i -lt $importedUsers.Length; $i++)
{
    if ($NULL -eq $criteria)
    {
        $criteria = New-AdmCriteria -Type "User"
        $criteria["User"].SetLogicalOperator("OR")
    }
    
    # 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
    $criteria["User"].Add({$identityPropertyName -eq $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.Criteria = $criteria
        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 criteria
        $criteria = $NULL
    }
}

# 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
    $userRecord = $NULL
    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
        }
        
        if ($userRecord -eq $NULL)
        {
            $userRecord = New-Object "System.Text.StringBuilder"
            $userName = $Context.GetDisplayNameFromAdsPath($userDataFromAD.AdsPath)
            [void]$userRecord.Append("<li>$userName<table border='1' width='100%%'><tr><th width='30%%'>Property name</th><th width='30%%'>Value in CSV</th><th width='30%%'>Value in AD</th></tr>")
        }
        [void]$userRecord.Append("<tr><td>$propertyName</td><td>$valueInCSV</td><td>$valueInAD</td></tr>")
    }
    
    if ($userRecord -ne $NULL)
    {
        [void]$userRecord.Append("</table><br/>")
        [void]$userRecord.Append("</li>")
        [void]$reports["ValuesDontMatch"].Append($userRecord.ToString())
    }
}

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