Use Case - Merging MS Teams Usage Reports into the Nimbus Reporting Template

Collect direct call information from MS Teams with Graph API and import it into the Nimbus reporting.

In this use case, we explore how you can collect direct call information from Microsoft Teams and combine it with the Nimbus reporting template

We can accomplish this by using the Microsoft Graph API to retrieve the data and then storing it in a table. The data can then be transformed to meet our requirements and imported into the Nimbus report.

The "Analytics & reports" section of the Teams admin center displays helpful statistics on usage and calls, including the "PSTN and SMS usage" report. This report includes data on direct calls that were not made during a Nimbus session and is thus currently not available in the Nimbus Power BI reporting template.

PRECONDITIONS

You require tenant administrator rights to create an enterprise application in your tenant.

Also make sure that:

  • A Nimbus service is set up and contains some calls to evaluate.
    💡You can also use test calls to increase metrics.
  • Teams direct PSTN calls have been made on the tenant.
  • A Nimbus PowerBI template is connected and refreshed with data from your tenant.
  • You created a database and/or a table in your Microsoft Dataverse (via PowerApps Admin center).

🔍 Note: it is recommended that you are familiar with the following documentation:

 

Show Icon Legend

💡 = A hint to signal learnings, improvements or useful information in context. 🔍 = Info points out essential notes or related page in context.
☝ = Notifies you about fallacies and tricky parts that help avoid problems. 🤔 = Asks and answers common questions and troubleshooting points.
❌ = Warns you of actions with irreversible / data-destructive consequence. ✅ = Intructs you to perform a certain (prerequired) action to complete a related step.
 
 

Here is an illustration of the implementation

 
 

Create an Application with Permissions on the Necessary Graph API Ressources

As a tenant admin, log into portal.azure.com and create an enterprise application. You can name it "Call Reports API Access" or similar.
💡In principle, we follow the instructions here https://learn.microsoft.com/en-us/graph/use-postman

In the permissions section, add the following permissions on the MS Graph API:

Application Permissions
Delegated Permissions

CallRecords.Read.All

Reports.Read.All

User.Read.All

Reports.Read.All

User.Read

User.Read.All

Generate a secret for the app.

Then save your application.

✅ Note down the the following information:

  • tenantI
  • clientI
  • appI
  • secret value

Test the Application

🔍 In this step, we want to test if we can now access the GraphAPI ressource using our new application. You can test the application using any HTTP client. We recommend to use postman.

Create the following requests:

Set up the authorization for each request using the following values:

 Response example

✅ We have access to the Teams call report data and can use it in the next steps.

Create the Table

  1. Go to https://make.powerapps.com/ and select your working environment.
  2. Create your database and table. 🔍 Follow the instructions here Add a Microsoft Dataverse database - Power Platform | Microsoft Learn.
    💡 We want to store the original data from the response into the table with their original property names and original values. Additionally, we want to create the columns for the Nimbus report in the same table with sounding column names and transformed values.
  3. For each property in the json response of the last step, add a column. 💡 We add a prefix "source_ " to the column names to indicate that these columns hold the original data.
    • source_startDateTime
    • source_startEndTime
    • source_duration
    • source_calleeNumber
    • source_callernumber
    • source_userPrincipleName
    • source_<columnname> 
    • ...
  4. Now, add some new columns used for the Nimbus data aggregation. 💡 We add a prefix "target_ " to the column names to indicate that these columns hold the transformed data for the target system - the Nimbus report.

Add the columns as follows:

Column Name Column Properties Calculated Field
target_UserSessionOutcomeId

Behaviour=Calculated

Datatype=Single line of text

If source_callType contains "in"

and source_finalSipCode equals 0

then Action  (target_UserSessionOutcomeId = 1)

If source_callType contains "in"

and source_finalSipCode equals 200

then Action  (target_UserSessionOutcomeId = 1)

If source_callType contains "in"

and source_finalSipCode equals 487

then Action  (target_UserSessionOutcomeId = 3)

If source_callType contains "in"

and source_finalSipCode equals 603

then Action  (target_UserSessionOutcomeId = 2)

And add the same for the Outbound calls:

If source_callType contains "out"

and source_finalSipCode equals 0

then Action  (target_UserSessionOutcomeId = 11)

If source_callType contains "out"

and source_finalSipCode equals 200

then Action  (target_UserSessionOutcomeId = 11)

If source_callType contains "out"

and source_finalSipCode equals 487

then Action  (target_UserSessionOutcomeId = 14)

If source_callType contains "out"

and source_finalSipCode equals 603

then Action  (target_UserSessionOutcomeId = 15)

🔍 map eventual other values in source_finalSipCode to UserSessionOutcomeId's 

target_TaskDirectionId

Behaviour=Calculated

Datatype=Whole number

If source_callType contains "in" then Action = 1

If source_callType contains "in" then Action = 2

target_Duration

Behaviour=Calculated

Datatype=Decimal

Action= DiffInMinutes(source_startdatetime, source_enddatetime)

target_TaskTypeId

Behaviour=Calculated

Datatype=Whole number

If source_callType equals "ucap_out" 

then Action  (target_TaskTypeId = 3)

If source_callType equals "ucap_in" 

then Action  (target_TaskTypeId = 4)

🔍 map eventual other values in source_callType to TaskTypeId 

The "Direct Routing Usage report" and "Calling Plans Usage Report" have different responses and the source columns may differ. Available fields are described here Microsoft Teams PSTN usage report - Microsoft Teams | Microsoft Learn

Create the Power Automate Flow

In this step we create a Power Automate flow that allows to fill the table with the MS teams reporting data.

  1. Go to https://make.powerautomate.com/ and select your working environment (it should be the same as in the previous step).

Add a new scheduled flow.

Description
Settings
Start with the Recurrence trigger

Choose your interval

Add a Initialize Variable to the flow
  • Name = fromDateTime
  • Value = formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')
Add a Initialize Variable to the flow
  • Name = toDateTime
  • Value = formatDateTime(utcNow(),'yyyy-MM-dd')

Add a HTTP Element to the flow

Add three Compose elements to the flow to store the data.

Add the following inputs:

  • Input for Compose = Body of the HTTP Element body('HTTP')
  • Input for Record Count = body('HTTP')?['@odata.count']
  • Input for Values = body('HTTP')?['value']

Add a Apply to Each element to the flow

Iterate over @{outputs('Values')}

OPTIONAL Add a Parse JSON element to the flow

Iterate over the Current Item. The schema is a s follows (we've generated it from the response value of the second step):

Note: You are not obliged to do this step. It is a convenient way to access the response's data in the next steps and allows for response data validation in case the response changes. Your flow will run into an error right where the problem is, which might faciliate debugging.

🔍 Make sure you adapt the schema with yours. If you are using DirectRouting, the schema of the response is different.

 Schema example

OPTIONAL Add a "Condition" to the flow

We want to filter out the Nimbus service calls from the list of all PSTN calls.

🔍 Make sure to adapt the filter to your scenario. If you are using DirectRouting, the values in callType are slightly different.

Information as of 01.03.2023 from Microsoft Teams PSTN usage report - Microsoft Teams | Microsoft Learn

Calling Plans callTypes
Direct Routing callTypes

Teams user call types

  • user_in - the user received an inbound PSTN call
  • user_out - the user placed an outbound PSTN call
  • user_out_conf - the user added two or more PSTN participants to the call such as a three-way conference call
  • user_out_transfer - the user transferred the call to a PSTN number
  • user_out_forwarding - the user forwarded the call to a PSTN number
  • conf_in - an inbound call to the Audio Conferencing bridge
  • conf_out - an outbound call from the Audio Conferencing bridge usually to add a PSTN number to the conference
  • unassigned_in - an inbound PSTN call via Calling Plan to an unassigned number


Teams bots call types (Nimbus service calls)

  • ucap_in - an inbound PSTN call to Teams bot such as auto attendant or call queue
  • ucap_out - an outbound PSTN call from a Teams bot such as auto attendant or call queue

Teams user call types

  • dr_in - the user received an inbound PSTN call
  • dr_out - the user placed an outbound PSTN call
  • dr_out_user_conf - the user added a PSTN participant to the call
  • dr_out_user_forwarding - the user forwarded the call to a PSTN number
  • dr_out_user_transfer - the user transferred the call to a PSTN number
  • dr_emergency_out - the user made an emergency call
  • dr_unassigned_in - an inbound PSTN call via Direct Routing to an unassigned number


Teams bots call types (Nimbus service calls)

  • dr_in_bot - an inbound PSTN call to a Teams bot such as auto attendant or call queue
  • dr_out_bot - an outbound PSTN call from a Teams bot such as auto attendant or call queue

Add a "Update a row" from the Dataverse connector to the flow.

Make sure to use the correct connection.

  • Row id=body('Parse_JSON')?['id']
  • Name = concat('Import_', variables('toDateTime'))

map the other column saccordingly using the parsed json.

✅ We can now run the flow in the next step.

Import the Data

We want to run the flow once and import all the data from the past.

  1. Set the variables in the Power Automate flow to the following values and then run it.
    • fromDateTime = 2023-01-01
      (or even older, this is a fixed value from the date you want the call records to be imported).
    • toDateTime = formatDateTime(utcNow(),'yyyy-MM-dd')
  2. Verify if the table holds the expected data - especially verify the calculated columns.
    1. If your imported values are incorrect and you need to reimport everything in a new fresh table, run a bulk delete in po we r apps: Settings > Advanced Sesstings > Settings > Data Management > Bulk delete > New.
    2. If your imported values are OK, change the variables in the Power Automate flow as follows:
      • fromDateTime = formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')
      • toDateTime = formatDateTime(utcNow(),'yyyy-MM-dd')

💡 The flow will now run once a day and get the data from the previous day.

Add the Table to Your Nimbus PowerBI Report

What is now left is to aggregate the data into the Nimbus report in the next step.

  1. Open the Nimbus power BI report
  2. Click on Dataverse and select your table. Select Import as import method.
  3. You can now apply the necessary steps to merge the data into the UserSessions Table. Open the query editor and apply the following steps :

a. Select the imported table and rename the following columns to 

column name in source
column name in UserSessions
source_startdatetime UserSelectedAt
source_userid UserId
target_UserSessionOutcomeId OutcomeId
target_Duration ConnectedTime
target_TaskTypeId TaskTypeId

b. Apply a SelectColumns step to only keep relevant columns
= Table.SelectColumns(cre41_currencyNullto0,{"target_UserSessionOutcomeId", "source_startdatetime", "source_enddatetime", "target_Duration",.....})
c. Select table UserSessions and apply the following steps:
= Table.Combine({cre41_directpstncalls})
= Table.CombineColumns(#"Appended Query",{"cre41_directpstncallsid", "Id"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Id")
= Table.SelectRows(CombineID, each [cre41_enddatetime] <> null and [cre41_enddatetime] <> "")

✅ The direct call report data is now available in the UserSessions table. The data can be viewed on the User Sessions Tab.

Table of Contents