0 votes

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.

by (3.2k points)

1 Answer

0 votes
by (216k points)
selected by
Best answer

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.

0

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

0

Hello,

Could you provide us with a screenshot?

0

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

0

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

Looks great thank you so much!

Related questions

0 votes
1 answer

Hi, We have two domains. Users has account in both domain Between these two domains running FIM , which replicates the account attributes, and Adaxes spans both domains. ... notification about expiring domain password only from domain A. How we can achieve it?

asked Apr 2, 2015 by axmaster (510 points)
0 votes
1 answer

Our helpdesk asked for a solution to easily compare 'member of' details between 2 (or more) users so they can see the differences in group memberships.

asked Oct 28 by ddesmedt (40 points)
0 votes
1 answer

Hello, Based on your script, we check whether there is already a request. However, we often get the following error and do not know why. Can you help us? ... Cannot compare "Softerra.Adaxes.Adsi.Search.AdmSearchResult" because it is not IComparable. Thank you

asked Apr 16 by DRiVSSi (300 points)
0 votes
1 answer

Good afternoon, I am attempting to create a report to flag Users where the email address in AD does not match the email address in our Payroll system. I am able to use ... ) to grab User objects. Any help or input would be greatly appreciated. Thank you, Keith

asked Aug 12, 2021 by kfrench (20 points)
0 votes
1 answer

HI, is it possible to compare a actual Value and the Value after modify from a CustomAttributeTextMultiValue and send the difference via mail? Thx,

asked Nov 18, 2014 by Napoleon (700 points)
3,548 questions
3,238 answers
8,232 comments
547,812 users