We use cookies to improve your experience.
By your continued use of this site you accept such use.
For more details please see our privacy policy and cookies policy.

Script Repository

Compare user accounts to CSV data

November 14, 2023 Views: 4447

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

Comments 2
avatar
Pudong Nov 14, 2023
Hello
In my CSV file the manager value is saved as samAccountName and not as Object DN?

For example
Value in CSV: jdoe
Value in AD: CN=Jon Doe,OU=user,DC=domain,DC=gvb,DC=local

Do you have a suggestion on how the script can be modified so that these values can also be compared?

regards
pudong
avatar
Support Nov 14, 2023
Hello,

Unfortunately, there is no easy way to do that. The thing is that manager is a DN-syntax property which means it stores a distinguished name of an object.
avatar
Pudong Nov 22, 2023
Hello,
I found the necessary hint on how to parse the value in this import script:
https://www.adaxes.com/script-repository/import-new-and-updated-users-from-csv-file-s246.htm

I have now updated my script, and it's working as desired. Here my code:




### New function: search the user by the sAMAccountName and return DistinguishedName.
$domainName = $Context.GetObjectDomain("%distinguishedName%")
function GetADObjectDistinguishedName {
param (
[string]$value
)

$aDObject = Get-AdmObject -Filter {(sAMAccountName -eq $value)} `
-AdaxesService localhost -ErrorAction SilentlyContinue -Server $domainName

if ($aDObject -is [System.Array]) {
$value = "Found more than one object with identity '$value'."
}
elseif ($aDObject -eq $NULL) {
$value = "Could not locate object with identity '$value'."
}
else {
$value = $aDObject.DistinguishedName
}

return $value
}



### Addition in the section "Compare properties"

# The Manager value must be converted from sAMAccountName to DistinguishedName so that a comparison is possible.
if($propertyName -eq "Manager")
{
$valueInCSV = GetADObjectDistinguishedName -value $valueInCSV
}
Leave a comment
Loading...

Got questions?

Support Questions & Answers