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

We are having some trouble with the following script.

$usersCSVFilePath = "\\\Server\share\exportedAdaxesUsers.csv" # TODO: modify me
#$computerCSVFilePath = "\\Server\share\computers.csv" # TODO: modify me

function BuildReport($filter, $properties)
{
    # Search objects in all domains
    $searcher = $Context.BindToObject("Adaxes://rootDSE")
    $searcher.SearchFilter = $filter
    $searcher.SearchScope = "ADS_SCOPE_SUBTREE"
    $searcher.PageSize = 500
    $searcher.ReferralChasing = "ADS_CHASE_REFERRALS_NEVER"
    $searcher.SetPropertiesToLoad($properties)
    $searcher.VirtualRoot = $True

    try
    {
        $searchResultIterator = $searcher.ExecuteSearch()
        $searchResults = $searchResultIterator.FetchAll()

        foreach ($searchResult in $searchResults)
        {
            # Add object to report
            $record = New-Object PSObject
            foreach ($propertyName in $properties)
            {
                $record | Add-Member -MemberType NoteProperty -Name $propertyName -Value $searchResult.Properties[$propertyName].Value
            }
            $record
        }
    }
    finally
    {
        $searchResultIterator.Dispose()
    }
}

# Create CSV file for users
$userReport = BuildReport "sAMAccountName", "employeeType", "Title", "employeeID", "manager", "departmentNumber"
$userReport | Export-Csv -NoTypeInformation -Path $usersCSVFilePath

# Create CSV file for computers
#$computerReport = BuildReport "(&(objectCategory=computer)(!(userAccountControl:1.2.840.113556.1.4.803:=8192)))" @("managedBy", "distinguishedName")
#$computerReport | Export-Csv -NoTypeInformation -Path $computerCSVFilePath

We are receiving errors stating the following:

We want to export the following attributes and run a comparison against a different excel file, samAccountName, employeeType, JobCode, Manager, title, employeeID and departmentNumber.

---

Helo,

The BuildReport function requires two parameters: $filter and $properties. In the Create CSV file for users section of your script the $filter paameter is absent for the function:

$userReport = BuildReport "sAMAccountName", "employeeType", "Title", "employeeID", "manager", "departmentNumber"

The correct line will be as follows:

$userReport = BuildReport "(sAMAccountType=805306368)" @("sAMAccountName", "employeeType", "Title", "employeeID", "manager", "departmentNumber")

---

Thank you, that give s a full report of the users in AD as needed. Now is there a way to script the comparsion of the fields in the two CSV files for differences? We would like this to be automated as much as possible.

---

Hello,

Find a script that meets your requirements in our Script Repository: Compare user accounts to CSV data.

---

Thank you, this script successfully compares and sends a report but the report format is way off. See 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
    $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)

---

Hello,

To remedy the issue, replace this line in the script

[void]$userRecord.Append("<table><br/>")

with the following one:

[void]$userRecord.Append("</table><br/>")

---

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!