Update On Premises AD from CSV using simple PS Script

Question

New to scripting with PS and I am trying to update several fields in our on premises AD environment using a .csv file and PowerShell. The fields I need to update are Description, Manager, Department, Location and Title. The csv file has columns of EmployeeName, ManagerName, EmployeeTitle, EmployeeDepartment and EmployeeLocation.
Here is my initial script which throws errors

$UpdatedUser = Import-csv "c:\temp\User_Update_test.csv"

foreach ($UpdatedUser in $UpdatedUser) {
Set-ADUser -Identity $UpdatedUser.EmployeeName -Manager $UpdatedUser.ManagerUPN
Set-ADUser -Identity $UpdatedUser.EmployeeName -Title $UpdatedUser.EmployeeTitle
Set-ADUser -Identity $UpdatedUser.EmployeeName -Department $UpdatedUser.EmployeeDepartment
Set-ADUser -Identity $UpdatedUser.EmployeeName -Office $UpdatedUser.EmployeeLocation
}

I know it is reading the csv file because the error references the first line of the file.
What am I missing or getting wrong.

Answer 1

This code assumes that the value in the "EmployeeName" column of the CSV is a display name and that there may be more than one employee that has the same name. It also correctly quotes the two filter strings.

Import-csv "c:\temp\User_Update_test.csv" |  
     ForEach-Object{  
         $EmployeeDN = (Get-ADUser -filter "DisplayName -eq '$($_.EmployeeName)'").DistinguishedName  
         $ManagerDN  = (Get-ADUser -filter "UserPrincipalName -eq '$($_.ManagerUPN)'").DistinguishedName  
         if ($EmployeeDN -and $ManagerDN -and $EmployeeDN -isnot [array]){  
             $props = @{  
                 Identity = $EmployeeDN  
                 Manager = $ManagerDN  
                 Title = $_.EmployeeTitle  
                 Department = $_.EmployeeDepartment  
                 Office = $_.EmployeeLocation  
             }  
             Set-ADUser @props  
         }  
         else {  
             Write-Host "Either the employee $($_.EmployeeName) or the manager $($_.ManagerUPN) could not be found in the AD\n or more than one user has the same display name: \n$($EmployeeDN)" -ForegroundColor Yellow  
         }  
     }  

Answer 2

Well, this: foreach ($UpdatedUser in $UpdatedUser) { is obviously wrong. You've used the same name on both sides of the "in". A common practice is to name things that hold more than one object using a plural form. E.g., foreach ($UpdatedUser in $UpdatedUsers){. That implies renaming the $UpdatedUser in the 1st line of your script to $UpdatedUsers, too.

Without an example of what your CSV file contains, two things are questionable (based on their column names):
The "EmployeeName" can't be just the name of the AD User object, and the "ManagerUPN" can't be a UPN.

ColumnName          MustBe  
----------          ------  
EmployeeName        distinguished name, GUID, SID, or samAcountName  
ManagerUPN          distinguished name, GUID, SID, or samAcountName  
Title               string  
Department          string  
EmployeeLocation    string  

Once you have a valid set of values in the CSV, try using this code:

Import-csv "c:\temp\User_Update_test.csv" |  
    ForEach-Object{  
        $EmployeeDN = (Get-ADUser -filter "UserPrincipalName -eq $($_.EmployeeName)").DistinguishedName  
        $ManagerDN  = (Get-ADUser -filter "UserPrincipalName -eq $($_.ManagerUPN)").DistinguishedName  
        if ($EmployeeDN -and $ManagerDN){  
            $props = @{  
                Identity = $EmployeeDN  
                Manager = $ManagerDN  
                Title = $_.EmployeeTitle  
                Department = $_.EmployeeDepartment  
                Office = $_.EmployeeLocation  
            }  
            Set-ADUser @props  
        }  
        else {  
            Write-Host "Either the employee $($_.EmployeeName) or the manager $($_.ManagerUPN) could not be found in the AD" -ForegroundColor Yellow  
        }  
    }  

There's no data validation being done in that script, nor is there any error handling. The script depends entirely on the CSV not having any errors in the data (e.g., leading or trailing spaces in a column).

EDIT: used UPN values to find the DN of the employee and manager. Added a small bit of error checking that identifies incorrect UPN values.

Answer 3

Hi @Stu Boughton ,

if you need the Distinguished Name of an user object you can just use the Get-AdUser cmdlet with the UPN to get the user object:

$userObjDN =  (Get-ADUser -Filter "UserPrincipalName -eq '<UPN of the user'").DistinguishedName  
$managerObjDN =  (Get-ADUser -Filter "UserPrincipalName -eq '<UPN of the Manager'").DistinguishedName  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten