In my last project related to migration our organization from on premises Active Directory to Microsoft 365 I have a task to consider how to add data used by organization tab in Microsoft Teams. Before that our IT team who manage users in Active Directory don’t fill fields in users objects on Organization tab like:
- Job Title
- Department
- Manager
This fields are used by Teams and Exchange to build organization structure and can be used for basic info about employees in our company. I’ve decided to do that task in a few steps:
- Export CSV file with users data from Active Directory containing columns Job Title, Department and Manager to be filled by HR team
- Give generated CSV file to HR team to completing the data
- Import CSV file to Active Directory and update users data
- Make sync with Azure AD Connect
I want to add that synchronization between Active Directory and Azure Active Directory (place where user accounts are stored and connected with Microsoft 365) is made by Azure AD Connect program. I have configured Azure AD Connect with my Azure subscription and all my test users (F1 race drivers and theirs managers) from one OU are synced.
Export users from Active Directory
To do that I’ve written PowerShell script which search domain, find users objects and export them to CSV file.
# Exclude OUs from domain search
$ExcludedOUs = @( "Domain Controllers",
"Builtin",
"Computers",
"Users",
"ForeignSecurityPrincipals")
$CSVFile = 'C:\Temp\ADUsers.csv'
# Get all OUs from AD
$AllOUs = @(Get-ADOrganizationalUnit -Filter {objectClass -eq "organizationalUnit"}) | Select-Object -ExpandProperty Name
foreach ($OU in $AllOUs) {
if ($OU -notin $ExcludedOUs) {
# Get DN for SearchBase switch for Get-ADUser cmdlet
$SearchBase = @(Get-ADObject -Filter {Name -eq $OU -AND ObjectClass -eq "organizationalUnit"}) | `
Select-Object $_.DistinguishedName
# Write on console
Write-Host Organizational Unit Name: $OU -ForegroundColor Green
$Users = Get-ADUser -Filter * -Properties * -SearchBase $SearchBase | `
Select-Object -Property SID, `
GivenName, `
Surname, `
UserPrincipalName, `
Title, `
Department, `
Manager, `
DistinguishedName
# For each user in OU
foreach ($User in $Users) {
# If user don't have manager or he is manager himself
if ([string]$User.Manager -eq "") {
$Manager = ""
} else {
$Manager = Get-ADUser -Identity $User.Manager | Select-Object -ExpandProperty Name
}
# Write on console
Write-Host First Name: $User.GivenName - `
Last Name: $User.Surname - `
UPN: $User.UserPrincipalName - `
Job Title: $User.Title - `
Department: $User.Department - `
Manager: $Manager
# Generate csv file from objects
$CSVOutput = New-Object -TypeName PSObject -Property @{
"SID" = $User.SID
"Name" = $User.GivenName
"Surname" = $User.Surname
"Login" = $User.UserPrincipalName
"Job Title" = $User.Title
"Department" = $User.Department
"Manager" = $Manager
"DistinguishedName" = $User.DistinguishedName
}
$CSVOutput | Select-Object "SID", "Name", "Surname", "Login", "Job Title", "Department", "Manager", "DistinguishedName" `
| Export-Csv -Path $CSVFile -Append -Encoding UTF8 -NoTypeInformation
}
}
}
After running above script without errors in directory C:\Temp\ file ADUsers.csv will be created very similar to this on screen below.
This file must be delivered to HR team who have the most updated information about employees and they should help to complete the data (Microsoft Excel is perfect tool to do this) from columns Job Title, Department and Manager.
Import updated users to Active Directory
To import updated attributes back to Active Directory I’ve also written PowerShell script which I present below:
$CSVFile = 'C:\Temp\ADUsers.csv'
$LogFile = 'C:\Temp\ImportADUsers.log'
$CSVObj = Import-Csv -Path $CSVFile -Header 'SID', `
'Name', `
'Surname', `
'Login', `
'Job Title', `
'Department', `
'Manager'
# For each row in CSV without headers line
foreach ($line in ($CSVObj | Select-Object -Skip 1)) {
# Get user from AD using login row from CSV file
$ADUser = Get-ADUser -Filter "UserPrincipalName -eq '$($line.Login)'"
$Login = $line.Login
$Date = Get-Date -Format "MM/dd/yyyy HH:mm:ss"
# If values of name, surname, login and SID are the same in CSV file and in AD
if ($line.Name -eq $ADUser.GivenName -AND `
$line.Surname -eq $ADUser.Surname -AND `
$line.Login -eq $ADUser.UserPrincipalName -AND `
$line.SID -eq $ADUser.SID ) {
# If manager column in this row is not empty in CSV file
if ($line.Manager) {
# Getting manager object from AD using value from CSV file
$ManagerFromFile = Get-ADUser -Filter "Name -like '$($line.Manager)*'"
# User's manager in AD
$ManagerInAD = Get-ADUser -Identity ([string]$line.Login.Split('@')[0]) -Properties Manager | Select-Object -ExpandProperty Manager
# If user's manager in AD isn't setted or manager in file is different from in AD
if (-not($ManagerInAD) -OR ([string]$ManagerFromFile -ne [string]$ManagerInAD)) {
# Set manager from CSV file
$ADUser | Set-ADUser -Manager $ManagerFromFile
$Manager = $line.Manager
$ManagerChanged = "- Manager Changed: $Manager"
}
}
# If job title column in this row is not empty in CSV file
if ($line.'Job Title') {
# Getting job title property from AD using value from CSV file
$JobTitleFromAD = Get-ADUser -Identity ([string]$line.Login.Split('@')[0]) -Properties Title | Select-Object -ExpandProperty Title
# If user's job title in AD isn't setted or job title in file is different from in AD
if (-not($JobTitleFromAD) -OR ($JobTitleFromAD -ne $line.'Job Title')) {
# Set job title from CSV file
$ADUser | Set-AdUser -Title $line.'Job Title'
$JobTitle = $line.'Job Title'
$JobTitleChanged = "- Job Title Changed: $JobTitle"
}
}
# If department column in this row is not empty in CSV file
if ($line.Department) {
# Getting department property from AD using value from CSV file
$DepartmentFromAD = Get-ADUser -Identity ([string]$line.Login.Split('@')[0]) -Properties Department | Select-Object -ExpandProperty Department
# If user's department in AD isn't setted or department in file is different from in AD
if (-not($DepartmentFromAD) -OR ($DepartmentFromAD -ne $line.Department)) {
# Set department from CSV file
$ADUser | Set-AdUser -Department $line.Department
$Department = $line.Department
$DepartmentChanged = "- Department Changed: $Department"
}
}
# If manager, job title or department changed in AD write to log file
if ($ManagerChanged -OR $JobTitleChanged -OR $DepartmentChanged) {
"$Date Login: $Login $ManagerChanged $JobTitleChanged $DepartmentChanged" | Tee-Object -FilePath $LogFile -Append
Clear-Variable -Name ("ManagerChanged", "JobTitleChanged", "DepartmentChanged") -ErrorAction SilentlyContinue
}
# If one of values name, surname, login and SID are the not the same in CSV file and in AD
} else {
"$Date Login: $Login - ERROR: Data from file and in AD are not the same" | Tee-Object -FilePath $LogFile -Append
}
}
Script also generates log file with changed user attributes. We only have one error about admin account. I did not change its data but error may be involved by Administrator account which also is catched by filter. I don’t recommend to change administrative account in that way and give you advice to always make a copies 🙂
Organization tab in Teams and Exchange OWA
After our updates of users attributes and Azure AD Connect synchronization without errors changes in Azure AD, Teams and Exchange may be checked. If everything done correctly updated data is visible in AAD Users:
Let’s check Teams and Exchange OWA:
That is end, goal was achieved – organizational tabs in Microsoft 365 apps are now usable. In this example I’ve used people connected with F1 and I don’t know that connections between users are correct but this is only example and I am a big fan of F1, more race drivers than their managers 🙂 I think this will be helpful for you.