Table of Contents
Much Faster to Create OneDrive Storage Report from Usage Data
Nearly five years ago, I wrote an article about a PowerShell script to report OneDrive for Business storage consumption. The script works well but it’s slow because it uses the Get-SPOSite cmdlet from the SharePoint Online management module. Everything is fine when running in a tenant with less than five hundred accounts. Past this point and you might have plenty of time for coffee.
That’s where the Graph usage reports API comes in handy. Despite being two or so days behind in terms of absolute accuracy for storage consumption, the usage reports API is extremely fast because it reads from a data warehouse populated with information by background processes running in the Microsoft datacenters. In this case, we need the OneDrive account detail report, which can cover usage from 7 to 180 days.
Including User Data in the OneDrive Storage Report
As noted previously, an ongoing issue affects usage reports for SharePoint Online data and prevents the population of site URLs in the reports. The same issue exists for OneDrive for Business data. This is a pain, but there’s often a silver lining in a bug. In this case, I decided to incorporate some user data into the report to make it possible for tenant administrators to sort by city, country, or department.
Outline of the Script to Create the OneDrive Storage Report
Here’s what the script does:
- Runs Connect-MgGraph to connect to the Graph. This report only needs the User.Read.All and Reports.Read.All permissions.
- Checks if the tenant obscures user data in reports. If this is true, the script updates the setting to allow it to fetch unobscured data.
- Runs Get-MgUser to fetch details of all licensed member accounts in the tenant.
- Populates a hash table. The key is the user principal name and the value is an array of user properties. Looking up a hash table to find user details is quicker than running Get-MgUser for each account or reading an array.
- Use the Invoke-MgGraphRequest cmdlet to fetch the OneDrive account detail data for the last seven days. The data is loaded into an array.
- Loop through the array to extract storage information for a user’s OneDrive for Business account and report what’s found. Included in the report is the information found by looking up the hash table for user details.
- Export the report data to a CSV file.
- Reset the tenant obscured report data setting if necessary.
Figure 1 shows an example of the OneDrive storage report generated by the script. When Microsoft fixes the Site URL problem for usage reports, I’ll update the script to include that property, but for now the script does a nice job of reporting OneDrive storage consumed by user accounts. And the script runs much faster than the older version based on the SharePoint Online management cmdlets.
Two Things to Learn About Reporting Microsoft 365 Data
This script demonstrates two things about reporting Microsoft 365 data. First, don’t assume that you need 100% up-to-date information about usage. The point is that data in reports might be accurate immediately after the generation of the report but degrades thereafter. There’s no great difference between an account that’s used 91.01% of its storage quota and 91.11%. The information available through the usage reports API gives as accurate a picture about usage in 99% of cases.
Second, don’t assume that the data returned by a cmdlet limit what you can use in a report. Properties like user identifiers (GUIDs) and user principal names enable matches for data drawn from multiple sources. Using hash tables to store information fetched from different sources is an excellent and fast way to create lookup tables for reports.
You can download the script from GitHub. Normal caveats apply. Don’t assume that the script has bulletproof error handling (it doesn’t) nor that a bug isn’t lurking somewhere. Test the script and have some fun chasing bugs if there are any.
Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.