Know What Sites Are Active and Using Storage
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 to grab the data from SharePoint Online and create the report.
The inevitable follow-up question is “can you create the same kind of report for SharePoint Online sites?” 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.
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.
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!