Microsoft Teams – User’s Organization information

Scenario

I work for a big company that has lots of Branch Offices across the country and approximately 4000 users that has access the Office 365 and Teams. Constantly we need to know the user’s manager or superior to get approvals and confirm any kind of information. And Microsoft Teams has a great feature that shows us graphically the hierarchy of the user all the way to the CEO.

The organizational hierarchy from the user to the CEO.

Teams uses the information provided by Active Directory and Azure AD, the information used to build this tree is the Manager field, and by adding this information for all the users, Teams automatically understands the top-down hierarchy.

The challenge

Well this is easy to do if the Active Directory has a few users, you can add that information manually, but in a scenario where there are thousands of users this becomes challenging because users change roles and departments, and even managers are changed, so I thought I could build a way to automate this by using Powershell scripts.

The information I need is stored on an Oracle Database that is updated by Human Resources so the source is reliable instead of asking them to generate CSV files for me every often decided to extract that information by myself.

Oracle part

The first thing I need to do is to install Oracle Client to get the binaries and libraries to connect to the Oracle database.

Second, I need an Oracle user that has a SELECT grant on the table I need (which doesn’t have any sensitive information) and now I can start building my script.

The script – part 1

First I load the Oracle Client assembly.

Copy Code

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

Created a variable to receive the connection string.

Copy Code

$connstring = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<database_host>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<database_service>)));uid=<database_user>;<database_password>;"

Open the connection referencing the above connection string.

Copy Code

$connection = New-Object System.Data.OracleClient.OracleConnection($connstring);
$connection.Open()

Put the query to retrieve only the needed data into a variable.

Copy Code

$query = "SELECT STATEMENT HERE"

Now I create the object to actually retrieve the data.

Copy Code

$command1 = New-Object System.Data.OracleClient.OracleCommand;
$command1.Connection = $connection
$command1.CommandText = $query
$command1.CommandType = [System.Data.CommandType]::Text

Create a datatable and load the results.

Copy Code

$datatable = New-Object System.Data.DataTable
$datatable.Load($command1.ExecuteReader())

Read through the datatable and export to CSV.

Copy Code

$datatable | Select-Object -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | Export-Csv -Path C:\Temp\HR_users.csv -NoTypeInformation -Encoding utf8

Here is an example of the exported CSV.

Explaining the fields:

Departamento: is the information that will be stored in the Department field of Active Directory.

CPF_GESTOR: is the user’s manager’s ID.

CPF: it is the user ID.

FUNCAO: the information that will be stored in the JobTitle field.

The script – part 2

Luckily in the HR database table, the Manager ID is stored together with the user information so this will help us to find the DistinguishedName from the manager for that particular user, because the Set-ADUser, -Manager parameter, requires the DN of the user.

So I have created a function to retrieve the manager’s DN based on the ID, filtering for only enabled users. I will use that function later in the script.

Copy Code

Function pega_cn_gestor {
    Param([string] $descricao)
    $result = Get-ADUser -Properties Description -Filter {Description -like $descricao} | Where-Object $_.Enabled -eq $true | Select-Object -ExpandProperty DistinguishedName
    Return $result   
}

Set a working path OU.

Copy Code

$OUPath = 'DC=contoso,DC=local'

Load the previously generated CSV into a variable.

Copy Code

$Table1 = import-csv C:\Temp\HR_users.csv -Delimiter ","

Now I will search and loop for all the users on the path described above and store the current user’s information into variables for each iteration.

Copy Code

Get-ADUser -Filter enabled -eq $true -SearchBase $OUPath -Properties samaccountname,description,title,department,manager | ForEach-Object {
    $desc = $_.description
    $samacc = $_.SamAccountName
    $cargo = $_.title
    $departamento = $_.department
    $gestor_atual = $_.manager

My goal is to find the current user inside the loaded CSV, so I loop through the $Table1 comparing the CPF (from the CSV) to the variable $desc (from above Get-ADUser), if there is a match it means that we find the user correctly because this field is unique in our AD, after that I found the user I load the values from the CSV to variables that I will use to update the user’s information.

Copy Code

If ($Table1 | Where-Object {$_.cpf -eq $desc}) {
        $resulttabela = $Table1 | Where-Object cpf -like $desc
        $dep =  $resulttabela.departamento
        $cpfgestor = $resulttabela.cpf_gestor
        $jobtitle =  $resulttabela.funcao

Some people don’t have managers, like the CEO for example, so I added a condition to check it first, if the user has a manager, I will use my function to get its DistinguishedName, another condition to check if the user needs to be updated to avoid unnecessary changes and if everything passed, the Set-ADUser command is issued and updates the user’s information.

Copy Code

If ($cpfgestor -ne "") {
            $cn_gestor = pega_cn_gestor($cpfgestor)
            If ($cargo -ne $jobtitle -or $departamento -ne $dep -or $gestor_atual -ne $cn_gestor) {
                Set-ADUser -Identity $samacc -Department $dep -Manager $cn_gestor -Title $jobtitle
                
            }  
        } Else {
            If ($cargo -ne $jobtitle -or $departamento -ne $dep) {
                Set-ADUser -Identity $samacc -Department $dep -Title $jobtitle                
            }
        }   
    }
}

Now the complete script in one block.

Copy Code

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

$connstring = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<database_host>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<database_service>)));uid=<database_user>;<database_password>;"

$connection = New-Object System.Data.OracleClient.OracleConnection($connstring);
$connection.Open()

$query = "SELECT STATEMENT HERE"

$command1 = New-Object System.Data.OracleClient.OracleCommand;
$command1.Connection = $connection
$command1.CommandText = $query
$command1.CommandType = [System.Data.CommandType]::Text

$datatable = New-Object System.Data.DataTable
$datatable.Load($command1.ExecuteReader())

$datatable | Select-Object -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | Export-Csv -Path C:\Temp\HR_users.csv -NoTypeInformation -Encoding utf8


Function pega_cn_gestor {
    Param([string] $descricao)
    $result = Get-ADUser -Properties Description -Filter {Description -like $descricao} | Where-Object $_.Enabled -eq $true | Select-Object -ExpandProperty DistinguishedName
    Return $result   
}

$OUPath = 'DC=contoso,DC=local'
$Table1 = import-csv C:\Temp\HR_users.csv -Delimiter ","
Get-ADUser -Filter enabled -eq $true -SearchBase $OUPath -Properties samaccountname,description,title,department,manager | ForEach-Object {
    $desc = $_.description
    $samacc = $_.SamAccountName
    $cargo = $_.title
    $departamento = $_.department
    $gestor_atual = $_.manager
    
    If ($Table1 | Where-Object {$_.cpf -eq $desc}) {
        $resulttabela = $Table1 | Where-Object cpf -like $desc
        $dep =  $resulttabela.departamento
        $cpfgestor = $resulttabela.cpf_gestor
        $jobtitle =  $resulttabela.funcao
        
        If ($cpfgestor -ne "") {
            $cn_gestor = pega_cn_gestor($cpfgestor)
            If ($cargo -ne $jobtitle -or $departamento -ne $dep -or $gestor_atual -ne $cn_gestor) {
                Set-ADUser -Identity $samacc -Department $dep -Manager $cn_gestor -Title $jobtitle
                
            }  
        } Else {
            If ($cargo -ne $jobtitle -or $departamento -ne $dep) {
                Set-ADUser -Identity $samacc -Department $dep -Title $jobtitle                
            }
        }   
    }
}

Now I can add this script to a Scheduled Task or run it periodically so that I will have the user’s information always updated in our Active Directory and therefore, Microsoft Teams.

Thanks for reading.

Rodrigo Ogiwara