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

[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]

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

  • Anonymous
    April 16, 2017
    I can't download the script
  • Anonymous
    April 19, 2017
    Hello, why can't we use the ClienID of Azure PowerShell to query the reporting endpoint ?
    • Anonymous
      April 19, 2017
      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.
      • Anonymous
        April 19, 2017
        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.
        • Anonymous
          April 24, 2017
          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?
          • Anonymous
            April 24, 2017
            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 :)
            • Anonymous
              April 24, 2017
              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
          • Anonymous
            April 24, 2017
            Thank you Damian for your confirmation :)Can you tell me which team is in charge of this ? Azure ? Office 365 ?
            • Anonymous
              April 25, 2017
              Office365, I have passed on the request to the team. There is also a discussion regarding this request on the Microsoft Tech Community.
  • Anonymous
    April 25, 2017
    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
    • Anonymous
      April 25, 2017
      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.
    • Anonymous
      May 22, 2017
      Hi Francois,The Office365 team has just released a public preview of the Office 365 Adoption Content Pack for PowerBIhttps://blogs.office.com/2017/05/22/announcing-the-public-preview-of-the-office-365-adoption-content-pack-in-powerbi/
  • Anonymous
    April 29, 2017
    Good script.
  • Anonymous
    June 15, 2017
    "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.
    • Anonymous
      June 15, 2017
      Hi Andrew,Correct. Regardless of the authentication method you will need to grant the application access to Reports.Read.All in AzureAD.
  • Anonymous
    October 15, 2017
    Hey thanks for providing this resource - it's coming in super handyI keep getting "WARNING: The app has not been granted permissions by the user. Opening an interactive prompt to grant permissions" and I need to enter credentials in the popup every time which is stopping me automating. I've given all permissions to the app though.Not great with Azure so wondering if there is something simple I'm missing?Once I enter credentials it runs fine, but next time I get prompted again.Thanks again!
    • Anonymous
      October 26, 2017
      Aaron, There were some changes to the reporting API right around Oct 15-16, it possible that this was causing some issues. I have updated the script to use the updated APIs. Are you still experiencing the same issue with the new script?
      • Anonymous
        October 30, 2017
        Thanks Damian I'll download and let you know
        • Anonymous
          November 21, 2017
          Hi DamianI figured out where I was going wrong after the changes to the APIs.It worked once and now I'm getting "Invoke-RestMethod : The remote server returned an error: (404) Not Found.At \scripts\Get-Office365Report.ps1:333 char:15+ ... $result = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method"Nothing had changed between it working and stopping though.Any thoughts?
  • Anonymous
    October 15, 2017
    It seems I keep getting 504 Gateway Timeout when I reach /beta/reports, either using script or Graph Explorer. I tested this on different tenants and all failed, the success rate is really low - I get 1 success only out of over 200 same requests. Is there any problem in reaching this endpoint? Thanks!
    • Anonymous
      October 26, 2017
      There have been some changes to the report API under the beta endpoint. I have updated the script sample to use these new APIs