Bulk update Azure AD with user attributes from CSV

[jebujohn]

I am looking for a way to update user attributes (OfficePhone and Department) for about 500 users from a CSV to AzureAD using a powershell. Does anyone know of a script that I could use? I am new here and if I have not given enough information, please let me know. I tried using Set-AzureADUser piping records using a foreach statement from a csv that I imported, but it was throwing up errors. 

Thanks!

Jacob

[#2 · Accepted solution]

@jebujohn 

 Hello Jacob,

Your  CSV has to look something like this:

UserPrincipalName;Department;TelephoneNumber
manfreddelaat@domain.nl;IT;0135113333
manfred@domain.nl;IT;0622222222

Your Powershell code:

# Connect to AzureAD
Connect-AzureAD

# Get CSV content
$CSVrecords = Import-Csv C:\Temp\Test.csv -Delimiter ";"

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

# Loop trough CSV records
foreach ($CSVrecord in $CSVrecords) {
    $upn = $CSVrecord.UserPrincipalName
    $user = Get-AzureADUser -Filter "userPrincipalName eq '$upn'"  
    if ($user) {
        try{
        $user | Set-AzureADUser -Department $CSVrecord.Department -TelephoneNumber $CSVrecord.TelephoneNumber
        } catch {
        $FailedUsers += $upn
        Write-Warning "$upn user found, but FAILED to update."
        }
    }
    else {
        Write-Warning "$upn not found, skipped"
        $SkippedUsers += $upn
    }
}

# Array skipped users
# $SkippedUsers

# Array failed users
# $FailedUsers

Good luck!

Kind Regards, Manfred de Laat

[#3]

Thanks @Manfred101 

Worked like a charm. Lovely to learn from you!

Keep safe and stay blessed

[#4]

@jebujohnYou're welcome! Take care! 

[#5]

Dear @Manfred101,

In the same scenario if I wanted to add -EmployeeID from a csv, would it be possible to use a similar script. -EmployeeID seems to be an azureaduserextension.

Jacob

[#6]

@Jacob John Just add this line below line 17:

$user | Set-AzureADUserExtension  -ExtensionName "employeeId" -ExtensionValue $CSVrecord.employeeId

And make sure you add the "employeeId" records are present in your CSV file.

Good Luck!

Grtz, Manfred de Laat 

[#7]

Thanks @Manfred101 

It worked like a charm.

Thanks. Stay safe.

Jacob

[#8]

@Manfred101 

Thanks for sharing script here to update user details. But, I would draw your  attention that script is working with only one user details, when I do it with more than one  row, its giving the following warning and finally users details not updated.

Here is the CSV file content:

Can you help me to resolve this issue?

[#9]

@Manfred101 Thank you for the script, i have two questions,

We update the AD attributes based on the EmployeeId, can the script be run by the EmployeeID instead of the upn, if yes, please how?

is the powershell script handle about 5k users or it's limited to a number of users only?

thank you in advance.

Daniel.

[#10]

Hi... thanks fr your script

its not adding for guest user

anything in need to do for guest user

@Manfred101 

[#11]

@Srini1987 

The UPN of a guest users is a bit different from normal users. Make sure you can map them from your csv file. Should look like this: m.delaat_mycompany.nl#EXT#@yourTenantname.onmicrosoft.com

Good luck!

Manfred de Laat

[#12]

@Manfred101Hi, i am traying to make bulk settings for guest users. But i receive the following error message 

Get-AzureADUser : Error occurred while executing GetUsers
Code: Request_UnsupportedQuery
Message: Unsupported or invalid query filter clause specified for property 'userPrincipalName' of resource 'User'.

I checked a script many times and everything seems good, also i changed proper UPN for "Guest user" in my CSV file.

Thanks in advanced for help.

[#13]

@DK_Belgrade 

I am getting the same issue - did you get it working?

[#14]

@jpcaid and @DK_Belgrade , I am very busy at the moment guys. But I will have a look at this in the next couple of days. Grtz, Manfred de Laat

[#15]

You need to change line 5 to

$CSVrecords = Import-Csv C:\Temp\Test.csv

 Then the script should work. (You do not need to delimit the ";")

[#16]

@Manfred101 

I'm having trouble bulk updating the Manager field.  Would I be able to use this script to bulk update the managers property for the individual users?

Thanks-

[#17]

@wllrkn  Thanks for your feedback  - I made the changes.

I also noticed spaces in my CSV file and i also removed fields not needed. 

Cheers buddy 

[#18]

@sunJeezy 

maybe - i just managed to do job title and department 

I changed the highlighted line below so maybe you can add -Manager $CSVrecord.Manager

Make sure there are no spaces in your CVS.

$user = Get-AzureADUser -Filter "userPrincipalName eq '$upn'"
if ($user) {
try{
$user | Set-AzureADUser -Department $CSVrecord.Department -jobTitle $CSVrecord.jobTitle

[#19]

@Manfred101

can you please advice how i can change the manager in bulk for all employees? 

there is just one manager account and 130 employees, all of them need to have this manager in Azure AD in order for a flow to work. 

thanks 

[#20]

https://docs.microsoft.com/en-us/powershell/module/addsadministration/set-aduser?view=win10-ps

Here a full list of attributes for the Set-ADUser command.