Bulk updating user details in Active Directory

Background and use cases

If you ever worked in a messy (mismanaged?) AD environment, you know this pain. User accounts with old job titles, outdated phone numbers, duplicated departments such as “Quality” next to “Quality Control”, etc. Maybe even missing these attributes altogether. General havoc in GAL. This is especially true for small and medium sized companies, where the environment is not mature enough to put appropriate procedures in place yet.

If no one can rely on info from GAL, this not only makes IT look bad, but it prevents us from reliably syncing this information down to other systems, or using it in automation. AD is supposed to be a single source of truth for Identity and Access Management. There can’t be any distrust about the data it stores.

Step one of getting out of such mess is to build JML (Joiners, Movers, Leavers) process. Properly documented. Only then we can start working on automation for user account management, ideally one that integrates with the ticketing system, or maybe even a HR system. Let the mundane tasks like user creation happen automatically, leaving only the important bits - restricted accesses and security groups - for manual control.

This post will focus on step two, which is fixing all the existing accounts that are already there in AD, and to address this we’ll use a simple PowerShell script. Additionally we will need current employee info in a CSV file. Even if your AD is shit, you can usually rely on HR for this. After all it’s their responsibility to keep employee records up to date.

Preliminary steps

To format the CSV properly, we need to decide on how to identify user accounts. We need a unique attribute. This will vary depending on the environment. samAccountName or userPrincipalName would be best. Email addresses should also work. Sometimes email addresses include UPN actually. Again, this will depend on the environment. I’ll use UPNs in this example.

We need a CSV in UTF-8 coding, formatted like this:

Scripting it all together

# Get CSV content
$CSVrecords = Import-Csv hr-data.csv -Delimiter ","

# Create arrays for skipped and failed users
$SkippedUsers = @()
$FailedUsers = @()

# Loop trough CSV records
foreach ($CSVrecord in $CSVrecords) {
    $upn = $CSVrecord.UserPrincipalName
    $managerUPN = $CSVrecord.managerUPN
    $user = Get-ADUser $upn
    if ($user) {
        try {
        $user | Set-ADUser -department $CSVrecord.department -city $CSVrecord.physicalDeliveryOfficeName -title $CSVrecord.JobTitle -manager $managerUPN
        } catch {
        $FailedUsers += $upn
        Write-Warning "$upn user found, but FAILED to update."
        }
    }
    else {
        Write-Warning "$upn not found, skipped"
        $SkippedUsers += $upn
    }
}

# Show skipped users
$SkippedUsers

# Show failed users
$FailedUsers

This should bring AD in line with reality.