How to Report SharePoint Online Site Usage Data with PowerShell and the Graph

Posted on by Tony Redmond

Remove the Need for Administrator Access

Following an article I wrote about using PowerShell to report SharePoint Online site storage usage, a reader asked if it is possible to create such a report without needing to sign in with a SharePoint Online administrator account. They’d like non-admins to be able to report data but don’t want them to run PowerShell scripts or access the admin center.

Global Reader Role and Usage Reports

The Global Reader role allows non-privileged access to reporting data exposed in some interfaces like the Microsoft 365 Admin Center. The reports section of the admin center (Figure 1) includes reports on SharePoint user and site activity.

The Reports section of the Microsoft 365 Admin Center
Figure 1: The Reports section of the Microsoft 365 Admin Center

The Admin Center includes an export option to download data as a CSV file. If you select the site usage report, this seems promising, until you realize that the report includes redirect sites and even the SharePoint Online Tenant Fundamental Site created when the tenant is initialized. Some of the fields output could be better formatted too.

Usage Reports Based on the Graph

The usage reports available in the Microsoft 365 Admin Center use the Graph reports API to generate their data. It’s very easy to write a quick and dirty PowerShell script to call the SharePoint Site Storage Usage API to return the same data as you see in the usage reports. Once the data is downloaded, you can manipulate it to meet your needs.

Granting Permissions for the Graph

To gain access to the Graph, PowerShell needs to use an app to authenticate with the Reports.Read.All and Sites.Read.All permissions. The basic idea is that you register an app for your tenant to use with PowerShell and generate an app secret to use for OAuth authentication. You then assign the necessary Graph permissions to the app. The request to add the permissions to the app must be approved by an administrator before the permissions can be used. You can then use the app identifier and secret to generate an access token for the Graph which contains the permission. Read this post for detailed steps of creating such an app for PowerShell to use.

Accessing the Graph

After the app is authorized with the necessary permissions, we can use it with PowerShell. This snippet:

  • Populates variables with the app identifier, secret, and tenant identifier. These values are unique to an app and to a tenant. You’ll have to change them for your tenant for this code to work.
  • Builds a request to get an access token.
  • Parses the returned token.
$AppId = "e716b32c-0edb-48be-9385-30a9cfd96155"
$TenantId = "c662313f-14fc-43a2-9a7a-d2e27f4f3478"
$AppSecret = 's_rkvIn1oZ1cNceUBvJ2or1lrrIsb*:='

# Build the request to get the OAuth 2.0 access token
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
    client_id     = $AppId
    scope         = "https://graph.microsoft.com/.default"
    client_secret = $AppSecret
    grant_type    = "client_credentials"}

# Request token
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing
# Unpack Access Token
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token
$headers = @{Authorization = "Bearer $token"}
$ctype = "application/json"

With a token, we can issue the Graph request to fetch the SharePoint Online storage usage data:

# Get SharePoint files usage data
$SPOFilesReportsURI = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D7')"
$Sites = (Invoke-RestMethod -Uri $SPOFilesReportsURI -Headers $Headers -Method Get -ContentType "application/json") -Replace "", "" | ConvertFrom-Csv

All that remains to be done is to parse the returned data and generate a report (a CSV file). You can download the script from GitHub. As always, the code is bare-bones and doesn’t include much in terms of error checking.

I also output the report data to Out-GridView (Figure 2) as it’s the easiest way to browse the information.

Reviewing the SharePoint site usage data retrieved from the Graph
Figure 2: Reviewing the SharePoint site usage data retrieved from the Graph

Advantages and Disadvantages

The big advantage of this approach is that no dependency exists on cmdlets in the PowerShell module for SharePoint Online or an administrator account. All the code is basic PowerShell that can be run by any user.

Because this approach uses data fetched from the Graph, the code is fast too – much faster than the version based on the SharePoint Online cmdlets, and the speed advantage becomes larger as the number of sites grows. This is because the Graph generates the report data and has it ready for fetching while the other approach requires you to generate the data for each site with the Get-SPOSite cmdlet. On the other hand, the Graph data is at least two days old, something that might not be too much of a concern when reviewing storage usage.

The downside is that the Graph usage data includes a limited set of properties. Some useful properties, like site files, active files, and views, aren’t returned by the Get-SPOSite cmdlet, but Get-SPOSite returns information like the site title, group identifier (to get a list of site owners), and sensitivity label among others.

Combine to Get Both

Combining data fetched from the Graph with that fetched by Get-SPOSite is the best of both worlds, even if you’ll need to use a SharePoint administrator account. The question is what data are needed. If you really need the extended information about a site, you’ll have to use the SharePoint Online module. But if all you need is simple storage data, the Graph can provide that information quickly, albeit if it’s slightly out-of-date.

41 Replies to “How to Report SharePoint Online Site Usage Data with PowerShell and the Graph”

  1. Pingback: Customizing the Office 365 Profile Card with Graph Explorer - Office 365 for IT Pros

  2. I’ve gone through all the steps (Created App with app secret, with the necessary graph permissions for Sharepoint), but I continually get “We do not recognize this tenant ID.” Any thoughts?

    Reply

    1. Where did you get the tenant id? The easiest way is to run Get-AzureADTenantDetail in PowerShell (after running Connect-AzureAD). It will tell you what your tenant id is and you can use that identifier with the script.

      Reply

      1. I pulled it out of the Azure Portal, so the tenant is correct. The Token request comes back successful. However, now it’s transitioned from that error to a 404 (Not Found):

        Here’s the script snippet:
        $uri = “https://login.windows.net/$TenantId/oauth2/v1.0/token”
        $body = @{
        client_id = $AppId
        scope = “https://graph.microsoft.com/.default”
        client_secret = $AppSecret
        grant_type = “client_credentials”
        }
        # Request token
        $tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType “application/x-www-form-urlencoded” -Body $body -UseBasicParsing
        # Unpack Access Token
        $token = ($tokenRequest.Content | ConvertFrom-Json).access_token
        $headers = @{Authorization = “Bearer $token”}
        $ctype = “application/json”

        $SPOFilesReportsURI = “https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period=’D7′)”
        $Sites = (Invoke-RestMethod -Uri $SPOFilesReportsURI -Headers $Headers -Method Get -ContentType “application/json”) -Replace “”, “” | ConvertFrom-Csv

      2. What permissions have you assigned to the app? I believe this one needs Reports.Read.All

      5. Does the account you’re using have admin access? You’d need at least the Report Reader role.

      6. I thought the point of doing the API call was to avoid needing user auth, which is why we generate the App ID and Secret Key?

      9. Any obvious differences between your script and mine? Thoughts on what I might be missing. Anything on the App I could have missed that’s not permission related?

      10. This is the code I ran:

        # Build the request to get the OAuth 2.0 access token
        $uri = “https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token”
        $body = @{
        client_id = $AppId
        scope = “https://graph.microsoft.com/.default”
        client_secret = $AppSecret
        grant_type = “client_credentials”}

        # Request token
        $tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType “application/x-www-form-urlencoded” -Body $body -UseBasicParsing
        # Unpack Access Token
        $token = ($tokenRequest.Content | ConvertFrom-Json).access_token
        $headers = @{Authorization = “Bearer $token”}
        $ctype = “application/json”

        Write-Host “Fetching SharePoint Online site data from the Graph…”
        # Get SharePoint files usage data – includes redirects, so we will have to remove them
        $SPOFilesReportsURI = “https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period=’D7′)”
        $Sites = (Invoke-RestMethod -Uri $SPOFilesReportsURI -Headers $Headers -Method Get -ContentType “application/json”) -replace “”, “” | ConvertFrom-Csv

        I created a brand new app (registered in Azure AD) and assigned it Reports.Read.All. For some reason, that didn’t work… But it does when you assign Sites.Read.All. Can you try that?

      11. Went ahead and created a new app registration. Added the Sites.Read.All permission. Plugged in the new app id and app secret. Now I’m back to getting We do not recognize this tenant ID. Our scripts look the same, so I don’t believe it’s anything related to something missing there. Did you add a redirect url on the app registration?

      14. Invoke-RestMethod : The remote server returned an error: (404) Not Found.
        At line:20 char:11
        + $Sites = (Invoke-RestMethod -Uri $SPOFilesReportsURI -Headers $Header …
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
        + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

      15. Your app has User.Read (delegated) permission as well? Mine does.

        Apart from that, it only has Reports.Read.All and Sites.Read.All, both of which are application permissions.

      16. And I assume that admin consent was granted for the tenant to allow the app to use the permissions to access data?

      17. Correct. The App has those 3 permissions and they’ve been admin granted. I didn’t have a redirect url either. Just wanted to make sure it wasn’t something I needed.

      18. When you look at the App manifest. Do these 4 properties all show false for Oauth2?
        “oauth2AllowIdTokenImplicitFlow”: false,
        “oauth2AllowImplicitFlow”: false,
        “oauth2Permissions”: [],
        “oauth2RequirePostResponse”: false,

      19. It’s a very simple app that was created without tweaking anything. Here’s what I see:

        {
        “id”: “0293f85e-7145-4387-9611-8ac1d9026fce”,
        “acceptMappedClaims”: null,
        “accessTokenAcceptedVersion”: null,
        “addIns”: [],
        “allowPublicClient”: null,
        “appId”: “f24bd1e1-7723-4e44-87d7-8ef6b5b24da0”,
        “appRoles”: [],
        “oauth2AllowUrlPathMatching”: false,
        “createdDateTime”: “2021-02-18T13:20:14Z”,
        “disabledByMicrosoftStatus”: null,
        “groupMembershipClaims”: null,
        “identifierUris”: [],
        “informationalUrls”: {
        “termsOfService”: null,
        “support”: null,
        “privacy”: null,
        “marketing”: null
        },
        “keyCredentials”: [],
        “knownClientApplications”: [],
        “logoUrl”: null,
        “logoutUrl”: null,
        “name”: “SPOReports”,
        “oauth2AllowIdTokenImplicitFlow”: false,
        “oauth2AllowImplicitFlow”: false,
        “oauth2Permissions”: [],
        “oauth2RequirePostResponse”: false,
        “optionalClaims”: null,
        “orgRestrictions”: [],
        “parentalControlSettings”: {
        “countriesBlockedForMinors”: [],
        “legalAgeGroupRule”: “Allow”

      23. Do your headers look like this:
        Name Value
        —- —–
        Authorization Bearer eyJ0eXAiOiJKV1QiLCJub25jZSI6IjliOFNiMHdyNVNVeld6X1V1UWtjMVFWVnRROC

      24. Name Value
        —- —–
        Authorization Bearer eyJ0eXAiOiJKV1QiLCJub25jZSI6IkswdTRHWHotUzE4MlpNcGtpQ2pFX016TURfLUxwbV80…
        Content-Type application\json
        ConsistencyLevel eventual

      25. I have the content-type in the rest call, but I don’t have ConsistencyLevel eventual. Are you adding that in to the header or as a switch in the rest call?

      26. I’m not sure that the consistency level is needed. It’s more important for the Groups API, but here’s what I do.

        $Headers = @{
        ‘Content-Type’ = “application\json”
        ‘Authorization’ = “Bearer $Token”
        ‘ConsistencyLevel’ = “eventual” }

      27. Tried it, but no dice. Is it possible that because I’m trying to query against a developer tenant. That could be the issue?

      29. If you look under Enterprise Applications and select Office365 Sharepoint ONline. Do you have any permissions listed there?

      31. This is a huge mystery to me. The code is pretty simple. You have an access token and an id for an app with the right permissions, so on the surface, that looks as if everything is good and ready to go. I really don’t know what to suggest now.

      32. I’m asking someone who is more Graph literate than I am to have a look and see if he can spot anything that’s going wrong.

      33. So I was able to figure out it was indeed because it was a developer tenant. I got access to our production tenant and it works perfectly fine. So there must be something in place on the developer tenants. I appreciate you working with me to try and figure out what was going on. Thanks!

  3. Aaron do you have all the variables set properly? The snipped you pasted above is missing things such as $tenant, which if you havent set will result in 404 error when trying to obtain the token. You are also using the old endpoint, I’m not even sure it supports OAuth. Use the following for the URI:

    $uri = “https://login.microsoftonline.com/tenantname.onmicrosoft.com/oauth2/v2.0/token”

    Other than that, check that your token has the proper permissions are actually stamped on the token (jwt.ms/jwt.io for parsing). The permissions need to be added for the Graph API, not Office365 Sharepoint ONline.

    If nothing else works, post the full snippet you are trying to run, including all variables (sans the secret of course). Same goes for any errors, post the full output so we know which line/cmdlet is causing the error.

    Reply
  4. Pingback: The New 250 GB Upload Limit for Microsoft 365. Good or Bad? – Alpha Sandbox

  5. I need help to resolve this error: “Attempted to divide by zero.”

    1. As such this line is not displaying any data:
    $Report | Export-CSV -NoTypeInformation D:\testing\SPOSiteConsumption.CSV #Report is empty : Error= “Attempted to divide by zero.”

    2. This line is not displays data in console. However, site URL, Owner Display name, and Owner Principal Name are displayed as GUID.
    $Report | Sort-Object { $_.UsedGB -as [decimal] }, url -Descending | Out-gridview

    3. I am newbie and do not how to troubleshoot this error. Any help is welcomed, please

    Thanks in advance

    Reply

    1. It looks as if the Graph API call to retrieve SPO site usage data returned no usage data. Is this a new tenant (it takes several days before usage data is available in a tenant).

      Reply

  6. Tony,
    You are right. I am checking on what I missed or messed up.
    The tenant is an old one with lots of data.
    Thanks for your response.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.