Use Case - Merging MS Teams usage reports into the Nimbus reporting template

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

1. 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

2. Generate a secret for the app.

3. Then save your application.

   Note down the the following information:

   • tenantI
   • clientI
   • appI
   • secret value

   Now we are ready to test the application in the next step.

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:

• Request for calls using CallingPlans: https://graph.microsoft.com/v1.0/communications/callRecords/getPstnCalls(fromDateTime=2023-02-11,toDateTime=2023-03-11)
• Request for calls using DirectRouting: https://graph.microsoft.com/v1.0/communications/callRecords/getDirectRoutingCalls(fromDateTime=2023-02-11,toDateTime=2023-03-11)

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

• Type = OAuth 2.0
• Token Name = Application Token
• Grant Type = Client Credentials
• Access = https://login.microsoftonline.com/ {{TenantId}} /oauth2/v2.0/token
• Client ID = {{ AppId }}
• Client Secret= {{ AppSecret }}
• Scope = https://graph.microsoft.com/.default
• Client Authentication = Send as basic Auth header

    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.callRecords.pstnCallLogRow)",
    "@odata.count": 9,
    "value": [
        {
            "id": "99999999-91ea-9999-9928-999849999968",
            "callId": "489175056_133955568@99.999.9.99",
            "userId": "4876a373-4ab2-9999-9c36-d3682fb2a3f4",
            "userPrincipalName": "nimbus.service@mytenant.com",
            "userDisplayName": "Nimbus Enterprise Service",
            "startDateTime": "2023-03-10T09:41:26.7040194Z",
            "endDateTime": "2023-03-10T09:47:17.7040194Z",
            "duration": 351,
            "charge": 0,
            "callType": "ucap_in",
            "currency": "GBP",
            "calleeNumber": "+4999209999999",
            "usageCountryCode": "GB",
            "tenantCountryCode": "GB",
            "connectionCharge": 0,
            "callerNumber": "+4199999000999",
            "destinationContext": null,
            "destinationName": null,
            "conferenceId": null,
            "licenseCapability": "MCOEV_VIRTUALUSER",
            "inventoryType": "Service",
            "operator": null,
            "callDurationSource": "Microsoft"
        },
....

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).

2. 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	Method = GET URI = CallingPlans: https://graph.microsoft.com/v1.0/communications/callRecords/getPstnCalls(fromDateTime=@{variables('fromDateTime')},toDateTime=@{variables('toDateTime')}) DirectRouting: https://graph.microsoft.com/v1.0/communications/callRecords/getDirectRoutingCalls (fromDateTime=@{variables('fromDateTime')},toDateTime=@{variables('toDateTime')}) Authentication = Active Directory OAuth Tenant = {{TenantId}} from step 1 Audience = https://graph.microsoft.com Client ID = {{AppId}} from step 1 Credential Type = Secret Secret = {{AppSecret}} from step 1
   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 Information as of 01 Mar 2023  Calling Plans Response Direct Routing Response { "type": "object", "properties": { "id": { "type": "string" }, "callId": { "type": "string" }, "userId": { "type": "string" }, "userPrincipalName": { "type": "string" }, "userDisplayName": { "type": "string" }, "startDateTime": { "type": "string" }, "endDateTime": { "type": "string" }, "duration": { "type": "integer" }, "charge": { "type": "integer" }, "callType": { "type": "string" }, "currency": { "type": "string" }, "calleeNumber": { "type": "string" }, "usageCountryCode": { "type": "string" }, "tenantCountryCode": { "type": "string" }, "connectionCharge": { "type": "integer" }, "callerNumber": { "type": "string" }, "destinationContext": {}, "destinationName": {}, "conferenceId": {}, "licenseCapability": { "type": "string" }, "inventoryType": { "type": "string" }, "operator": {}, "callDurationSource": { "type": "string" } } } JSON { "type": "object", "properties": { "id": { "type": "string" }, "correlationId": { "type": "string" }, "userId": { "type": "string" }, "userPrincipalName": { "type": "string" }, "userDisplayName": { "type": "string" }, "startDateTime": { "type": "string" }, "inviteDateTime": { "type": "string" }, "failureDateTime": { "type": "string" }, "endDateTime": { "type": "string" }, "duration": { "type": "integer" }, "callType": { "type": "string" }, "successfulCall": { "type": "boolean" }, "callerNumber": { "type": "string" }, "calleeNumber": { "type": "string" }, "mediaPathLocation": { "type": "string" }, "signalingLocation": { "type": "string" }, "finalSipCode": { "type": "integer" }, "callEndSubReason": { "type": "integer" }, "finalSipCodePhrase": { "type": "string" }, "trunkFullyQualifiedDomainName": { "type": "string" }, "mediaBypassEnabled": { "type": "boolean" } } } JSON
   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 :

   1. 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

   2. Apply a SelectColumns step to only keep relevant columns

      = Table.SelectColumns(cre41_currencyNullto0,{"target_UserSessionOutcomeId", "source_startdatetime", "source_enddatetime", "target_Duration",.....})

   3. 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.