Generate SharePoint Online Site Usage Reports using Graph API and PowerShell

Requirement: Generate usage reports for SharePoint Online sites.

How to Generate Usage Reports for All SharePoint Online Sites in the Tenant?

If you are a SharePoint administrator, you may find it useful to generate reports about how your users use the sites and content. Microsoft has made it easy for you to generate a usage report that includes statistics such as Total Files, Storage Used, Pages Views, etc. This blog post will show you how to generate the usage reports for all SharePoint Online sites in your tenant. These reports can be used to help you track and monitor user activity on your SharePoint sites.

From Microsoft 365 admin portal, you can export the usage data in CSV format. To get usage and activity reports for all SharePoint Online sites, follow these steps:

  1. Navigate to Microsoft 365 Admin Center at https://admin.microsoft.com/
  2. Click on Reports >> Usage in the left navigation.
  3. Choose the required report. E.g. SharePoint Site Usage.

The SharePoint Site usage reports are available for the last 7 days, 30 days, 90 days, and 180 days. You can also export the report data into a CSV file by selecting the Export link.

sharepoint online site usage report

How to Generate Usage Reports using Graph API and PowerShell?

Microsoft Graph API is a communication service that connects and handles data between Azure and Microsoft 365 services. To call Microsoft Graph API methods, we need an App. Follow these steps to add a new App.

Step 1: Create New App Registration in Azure AD

To use the Microsoft Graph APIs, you first have to register an application.

  1. Login to Azure Active Directory at https://portal.azure.com  >> Open  “Azure Active Directory” >> Click on “App Registrations” from the left navigation
  2. Click on “New Registration” >> Provide a Name for your application, select “Who can use this application or access this API?”, Click on the “Register” button at the bottom to add a new app.
    register application in Azure AD

Step 2: Grant Permissions to the App

Once the App is created, the next step is to provide the necessary permissions to read usage reports.

  1. From the App Registrations page, Open the App you just created >> Click on “API Permissions”
  2. Click on “Add a Permission” >>  Select “Microsoft Graph” >> Choose “Application Permissions” >> Under Reports, Select “Reports.Read.All”>> Click on “Add Permissions”.
    add permissions to App

You may need to select different permission depending on what you want to access. Microsoft Graph API can query almost all details from Azure AD or other Office 365 services.

Consent to the application

Application permission requires admin consent in a tenant before it can be used. Create a consent URL like the following:

  • Select the “Reports.Read.All” and Click on the “Grant admin consent for <Your-Tenant>” button once.

Step 3: Add the Client Secret

As a final step, we need a client secret key associated with the App.

  1. From the App page, click on the “Certificates & secrets” link in the left navigation. Click on the “New client secret” button. Choose the validity duration for the app and create a client secret key.
    add client secret

PowerShell to Call Graph API method to get Usage Reports

Once we have the App ready, Copy and Paste the Client ID, Secret, and Other Parameters to this script and execute. Let’s generate usage reports for your SharePoint Online sites using the Graph API and PowerShell:

#Function to Call Graph API
Function Get-UsageReport {
param (
    [parameter(Mandatory = $true)] [string]$ClientID,
    [parameter(Mandatory = $true)] [string]$ClientSecret,
    [parameter(Mandatory = $true)] [string]$TenantName, 
    [parameter(Mandatory=$true)] [string]$GraphUrl
)
    Try {
        #Graph API URLs
        $LoginUrl = "https://login.microsoft.com"
        $ResourceUrl = "https://graph.microsoft.com"
 
        #Compose REST request
        $Body = @{ grant_type = "client_credentials"; resource = $ResourceUrl; client_id = $ClientID; client_secret = $ClientSecret }
        $OAuth = Invoke-RestMethod -Method Post -Uri $LoginUrl/$TenantName/oauth2/token?api-version=1.0 -Body $Body
 
        #Perform REST call
        $HeaderParams = @{ 'Authorization' = "$($OAuth.token_type) $($OAuth.access_token)" }
        $Result = (Invoke-WebRequest -UseBasicParsing -Headers $HeaderParams -Uri $GraphUrl)

        #Format Microsoft Graph Output
        (($Result).RawContent -Split "\?\?\?")[1] | ConvertFrom-Csv
    }    
    Catch {
        Write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
    }
}

#Parameters
$ClientID = "98ax2c6d2-264c-424e-b911-c33x547de5211"
$ClientSecret = "T1ZP3-a_NIoA4cej2xky.2xaDcF..6E5"
$TenantName = "crescentintranet.onmicrosoft.com"
$GraphUrl = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D30')"

#Call the function to get usage data
$UsageData = Get-UsageReport -ClientID $ClientID -ClientSecret $ClientSecret -TenantName $TenantName -GraphUrl $GraphUrl
$UsageData
$UsageData | Export-Csv "C:\Temp\siteusage.csv" -NoTypeInformation

This PowerShell script generates a CSV report as:

sharepoint online usage reports using graph API PowerShell

Microsoft Graph REST API Reference https://docs.microsoft.com/en-us/graph/api/resources/sharepoint-activity-reports?view=graph-rest-1.0

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

15 thoughts on “Generate SharePoint Online Site Usage Reports using Graph API and PowerShell

  • No longer appears to work as of latest MS updates. I am getting a Error: The remote server returned an error: Bad Request

    Reply
    • This is also happening for me.

      Reply
      • I am also having the same issue. “400 bad request”

        Reply
  • Hi We are trying to execute the above script after changing parameters from azure cloud power shell should we need to make any changes in power shell script above

    Reply
  • How and where can I run the script. I am actually new at using API’s and finding it difficult to follow up on this.

    Reply
  • Is there a way to use this tool/report to compare the changes in storage from one day to another? For example, I want to see what changed in tenant storage overnight (because I’m near my tenant storage limit).

    Thanks

    Reply
  • Getting a Error: The remote server returned an error: (401) Unauthorized. Even though I created the app registration and granted myself access. Do you know if this is allowed in GCC/DoD?

    Reply
  • No You need to change the setting of the reports in the tenant admin. then this type of report will not come

    Reply
  • This is a great script, however, i am seeing the URL and Names as numbers, “2021-10-16″,”00000000-0000-0000-0000-000000000000″,”142BD27DA3A0B1C3C4B8FBD542CE53C7″,”A4364F2686A5BFA1228FAE34A3EF057E”,”False”,”2021-10-15″,”34″,”15″,”2″,”1″,”188504932″,”27487790694400″,”Group”,”10793E1B642F93C90EE32573CAD62CFA”,”30″
    Is there something that has changed with the API?

    Reply
    • If you get GUIDs instead of actual site URL or user Email Ids – This should be due to Data privacy settings for Admin Center reports. Navigate to Settings >>> Org Settings >> Services >> Reports, Uncheck “Display concealed user, group, and site names in all reports” Checkbox and save.

      Reply
  • Thanks for this great post! I am also wondering how to retrieve usage data on subsite level. I can easily get the LastItemUserModifiedDate or site users count but no data about page views. Any ideas?

    Reply
  • Hi, were you able to pull report for usage on subsite/folder level? I have a similar requirement.

    Reply
  • Hello, this tutorial is great, im on my way to getting some dashboards created, however, this report does not include subsites. Is there a way to include them or do I need to iterate through all the sites and look them up individually?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *