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]

i think you can use this
$user | Set-AzureADUser Manager $CSVrecord.Manager

[#3]

@wllrkn 

Can you use Set-ADUser in the Azure AD Powershell console?  We are running Azure AD and do not have an on-premise AD server.

Thanks-

[#4]

yea i did this on azure ad

[#5]

I just tried loading up powershell and typing,

Connect-AzureAD
Get-ADUser

Received the following error:

Get-ADUser : The term 'Get-ADUser' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Get-ADUser
+ ~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Get-ADUser:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

[#6]

@sunJeezy lets see the whole script

[#7]

@Srini1987I have been looking into similar functionality this evening and have achieved with the following changes to the really useful code already included.

The -objectId can be populated from the id field in the Azure Active Directory Export users CSV.

foreach ($CSVrecord in $CSVrecords) {
$ObjectID = $CSVrecord.ObjectID
$user = Get-AzureADUser -ObjectID "$ObjectID"
if ($user) {
try{
$user | Set-AzureADUser -Department $CSVrecord.Department -TelephoneNumber $CSVrecord.TelephoneNumber
} catch {
$FailedUsers += $ObjectID
Write-Warning "$ObjectID user found, but FAILED to update."
}
}
else {
$SkippedUsers += $ObjectID
Write-Warning "$ObjectID not found, skipped"
}
}

[#8]

The Azure_manager.csv file contains userprincipalname;manager

# Connect to AzureAD

Connect-AzureAD

# Get CSV content

$CSVrecords = Import-Csv C:\Users\k4rna\Desktop\azureinfocorrection\azure_manager.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 -Manager $CSVrecord.Manager 

        } 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

[#9]

@sunJeezyYou need to install the AzureModule (install-module azuread)

[#10]

@sunJeezy copy my script posted above and edit it to suit you.

also make sure there is no spaces in your CSV file

[#11]

There are no spaces in the CSV. I've modified the script a bunch of times in order to update all the fields for my 1500 users, which worked flawlessly. The manager piece is what I am having a challenge with.

I already have the AzureAD module installed. I've already updated all the other fields with no issues.

Going back to the documentation for Set-AzureADUser (https://docs.microsoft.com/en-us/powershell/module/azuread/set-azureaduser?view=azureadps-2.0) I do not see "Manager" listed as a parameter.

[#12]

@sunJeezy 

The appropriate command is set-azureadusermanager

you will need the azureAD objectid of the manager, which we will have to first call. So this script will not work as is.

$user=$CSVrecord.UserPrinicipalName

$Manager= $CSVrecord.Manager

$ManagerObj=Get-AzureADUser -Objectid $Manager

Set-AzureADUserManager -ObjectId $User -RefObjectId $ManagerObj.ObjectId

In your specific instance, it may be simple to populate the csv with the objectid of the single manager or the script with the objectid as only one manager exists. 

[#13]

@Manfred101 

Hi,

PS Code turns "unsupported or invalid query filter clause specified for property userprincipalname" on my environment.

I'm trying change jobtitle and mobile attributes and edited 17th line to "Set-AzureADUser -JobTitle $CSVrecord.JobTitle -Mobile $CSVrecord.Mobile"

Do you have any suggestions ?

Regards

[#14]

@jpcaid If you can't use spaces in the .csv, how do you handle things like Job titles or department names that have spaces in them?

[#15]

@leewegener thats fine - i mean after the "," or at the end of the line

[#16]

@cevatcekli Please include the script you try to run together with your csv (example data). Also make sure you are using the latest module.

 # Get My Module verion
 Get-Module AzureAD | Select Version

 # Get latest PSGallery version
 Find-Module AzureAd | Select Version

 # Update Module
 Get-Module AzureAD | Update-Module

[#17]

Having the same issue as @cevatcakli even after including the module update lines
Error:

Get-AzureADUser : Error occurred while executing GetUsers
Code: Request_UnsupportedQuery
Message: Unsupported or invalid query filter clause specified for property 'userPrincipalName' of resource 'User'.
RequestId: 5cfe785f-7d53-4fcd-b74e-b411d1b5db09
DateTimeStamp: Fri, 18 Dec 2020 09:32:43 GMT
HttpStatusCode: BadRequest
HttpStatusDescription: Bad Request
HttpResponseStatus: Completed
At line:23 char:13
+ $user = Get-AzureADUser -Filter "userPrincipalName eq '$upn'"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureADUser], ApiException
+ FullyQualifiedErrorId : Microsoft.Open.AzureAD16.Client.ApiException,Microsoft.Open.AzureAD16.PowerShell.GetUser

[#18]

@Manfred101i have modified your script as per my need but somehow its throwing errors, with first four field its working perfect but as i added another entries it says the "WARNING: xyz@abc.org user found, but FAILED to update. please note i have changed the delimiter as per my regional settings. any help regarding this would be appreciated.

# Connect to AzureAD
Connect-AzureAD

# Get CSV content
$CSVrecords = Import-Csv C:\Users\neofreedom\Desktop\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 -jobTitle $CSVrecord.jobTitle -Department $CSVrecord.Department -state $CSVrecord.state -country $CSVrecord.country -officeLocation $CSVrecord.officeLocation -city $CSVrecord.city -postalCode $CSVrecord.postalCode -TelephoneNumber $CSVrecord.TelephoneNumber -mobilePhone $CSVrecord.mobilePhone
        $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

[#19]

@mesuhaib this script is not going to work. On line 16 you are starting a try{} block, but you are missing the catch {} part. A try always needs a catch! Check out this video: (295) Using Try/Catch Blocks In PowerShell - YouTube . You need to resolve that. 

Whene you ar done fixing the try block, you can troubleshoot line 17 by commenting out the options one-by-one: (see below, in this line you only set the jobtitle and department value. Try to find where things are going wrong)

$user | Set-AzureADUser -jobTitle $CSVrecord.jobTitle -Department $CSVrecord.Department #-state $CSVrecord.state -country $CSVrecord.country -officeLocation $CSVrecord.officeLocation -city $CSVrecord.city -postalCode $CSVrecord.postalCode -TelephoneNumber $CSVrecord.TelephoneNumber -mobilePhone $CSVrecord.mobilePhone

Good luck!

Grtz, Manfred de Laat

[#20]

@Manfred101 could you help me out too please?

[#21]

@Manfred101 

Nevermind I solved it!

I had to update the exo V2 module and login with MFA

And there was an issue with my source-file -> text to column in excel messed up the file, hoever this resolved that issue.

After that it worked like a charm! Thx man.