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

clip_image001

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

clip_image002

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”

clip_image003

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>

clip_image004

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

clip_image005

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

clip_image006

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

clip_image007

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

#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

clip_image008

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

Comments (17)

  1. Ondrej Kracmar says:

    I can’t download the script

    1. Damian Wiese says:

      Thank you for letting me know. Please try again, it should be available now at: https://gallery.technet.microsoft.com/Get-Office365-usage-f955ade4?redir=0

      1. Ondrej Kracmar says:

        Thank you

  2. Emmanuel says:

    Hello, why can’t we use the ClienID of Azure PowerShell to query the reporting endpoint ?

    1. Damian Wiese says:

      Hi Emmanuel,
      In my testing I found that the Azure AD PowerShell well known client ID (1950a258-227b-4e31-a9cf-717495945fc2) did not have permission to the usage reports, so I created a native app where I could control the permissions.

      1. Emmanuel says:

        Do you know if this is Something that will change for GA ? That sort of prerequisite is kind of a “NoGo” for a “migration” from the older method.

        1. Damian Wiese says:

          Hi Emmanuel,
          I do not know at this time, but I do have a couple items that I want to test to streamline the access token generation. I will update this post once I have tested them. Just curious, why is the creation of an App in AzureAD a blocker for migration?

          1. Emmanuel says:

            I work as a consultant and I use some PowerShell script to assess my client’s tenant, I can’t ask them to create an Azure app just for this basic need. Beside, it worked without it before 🙂

          2. Damian Wiese says:

            I took a look in my tenant and the only apps that I see with the Reports.Read.All permission grant are the ones that I created. It looks like you already created an UserVoice entry for this, which is the best way to get this feed back to the team that owns this.

            Here is the UserVoice entry for anyone else who wants to request this adding these permissions to the PowerShell clientID

          3. Emmanuel says:

            Thank you Damian for your confirmation 🙂
            Can you tell me which team is in charge of this ? Azure ? Office 365 ?

          4. Damian Wiese says:

            Office365, I have passed on the request to the team. There is also a discussion regarding this request on the Microsoft Tech Community.

  3. François says:

    Hi Damian, thanks a lot for this post, very interesting! I’m eager to read the next one about how to analyze this data in PowerBI and refresh the data on demand as I’m struggling to do so… When will you post it? 🙂 Thanks again and have a nice day

    1. Damian Wiese says:

      Thank you Francois, I’m glad you found this useful. I’m still working on the follow-up post but will be sure to get it up as soon as I can 🙂 There is also a PowerBI pack that is currently in preview and should be widly available soon. Please see the discussion on Microsoft Tech Community for more details.

    2. Damian Wiese says:

      Hi Francois,
      The Office365 team has just released a public preview of the Office 365 Adoption Content Pack for PowerBI
      https://blogs.office.com/2017/05/22/announcing-the-public-preview-of-the-office-365-adoption-content-pack-in-powerbi/

  4. Andrew Holler says:

    “You could also use an app with an app secret to provide access without requiring the administrators credentials.”

    In regards to this statement, would an Azure AD admin need to set the permissions for Reports.Read.All? Currently I can acquire a token but the response I get back is a 403 forbidden. Apologies if this is obvious, I am new to both PowerShell and Azure AD.

    1. Damian Wiese says:

      Hi Andrew,
      Correct. Regardless of the authentication method you will need to grant the application access to Reports.Read.All in AzureAD.

Skip to main content