One of the most important parts of technology adoption is incentivising people. One simple way to do this is to acknowledge their contributions in Microsoft Teams. I’ve spoken about this before using something like:

Custom Praise badges in Microsoft Teams

The challenge there is, that this is a manual process. How can the same be achieved the same kind of thing using automation I recently wondered?

What if you could automatically determine the number of chat messages that someone posted into Microsoft Teams, and then acknowledge the highest contributors with a custom ‘thank you’ message? Here’s how I managed to do just that using the Microsoft Graph and Power Automate.

The core information needed about messages posted in Microsoft Teams will come the Microsoft Graph, specifically:

getTeamsUserActivityUserDetail

The first step in this process will be to create an Azure AD application in the tenant, which I have detailed previously:

https://blog.ciaops.com/2019/04/17/using-interactive-powershell-to-access-the-microsoft-graph/

the credentials for this Azure AD application will then need to be uploaded into Azure Key Vault as I have covered here:

Uploading Graph credentials to Azure Key Vault

You’ll also need to set the API permissions for this application to :

Reports.Read.All

 You’ll need to trigger your Flow the way that suits you. Typically you’d schedule it to run on the first of each month. You’ll then need to use the Get secret action from the Azure Key Vault as shown above. You should also note that this is a premium connectors, so you’ll need an appropriate Power Platform license to use this.

Next, will be the HTTP action that also a premium connector. The URI to use here will be:

https://graph.microsoft.com/beta/reports/getTeamsUserActivityUserDetail(period=’D30’)?$format=application/json

The D30 parameter determines how many previous days to consider. Valid options for the number of days is 7,30, 90, 180 according to the documentation.

The security parameters come from the Azure Key Vault Get secret actions.

The data returned from this HTTP action will be in JSON format, so that is fed into the Parse JSON action as shown above.

One tricky things about parsing json is to get the right schema. When I first attempted this I received this error:

“message”: “Invalid type. Expected String but got Null.”,

After some digging I found that sometimes the lastactivitydate parameter returned a string or NULL value. Thus, I need to modify the original JSON for that parameter to accommodate both potential results:

“lastActivityDate”: {
     “type”: [
         “string”,
         “null”
     ]
},

Here the whole working JSON schema I used:

{
     “type”: “object”,
     “properties”: {
         “value”: {
             “type”: “array”,
             “items”: {
                 “type”: “object”,
                 “properties”: {
                     “reportRefreshDate”: {
                         “type”: “string”
                     },
                     “userId”: {
                         “type”: “string”
                     },
                     “userPrincipalName”: {
                         “type”: “string”
                     },
                     “lastActivityDate”: {
                         “type”: [
                             “string”,
                             “null”
                         ]
                     },
                     “isDeleted”: {
                         “type”: “boolean”
                     },
                     “deletedDate”: {},
                     “assignedProducts”: {
                         “type”: “array”
                     },
                     “teamChatMessageCount”: {
                         “type”: “integer”
                     },
                     “privateChatMessageCount”: {
                         “type”: “integer”
                     },
                     “callCount”: {
                         “type”: “integer”
                     },
                     “meetingCount”: {
                         “type”: “integer”
                     },
                     “meetingsOrganizedCount”: {
                         “type”: “integer”
                     },
                     “meetingsAttendedCount”: {
                         “type”: “integer”
                     },
                     “adHocMeetingsOrganizedCount”: {
                         “type”: “integer”
                     },
                     “adHocMeetingsAttendedCount”: {
                         “type”: “integer”
                     },
                     “scheduledOneTimeMeetingsOrganizedCount”: {
                         “type”: “integer”
                     },
                     “scheduledOneTimeMeetingsAttendedCount”: {
                         “type”: “integer”
                     },
                     “scheduledRecurringMeetingsOrganizedCount”: {
                         “type”: “integer”
                     },
                     “scheduledRecurringMeetingsAttendedCount”: {
                         “type”: “integer”
                     },
                     “audioDuration”: {
                         “type”: “string”
                     },
                     “videoDuration”: {
                         “type”: “string”
                     },
                     “screenShareDuration”: {
                         “type”: “string”
                     },
                     “hasOtherAction”: {
                         “type”: “boolean”
                     },
                     “urgentMessages”: {
                         “type”: “integer”
                     },
                     “postMessages”: {
                         “type”: “integer”
                     },
                     “replyMessages”: {
                         “type”: “integer”
                     },
                     “isLicensed”: {
                         “type”: “boolean”
                     },
                     “reportPeriod”: {
                         “type”: “string”
                     }
                 },
                 “required”: [
                     “reportRefreshDate”,
                     “userId”,
                     “userPrincipalName”,
                     “lastActivityDate”,
                     “isDeleted”,
                     “deletedDate”,
                     “assignedProducts”,
                     “teamChatMessageCount”,
                     “privateChatMessageCount”,
                     “callCount”,
                     “meetingCount”,
                     “meetingsOrganizedCount”,
                     “meetingsAttendedCount”,
                     “adHocMeetingsOrganizedCount”,
                     “adHocMeetingsAttendedCount”,
                     “scheduledOneTimeMeetingsOrganizedCount”,
                     “scheduledOneTimeMeetingsAttendedCount”,
                     “scheduledRecurringMeetingsOrganizedCount”,
                     “scheduledRecurringMeetingsAttendedCount”,
                     “audioDuration”,
                     “videoDuration”,
                     “screenShareDuration”,
                     “hasOtherAction”,
                     “urgentMessages”,
                     “postMessages”,
                     “replyMessages”,
                     “isLicensed”,
                     “reportPeriod”
                 ]
             }
         }
     }
}

I filtered the results produced to firstly remove users with zero messages and then I removed specific users (in this case me) from the results using the two Filter action shown above.

One of the limitations I found during this development process was that Flows are not very good at sorting information. It would have been nice if I could have just sorted my results from largest to smallest. I solved this but firstly creating a temporary variable into which I would store the highest number of chat messages. I then looped through all the remaining results using the Apply to Each action shown above, and set this variable to the greatest value found throughout the results.

I then employed another Filter action again to remove any results that didn’t match this maximum value.

The next challenge came when considering external users, who have a UPN in the format of:

user_domain.com#EXT#tenant.onmicrosoft.com

Turns out the the Power Automate lookup actions don’t seem to work using these type of UPNs, they only work with an email address apparently!

To solve this, I now created two more variables to use when extracting the external users email address.

I need to extract and create a user email address before using it in the Search for users (V2) action. To do this I used two functions inside two Set variable actions. The first is:

slice(items(‘Apply_to_each_2’)?[‘userPrincipalName’],0,lastIndexOf(items(‘Apply_to_each_2’)?[‘userPrincipalName’],’#EXT’))

which removes anything from the #EXT and beyond. This typically leaves the format of:

user_domain.com

The second function is:

replace(variables(‘EmailAddress’),’_’,’@’)

that replaces the underscore character with an “at” symbol. The output after both of these functions complete is the users full email address which I can then feed into the Search for users (V2) action to locate my user(s).

Now that I have the user information I can use it with the Get an @mention token for a user action and then post a message with that @mention and anything else into the Team to publicly recognise the user(s) using the Post message in a chat or channel action.

The end result is a nice message, like shown above, that acknowledges the user(s) for their contribution in the Microsoft Team. Best part is that now it is automated it’ll do all the hard work for you going forward!

The moral of the story here is that Power Automate combined with the Microsoft Graph can achieve just about anything you want. Yes, it does a little bit of setting up with an Azure AD application, Keyvault and so on but they are typically only once off. There is also some advanced techniques when it comes to the JSON parameters but once you start working with, it becomes easier. In short, none of that should put you off from what is possible with the Microsoft Cloud environment and putting the technology to work for you to give you more freedom and improve your business processes.