Recognize contributions using the CLI for Microsoft 365

Recognize contributions using the CLI for Microsoft 365 header image

Recognizing contributions is a great way to boost morale. The SharePoint Better Practices documentation even has a section on Identifying Your Microsoft 365 Champions. As pointed out in that article you can use the usage reports to identify your metrics and match people who are active on different platforms.

Usage Reports

Using the CLI for Microsoft 365 you can retrieve several different usage reports. Tenant level reports and service level reporting like Teams or SharePoint are both available as commands. Using those commands, you can retrieve that log data. All commands use the following structure m365 service report activityuserdetail. Using parameters, you can specify the length of the report (D7 for results of the last 7 days) and specify the output to json. You can also specify a JMESPath query to sort the result set. Assuming you want to get the user activity for SharePoint you can use the following command: m365 spo report activityuserdetail --period D7 --output json While retrieving Teams would be the following command: m365 teams report useractivityuserdetail --period D7 --output json

Making sense of your results

When you retrieve the activityuserdetail results you will quickly learn that it is not sorted based on activity. It is sorted based on the User Principal Name. To get meaningful results that is something you need to change. Using JMESPath you can sort using the sort_by functionality. The sort_by can take any value from the JSON object. When querying SharePoint, you can use the Viewed or Edited File Count. Or if you are working with intranet functionality, use the Visited Page Count. Using a reverse you can get the top contributor. Using a | [*].\"User Principal Name\ you only get returned the user principal name or using | [0:3].\"User Principal Name\ you get returned the top 3. Meaning a full command would look as follows: m365 spo report activityuserdetail --period D7 --output json --query 'reverse(sort_by(@, &\"Viewed Or Edited File Count\")) | [0:3].\"User Principal Name\"'

Presenting your results

Now that we have some results, we want to present them. Luise Freese wrote about How to send adaptive cards with CLI for Microsoft 365. It is a great way to present results, so I suggest going through that blog to see how to setup your webhooks and design an adaptive card. Make sure to pick a logo and a correct description to make things look pretty in Teams. I choose to pick my ‘Intranet’ team to post about these champions, but you can pick any team you like.

Generated webhook result

For the results I took a fairly ‘simple’ adaptive card, and put in Some PowerShell variables for the title, description and the results.

{
    "type": "AdaptiveCard",
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "version": "1.2",
    "body": [
        {
            "type": "TextBlock",
            "text": "$($Title)",
            "wrap": true,
            "size": "Medium",
            "weight": "Bolder",
            "color": "Attention"
        },
        {
            "type": "TextBlock",
            "wrap": true,
            "text": "Week $(get-date -UFormat %V)",
            "fontType": "Default",
            "size": "Small",
            "weight": "Lighter",
            "isSubtle": true
        },
        {
            "type": "FactSet",
            "facts": [
                {
                "title": "First place",
                "value": "$($activityUsers[0])"
                },
                {
                "title": "Second place",
                "value": "$($activityUsers[1])"
                },
                {
                "title": "Third place",
                "value": "$($activityUsers[2])"
                },
                {
                "title": "Fact 4",
                "value": "Value 5"
                }
            ]
        }
    ]
}

For scripting purposes you need to encode this result and put it all to a single line, so the final sample looks a bit different.

Sample

Combining all those things together the following sample retrieves the top 3 active users for SharePoint, Teams and Yammer and posts those results as separate adaptive cards to the webhook of your choosing.

Social Champion results

$m365Status = m365 status

if ($m365Status -eq "Logged Out") {
  # Connection to Microsoft 365
  m365 login
}

$webhookUrl = "<PUTYOURURLHERE>"

# Send top 3 for SharePoint based on file actions.
$activityUsers = m365 spo report activityuserdetail --period D7 --output json --query 'reverse(sort_by(@, &\"Viewed Or Edited File Count\")) | [0:3].\"User Principal Name\"' | ConvertFrom-Json
$title = "SharePoint Weekly Social Champions"
$card = '{ \"type\": \"AdaptiveCard\", \"$schema\": \"http://adaptivecards.io/schemas/adaptive-card.json\", \"version\": \"1.2\", \"body\": [  {  \"type\": \"TextBlock\",  \"text\": \"'+$($title)+'\",  \"wrap\": true,  \"size\": \"Medium\",  \"weight\": \"Bolder\",  \"color\": \"Attention\"  },  {  \"type\": \"TextBlock\",  \"wrap\": true,  \"text\": \"Week '+$(get-date -UFormat %V)+'\",  \"fontType\": \"Default\",  \"size\": \"Small\",  \"weight\": \"Lighter\",  \"isSubtle\": true  },  {  \"type\": \"FactSet\",  \"facts\": [   {   \"title\": \"First place\",   \"value\": \"'+$($activityUsers[0])+'\"   },   {   \"title\": \"Second place\",   \"value\": \"'+$($activityUsers[1])+'\"   },   {   \"title\": \"Third place\",   \"value\": \"'+$($activityUsers[2])+'\"   }  ]  } ] }'
m365 adaptivecard send --url $webhookUrl --card $card

# Send top 3 for Teams based on chat messages
$activityUsers = m365 teams report useractivityuserdetail --period D7 --output json --query 'reverse(sort_by(@, &\"Team Chat Message Count\")) | [0:3].\"User Principal Name\"' | ConvertFrom-Json
$title = "Teams Weekly Social Champions"
$card = '{ \"type\": \"AdaptiveCard\", \"$schema\": \"http://adaptivecards.io/schemas/adaptive-card.json\", \"version\": \"1.2\", \"body\": [  {  \"type\": \"TextBlock\",  \"text\": \"'+$($title)+'\",  \"wrap\": true,  \"size\": \"Medium\",  \"weight\": \"Bolder\",  \"color\": \"Attention\"  },  {  \"type\": \"TextBlock\",  \"wrap\": true,  \"text\": \"Week '+$(get-date -UFormat %V)+'\",  \"fontType\": \"Default\",  \"size\": \"Small\",  \"weight\": \"Lighter\",  \"isSubtle\": true  },  {  \"type\": \"FactSet\",  \"facts\": [   {   \"title\": \"First place\",   \"value\": \"'+$($activityUsers[0])+'\"   },   {   \"title\": \"Second place\",   \"value\": \"'+$($activityUsers[1])+'\"   },   {   \"title\": \"Third place\",   \"value\": \"'+$($activityUsers[2])+'\"   }  ]  } ] }'
m365 adaptivecard send --url $webhookUrl --card $card

# Send top 3 for Yammer based on posts
$activityUsers = m365 yammer report activityuserdetail --period D7 --output json --query 'reverse(sort_by(@, &\"Posted Count\")) | [0:3].\"User Principal Name\"' | ConvertFrom-Json
$title = "Yammer Weekly Social Champions"
$card = '{ \"type\": \"AdaptiveCard\", \"$schema\": \"http://adaptivecards.io/schemas/adaptive-card.json\", \"version\": \"1.2\", \"body\": [  {  \"type\": \"TextBlock\",  \"text\": \"'+$($title)+'\",  \"wrap\": true,  \"size\": \"Medium\",  \"weight\": \"Bolder\",  \"color\": \"Attention\"  },  {  \"type\": \"TextBlock\",  \"wrap\": true,  \"text\": \"Week '+$(get-date -UFormat %V)+'\",  \"fontType\": \"Default\",  \"size\": \"Small\",  \"weight\": \"Lighter\",  \"isSubtle\": true  },  {  \"type\": \"FactSet\",  \"facts\": [   {   \"title\": \"First place\",   \"value\": \"'+$($activityUsers[0])+'\"   },   {   \"title\": \"Second place\",   \"value\": \"'+$($activityUsers[1])+'\"   },   {   \"title\": \"Third place\",   \"value\": \"'+$($activityUsers[2])+'\"   }  ]  } ] }'
m365 adaptivecard send --url $webhookUrl --card $card

I hope the script inspires you to try out the CLI for Microsoft 365 and helps you with the adoption of your Microsoft 365 tenant.

Loading comments…