How to Generate a SharePoint Online Site Storage Report using PowerShell

4.8K

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.

Requirements

• A machine with Windows PowerShell 5.1 or PowerShell 7+.
• PnP.PowerShell version 2.x or later.
• SharePoint Administrator/Global Administrator permissions.
• An account that can authenticate using modern Microsoft Entra ID authentication.

How to Install PnP PowerShell

In order to install PnP PowerShell module, use the command:

Install-Module PnP.PowerShell `
-Scope CurrentUser `
-Force

Now check if the module is available by running the command:

Get-Module PnP.PowerShell -ListAvailable

Retrieve the SharePoint Site Collections

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. Keep in mind that you can connect to SharePoint Online using either interactive sign-in/Entra ID app registration.

Interactive sign-in:

Connect-PnPOnline `
-Url https://<tenant>.sharepoint.com `
-Interactive

App registration authentication:

Connect-PnPOnline `
-Url https://<tenant>.sharepoint.com `
-ClientId <ApplicationID> `
-Tenant <tenant>.onmicrosoft.com `
-CertificateThumbprint <Thumbprint>

App-based authentication is commonly used for automation cases (such as scheduled tasks, Azure Automation runbooks, and unattended scripts).

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” units of measurement are 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.

Using the SharePoint Storage Usage Report Script

You can copy the code below and save it to your computer as Get-SPO

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()]
[switch]$IncludeOwnerInfo,

[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 
}

#Region Build site template lookup table

$ClientContext = Get-PnPContext
$Web = Get-PnPWeb

$WebTemplateCollection = $Web.GetAvailableWebTemplates(1033, 0)
$ClientContext.Load($WebTemplateCollection)
$ClientContext.ExecuteQuery()

$webTemplateTable = [ordered]@{}

$WebTemplateCollection | Sort-Object Name | ForEach-Object {
$webTemplateTable.Add($_.Name, $_.Title)
}

#EndRegion

# 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') {

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 ($IncludeOwnerInfo) {

if ($spoSite.Template -like "GROUP*") {

try {
$group = Get-PnPMicrosoft365Group -Identity $spoSite.GroupId -ErrorAction Stop

if ($group.Mail) {
"$($group.DisplayName) <$($group.Mail)>"
}
else {
"$($group.DisplayName) <No Email>"
}
}
catch {
$null
}
}

else {

if ($spoSite.Owner) {

try {
$user = Get-PnPAzureADUser -Identity $spoSite.Owner -ErrorAction Stop

if ($user) {

if ($user.Mail) {
"$($user.DisplayName) <$($user.Mail)>"
}
else {
"$($user.DisplayName) <No Email>"
}
}
}
catch {
$null
}

}
}
}
else {
$null
}
)

'Template' = $( 
if ($spoSite.Url -like "*-my.sharepoint.com/personal/*") { 
'OneDrive' 
} 
else { 
$webTemplateTable[$($spoSite.Template)] 
} 
) 
SiteAttributes = $( 
if ($siteAttributes.Count -gt 0) { 
$siteAttributes -join "," 
} 
) 
} 
) 
) 
} 
return $spoSiteStorageUsageResult

Note that when IncludeOwnerInfo is not specified, the Owner column will remain empty in order to improve performance. Also, keep in mind that in very large tenants, owner lookup may take several minutes because Microsoft Graph is queried separately for each site.

Run the SharePoint Storage Usage Report Script

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.

# Interactive sign-in
Connect-PnPOnline `
-Url https://<tenant>.sharepoint.com `
-Interactive

# App registration authentication
Connect-PnPOnline `
-Url https://<tenant>.sharepoint.com `
-ClientId <ApplicationID> `
-Tenant <tenant>.onmicrosoft.com `
-CertificateThumbprint <Thumbprint>

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.

Including Site Owner Information

By default, the script does not retrieve detailed owner info in order to improve performance and reduce Microsoft Graph API requests. You can run the script without owner lookups (we recommend it for large tenants):

$spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 -View 'All Sites'

To include detailed owner information, run the following command:

$spoSiteStorageUsage = .\Get-SPOSiteStorageUsage.ps1 `
-View 'All Sites' `
-IncludeOwnerInfo

Keep in mind that when you are using the IncludeOwnerInfo parameter, the connected account/app must have permission to read Microsoft Entra ID users and Microsoft 365 groups. Otherwise owner info cannot be retrieved.

Note. Retrieving owner info requires additional Microsoft Graph queries and may significantly increase execution time in tenants with hundreds/thousands of SharePoint sites.

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

Note. The ConvertTo-Yaml cmdlet is not included in Windows PowerShell, PowerShell 7, or the PnP.PowerShell module. In order to export the report to YAML format, install the powershell-yaml module first:

Install-Module powershell-yaml -Scope CurrentUser

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 site owner display name and email address when IncludeOwnerInfo is enabled.
• 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, 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!