Forum Discussion

jebujohn's avatar
jebujohn
Brass Contributor
May 08, 2020
Solved

Bulk update Azure AD with user attributes from CSV

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 her...
azure active directory
office 365
  • Manfred101's avatar
    Manfred101
    May 08, 2020

    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

Jacob John's avatar
Jacob John
Copper Contributor
May 22, 2020

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

Manfred101's avatar
Manfred101
Iron Contributor
May 22, 2020

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 

  • Tamerhalawah's avatar
    Tamerhalawah
    Copper Contributor
    Nov 27, 2020

    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 

    • BucheronPW's avatar
      BucheronPW
      Copper Contributor
      Apr 01, 2022

      Tamerhalawah 

      i did it like That: 

       

      # Loop trough CSV records
      foreach ($CSVrecord in $CSVrecords) {
      $upn = $CSVrecord.UserPrincipalName
      $upnmanager = $CSVrecord.Manager
      $user = Get-AzureADUser -Filter "UserPrincipalName eq '$upn'"
      $Manager = Get-AzureADUser -Filter "UserPrincipalName eq '$upnmanager'"
      if ($user) {
      try{
      $user | Set-AzureADUser -City $CSVrecord.City -CompanyName $CSVrecord.CompanyName -Department $CSVrecord.Department -StreetAddress $CSVrecord.StreetAddress -PostalCode $CSVrecord.PostalCode -JobTitle $CSVrecord.JobTitle -Mobile $CSVrecord.Mobile -PhysicalDeliveryOfficeName $CSVrecord.PhysicalDeliveryOfficeName
      Set-AzureADUserManager -ObjectId $user.ObjectId -RefObjectId $Manager.objectId
      } catch {
      $FailedUsers += $upn
      Write-Warning "$upn user found, but FAILED to update."
      }
      }
      else {
      Write-Warning "$upn not found, skipped"
      $SkippedUsers += $upn
      }
      }

       

      Thanks au lot to all of you ! 

    • jpcaid's avatar
      jpcaid
      Copper Contributor
      Nov 27, 2020
      i think you can use this
      $user | Set-AzureADUser Manager $CSVrecord.Manager
    • wllrkn's avatar
      wllrkn
      Copper Contributor
      Nov 27, 2020
      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.
      • sunJeezy's avatar
        sunJeezy
        Copper Contributor
        Nov 27, 2020

        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-

  • sunJeezy's avatar
    sunJeezy
    Copper Contributor
    Nov 27, 2020

    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-

     

    • jpcaid's avatar
      jpcaid
      Copper Contributor
      Nov 27, 2020

      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

      • leewegener's avatar
        leewegener
        Copper Contributor
        Dec 09, 2020

        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?

  • jebujohn's avatar
    jebujohn
    Brass Contributor
    May 22, 2020

    Thanks Manfred101 

    It worked like a charm.

    Thanks. Stay safe.

     

    Jacob

Resources