Using PowerShell to Update an AD User from a CSV file

I needed to update a bunch of AD Users with their new job title. A new year has started and some made promotion and got a new job title. So I received an Excel file with their full name and their new title from HR. Because this is happening every year, is it time to create a PowerShell script for it.

As a bonus, we are also going to send the user an email to notify them that their job title is changed. So they don’t have to contact IT for it anymore.


Importing the CSV File in PowerShell

To update the AD User we are going to use a CSV file. This allows us to use the import-csv cmdlet in PowerShell. If got the following Excel table:

namejobtitle
John Doesenior accountmanager
Jane Doeaccountmanager
Salina Scottengineer
Jasper Rempeljunior engineer

I have stored the CSV file in the same folder as the final script. To reference the file I like to use a variable to get the script root path:

$rootPath = (Get-Item $PSScriptRoot).FullName

Now to import the CSV file into PowerShell we use the following cmd:

$promotions = Import-Csv -Delimiter ";" -Path ($rootPath + "\promotions-2019.csv")

By default, the Delimiter is a comma ( , ), but you can change to by using the -Delimiter parameter. As you can see I am using the $rootPath to reference to the script location. You can also fill in the absolute path to the file here.

The result of $promotions is:

name    jobtitle
-------------
John Doe        senior accountmanager
Jane Doe        accountmanager
Salina Scott    Engineer
Jasper Rempel   junior engineer

Finding the AD user to update

The next step is to find the user in the Active Directory. We need to find the users based on their full name. We are going to use the Get-ADUser cmdlet for this and filter the results on the display name.

We don’t have one user, but a whole list. So we are going to use a foreach loop to walk through the list of users.

foreach($user in $promotions){

    # Find user
    $ADUser = Get-ADUser -Filter "displayname -eq '$($user.name)'"
}

So we are trying to get each user in the table promotions. Now a good practice is to implement a catch in case the user doesn’t exist. Maybe HR made a type which can result in the user not being found. Also, we need the user’s email address later on to send him the notification. Add the Property mail to the Get-ADUser cmd.

foreach($user in $promotions){

    # Find user
    $ADUser = Get-ADUser -Filter "displayname -eq '$($user.name)'" -Properties mail

    if ($ADUser){
     # <update the user>
    }else{
        Write-Warning ("Failed to update " + $($user.name))
    }
}

Updating the AD User

If the users exist in the Active Directory we can use the Set-ADUser to update an attribute. In this case the title attribute, but that can be any AD User related attribute.

foreach($user in $promotions){

    # Find user
    $ADUser = Get-ADUser -Filter "displayname -eq '$($user.name)'" -Properties mail

    if ($ADUser){
        Set-ADUser -Identity $ADUser -Title $user.jobtitle
    }else{
        Write-Warning ("Failed to update " + $($user.name))
    }
}

If you only want to update AD User from a CSV file then you are done. If you also want to send them an email to notify them about the change, then keep reading.

Sending the User an Email

I am using an email template to send the user an email.¬† The template is an¬†HTML file that you can download here on my Technet gallery along with this script. Before we can the email we need to replace some placeholders with the user’s name and a new title. Also, we need an SMTP server for sending the email.

Setting the STMP details

Below is a simple array with the SMTP details, like the server and from address.

#SMPT Details to send the email
$smtp = @{}
$smtp.address = "contoso-com.mail.protection.outlook.com"
$smtp.from = "IT Department <[email protected]>"
$smtp.subject = "Your job title is updated"

Creating the Email body

I have a function that I use for creating the email body. Using functions allow me to reuse parts of code in different scripts. The first function will get the email template and replace the placeholders with the correct data.

Function Get-EmailTemplate
{
	PARAM(
		[parameter(Mandatory=$true)]
		$user,
		[parameter(Mandatory=$true)]
		$jobtitle
	)

	PROCESS
	{
		#Get the mailtemplate
		$mailTemplate = (Get-Content ($rootPath + '\MailTemplate.html')) | ForEach-Object {
			$_ 	-replace '{{user.jobtitle}}', $jobtitle`
			-replace '{{user.firstname}}', $user.givenName
		} | Out-String	
		
		return $mailTemplate
	}
}

In the foreach loop, after the Set-ADuser cmd we add the following line to call the function an build the email body:

 $emailBody = Get-EmailTemplate -user $ADUser -JobTitle $user.jobtitle

Sending the email

The last function is for sending the email. It takes the SMTP details from the SMTP array and uses the supplied information to send the email. I added a whatif variable in it so you can run a test before sending the actual email.

Function Send-Mail
{
	PARAM(
		[parameter(Mandatory=$true)]
		$emailBody,
		[parameter(Mandatory=$true)]
		$user,
		[parameter(Mandatory=$false)]
		[bool]$whatIf
	)
	
    PROCESS
	{
		#Set encoding
		$encoding        = [System.Text.Encoding]::UTF8

		Try 
		{
			if ($whatIf -ne $true)
			{
				send-MailMessage -SmtpServer $smtp.address -To $user.mail -From $smtp.from -Subject $smtp.subject -Encoding $encoding -Body $emailBody -BodyAsHtml
			}
			else
			{
				Write-host ("Send mail to -SmtpServer " + $smtp.address + " -To " + $user.mail + " -From " + $smtp.from + " -Subject $smtp.subject")
			}
		}
		Catch
		{
			Write-Error "Failed to send email to, $_"
		}

	}
}

We call this function with the following cmd that we add below the email body:

Send-Mail -user $ADUser -EmailBody $emailBody

So the complete foreach loop now looks like this:

foreach($user in $promotions){
    #find user
    $ADUser = Get-ADUser -Filter "displayname -eq '$($user.user)'" -Properties mail

    if ($ADUser){
        Set-ADUser -Identity $ADUser -Title $user.jobtitle -WhatIf

        $emailBody = Get-EmailTemplate -user $ADUser -JobTitle $user.jobtitle
        Send-Mail -user $ADUser -EmailBody $emailBody -whatIf $true
    }else{
        Write-Warning ("Failed to update " + $($user.user))
    }
}

Notice that I added two WhatIf flags. One for the Set-ADUser and one for the Send-mail. This way you can run your script to test it before actually changing or sending anything.

Conclusion

I hope the code above helped you updating your AD User from a CSV file with PowerShell. If you have any question just add a comment below. You can download the script, csv-file and email template here on my TechNet Gallery.

Get more stuff like this

IT, Office365, Smart Home, PowerShell and Blogging Tips

I hate spam to, so you can unsubscribe at any time.

2 thoughts on “Using PowerShell to Update an AD User from a CSV file”

  1. Great post and I’m trying to run the script but keep getting the following error:
    Get-ADUser : The search filter cannot be recognized
    At C:\Script\UpdateADUser.ps1:5 char:15
    + $ADUser = Get-ADUser -Filter “displayname -eq ‘$($user.name)'”
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    WARNING: Failed to update

    • In the post, I have an example table with the fields User and Jobtitle, but the scripts are looking for name and jobtitle. Make sure you refer to the correct field names, I will update the article.

Leave a Comment