Report SharePoint Sites with File Counts and Storage Usage

Generate the report from the SharePoint admin center

The GUI path gives you most of the data without scripting, though item counts are aggregated per library rather than per site.

1. Sign in to the Microsoft 365 Admin Center as a Global Administrator or SharePoint Administrator.
2. In the left nav, expand Admin centers → SharePoint to open the SharePoint admin center.
3. Go to Sites → Active sites.
4. Click the Storage used (GB) column header to sort descending — the largest sites float to the top.
5. To add the file-count metric, click Customize columns in the toolbar and enable the Files column (if available in your tenant). Some tenants expose this via the Files viewed or edited usage column instead.
6. Click Export in the toolbar to download a CSV of all sites with URL, storage used, storage limit, last activity date, and owner.
7. For more granular metrics, go to Reports → Usage → SharePoint activity and Reports → Usage → SharePoint site usage. The site usage report includes file counts, active file counts, and storage used per site.
8. Export both reports and join them on Site URL in Excel or Power BI to build the combined view.

Tip: The Active sites list caches data and may be up to 24 hours stale. Usage reports refresh every 1–2 days.

Build the report with Microsoft Graph

Three Graph calls per site: list all sites, list drives per site, and inspect the drive root. Aggregate the results client-side.

Step 1 — Enumerate all sites

The reliable way to list every site is a wildcard search. The getAllSites endpoint frequently returns 403 even with full permissions.

GET https://graph.microsoft.com/v1.0/sites?search=*&$select=id,displayName,name,webUrl

Response:

{
  "value": [
    {
      "id": "contoso.sharepoint.com,<siteGuid>,<webGuid>",
      "displayName": "Marketing Internal",
      "webUrl": "https://contoso.sharepoint.com/sites/MarketingInternal"
    }
  ]
}

Step 2 — Get the default drive for each site

GET https://graph.microsoft.com/v1.0/sites/{site-id}/drives?$select=id,name,quota

The quota.used property returns storage consumption in bytes. Not all drives populate quota data — fall back to the root folder size if quota is empty.

Step 3 — Get root item count and fallback size

GET https://graph.microsoft.com/v1.0/drives/{drive-id}/root?$expand=children($select=id,name,file,folder)

Key fields in the response:

• size — total bytes stored in the library (root + all descendants).
• folder.childCount — number of items at the root level only.
• children[] — each root item with file or folder facet.

Step 4 — Convert and aggregate

Convert bytes to MB: MB = bytes / 1048576. Sort by size descending and sum for a tenant total.

Handling throttling

If you receive HTTP 429, read the Retry-After header (seconds) and pause before retrying. Batch drive-root calls with $batch (max 20 requests per batch) to reduce round trips in large tenants.

Automate with PowerShell and Microsoft Graph

The script below uses the Microsoft.Graph PowerShell SDK to produce the same report programmatically. It handles pagination, quota fallback, and exports to CSV.

Prerequisites

1. Install the Graph SDK: Install-Module Microsoft.Graph -Scope CurrentUser
2. You need SharePoint Administrator or Global Reader role.
3. Consent to Sites.Read.All and Files.Read.All on first run.

How the script works

1. Connect-MgGraph with the required scopes.
2. Get-MgSite -Search '*' enumerates every site (auto-paginates with -All).
3. For each site, Get-MgSiteDrive returns its document libraries.
4. For the default drive, the script inspects Quota.Used first, then falls back to the root Size property.
5. Get-MgDriveRoot -ExpandProperty Children returns the child count.
6. Results are sorted by size and written to SharePointSitesReport.csv.

Running it

.\Get-SharePointSiteUsageReport.ps1 -OutputPath "C:\Reports\SPSites.csv"

The script prints a summary table to the console and writes the full CSV at the path you specify.