Which department is most active on Microsoft Teams?

In the Microsoft Teams admin center you have various usage reports under “Analytics & reports->Usage reports”. One of them is the “Teams user activity” report, that shows you how many meetings, chat messages, video minutes and so on that each user have and in total for your tenant. I have created a small script that gather all user data and add it to a report that show all these stats for each department in your organization. We use the department field in Azure AD to find each users department.

This is somewhat a semi automaticaly process. You need two things:

  1. Token from the Microsoft Teams admin center.
  2. Token from Azure App.

Token from the Microsoft Teams admin center.

Go to the Microsoft Teams admin center and select “Analytics & reports->Usage reports” in the left menu. Select the “Teams user activity” report and set date range to for example “Last 90 days”. In my case i use the Edge Chromium browser. Hit F12 or Ctrl+Shift+I to open developer mode . Click the ‘Network’ tab and click ‘Clear’. Recording must be activated(Red dot when activated). Now click “Run report”.

Select ‘aggregated-summary…..’ where you see the request method used is GET. Now copy the token, here marked in blue.

Token from Azure App.

Go to portal.azure.com and search for ‘App registrations’

Click ‘New registration’.

Give the application a name and click ‘Register’.

Copy the Application/Client ID and Directory/Tenant ID.

Go to ‘Certificates & secrets’ and click ‘New client secret’.

Give the secret a name and click ‘Add’.

Copy the secret value.

Go to ‘API permissions’ and click ‘Add a permission’.

Click ‘Microsoft Graph’.

Click ‘Application permissions’.

Search for ‘User.Read.All’ , select the permission and click ‘Add permissions’.

No you need to click ‘Grant admin consent for …..’ and ‘Yes’.

Next is to gather all the details you copied and paste into my script, then run it. Remember that next time you run this script you will need to get a new token from the admin portal, as the token lifetime is about 1 hour.

I don’t have that much test data in my tenant, but here is an example output:

### Teams User activity by department                 ###
### Version 1.1                                       ###
### Author: Alexander Holmeset                        ###
### Email: [email protected]               ###
### Twitter: twitter.com/alexholmeset                 ###
### Blog: alexholmeset.blog                           ###
 
 
 #Number of days with activity.
 #Possible values are last-seven-days, last-thirty-days and last-ninety-days
 $Days = 'last-ninety-days'
 
#Enter API token you get from the Teams Admin portal here.
$token1 = 'xxxxxxxxxxxxxxxxxxxx'
 

 
$url1 = "https://tas.teams.microsoft.com/v2/users/summary-timeseries?timeperiod=$Days&includeTimeSeries=none&metrics=all&pageSize=20&nextCursor=null"
 
 
$global:Header = @{"Authorization" = "Bearer $Token1" }
 
 



# Azure AD OAuth Application Token for Graph API
# Get OAuth token for a AAD Application (returned as $token)

# Application (client) ID, tenant ID and secret from your Azure App
$clientId = "xxxxx"
$tenantId = "xxxxxx"
$clientSecret = 'xxxxxxx'

# Construct URI
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"

# Construct Body
$body = @{
    client_id     = $clientId
    scope         = "https://graph.microsoft.com/.default"
    client_secret = $clientSecret
    grant_type    = "client_credentials"
}

# Get OAuth 2.0 Token
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing

# Access Token
$token2 = ($tokenRequest.Content | ConvertFrom-Json).access_token

 

 function Get-GraphRequest($uri){


Process {   
    $ErrorActionPreference = "Stop"

    try {
    #Graph API request that loops through every '@odata.nextLink' if there are more than 1000 devices.
    $content = while (-not [string]::IsNullOrEmpty($Uri)) {

        # API Call
        Write-Host "`r`nQuerying $Uri..." -ForegroundColor Yellow
        try{
   
        $apiCall = Invoke-RestMethod -Method "GET" -Uri $Uri -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing
        }
        catch{
           Start-Sleep -Seconds 30
           try{$apiCall = Invoke-RestMethod -Method "GET" -Uri $Uri -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing}
            catch{$apiCall = $null}
        }

        $nextLink = $null
        $Uri = $null
        
        if ($apiCall) {
            


            # Check if any data is left
            $nextLink = ($apiCall.paging).nextcursor
            If($nextLink){
            $uri = "https://tas.teams.microsoft.com/v2/users/summary-timeseries?timeperiod=$Days&includeTimeSeries=none&metrics=all&pageSize=100&nextCursor=$nextLink"
                     
                 }
             $apiCall.users
                   

        }

    }
    }
    catch [System.Exception] {
        Write-Warning "Failed to complete request, Error message: $($_.Exception.Message)"; break
    }


  

        return $content

}
}






 
 
$users = Get-GraphRequest "https://tas.teams.microsoft.com/v2/users/summary-timeseries?timeperiod=$Days&includeTimeSeries=none&metrics=all&pageSize=100&nextCursor=null"

$DepartmentRecord = @()

foreach($user in $users){

$userid = $user.id


$apiUrl2 = "https://graph.microsoft.com/beta/users/$userid`?select=department"


$userdepartment = try{(Invoke-RestMethod -Headers @{Authorization = "Bearer $token2"} -Uri $apiUrl2  -Method GET -ContentType 'application/json').department}catch{}
If($userdepartment){


$IndexNumber = @()
$IndexNumber = try{[array]::indexof($DepartmentRecord.Department,"$userdepartment")}catch{-2}


If($IndexNumber -eq -1 -or $IndexNumber -eq -2){

    "department $userdepartment Created"
    $DepartmentRecordObject = @()
            $DepartmentRecordObject = [pscustomobject]@{
                
                Department = "$userdepartment"
                ChannelMessages = "$((($user.metrics).channelMessages).value)"               
                ReplyMessages = "$((($user.metrics).replyMessages).value)"
                ChatMessages = "$((($user.metrics).chatMessages).value)"
                UrgentMessages = "$((($user.metrics).urgentMessages).value)"
                OneOnOneCalls = "$((($user.metrics).oneOnOneCalls).value)"
                GroupCalls = "$((($user.metrics).groupCalls).value)"
                'AudioTime' = "$((($user.metrics).audioTime).value)"
                'VideoTime' = "$((($user.metrics).videoTime).value)"
                'ScreenShareTime' = "$((($user.metrics).screenShareTime).value)"
                MeetingsOrganizedAdhoc = "$((($user.metrics).meetingsOrganizedAdhoc).value)"
                MeetingsOrganizedScheduledOneTime = "$((($user.metrics).mOrgSchOneTime).value)"
                MeetingsOrgnizedScheduledRecurring = "$((($user.metrics).mOrgSchRecurring).value)"
                MeetingsOrganizedTotal = "$((($user.metrics).totalMeetingsOrganized).value)"
                MeetingsParticipatedAdhoc = "$((($user.metrics).meetingsParticipatedAdhoc).value)"
                MeetingsParticipateddScheduledOneTime = "$((($user.metrics).mPartSchOneTime).value)"
                MeetingsParticipatedScheduledRecurring = "$((($user.metrics).mPartSchRecurring).value)"
                MeetingsParticipatedTotal = "$((($user.metrics).totalMeetingsParticipated).value)"

            
            }
            $DepartmentRecord += $DepartmentRecordObject
    

}
else{

$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'ChannelMessages' -value ($DepartmentRecord.channelMessages["$IndexNumber"]+$user.channelMessages) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'ReplyMessages' -value ($DepartmentRecord.replyMessages["$IndexNumber"]+$user.replyMessages) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'ChatMessages' -value ($DepartmentRecord.chatMessages["$IndexNumber"]+$user.chatMessages) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'OneOnOneCalls' -value ($DepartmentRecord.oneOnOneCalls["$IndexNumber"]+$user.oneOnOneCalls) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'GroupCalls' -value ($DepartmentRecord.groupCalls["$IndexNumber"]+$user.groupCalls) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'AudioTime' -value ($DepartmentRecord.audioTime["$IndexNumber"]+($user.audioTime)) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'VideoTime' -value ($DepartmentRecord.videoTime["$IndexNumber"]+($user.videoTime)) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'ScreenShareTime' -value ($DepartmentRecord.screenShareTime["$IndexNumber"]+($user.screenShareTime)) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'UrgentMessages' -value ($DepartmentRecord.urgentMessages["$IndexNumber"]+$user.urgentMessages) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsOrganizedAdhoc' -value ($DepartmentRecord.meetingsOrganizedAdhoc["$IndexNumber"]+$user.meetingsOrganizedAdhoc) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsOrganizedScheduledOneTime' -value ($DepartmentRecord.MeetingsOrganizedScheduledOneTime["$IndexNumber"]+$user.mOrgSchOneTime) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsOrgnizedScheduledRecurring' -value ($DepartmentRecord.MeetingsOrgnizedScheduledRecurring["$IndexNumber"]+$user.mOrgSchRecurring) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsOrganizedTotal' -value ($DepartmentRecord.MeetingsOrganizedTotal["$IndexNumber"]+$user.totalMeetingsOrganized) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsParticipatedAdhoc' -value ($DepartmentRecord.meetingsParticipatedAdhoc["$IndexNumber"]+$user.meetingsParticipatedAdhoc) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsParticipateddScheduledOneTime' -value ($DepartmentRecord.MeetingsParticipateddScheduledOneTime["$IndexNumber"]+$user.mPartSchOneTime) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsParticipatedScheduledRecurring' -value ($DepartmentRecord.MeetingsParticipatedScheduledRecurring["$IndexNumber"]+$user.mPartSchRecurring) -MemberType NoteProperty -Force
$DepartmentRecord["$IndexNumber"] | Add-Member -Name 'MeetingsParticipatedTotal' -value ($DepartmentRecord.MeetingsParticipatedTotal["$IndexNumber"]+$user.totalMeetingsParticipated) -MemberType NoteProperty -Force
    

}


}
}


$DepartmentRecordRounded = @()
foreach($Department in $DepartmentRecord){
    $DepartmentRecordRoundedObject = @()
            $DepartmentRecordRoundedObject = [pscustomobject]@{
                
                Department = "$($Department.department)"
                ChannelMessages = "$($Department.channelMessages)"               
                ReplyMessages = "$($Department.replyMessages)"
                ChatMessages = "$($Department.chatMessages)"
                UrgentMessages = "$($Department.urgentMessages)"
                OneOnOneCalls = "$($Department.oneOnOneCalls)"
                GroupCalls = "$($Department.groupCalls)"
                'AudioTime(Hours)' = "$([int]($Department.audioTime/60/60))"
                'VideoTime(Hours)' = "$([int]($Department.videoTime/60/60))"
                'ScreenShareTime(Hours)' = "$([int]($Department.screenShareTime/60/60))"
                MeetingsOrganizedAdhoc = "$($Department.meetingsOrganizedAdhoc)"
                MeetingsOrganizedScheduledOneTime = "$($Department.MeetingsOrganizedScheduledOneTime)"
                MeetingsOrgnizedScheduledRecurring = "$($Department.MeetingsOrgnizedScheduledRecurring)"
                MeetingsOrganizedTotal = "$($Department.MeetingsOrganizedTotal)"
                MeetingsParticipatedAdhoc = "$($Department.meetingsParticipatedAdhoc)"
                MeetingsParticipateddScheduledOneTime = "$($Department.MeetingsParticipateddScheduledOneTime)"
                MeetingsParticipatedScheduledRecurring = "$($Department.MeetingsParticipatedScheduledRecurring)"
                MeetingsParticipatedTotal = "$($Department.MeetingsParticipatedTotal)"

            
            }
            $DepartmentRecordRounded += $DepartmentRecordRoundedObject
            }

$DepartmentRecordRounded | export-csv C:\temp\TeamsDepartmentReport.csv -NoTypeInformation -Encoding UTF8

2 thoughts on “Which department is most active on Microsoft Teams?

Leave a Reply to Mark Graham Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s