SharePoint storage reaching the red line is terrible. Users will start complaining they cannot store more documents on their team sites. Administrators must ensure SharePoint site owners are notified of their site’s storage usage, so they can perform housekeeping as needed.
We’ll discover in this post how to generate a SharePoint storage report using PowerShell.
Check out our article on how to upload files and folders to SharePoint Online using PowerShell.
Table of Contents
Requirements
- A computer with Windows PowerShell 5.1 or the latest version of PowerShell Core (7+)
- The PnP PowerShell module must be installed on the computer.
- SharePoint Administrator access in your Office 365 tenant.
Retrieving the site collections is the first step to generating the SharePoint storage report. The specific cmdlet to get a site collection’s information is the Get-PnpTenantSite. Depending on the type of sites you want to retrieve, you can apply filters and switches to this command.
But before anything else, you must connect to the SharePoint Online PowerShell.
Connect-PnPOnline -Url https://<tenant>.sharepoint.com
Next, run the following commands to create an exclusion list. This command retrieves the root and host sites of your SharePoint Online tenant and adds them to the $excludedURLs variable.
$pnpTenantInstance = Get-PnPTenantInstance -ErrorAction Stop $excludedUrls = [System.Collections.ArrayList]@( "$($pnpTenantInstance.RootSiteUrl)" "$($pnpTenantInstance.RootSiteUrl)sites/appcatalog" "$($pnpTenantInstance.RootSiteUrl)portals/hub", "$($pnpTenantInstance.RootSiteUrl)search", "$($pnpTenantInstance.MySiteHostUrl)", "$($pnpTenantInstance.TenantAdminUrl)" )
Now that we have the exclusion list, let’s retrieve the SharePoint site collections. Depending on which types of sites you wish to retrieve, run the appropriate command from the list below.
# All SharePoint Online Sites, including OneDrive
$siteCollection = Get-PnPTenantSite -IncludeOneDriveSites | Where-Object { $_.Url -notin $excludedUrls }
# SharePoint Sites only
$siteCollection = Get-PnPTenantSite | Where-Object { $_.Url -notin $excludedUrls }
# OneDrive Sites only
$siteCollection = Get-PnPTenantSite -IncludeOneDriveSites -Filter "Url -like '-my.sharepoint.com/personal/'" | Where-Object { $_.Url -notin $excludedUrls }
# Microsoft 365 Groups-Connected Sites only
$siteCollection = Get-PnPTenantSite -GroupIdDefined:$true | Where-Object { $_.GroupId -ne '00000000-0000-0000-0000-000000000000' -and $_.Url -notin $excludedUrls }
# Sites Without a Group
$siteCollection = Get-PnPTenantSite -GroupIdDefined:$false | Where-Object { $_.Url -notin $excludedUrls }
# Sites Connected to Teams
$siteCollection = Get-PnPTenantSite -GroupIdDefined:$true | Where-Object { $_.IsTeamsConnected -eq $true -and $_.Url -notin $excludedUrls }
# Sites Connected to Teams Channel (Private or Shared)
$siteCollection = Get-PnPTenantSite | Where-Object { $_.IsTeamsChannelConnected -and $_.Url -notin $excludedUrls }
In this example, I’ll run the command to get all sites, including OneDrive. My tenant has 29 sites, including personal OneDrive sites.
You can now display the SharePoint storage report by selecting the relevant properties. You can export to CSV, display on the screen, or in a grid. In this instance, let’s display the results in a grid.
$siteCollection | Select-Object Title,Template,Url,StorageQuota,StorageUsageCurrent,Owner,GroupId,IsTeamsConnected,IsTeamsChannelConnected | Out-GridView
As you can see, the output is “usable” but not pretty. What are the issues in this current report?
- The “Template” is not readily recognizable. It shows the template ID instead of the template name (e.g., GROUP#0 = Team Site)
- The “StorageQuota” and “StorageCurrentusage” unit of measurement is not clear. Is it in bytes, MB, GB?
- The “Owner” value for Microsoft 365 Group-Connected sites is missing. You can only recognize that it has an associated group through its “GroupId” property.
We’ll need to do more result manipulation in PowerShell to produce a more sensible SharePoint storage usage report. But don’t worry. We’ve already provided the script for you.
This script provides the base in the sense that it generates the SharePoint Storage Usage Report as an object. It is up to you to format that objects into your final output, such as a CSV or HTML file.
Save or Download the Script
You can copy the code below and save it to your computer as Get-SPOSiteStorageUsage.ps1. For your convenience, you can also download the script from this GitHub repository.
[CmdletBinding(DefaultParameterSetName = 'PSetAll')]
param (
[Parameter(Mandatory, ParameterSetName = 'PSetSpecified')]
[String[]]
$URL,
[Parameter(Mandatory, ParameterSetName = 'PSetAll')]
[ValidateSet(
'All Sites',
'SharePoint Sites',
'Microsoft 365 Group Sites',
'Sites Connected to Teams',
'Sites Connected to Teams Channel',
'Sites Without a Group',
'OneDrive Sites'
)]
[string]
$View,
[Parameter(ParameterSetName = 'PSetAll')]
[string[]]
$Exclude
)
try {
$pnpTenantInstance = Get-PnPTenantInstance -ErrorAction Stop
$excludedUrls = [System.Collections.ArrayList]@(
"$($pnpTenantInstance.RootSiteUrl)"
"$($pnpTenantInstance.RootSiteUrl)sites/appcatalog"
"$($pnpTenantInstance.RootSiteUrl)portals/hub",
"$($pnpTenantInstance.RootSiteUrl)search",
"$($pnpTenantInstance.MySiteHostUrl)",
"$($pnpTenantInstance.TenantAdminUrl)"
)
if ($Exclude) {
$excludedUrls.AddRange($Exclude)
}
}
catch {
$_.Exception.Message
return $null
}
# If URL is specified
if ($PSCmdlet.ParameterSetName -eq 'PSetSpecified') {
$siteCollection = [System.Collections.ArrayList]@()
$URL | ForEach-Object {
try {
$null = $siteCollection.Add($(Get-PnPTenantSite -Url $_ -Detailed -ErrorAction Stop))
}
catch {
$_.Exception.Message | Out-Default
}
}
}
# If View is specified
if ($PSCmdlet.ParameterSetName -eq 'PSetAll') {
#Region Build site template lookup table
## Get all available site templates
$ClientContext = Get-PnPContext
$Web = Get-PnPWeb
## Get All Web Templates
$WebTemplateCollection = $Web.GetAvailableWebTemplates(1033, 0)
$ClientContext.Load($WebTemplateCollection)
$ClientContext.ExecuteQuery()
## Create a lookup dictionary
$webTemplateTable = [ordered]@{}
$WebTemplateCollection | Sort-Object Name | ForEach-Object {
$webTemplateTable.Add($_.Name, $_.Title)
}
#EndRegion
switch ($View) {
'All Sites' { $siteCollection = Get-PnPTenantSite -IncludeOneDriveSites | Where-Object { $_.Url -notin $excludedUrls } }
'SharePoint Sites' { $siteCollection = Get-PnPTenantSite | Where-Object { $_.Url -notin $excludedUrls } }
'OneDrive Sites' { $siteCollection = Get-PnPTenantSite -IncludeOneDriveSites -Filter "Url -like '-my.sharepoint.com/personal/'" | Where-Object { $_.Url -notin $excludedUrls } }
'Microsoft 365 Group Sites' { $siteCollection = Get-PnPTenantSite -GroupIdDefined:$true | Where-Object { $_.GroupId -ne '00000000-0000-0000-0000-000000000000' -and $_.Url -notin $excludedUrls } }
'Sites Without a Group' { $siteCollection = Get-PnPTenantSite -GroupIdDefined:$false | Where-Object { $_.Url -notin $excludedUrls } }
'Sites Connected to Teams' { $siteCollection = Get-PnPTenantSite -GroupIdDefined:$true | Where-Object { $_.IsTeamsConnected -eq $true -and $_.Url -notin $excludedUrls } }
'Sites Connected to Teams Channel' { $siteCollection = Get-PnPTenantSite | Where-Object { $_.IsTeamsChannelConnected -and $_.Url -notin $excludedUrls } }
Default {}
}
}
"Found $($siteCollection.Count) site(s) ..." | Out-Default
if ($siteCollection.Count -lt 1) {
return $null
}
$spoSiteStorageUsageResult = [System.Collections.ArrayList]@()
foreach ($spoSite in ($siteCollection | Sort-Object Url)) {
"Processing $($spoSite.Url) ..." | Out-Default
$siteAttributes = [System.Collections.ArrayList]@()
if ($spoSite.GroupId -ne '00000000-0000-0000-0000-000000000000') {
$null = $siteAttributes.Add('Microsoft 365 Group')
}
if ($spoSite.IsTeamsConnected) {
$null = $siteAttributes.Add('Microsoft Teams')
}
if ($spoSite.TeamsChannelType -eq 'PrivateChannel') {
$null = $siteAttributes.Add('Private Channel')
}
if ($spoSite.TeamsChannelType -eq 'SharedChannel') {
$null = $siteAttributes.Add('Shared Channel')
}
$null = $spoSiteStorageUsageResult.Add(
$([PSCustomObject]@{
'SiteName' = $spoSite.Title
'Url' = $spoSite.Url
'StorageQuota(GB)' = $(($spoSite.StorageQuota * 1MB) / 1GB)
'StorageUsage(GB)' = $([System.Math]::Round((($spoSite.StorageUsageCurrent * 1MB) / 1GB), 2))
'StorageUsage(%)' = $(
if ($spoSite.StorageUsageCurrent -gt 0) {
$([System.Math]::Round((($spoSite.StorageUsageCurrent / $spoSite.StorageQuota) * 100), 2))
}
else {
0
}
)
'Owner' = $(
if ($spoSite.Template -like "GROUP*") {
$group = Get-PnPMicrosoft365Group -Identity $spoSite.GroupId
if ($group.Mail) {
"$($group.DisplayName) <$($group.Mail)>"
}
else {
"$($group.DisplayName) <No Email>"
}
}
else {
if ($spoSite.Owner) {
if ($user = Get-PnPAzureADUser -Identity $spoSite.Owner) {
if ($user.Mail) {
"$($user.DisplayName) <$($user.Mail)>"
}
else {
"$($user.DisplayName) <No Email>"
}
}
}
}
)
'Template' = $(
if ($spoSite.Url -like "*-my.sharepoint.com/personal/*") {
'OneDrive'
}
else {
$webTemplateTable[$($spoSite.Template)]
}
)
SiteAttributes = $(
if ($siteAttributes.Count -gt 0) {
$siteAttributes -join ","
}
)
}
)
)
}
return $spoSiteStorageUsageResult
Open a PowerShell session and change the working directory to where you saved the script.
Push-Location <path to script>
Next, connect to the PnP PowerShell session.
Connect-PnPOnline -Url https://<tenant>.sharepoint.com
Depending on which type of sites you want to report, choose the appropriate command below and run it in PowerShell. The result will be stored in the $spoSiteStorageUsage variable.
# Get All Sites (including OneDrive) $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'All Sites' # Get SPO Sites Only $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'SharePoint Sites' # Get OneDrive Sites Only $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'OneDrive Sites' # Get Sites connected to Microsoft 365 Group $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'Microsoft 365 Group Sites' # Get Sites connected to Microsoft Teams $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'Sites Connected to Teams' # Get Sites connected to Microsoft Teams Channel (Private or Shared) $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'Sites Connected to Teams Channel' # Get Sites without a Microsoft 365 Group $spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'Sites Without a Group'
For example, I’ll run the SPO sites report only.
Interpreting the Result
You can now view the report on the screen or export it to a file.
$spoSiteStorageUsage $spoSiteStorageUsage | Out-GridView $spoSiteStorageUsage | Export-Csv -Path .\spoSiteStorageUsage.csv -NoTypeInformation $spoSiteStorageUsage | Export-Clixml -Path .\spoSiteStorageUsage.xml $spoSiteStorageUsage | ConvertTo-Json | Out-File -Path .\spoSiteStorageUsage.json $spoSiteStorageUsage | ConvertTo-Html | Out-File -Path .\spoSiteStorageUsage.html $spoSiteStorageUsage | ConvertTo-Yaml | Out-File -Path .\spoSiteStorageUsage.Yaml
The script output has the following properties:
- SiteName — The SharePoint or OneDrive site’s title.
- Url – The SharePoint or OneDrive site’s URL.
- StorageQuota(GB) — The SharePoint or OneDrive site’s assigned quota in GB.
- StorageUsage(GB) — The SharePoint or OneDrive site’s storage usage in GB.
- StorageUsage(%) — The SharePoint or OneDrive site’s storage usage in percent.
- Owner — The SharePoint or OneDrive site owner’s display name and email address (if present).
- Template — The site’s web template (i.e., OneDrive, Communication, Team Site, etc.)
- SiteAttributes — This property indicates if the site is connected to a Microsoft 365 365, Microsoft Teams, or Teams Channel (Private and Shared).
You can easily apply a CSS template to format your HTML report like so:
$spoSiteStorageUsage | ConvertTo-Html ` -CssUri .\style1.css | Out-File .\spoSiteStorageUsage.html
The sample CSS file is in the GitHub repository.
Conclusion
Generating a SharePoint Storage Usage Report using PowerShell provides invaluable insights into your organization’s content usage and storage patterns. In this blog post, we delved into the step-by-step process of creating a comprehensive storage report, enabling administrators to make data-driven decisions and optimize their SharePoint environment effectively.
PowerShell proves to be an indispensable tool in managing SharePoint Online environments, and the storage report generation process demonstrated here is just one example of its capabilities. As you continue to explore PowerShell’s features, you will discover various ways to streamline administrative tasks, enhance efficiency, and empower your organization’s collaboration platform.
By adopting this practice, you equip your organization with the necessary insights to optimize storage usage, ensure compliance, and deliver a seamless user experience, all while staying one step ahead in managing your SharePoint Online ecosystem effectively.
So, get started with the PowerShell script provided in this blog post and unlock the full potential of your SharePoint Online environment today!
