In the previous chapter of CRI, I discussed an option for inserting user UPNs into the 'Call Record Insights' query results. To further simplify the work of administrators, this post will explore how to automate the integration of user and UPN data from Entra ID. We'll cover how to automate the replacement of this table on a scheduled basis using Azure Automation (Runbooks) or PowerShell.
RecapThe previous posts on Teams Call Record Insights covered getting started and query updates to include additional data. Below are links to those chapters.
In the last session we reviewed how to create and populate user UPN's in the CRI database, and then use Kusto Query join to return the UPN in the call records query.
The real takeaway from the last chapter—and this post—isn't just about adding UPNs to call records; it's about empowering your organization with complete ownership, management, and flexibility of your records database. Once implemented, this data is yours to control, allowing you to retain, modify, and adapt it as your needs evolve.
Previously, we created the user_UPN table through the ADX web interface, setting up a new table and importing users, GUIDs, and UPNs using the data import tool in ADX.
But what if you need to ensure this user UPN table stays up-to-date daily, reflecting changes as users are added or removed from your tenant
Automation of table data
Given my familiarity with PowerShell and runbooks, and the added support from GitHub Copilot, this demonstration will focus on automating the process using PowerShell and Azure Automation (Runbooks). While this could also be achieved with C# or Python (I initially experimented with Python but ultimately chose PowerShell for the final solution), PowerShell’s robust capabilities made it the ideal choice for this task.
The Process
First, we'll connect to Entra ID and export the upn, display name, and GUID into a CSV file. Then, we'll rename the existing user_UPN table to keep a backup without including it in future queries. After that, we'll create a new, blank user_UPN table and import the CSV data from Entra ID to populate it with updated user information. From the previos session, I chose to export and populate upn, GUID, and display name but only upn and one of the other fields are required to match and join from the CRI table. Additionally, I've incorporated some error checking and output presentation for validation, which is helpful during setup but not necessary for the final runbook
Step 1 - connect and export user data
# Define the CSV path with a date stamp
$dateStamp = Get-Date -Format "yyyyMMdd"
$csvPath = "[your directory path]\userExport_$dateStamp.csv"
# Ensure you're connected to Azure
Connect-AzAccount -Tenant "[your tenant ID]"
# Fetch user information
$users = Get-AzADUser | Select-Object DisplayName, Id, UserPrincipalName
# Export to CSV
$userCount = $users.Count
Write-Host -ForegroundColor Blue "Number of users: $userCount"
$users | Export-Csv -Path $csvPath -NoTypeInformation
# Display in a table if needed
$users | Format-Table -AutoSize
# Prompt the user to proceed to update the Kusto database
$proceed = Read-Host "CSV file generated at $csvPath. Do you want to proceed with updates to CRI Kusto Database (yes/no)"
if ($proceed -ne "yes") {
Write-Host "Operation aborted by the user."
exit
}
Note - with runbook the Write-Host and Prompt to proceed should be omitted.
Step 2 - Import modules, libraries and set variables (Kusto libraries can be downloaded and locally referenced)
Reference THIS LINK for details on Kusto in PowerShell.
# Define the tenant ID, client ID, and client secret of the service principal
$tenantId = "[your tenant ID]"
$clientId = "[your client ID"
$clientSecret = "[your client secret]"
$authority = "[your tenant domain name]"
# Define the path to the Kusto client libraries
$kustoLibPath = "[your library path]\net6.0\"
# Load the Kusto client libraries in the correct order
Add-Type -Path "$kustoLibPath\Microsoft.Identity.Client.dll"
Add-Type -Path "$kustoLibPath\Kusto.Data.dll"
Note - ClientID, ClientSecret and Authority are needed when not using a user login, or when running script as runbook where service principle is required. This
section of the documentation explains the variables needed with User, Application or Azure CLI interaction with Kusto in PowerShell.
Step 3 - Create Kusto query with Kusto Custom String Builder and Client Request Properties
# Define Kusto connection string variables
$clusterUrl = "[your Azure Data Explorer cluster url]"
$databaseName = "CallRecordInsights"
# Create the Kusto connection string with the OAuth token if using service principal
$kcsb = [Kusto.Data.KustoConnectionStringBuilder]::new($clusterUrl, $databaseName)
$kcsb = $kcsb.WithAadApplicationKeyAuthentication($clientId, $clientSecret, $authority)
# Define client request properties
$crp = [Kusto.Data.Common.ClientRequestProperties]::new()
$crp.ClientRequestId = "UserUPNpsh.ExecuteQuery." + [Guid]::NewGuid().ToString()
$crp.SetOption([Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout, [TimeSpan]::FromSeconds(30))
Step 4a (run as user) or 4b (run as application)
Here is an example of a query to test connecting and querying the manually created user_UPN table and displaying the output to screen. (this would not be needed or valuable in a runbook)
# Query and present the existing table to test
$queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($kcsb)
$query = "user_upn | take 10"
Write-Host "Executing query: '$query' to test access to Kusto with connection string: '$($kcsb.ToString())'"
$reader = $queryProvider.ExecuteQuery($query, $crp)
$dataTable = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader).Tables[0]
$dataView = New-Object System.Data.DataView($dataTable)
$dataView | Sort DisplayName | Format-Table -AutoSize
Here is the steps to rename the existing table and create a new table, ingesting the previously created CSV file, and ignoring the top header row (we don't want to ingest the column headers as data in the Cosmos DB table). Also note: Write-Host and Proceed lines should be omitted in a runbook.
# Rename the existing user_upn table to user_upn_old to preserve the table
$renameQuery = ".rename table user_upn to user_upn_old"
Write-Host -ForegroundColor Blue "Executing rename query: '$renameQuery'"
# Execute the rename query
try {
$queryProvider.ExecuteControlCommand($renameQuery, $crp)
Write-Host "Table renamed successfully."
} catch {
Write-Host "Error renaming table: $_"
}
# Prompt the user to proceed to update the Kusto database
$proceed = Read-Host "User_upn renamed. Do you want to proceed with updates to CRI Kusto Database (yes/no)"
if ($proceed -ne "yes") {
Write-Host "Operation aborted by the user."
exit
}
# Define the create table query to recreate user_upn table with new upn data
$createTableQuery = @"
.create table user_upn (
DisplayName: string,
Id: guid,
UserPrincipalName: string
)
"@
Write-Host -ForegroundColor Blue "Executing create new table: '$createTableQuery'"
# Execute the create table query
try {
$queryProvider.ExecuteControlCommand($createTableQuery, $crp)
Write-Host "New table created successfully."
} catch {
Write-Host "Error creating new table: $_"
}
# Ingest data into the new table
$rowCount = (Import-Csv -Path $csvPath | Measure-Object).Count
Write-Host -ForegroundColor Blue "Executing table data ingest with $rowCount records:"
# Construct the ingestion query to ingest the CSV file into the table, and remove the column headers from the table data
$csvContent = Get-Content -Path $csvPath -Raw
$csvLines = $csvContent -split "`n"
$csvLinesWithoutHeader = $csvLines | Select-Object -Skip 1
$csvContentWithoutHeader = $csvLinesWithoutHeader -join "`n"
$tableName = "user_upn"
$ingestQuery = @"
.ingest inline into table $tableName <|
$csvContentWithoutHeader
"@
# Execute the ingestion query
try {
$queryProvider.ExecuteControlCommand($ingestQuery, $crp)
Write-Host "CSV file ingested successfully into the table $tableName."
} catch {
Write-Error "Failed to ingest CSV file: $_"
}
Write-host -ForegroundColor Green "User UPN data updated successfully. Please check Kusto database for the updated data."
Azure Automation
Create Automation Account
Assign Roles and Perms for Automation Account
Enable Managed Identity for the Azure Automation Account:
- Navigate to your Azure Automation account in the Azure portal.
- Under "Account Settings", select "Identity".
- Enable the System-Assigned Managed Identity or add a User-Assigned Managed Identity.
Assign Azure Roles:
- Go to the "Access control (IAM)" section of your subscription.
- Add a role assignment for the managed identity with the Contributor role at the subscription level. This ensures it has the necessary permissions to manage resources within the subscription.
- Additionally, assign the Reader role on the Azure AD to the managed identity. This allows it to read user information.
Grant Azure AD Permissions:
- Navigate to the Azure AD section in the Azure portal.
- Go to "App registrations" and find the managed identity.
- Under "API permissions", add the Directory.Read.All permission.
- Grant admin consent for the permission.
or run script to assign permissions
# Install the Microsoft Graph PowerShell module if not already installed
Install-Module Microsoft.Graph -Scope CurrentUser
# Connect to Microsoft Graph
Connect-MgGraph -Scopes "Application.ReadWrite.All", "AppRoleAssignment.ReadWrite.All", "Directory.Read.All"
# Find the managed identity
$DisplayNameOfMSI = "<Your-Managed-Identity-Name>"
$MSI = Get-MgServicePrincipal -Filter "displayName eq '$DisplayNameOfMSI'"
# Find the Microsoft Graph service principal
$GraphAppId = "00000003-0000-0000-c000-000000000000"
$GraphServicePrincipal = Get-MgServicePrincipal -Filter "appId eq '$GraphAppId'"
# Find the app role for Directory.Read.All
$PermissionName = "Directory.Read.All"
$AppRole = $GraphServicePrincipal.AppRoles | Where-Object {$_.Value -eq $PermissionName -and $_.AllowedMemberTypes -contains "Application"}
# Assign the app role to the managed identity
New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MSI.Id -PrincipalId $MSI.Id -ResourceId $GraphServicePrincipal.Id -AppRoleId $AppRole.Id
Lets build the automation account
Runbooks can be environment specific, we can load the Kusto Client DLLs from storage already created during the CRI deployment steps - from the NUGET file share in the CRI storage account. Optionally you could create blob storage and upload the libraries from this link - https://www.nuget.org/packages/Microsoft.Azure.Kusto.Data/
# Define the path to the Kusto client libraries
$kustoLibPath = "[your library path]\net6.0\"
# Load the Kusto client libraries in the correct order
Add-Type -Path "$kustoLibPath\Microsoft.Identity.Client.dll"
Add-Type -Path "$kustoLibPath\Kusto.Data.dll"
These libraries are required for the script to construct the Kusto client query, used in this context:
$kcsb = [Kusto.Data.KustoConnectionStringBuilder]::new($clusterUrl, $databaseName)
I also simplified the runbook, removing the dependency to write and store the user data in CSV, and just held the data in temp storage (JSON format) in the script.
# Fetch user information
$users = Get-AzADUser | Select-Object DisplayName, Id, UserPrincipalName
# Convert user data to JSON format for Cosmos DB ingestion
$usersJson = $users | ForEach-Object {
[PSCustomObject]@{
DisplayName = $_.DisplayName
Id = $_.Id
UserPrincipalName = $_.UserPrincipalName
}
}
In our query we get UPN's in our call records, with an automated process to keep things up to date:
Next
I hope this post helps share the flexibility and specific ways to integrate additional data into call records with automation. You may not need this specific use case, but experiment with data integration needs for your organization.
In our next chapter we will investigate visualizing Call Record Insights in Power BI, and the value of CRI data retention in comparison to Teams Call Quality Dashboard, and QER in PowerBI - and way to leverage CRI to investigate call issues and correlate call ID's between TAC and CRI.
Thanks
JB
