Getting Microsoft 365 Individual User Usage Reports with PowerShell

The ability to obtain Individual User Usage Reports has been possible in Office365 for many years. However, they were only available from each individual service such as Teams, OneDrive, Exchange, SharePoint and Yammer. If you wanted a holistic view you needed to query each of the services API’s and collate the responses for each identity. In August 2020 Microsoft added the ability to get M365 User Usage Reports to the BETA Microsoft Graph API. This means you can now use the Microsoft Graph Reports API to get a Microsoft 365 Individual User Usage Report with PowerShell rather than using the Microsoft 365 Admin Portal and the reports functionality there.

This blog post covers using the Microsoft Graph Reports API to get a Microsoft 365 Individual User Usage Reports with PowerShell and exporting the data to an Excel spreadsheet. The information is returned with granularity down to each Microsoft 365 licensed user account, the date the report was generated within Microsoft 365, the device type(s) individual users are using (Windows, Mac, Mobile, Web) and Microsoft 365 Application (Windows, Mac, Mobile, Web) usage. As such the report columns are:

Report Refresh Date,User Principal Name,Last Activation Date,Last Activity Date,Report Period,Windows,Mac,Mobile,Web,Outlook,Word,Excel,PowerPoint,OneNote,Teams,Outlook (Windows),Word (Windows),Excel (Windows),PowerPoint (Windows),OneNote (Windows),Teams (Windows),Outlook (Mac),Word (Mac),Excel (Mac),PowerPoint (Mac),OneNote (Mac),Teams (Mac),Outlook (Mobile),Word (Mobile),Excel (Mobile),PowerPoint (Mobile),OneNote (Mobile),Teams (Mobile),Outlook (Web),Word (Web),Excel (Web),PowerPoint (Web),OneNote (Web),Teams (Web)

The data exported in Excel format with columns auto sized, a filter applied on each column and some conditional formatting on the values (yes or no) looks like this:

Prerequisites

The example script in this post utilizes additional PowerShell Modules to simplify the process. You will require:

  • MSAL.PS to simplify authentication to Microsoft Graph
    • Install the module from the PS Gallery using PowerShell 5.1+ using command
      • Install-Module MSAL.PS -Force -AcceptLicense
  • ImportExcel to export the report data direct into an Excel Spreadsheet.
    • Note, the ImportExcel PowerShell Module doesn’t require that you have Excel installed. This provides you the ability to generate the output on automation/server hosts
    • Install the module from the PS Gallery using PowerShell 5.1+ using command
      • Install-Module ImportExcel

You will need to register an Azure AD Application with Application Permissions for the Reports.Read.All scope. The registered application will need to be authorized (Admin consent) for the tenant. You will need to record the registered Application (client) ID and Directory (tenant) ID from the Overview page of the registered application for use in the script. Finally, generate a secret from the Certificates & secrets tab and record the secret also for use in the script.

Microsoft 365 Individual User Usage Reports AAD Application API Permissions

The Script

The script contains two functions. One to perform authentication to Microsoft Graph using the Tenant ID and Application ID of the AAD Registered Application that contains Reports.Read.All Application permissions, and a function to obtain a Microsoft 365 Individual User Usage Report. It will output the report data as an Excel spreadsheet into the same directory as the script is located. If a previous report is present it will overwrite it. The filename is M365UserUsageReport-XDays.xlsx where X is the number of days the report was run for.

As mentioned above there is a function that leverages the MSAL.PS PowerShell Module to simplify authentication to Microsoft Graph. The AuthN function takes the -credential and -tenantID parameters. The credential parameter is the ClientID and ClientSecret from the AAD registered application. The tenantID parameter is the objectID of the Azure AD Tenant where the application is registered.

Reports are automatically generated within Microsoft 365 every 7 Days. Reports can only be retrieved for report periods of the last 7, 30, 90, or 180 days. The GetM365UserActivity function in the script below accepts those values as a parameter for the -days parameter.

The script is parameterized. Update the following lines with your configuration information that you recorded when you registered your AAD Application.

  • 99 for the Tenant ID that you will be retrieving the report from
  • 101 for the ClientID and ClientSecret of the registered Azure AD Application
  • 103 for the number of days to report on (7, 30, 90 or 180)

Summary

Leveraging two community PowerShell Modules and an Azure AD registered application we can quickly obtain Microsoft 365 Individual User Usage Report data and export it in Excel format with some conditional formatting. From here you can extend and customize the Excel spreadsheet with additional criteria, graphs, or pivot tables to highlight the data in a format relevant to you and your organization.