Table of Contents
Remove the Need for Administrator Access to Create a SharePoint Site Report
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 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.
Update: The current version of the script has been updated to use the Microsoft Graph PowerShell SDK. This removes the requirement to use an app. The information described below shows how to use an app to authenticate and make Graph API requests. The same results are obtainable using the Graph SDK.
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.
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.
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?
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.
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
What permissions have you assigned to the app? I believe this one needs Reports.Read.All
Confirm that the program works with Reports.Read.All
Reports.Read.All, Sites.FullControl.All, Sites.Read.All, and
User.Read
Does the account you’re using have admin access? You’d need at least the Report Reader role.
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?
Yep. I’m just going through possible issues…
I tested with an account with no admin roles and everything worked perfectly.
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?
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?
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?
No redirect URI. It’s a very simple app.
Now it’s 404 Not Found.
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
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.
And I assume that admin consent was granted for the tenant to allow the app to use the permissions to access data?
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.
When you look at the App manifest. Do these 4 properties all show false for Oauth2?
“oauth2AllowIdTokenImplicitFlow”: false,
“oauth2AllowImplicitFlow”: false,
“oauth2Permissions”: [],
“oauth2RequirePostResponse”: false,
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”
Yours looks just like mine, so I’m not sure why it’s not functioning.
I don’t know. It’s impossible for me to say…
Long shot. Maybe create a new app and see if that works…
Do your headers look like this:
Name Value
—- —–
Authorization Bearer eyJ0eXAiOiJKV1QiLCJub25jZSI6IjliOFNiMHdyNVNVeld6X1V1UWtjMVFWVnRROC
Name Value
—- —–
Authorization Bearer eyJ0eXAiOiJKV1QiLCJub25jZSI6IkswdTRHWHotUzE4MlpNcGtpQ2pFX016TURfLUxwbV80…
Content-Type application\json
ConsistencyLevel eventual
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?
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” }
Tried it, but no dice. Is it possible that because I’m trying to query against a developer tenant. That could be the issue?
I don’t know. You’re using the code from GitHub? https://github.com/12Knocksinna/Office365itpros/blob/master/ReportSPOSiteStorageUsedGraph.PS1 Or is it your own code?
If you look under Enterprise Applications and select Office365 Sharepoint ONline. Do you have any permissions listed there?
That code. Obviously updated with my tenant id, app id, and secret.
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.
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.
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!
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.
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
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).
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.
I want to automate the email to use the site usage report. I dont have any coding experience. can anyone help to get this done.