Get Office365 usage reports from the Microsoft Graph using Windows PowerShell

If you just want the script, please visit TechNet Gallery, for more information on how to use it, read on!

The Microsoft Graph is a single endpoint that provides access to multiple APIs for Office 365 and other Microsoft cloud services. A preview Reporting API was recently released that brings Office 365 usage and analytics reporting into the Microsoft Graph.

Along with this release, the deprecation of the several reporting APIs and PowerShell cmdlets in the Reporting Web Service was announced and these will be disabled on 10/1/2017. This post and sample script demonstrates how to consume Office 365 usage information from the Microsoft Graph using Windows PowerShell. A basic understanding of OAuth is helpful but not required.

Understanding the Usage Reports

The Office 365 Usage reports provide two main types of reporting

Activity - The amount of interaction with the service such as number of files viewed

Usage - The amount of content created or generated in the service, such as total storage used

A full list of the available reports and details on each report type can be found in Activity Reports in the Office 355 Admin Center.

Details on the corresponding Reporting API can be found in the Microsoft Graph Documentation

Accessing the Office365 Usage and Activity reports using Windows PowerShell

Accessing the Microsoft Graph requires that you register an App in Azure Active Directory and grant that app permission to view the usage reports. In this case we will use this App and the administrators credentials when authenticating to the Microsoft Graph. You could also use an app with an app secret to provide access without requiring the administrators credentials. This post will walk through the steps to configure the app permissions to use with the script, but if you want more information on authentication and authorization please see App authentication with Microsoft Graph.

Step 1: Register an App in AzureAD to access the Usage Reports

Login to https://portal.azure.com and navigate to "Azure Active Directory" > "App Registrations" and click "New Application Registration"

Give your application a friendly name, Select application type "native", and enter a redirect URL in the format urn:foo and click create. Note: For this script we do not have a specific requirement for the Redirect URI, the exact values doesn't matter as long as it's in the format URN:<Value>

Click on the App, navigate to required permissions, click add and select the Microsoft Graph API

Choose Select Permissions and grant the App the "Read All Usage Reports" permission

Copy the Application ID and Redirect URI and save them for use in the script

Step 2: Use Get-Office365Report.ps1 to access the Graph API and download your report

A sample script for using PowerShell with the Microsoft Grapth API is available in the TechNet Gallery. This script will return the chosen report as a system.array object. You can then manipulate that further in Windows PowerShell using standard cmdlets. In this example we are going to export the output to a CSV file using Export-CSV

[powershell]#Get the credentials for the user with access to usage reports
$cred = Get-Credential
#Run the report
$report = .\Get-Office365Report.ps1 `
-TenantName "contoso.onmicrosoft.com" `
-ClientID "b5dc219e-58b4-458a-a415-c203b10380f5" `
-Report SharePointSiteUsage `
-Period D180 `
-View Sites `
-Credential $cred `
-RedirectURI "urn:contoso"

#Export report to CSV file
$report | Export-CSV -path C:\temp\SharePointSiteUsage.csv -NoTypeInformation[/powershell]

Once you have the CSV file you can open it your BI tool of choice and build your visuals. In a future post I will show how to analyze this data in PowerBI and use this script to automatically refresh the data on demand.

Resources

Get Office365 usage reports from the Microsoft Graph using Windows PowerShell https://gallery.technet.microsoft.com/Get-Office365-usage-f955ade4

Overview of Microsoft Graph https://developer.microsoft.com/en-us/graph/docs

App authentication with Microsoft Graph https://developer.microsoft.com/en-us/graph/docs/authorization/auth_overview

What’s new in Office 365 administration—public preview of Microsoft Graph reporting APIs https://blogs.office.com/2017/03/31/whats-new-in-office-365-administration-public-preview-of-microsoft-graph-reporting-apis/

Microsoft Graph: Office 365 Usage Reports https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/resources/report