Compare account information

Question

Hello Support Team

We have a need to compare an Excel spreadsheet of employee master data to Adaxes (Active Directory) and get a report of what fields are different. We use the daily export from Human Resources as our guide to employee accounts and need to reconcile the areas when we aren't told of a change.

ex. user: Bob Smith is listed as Office Manager for 0666 in the master report (from HR) but Adaxes (Active Directory) still has the old office for Bob as 0333.

Answer 1

Hello,

You can do that with the help of scripts. As a starting point, you can use the following scripts form our Script Repository: Export and import user and computer properties. The scripts demonstrate how to pull user data both from AD accounts and from CSV files.

Based on that information, you can derive your own script that compares data from both the sources and creates a report of some kind. If you need assistance implementing such a script, we will help you.

Comments

excellent, it looks better. We just have an extra bullet before each person

---

Hello,

Could you provide us with a screenshot?

---

See attached screenshot. Extra dots are pointed out in red arrows.

---

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)

---

Looks great thank you so much!