Compare user accounts to CSV data

The script compares values of properties of AD user accounts to data contained in a CSV file. Any discrepancies between AD and data in the CSV file are emailed in the form of an HTML report.

To generate such a report on demand, you can create a custom command configured for the Domain-DNS object type that runs the script. To schedule the report, you need to create a scheduled task and include any of your AD domains in the Activity Scope. To add the script to a command or task, use the Run a program or PowerShell script action.

Parameters:

$csvFilePath - Specifies a full path to the CSV file to compare AD data to.
$identityColumnName - Specifies the name of the column that contains user identities. User accounts will be matched by the value of this column.
$identityPropertyName - Specifies the LDAP display name of the AD user account property that contains the user identity. Users will be matched by the value of this property.
$propertiesTo Check - Specifies a map of CSV column names to the corresponding properties of user accounts in Active Directory. Only properties included in this map will be compared. Properties must be specified by their LDAP display name.
$to - Specifies a comma separated list of recipients of the report.
$subject - Specifies the email message subject.
$reportHeader - Specifies the email message header.
$reportFooter - Specifies the email message footer.

Edit Remove

PowerShell

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

Script Repository

Parameters:

Got questions?