How to Generate a SharePoint Online Site Storage Usage Report with PowerShell

Tracking the Storage Used by Each Site

Some time ago, I wrote about a script to report OneDrive for Business storage for users in an Office 365 tenant. As it turns out, the code is straightforward and only needs a few lines of PowerShell to grab the data and create the report.

The inevitable follow-up question is “can you create a SharePoint Online site storage report?” Due to the pressing needs of other topics, it’s taken me a while to get to this question. The answer is “absolutely!” even if the code is a little more complicated.

Why You Should Monitor SharePoint Online Site Storage

It’s important to check the storage used by SharePoint Online sites. Although Microsoft allocates a reasonable amount of free storage (1 TB per tenant plus 10 GB per licensed user) and you can buy more storage if necessary, a substantial amount of storage can be consumed by retention, and it’s surprising where the allocation goes.

Difference Processing OneDrive and SharePoint Sites

OneDrive for Business sites are owned by one person. They only come in one form. SharePoint Online sites come in a variety of forms (team-connected, hub, communications, private channel, and so on) and can have multiple administrators (owners).

In addition, when you run the Get-SPOSite cmdlet to fetch a list of sites, SharePoint Online optimizes for performance by not returning all properties. If you want to know about an “expensive” property, like the connected group for a team-enabled site, or the sensitivity label assigned to a site, you must use an explicit Get-SPOSite call to the site URL. All of which complicates matters a tad.

SharePoint Admin Center Option

The SharePoint admin center includes the option to Export details of Active sites (Figure 1), so you don’t have to write any PowerShell unless you want to.

The fun in writing your own script is that you can format the report the way that you want and include extra processing, such as returning the list of owners for a site belonging to an Office 365 group or team.

Approaching the Script

Conceptually, the code is uncomplicated:

• Fetch a list of sites in the tenant with Get-SPOSite -Limit All.
• Check each site, and based on its template, decide if any extra processing is needed (for instance, for team-enabled sites, fetch details of the group owners).
• Populate the report by writing out the assigned quota and storage used for each site, along with its URL and owners. In many cases, the assigned quota is 25,600 GB or 25 TB (the maximum storage assignment possible for a site). When sites are assigned this quota, it’s an indication that SharePoint is managing quota automatically for the tenant.
• After all sites are processed, generate the report.

You can download the full script from GitHub. All suggestions about potential improvements are welcome. I’ve tested the script against 300 sites and it ran without problems in just over 2 minutes. Your mileage might vary.

Lingering Sites

I also discovered that SharePoint Online sometimes returns a team site without an associated group. This situation might occur when a group has been deleted and the site is retained due to an Office 365 retention policy. I suspect it’s an edge case but included some code to handle the problem.

Report Output

The script output is a CSV file. You can slice and dice the content of the CSV to do whatever analysis is needed. If you prefer to view the report online, pipe the data to Out-GridView, which makes it easy to get a quick overview of the report, sort by different headings, and so on (Figure 2).

$Report | Out-GridView

---

Need more information about managing SharePoint Online with PowerShell? Check out the content of the Office 365 for IT Pros eBook!