Get-TenantStorage

This PnP PowerShell script connects to the SharePoint admin centre, retrieves storage data for every site collection in the tenant, and exports a dated CSV report. Each site is assessed for storage usage percentage and days since last activity, with a Flag column identifying sites that are approaching their quota or have been inactive for more than six months.

Purpose

Tenant-wide storage reporting is the starting point for any storage governance exercise. This script gives you an actionable snapshot by:

• Querying all SharePoint site collections (OneDrive excluded by default)
• Calculating storage used as a percentage of each site's quota
• Calculating days since last content modification for each site
• Flagging sites over 80% storage usage as High Usage
• Flagging sites with no activity in over 180 days as Inactive
• Exporting a dated CSV so reports can be compared over time

Prerequisites

• PnP PowerShell module installed
• SharePoint Administrator or Global Administrator permissions
• A registered Entra ID app with SharePoint admin-scoped permissions and a Client ID

Parameters to update

• Url — your SharePoint admin centre URL in the format https://tenantName-admin.sharepoint.com
• ClientId — your registered Entra ID app Client ID
• Flag thresholds — adjust 80 (high usage %) and 180 (inactive days) to match your governance policy

Output

A CSV file named StorageReport_YYYYMMDD.csv exported to the directory from which the script is run. Columns: Url, StorageMB, QuotaMB, PercentUsed, DaysSinceActivity, Owner, Flag.

$clientId = ""
Connect-PnPOnline -Url "https://tenantName-admin.sharepoint.com" -ClientId $clientId -Interactive

$sites = Get-PnPTenantSite -IncludeOneDriveSites:$false |
    Select-Object Url, StorageUsageCurrent, StorageQuota, LastContentModifiedDate, Owner

$report = $sites | ForEach-Object {
    $percentUsed = if ($_.StorageQuota -gt 0) {
        [math]::Round(($_.StorageUsageCurrent / $_.StorageQuota) * 100, 1)
    } else { 0 }

    $daysSinceActivity = if ($_.LastContentModifiedDate) {
        (New-TimeSpan -Start $_.LastContentModifiedDate -End (Get-Date)).Days
    } else { 999 }

    [PSCustomObject]@{
        Url               = $_.Url
        StorageMB         = $_.StorageUsageCurrent
        QuotaMB           = $_.StorageQuota
        PercentUsed       = $percentUsed
        DaysSinceActivity = $daysSinceActivity
        Owner             = $_.Owner
        Flag              = if ($percentUsed -gt 80) { "High Usage" }
                            elseif ($daysSinceActivity -gt 180) { "Inactive" }
                            else { "" }
    }
}

$report | Sort-Object PercentUsed -Descending |
    Export-Csv -Path "StorageReport_$(Get-Date -Format 'yyyyMMdd').csv" -NoTypeInformation

Write-Host "Report saved. Sites flagged: $(($report | Where-Object { $_.Flag -ne '' }).Count)"