Need help updating User Objects from CSV

[Baron164]

I am attempting to update a script that will import a csv filled with user information such as Department, Title, Location, Phone Number, etc and merge it with the account information in Active Directory. However I can't rely on usernames to import data so I need to use the EmployeeID Attribute.

So this script will need to cross reference the EmployeeID value listed in the CSV with what is in Active Directory. Once it finds a match it should then ensure that the rest of the data in the CSV is present in the User Account. If data in the user object is missing or incorrect the script should update the AD object so it matches.

Here is what I have so far for the script.

# Import AD Module
Import-Module ActiveDirectory
 
write-Host 'Updating AD attributes for users with employee IDs.......' -NoNewline -ForegroundColor Yellow
# Import CSV into variable $users
 
#$users = Import-Csv -Path C:\Scripts\DataFiles\user-import.csv
$users = Import-Csv -Path C:\Scripts\DataFiles\user_import.csv
# Loop through CSV and update users if they exist in CVS file
 
foreach ($user in $users) {
#Search in specified OU and Update existing attributes
Get-ADUser -Filter "EmployeeID -eq '$($user.EmployeeID)'" -Properties * -SearchBase "DC=domain,DC=local" |
Set-ADuser -replace @{employeeType=$user.employeeType;c=$user.c;o=$user.o;department=$user.Department;title=$user.Title;;physicalDeliveryOfficeName=$user.Office;street=$user.StreetAddress;streetAddress=$user.StreetAddress;l=$user.City;st=$user.State;PostalCode=$user.PostalCode;telephoneNumber=$user.OfficePhone}
#Write-Host $user
}

 
Here is an example of the csv.

"LAST_NAME","MI","FIRST_NAME","SUFFIX","EMP_TITLE","PHONE","EXT4","FAX_NUM","OFFICE","LOCAT","OFFICE_STREET","OFFICE_CITY","ST","OFF_ZIP","XREF_NUM"
"Doe","A.","John","","Secretary","(123) 456-7890","1234","(123) 456-7891","Front Office","Building-A","100 East First ST., Ste. 100","Boston","MA","12345","14563"

Any help would be greatly appreciated, thank you.

[#2]

@Baron164 

Quick observation/question:

Your CSV doesn't contain a field named "EmployeeID", yet your script attempts to make reference it on line 13:

'$($user.EmployeeID)'

Is it simply missing from the CSV or is it one of the other fields, like "EXT4" or "XREF_NUM"?

Cheers,

Lain

[#3]

@Baron164 

Actually, lines 13 and 14 are completely botched at the moment.

Line 13 runs the search and then pipes the results into line 14, yet line 14 makes no references at all to the CSV entry since none of the properties references exist within the CSV - it's not just employeeID.

For example, the first replacement reference is "$user.employeeType", which will always be null as there is no header named employeeType in the CSV, resulting in the "$user" object containing no such property. This holds true for every "$user.xxxxx" reference in line 14.

Before looking at the script requirements, you need to either update the CSV headings to match what you've used in line 14, or vice versa (i.e. update the "$user.xxxxx" references in line 14 to match the CSV headings.)

Cheers,

Lain

[#4]

Yes, so that is one of my issues. The csv export has different headers so I know I need to fix that but I haven't been sure how best to do it. I can either fight with the source export or modify this script to import using these headers. For example, "XREF_NUM" is going to be the EmployeeID, so for that would I do "XREF_NUM=$user.EmployeeID"?

[#5]

@Baron164 

Close, but you need to flip that around. Remember, as per your lines 11 and 8, $user is coming from $users, which is coming from the CSV, meaning the properties on $user are named the same as the headings from your CSV, not the Active Directory user object (which is how you've named them in your script on lines 13 and 14.)

For example, based on what you just said, line 13 would look like:

Get-ADUser -Filter "EmployeeID -eq '$($user.xref_num)'" -Properties * -SearchBase "DC=domain,DC=local" |

Line 14 would look like (and I'm only going to do a couple of attributes - you can map out the rest):

Set-ADuser -replace @{title=$user.EMP_TITLE;street=$user.OFFICE_STREET;streetAddress=$user.OFFICE_STREET;l=$user.OFFICE_CITY;st=$user.ST;PostalCode=$user.OFF_ZIP;telephoneNumber=$user.PHONE

And so on and so forth.

What this shows is $user utilising the CSV header names as its properties, which is the key missing link in the current script.

Additional considerations

The script is very, very basic and could cause you some issues. For example, it's only using the "-Replace" parameter meaning you'll run into issues where null values are provided in the CSV, or even worse, empty string values (as they don't always stand out visually as issues.)

Additionally, if you have duplicate values for "employeeID" in Active Directory (and I've seen this a lot in larger environments), you could end up unintentionally modifying multiple users. You really only want to go ahead with the change if exactly one user is returned.

I can drop a sample script in a bit later that demonstrates how to navigate these issues but I'll have to make some guesses at the CSV headings-to-AD attributes mappings. Still, it should be enough for you to see what's going on.

Cheers,

Lain

[#6 · Accepted solution]

@Baron164 

Here's a basic script that deals with the additional considerations mentioned above.

It does have some limits as noted within the script itself, but I'd wager you're not going to come across those anyway.

With respect to the attribute mappings between the CSV and Active Directory's attributes, the function named Get-CsvToADData is where you would make any necessary changes.

As an aside, it does not require the ActiveDirectory module, making it a bit more portable. This adds a handful of extra lines but the portability gain is easily worth the cost.

[cmdletbinding()]
Param(
    [parameter(Mandatory=$true)][string]$Path
)

<#
    .SYNOPSIS
    Updates an existing Active Directory object matching on employeeID.

    .DESCRIPTION
    Relatively basic script that features just enough error checking to not make a mess of Active Directory.
    It hasn't been written to cope with multi-valued attributes.
#>

#region Function definitions.
# Define the CSV-to-AD attribute mappings.
function Get-CsvToADData([System.Object] $User, [System.Collections.HashTable] $Replacements, [System.Collections.Generic.List[string]] $Clearances)
{
    if ($Replacements -isnot [System.Collections.HashTable] -or $Clearances -isnot [System.Collections.Generic.List[string]])
    {
        return($false);
    }

    #region Clear any previous values.
    $Replacements.Clear();      # Attributes to be updated.
    $Clearances.Clear();        # Attributes to be cleared.
    #endregion

    try
    {
        # The basic 1-to-1 mappings.
        if ([string]::IsNullOrWhiteSpace($User.last_name)) { $Clearances.Add("sn"); } else { $Replacements.Add("sn", $User.last_name); };
        if ([string]::IsNullOrWhiteSpace($User.mi)) { $Clearances.Add("initials"); } else { $Replacements.Add("initials", $User.mi); };
        if ([string]::IsNullOrWhiteSpace($User.first_name)) { $Clearances.Add("givenName"); } else { $Replacements.Add("givenName", $User.first_name); };
        if ([string]::IsNullOrWhiteSpace($User.emp_title)) { $Clearances.Add("title"); } else { $Replacements.Add("title", $User.emp_title); };
        if ([string]::IsNullOrWhiteSpace($User.phone)) { $Clearances.Add("telephoneNumber"); } else { $Replacements.Add("telephoneNumber", $User.phone); };
        if ([string]::IsNullOrWhiteSpace($User.ext4)) { $Clearances.Add("ipPhone"); } else { $Replacements.Add("ipPhone", $User.ext4); };
        if ([string]::IsNullOrWhiteSpace($User.fax_num)) { $Clearances.Add("facsimileTelephoneNumber"); } else { $Replacements.Add("facsimileTelephoneNumber", $User.fax_num); };
        if ([string]::IsNullOrWhiteSpace($User.office_street)) { $Clearances.Add("streetAddress"); } else { $Replacements.Add("streetAddress", $User.office_street); };
        if ([string]::IsNullOrWhiteSpace($User.office_city)) { $Clearances.Add("l"); } else { $Replacements.Add("l", $User.office_city); };
        if ([string]::IsNullOrWhiteSpace($User.st)) { $Clearances.Add("st"); } else { $Replacements.Add("st", $User.st); };
        if ([string]::IsNullOrWhiteSpace($User.off_zip)) { $Clearances.Add("postalCode"); } else { $Replacements.Add("postalCode", $User.off_zip); };

        # Mappings involving multiple attributes and/or transformations.
        #region physicalDeliveryOfficeName
        $Has_office = -not [string]::IsNullOrWhiteSpace($User.office);
        $Has_locat  = -not [string]::IsNullOrWhiteSpace($User.locat);

        if (-not ($Has_office -or $Has_locat))
        {
            $Clearances.Add("physicalDeliveryOfficeName");
        }
        elseif ($Has_office -and $Has_locat)
        {
            $Replacements.Add("physicalDeliveryOfficeName", "$($User.office) $($User.locat)");
        }
        elseif ($Has_office)
        {
            $Replacements.Add("physicalDeliveryOfficeName", $User.office);
        }
        else
        {
            $Replacements.Add("physicalDeliveryOfficeName", $User.locat);
        };
        #endregion
    }
    catch
    {
        $Replacements.Clear();
        $Clearances.Clear();
        throw;
    }

    return($Replacements.Count + $Clearances.Count -gt 0);
}
#endregion

#region Script body.
try
{
    # Knock up an Active Directory searcher.
    #region Create an Active Directory searcher.
    $RootDSE = [adsi]"LDAP://RootDSE";
    $CurrentServer = $RootDSE.Properties["dNSHostName"][0].ToLowerInvariant();                  # Used here and there to ensure all read and write operations are using the same server during this run.
    $DefaultNamingContext = $RootDSE.Properties["defaultNamingContext"][0];
    $RootDSE.Dispose();

    $Searcher = [adsisearcher]::new([adsi]"LDAP://$CurrentServer/$DefaultNamingContext");

    Write-Warning "Connected to domain controller $CurrentServer";
    #endregion

    #region Create the lists that will hold the changes.
    $Replacements = [System.Collections.Hashtable]::new();
    $Clearances   = [System.Collections.Generic.List[string]]::new();
    #endregion

    Import-Csv -Path $Path | ForEach-Object {
        $User = $_;

        # Check employeeID is not empty otherwise we're wasting our time.
        if (-not [string]::IsNullOrWhiteSpace($User.XREF_NUM))
        {
            $EmployeeID = $User.XREF_NUM;

            #region Check if exactly one user can be found in Active Directory.
            $Searcher.Filter = "(&(objectClass=user)(objectCategory=person)(employeeID=$EmployeeID))";
            $Results = $Searcher.FindAll();
            #endregion

            #region If exactly one result was returned, proceed.
            if (($Results.Count -eq 1) -and (Get-CsvToADData -User $User -Replacements $Replacements -Clearances $Clearances))
            {
                #region Fetch the Active Directory user and make any relevant changes.
                try
                {
                    # Fetch the user from Active Directory.
                    $ADUser = $Results[0].GetDirectoryEntry();

                    # Perform the relevant clearing and/or updating.
                    foreach ($Attribute in $Replacements.Keys)
                    {
                        if ($ADUser.Properties.Contains($Attribute))
                        {
                            # If the Active Directory attribute already has a value, we can simply change it.
                            $ADUser.Properties[$Attribute].Value = $Replacements[$Attribute];
                        }
                        else
                        {
                            # If the Active Directory attribute is null (not to be confused with an empty string), then we need to add a key-value pair.
                            $null = $ADUser.Properties[$Attribute].Add($Replacements[$Attribute]);
                        }
                    }

                    foreach ($Attribute in $Clearances)
                    {
                        # Clear the value from any attribute where no matching value was provided in the CSV.
                        $ADUser.Properties[$Attribute].Clear() ;
                    }

                    $ADUser.CommitChanges();
                }
                catch
                {
                    # Electing to bail out of the script entirely by re-throwing the exception. Alternatively, you could elect to simply continue the loop but re-throwing is the lesser evil.
                    throw;
                }
                finally
                {
                    if ($ADUser -is [System.DirectoryServices.DirectoryEntry])
                    {
                        $ADUser.Dispose();
                    }
                }
                #endregion
            }
            else
            {
                Write-Warning "Skipping EmployeeID ""$EmployeeID"". Results = $($Results.Count).";
                $Results.Dispose();
            }
            #endregion

        }
    }
}
catch
{
    throw;
}
finally
{
    if ($Searcher)
    {
        $Searcher.Dispose();
    }
}
#endregion

Cheers,

Lain

[#7]

@LainRobertson 

Thanks, I've tested the shorter version and so far it looks to be working properly. I'll look over the longer version and play with that. Due to the nature of the CSV data NULL values and duplicate EmployeeID's "should not" be an issue. But it's always possible that someone will fat finger a number.

[#8]

@LainRobertson

I've been asked to also assign a user's Manager attribute via this process. I was able to get them to include the Manager's EmployeeID number as a new column. So while the Employee's ID number is listed in "XREF_NUM" column the user's Manager's ID number is in the new column "SUPV".

So now the issue is, how to lookup the Manager's User Account using their EmployeeID Attribute, pull the DistinguishedName Attribute from the Manager's User Account, and assign that value to the Manager attribute of the user account.