Create a User Activity Report for Multiple Office 365 Workloads

The Graph is the Source for Report Data

The usage reports available in the Microsoft 365 admin center give a reasonable picture of user activity within a tenant. However, they don’t deliver the kind of flexibility that a customized report can provide. Given that the data comes from the Microsoft Graph, it seems reasonable to extract the data from the Graph to see what can be done.

You can access the Graph with a variety of languages. I decided to attack the problem with PowerShell. The code is very accessible and easily altered. Using PowerShell also emphasizes how feasible it is to combine the Graph with PowerShell to create solutions.

My aim is to gather usage data from multiple workloads and present it in a way that administrators know how active users are with each workload and across the service. Understanding user activity can help tenants manage licenses more efficiently. For instance, if you find that an account is inactive, you can remove its license.

Retrieving Usage Data from the Graph

The first step in the script is to access the Graph and download the usage data. As an intermediary for PowerShell, we need a registered an Azure Active Directory application to access the Graph (directions are in this post). Make sure that the app is assigned the Graph Reports.Read.All permission to allow it to read the usage data with the Reports API. To fetch the signin data from Azure AD, you’ll need to assign the User.Read.All permission.

The script uses the following APIs to retrieve information for the last 90 days.

  • getTeamsUserActivityUserDetail: Teams user activity.
  • getOneDriveUsageAccountDetail: OneDrive for Business user activity.
  • getEmailActivityUserDetail: Exchange Online user activity.
  • getMailboxUsageDetail: Exchange Online mailbox statistics.
  • getSharePointActivityUserDetail: SharePoint Online user activity.
  • getYammerActivityUserDetail: Yammer user activity.

For example, this code fetches the Exchange mailbox data:
# Get Exchange Storage Data

$MailboxUsageReportsURI = "https://graph.microsoft.com/v1.0/reports/getMailboxUsageDetail(period='D90')"
$MailboxUsage = (Invoke-RestMethod -Uri $MailboxUsageReportsURI -Headers $Headers -Method Get -ContentType "application/json") -replace "", "" | ConvertFrom-Csv

Overall, data covering usage of five workloads is available. In addition, the script fetches user sign-in data for tenant accounts from Azure Active Directory.

Normalizing Graph Data

The intention is to create a picture of each user’s activity across multiple workloads. To do this, some normalization is needed. For instance, most workloads reference to accounts via “User Principal Name” but OneDrive for Business uses “Owner Principal Name.” We therefore process the data fetched for each workload to extract the information we need and fix it up where necessary. The result is a set of usage records in a PowerShell list object. Here’s the code to process the Teams usage data:
# Process Teams Data

ForEach ($T in $TeamsUserData) {
   If ([string]::IsNullOrEmpty($T."Last Activity Date")) { 
      $TeamsLastActivity = "No activity"
      $TeamsDaysSinceActive = "N/A" }
   Else {
      $TeamsLastActivity = Get-Date($T."Last Activity Date") -format "dd-MMM-yyyy" 
      $TeamsDaysSinceActive = (New-TimeSpan($TeamsLastActivity)).Days }
   $ReportLine  = [PSCustomObject] @{          
     UPN               = $T."User Principal Name"
     LastActive        = $TeamsLastActivity  
     DaysSinceActive   = $TeamsDaysSinceActive      
     ReportDate        = Get-Date($T."Report Refresh Date") -format "dd-MMM-yyyy"  
     License           = $T."Assigned Products"
     ChannelChats      = $T."Team Chat Message Count"
     PrivateChats      = $T."Private Chat Message Count"
     Calls             = $T."Call Count"
     Meetings          = $T."Meeting Count"
     RecordType        = "Teams"}
   $Report.Add($ReportLine) }

Per-User Processing

The next step is to generate an array of user principal names (accounts).

[array]$Users = $Report | Sort UPN -Unique | Select -ExpandProperty UPN

The script loops through the array to create the output report, checks each account against the usage records to find matches for each workload, extracts the usage data, and combines the usage data for all workloads into a single record for the account. The record is rounded off with the date and time of the account’s last sign-in from Azure Active Directory.

Figuring Out Inactive Accounts

Collectively, the usage information for an account tells us how active that account is. Part of the data is how long it is since an account used each workload. For instance, an account might have used Exchange Online 3 days ago but hasn’t used Teams for 35 days and SharePoint Online for 67 days. This tells us that the account holder uses email but isn’t so active with the other workloads.

The script includes some code to assess whether an account is active. This is based on the average number of days since an account was active based on the five workloads. The Graph data is normally two days behind the actual date, so the most active users across all workloads have a computed figure of around 2. The higher the figure, the less active a user is. Depending on thresholds set in the script, accounts are labelled as in use, moderate use, poor use, needing review, or unused. It’s easy to adjust the thresholds to match the needs of a tenant. For instance, a workload isn’t used, its data could be excluded from the computation.

The Final Output

Eventually we have a picture of each user’s activity across the five workloads. The script writes the data out to a CSV file and pipes the data to the Out-GridView cmdlet for interactive review (Figure 1).

Image # Expand
Graph User Statistics 1
Figure 1: Usage information for Office 365 accounts (image credit: Tony Redmond)

Standard PowerShell and Graph

The script uses standard PowerShell cmdlets and doesn’t need to load any extra modules (like Exchange Online, Teams, or SharePoint Online). Performance is acceptable but will clearly vary depending on the number of accounts that need to be processed – and anyway, this script is not something you’ll want to run daily. Fetching data for 25,000 accounts from the Graph takes about 40 seconds. This isn’t the heaviest part of processing as it takes time to assemble the report thereafter. Sometimes too long!

The complete script is available to download from GitHub. Normal caveats apply. Always test any downloaded code before introducing a script or program to a production system. And because it’s PowerShell, feel free to improve the code in whatever way you think necessary.