Unlocking Insights: Deploying and Utilizing Microsoft Teams Call Record Insights

The "Call Record Insights" application template on GitHub is a comprehensive solution for retrieving, parsing, and storing Microsoft Teams call records. Using the Microsoft Graph API, this template simplifies data retrieval and storage into Cosmos DB and Kusto in Azure, providing scalability and deep data analysis capabilities. It is fully deployable within your tenant, ensuring data processing and storage are managed according to your organizational needs. The repository includes detailed deployment instructions, architecture guidelines, and administrative functions, making it a valuable resource for administrators aiming to manage Teams call records efficiently.

https://github.com/OfficeDev/microsoft-teams-apps-callrecord-insights

Foundation:

In this post, we'll explore the Call Record Insights application: what it is, how to deploy it, and methods to access and enhance its data. We'll explain how these records differ from traditional Call Detail Records (CDRs) and discuss automation strategies for accessing records and gaining insights. By the end, you'll have an understanding of how to leverage this tool to maximize your Microsoft Teams data management and analysis capabilities.

Deployment:

Pre-requisites: Ensure you have an Azure subscription, access to Microsoft Teams, and required permissions.

Clone Repository: Clone the GitHub repository to your local machine (or download and extract the ZIP package)

Azure Resources and Parameters: Define the necessary Azure resource parameters following the GitHub guidance. Here is an example:

$DeploymentParameters = @{

•   ResourceGroupName = 'cridemo1rg'
•   BaseResourceName  = 'cridemo1'
•   SubscriptionId    = 'YourSubscriptionID'
•   TenantDomain      = 'YourTenantDomain.com'
•   Location        = 'EASTUS'
• }

Configure and Deploy: Use the provided scripts and templates to configure and deploy the solution in your Azure environment.

MWCRI\deploy> .\deploy.ps1 @DeploymentParameters

Follow the deployment script output:

Once Deployed your resource group will look similar to this:

Note this post wont cover much troubleshooting of the deployment or health once deployed, but you can leverage Function App Logs to review errors when needed.

Accessing Records:

With deployment complete, first thing we want to do is "see" the data.

There are multiple ways to access Call Record Insights data. Here we will outline a few.

Option 1 - Direct from Azure Portal:

Navigate to the defined resource group and select the Azure Data Explorer Cluster for Call Record Insights.

Expand the Data section and select query. In this view I also expanded the CallRecordsInsights database so we can see the table name "CallRecords". A simple KQL: CallRecords | Take 100 will return the first 100 records in the lower right quadrant of the Explorer panel. Click "Run" after inserting the query.

Note in the upper right corner you can then export these records to Excel CSV.

Option 2 - Azure Data Explorer (ADX) web:

From the overview section of the Azure Data Explorer Cluster, copy the URI and paste into a a browser.

Note this method does require permissions to access the cluster, make sure your user has access permissions, and authenticated as the appropriate user and directory (this can be found in the upper right corner of the Data Explorer interface).

Option 3 - Kusto Explorer

More information and installation links can be found here - Kusto.Explorer installation and user interface - Azure Data Explorer | Microsoft Learn

Kusto Explorer is designed with a focus on data exploration and ad-hoc querying, making it easier for users who need to quickly analyze data without the need for extensive management capabilities.

Once Kusto Explorer (KE) is installed, select Add Connection.

Insert the URI from ADX (noted in Option 2 above) into the Cluster Connection Field. The Connection Alias can be set to any familiar name for Call Record Insights. 

Note if you are connecting KE to and ADX instance outside your traditional tenant ID - you can modify the Advanced Connection String to include the tenant ID required, which will introduce the interactive login prompt.

Data Source=https://<cluster-name>.kusto.windows.net;Initial Catalog=<database-name>;AAD Federated Security=True;Authority=<tenant-ID>

Once connected, select your Call Records Insights Database, right click, and launch a new query tab.

Enhanced Data Collection:

Once you have access to the records in Cosmos DB, it's crucial to understand the data. Each record contains up to 244 fields, but not all fields are populated for every record. The Microsoft Graph API populates fields based on call type, stream direction, and components used.

Understanding call types is essential. Unlike traditional CDRs, modern cloud calling solutions include additional components like multiple participants, video, screen sharing, and IVR endpoints. This makes the data more complex, with streams representing various call aspects.

To manage the extensive data, you can use KQL (Kusto Query Language) to focus on key fields for easier analysis and filtering. This helps streamline the process of sorting through thousands of records and extracting meaningful insights. 

Below is a KQL example - Select from CallRecords, the past 24 hours of records, sort by oldest to newest, AND .... insert a calculation for call duration. This field is not natively provided from the Graph API endpoint, but this value can be calculated using CallEndTime and CallStartTime using "extend", and then display the desired fields using "project".

CallRecords

| where CallEndTime >= ago(24h)

| sort by CallStartTime desc

| extend CallDuration = todatetime(CallEndTime) - todatetime(CallStartTime)

| project CallId, SessionId, StreamDirection, MediaLabel, CallStartTime, CallEndTime,

CallDuration, Caller_UserDisplayName, Caller_PhoneId, Callee_UserDisplayName, Callee_PhoneId,

Organizer_UserDisplayName, Caller_UserAgentHeaderValue, Callee_UserAgentHeaderValue

In the "project" statement here - I selected 13 of the available fields in the database and added the calculated field of call duration.

For an additional filter (only audio records for calls) we could modify the following KQL:

| where CallEndTime >= ago(24h) and MediaLabel == "main-audio"

Keep in mind there are potentially 244 available fields available, depending on what insights you chose to evaluate - including stream type, audio and video codec, audio quality and packet metrics.  

Record Example Types:

Remembering that each record is a call leg, or stream, understanding the field terms will be helpful in classifying, or identifying individual calls. Reminder in the examples below I have only selected certain fields using KQL - there are many more available for your particular analysis.

CallID - Call Record Identifier

SessionID - identifies individual sessions as part of the CallID

MediaLabel - Identifies the payload type for each stream (for phone calls over IP or PSTN, we use "main-audio")

-- Apologies for the format here - displaying long records doesn't fit well on the screen (Click the image for a full view) --

Example: Teams User to Outbound PSTN call (Direct Routing)

Note we see the caller (Caller_UserDisplayName) identified Administrator, and the callee (Callee_PhoneID) identified by PSTN digits 

Example: User to User (Click-to-Call within Teams)

In this example we know the caller and callee Display Names and there was no PSTN endpoints involved so Caller_PhoneID and Callee_PhoneID are both blank

Example: Inbound PSTN (Direct Routing), to Auto Attendant, transfer to Teams User

It's important to recognize how fields are populated based on call direction. The Caller fields (either Caller_UserID or Caller_PhoneID) identify the call originator.

I plan to publish a follow-up post with additional call examples and explanations, including Auto Attendant, Call Queue, Call Transfer, and Meetings with PSTN dial-in/out. Stay tuned for chapter 2.

Automate Data Collection:

While Call Record Insights (CRI) already operates in near real-time as a function app, you might want to automate the collection of records at specific intervals. For example, you could collect records every 24 hours and export them to Excel, SQL, or Power BI for analysis or cost management. Cosmos DB stores all collected records, allowing you to query them as needed, but scheduled exports can enhance your data analysis and reporting capabilities.

In PowerShell a script can run to perform the KQL query and export to CSV.

# Interactive Login

Connect-AzAccount

# Non-Interactive Login (Optional)

# Uncomment and fill in the details if using a service principal)

# $tenantId = "<Your-Tenant-ID>"

# $appId = "<Your-App-ID>"

# $password = ConvertTo-SecureString "<Your-Password>" -AsPlainText -Force

# Connect-AzAccount -ServicePrincipal -Credential

(New-Object System.Management.Automation.PSCredential($appId, $password)) -Tenant $tenantId

# Install the required modules

Install-Module -Name Az.CosmosDB

Install-Module -Name Az.Kusto

# Set Output File Path with timestamp

$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

$filePath = "C:\yourfilepath_$timestamp.csv"

#Define Cosmos DB Connection String and Query

$cluster = "https://yourclusterurl"

$database = "CallRecordInsights"

$query = @"

CallRecords

| where CallEndTime >= ago(24h)

| sort by CallStartTime desc

| extend CallDuration = todatetime(CallEndTime) - todatetime(CallStartTime)

| project CallId, SessionId, StreamDirection, MediaLabel, CallStartTime, CallEndTime,

CallDuration, Caller_UserDisplayName, Caller_PhoneId, Callee_UserDisplayName, Callee_PhoneId,

Organizer_UserDisplayName, Caller_UserAgentHeaderValue, Callee_UserAgentHeaderValue

"@

#run the query

$results = Invoke-AzKustoQuery -Cluster $cluster -Database $database -Query $query

# Convert to CSV

$results | Export-Csv -Path $filePath -NoTypeInformation

# Optional - Convert CSV to Excel

$excelFilePath = "C:\yourfilepath__$timestamp.xlsx"

$excel = New-Object -ComObject Excel.Application

$workbook = $excel.Workbooks.Open($filePath)

$workbook.SaveAs($excelFilePath, 51)  # 51 is the Excel file format code for .xlsx

$workbook.Close()

$excel.Quit()

Additionally, you can use Power Automate with the Azure Data Explorer connection to export data to Excel, Power BI, or SQL. This method is particularly useful because the user UPN (User Principal Name) is not directly available from the Graph API. Power Automate, or a table join can look up the UPN and return it to the chosen dataset. 

Update - Here is a method to provide UPN within Call Record Insights.

More Examples can be found Usage examples for Azure Data Explorer connector to Power Automate - Azure Data Explorer | Microsoft Learn

Boom !!

Conclusion:

In this post, we've explored the Call Record Insights application, from deployment to data enhancement and automation. Understanding the comprehensive data captured by the Microsoft Graph API, especially how it differs from traditional CDRs, is crucial for effective analysis. By leveraging tools like KQL and Power Automate, you can streamline data management and extract valuable insights. Stay tuned for the next chapter, where we'll dive deeper into call examples and scenarios, including Auto Attendant, Call Queue, and PSTN meetings. Get started with your deployment and dig into the insights.

Big thanks to Andy Thompson and Justin Walker for the ongoing development effort!!

Update July - Here is a method to provide UPN within Call Record Insights.