Get-SPOSiteUsageReport

PowerShell Script

<#
.SYNOPSIS
    Pulls the Microsoft Graph SharePoint site usage report, tenant-wide,
    showing genuine last-activity data, the "was this site actually
    looked at" half of the picture that Get-SPOSiteActivityAudit.ps1
    can't answer on its own, since that script only tracks content
    modification.

.DESCRIPTION
    Get-SPOSiteActivityAudit.ps1 answers "when did content last change,"
    using LastItemUserModifiedDate. That's a genuinely different question
    from "when did someone last visit this site," and a site can score
    badly on one axis while being perfectly healthy on the other, most
    commonly a reference or archive library nobody edits but people still
    regularly read.

    This script pulls Microsoft Graph's getSharePointSiteUsageDetail
    report. Its key field, Last Activity Date, reflects the genuine last
    recorded activity (reads and writes combined) for each site, this is
    the same figure the SharePoint Admin Center's own Activity tab shows,
    and it is NOT limited to a short rolling window, it can and does show
    dates going back years if that's genuinely the last time anyone did
    anything on that site.

    The -Period parameter (D7/D30/D90/D180) only affects the two
    aggregate counts also returned by this report, Page View Count and
    Visited Page Count, which are tallies of activity within that chosen
    window only. Last Activity Date is unaffected by -Period and is the
    field to trust for a genuine "how long has this site truly been
    dormant" answer.

    KNOWN MICROSOFT BUG (advisory SP676147, unresolved since 2023): the
    report's own "Site URL" column comes back empty - a service-side
    change made to fix an unrelated issue stripped URLs from this report
    and it was never fully fixed. Site Id IS populated correctly though,
    so this version resolves each site's real URL via a separate Graph
    call keyed on Site Id, rather than trusting the report's own URL
    column. Adds one Graph call per site - trivial for normal tenant
    sizes, would want batching at several thousand+ sites.

.NOTES
    Requires: PnP.PowerShell 3.x, and the connecting account needs the
    Reports.Read.All Graph permission (either via delegated admin consent
    on first run, or granted to the app registration in advance).
    Author:   Cameron Griffiths
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [string]$AdminUrl = "https://tenantName-admin.sharepoint.com",

    [Parameter(Mandatory = $false)]
    [string]$ClientId = "",

    [Parameter(Mandatory = $false)]
    [ValidateSet("D7", "D30", "D90", "D180")]
    [string]$Period = "D180",

    [Parameter(Mandatory = $false)]
    [string]$OutputPath = ".\SiteUsageReport_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
)

Write-Host "Connecting to $AdminUrl ..." -ForegroundColor Cyan
Connect-PnPOnline -Url $AdminUrl -Interactive -ClientId $ClientId

Write-Host "Requesting SharePoint site usage report from Microsoft Graph ..." -ForegroundColor Cyan
Write-Host "Last Activity Date reflects genuine historical activity, not limited to the $Period window." -ForegroundColor Cyan
Write-Host "Page View Count / Visited Page Count ARE limited to the $Period window (last $($Period.Substring(1)) days only)." -ForegroundColor Yellow

try {
    # The Graph reporting endpoint returns raw CSV content, not JSON.
    $rawCsv = Invoke-PnPGraphMethod -Url "reports/getSharePointSiteUsageDetail(period='$Period')" -Method Get -Raw -ErrorAction Stop
}
catch {
    Write-Host "Failed to retrieve the usage report: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "This usually means the connecting account is missing the Reports.Read.All permission, or admin consent hasn't been granted for it yet." -ForegroundColor Yellow
    exit 1
}

# Parse the CSV response into objects
$usageData = $rawCsv | ConvertFrom-Csv

Write-Host "Retrieved usage data for $($usageData.Count) sites." -ForegroundColor Cyan
Write-Host "Resolving real Site URLs via Graph (works around the report's blank Site URL bug)..." -ForegroundColor Cyan

$today = Get-Date
$counter = 0

$report = foreach ($site in $usageData) {
    $counter++
    Write-Progress -Activity "Resolving site URLs" -Status $site.'Site Id' -PercentComplete (($counter / $usageData.Count) * 100)

    $lastActivity = $null
    if ($site.'Last Activity Date' -and $site.'Last Activity Date' -ne '') {
        $lastActivity = [datetime]$site.'Last Activity Date'
    }

    $daysSinceActivity = if ($lastActivity) {
        [math]::Round(($today - $lastActivity).TotalDays)
    } else {
        $null
    }

    # Tiered indicator based on the genuine, unbounded Last Activity Date,
    # not on the period-limited view counts.
    $activityIndicator = if ($null -eq $daysSinceActivity) {
        "No activity ever recorded"
    } elseif ($daysSinceActivity -ge 730) {
        "Inactive 2+ years"
    } elseif ($daysSinceActivity -ge 365) {
        "Inactive 1-2 years"
    } else {
        "Active"
    }

    # Resolve the real URL via Graph, keyed on Site Id - the report's own
    # Site URL column is unreliable (see .DESCRIPTION), Site Id is not.
    $resolvedUrl = "UNKNOWN - could not resolve"
    if ($site.'Site Id') {
        try {
            $graphSite = Invoke-PnPGraphMethod -Url "sites/$($site.'Site Id')" -Method Get -ErrorAction Stop
            if ($graphSite.webUrl) { $resolvedUrl = $graphSite.webUrl }
        }
        catch {
            $resolvedUrl = "UNKNOWN - Graph lookup failed: $($_.Exception.Message)"
        }
    }
    else {
        $resolvedUrl = "UNKNOWN - no Site Id in report row"
    }

    [PSCustomObject]@{
        SiteUrl               = $resolvedUrl
        SiteId                 = $site.'Site Id'
        OwnerDisplayName      = $site.'Owner Display Name'
        LastActivityDate      = $lastActivity
        DaysSinceActivity     = $daysSinceActivity
        ActivityIndicator     = $activityIndicator
        "PageViewCount(${Period}window)"    = $site.'Page View Count'
        "VisitedPageCount(${Period}window)" = $site.'Visited Page Count'
        FileCount             = $site.'File Count'
        ActiveFileCount       = $site.'Active File Count'
        StorageUsedBytes      = $site.'Storage Used (Byte)'
    }
}

Write-Progress -Activity "Resolving site URLs" -Completed

$report | Sort-Object DaysSinceActivity -Descending | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8

Write-Host "`nReport complete. Written to $OutputPath" -ForegroundColor Green

$summary = $report | Group-Object ActivityIndicator | Select-Object Name, Count | Sort-Object Name
Write-Host "`nActivity summary (based on Last Activity Date, genuine history, not period-limited):" -ForegroundColor Yellow
$summary | Format-Table -AutoSize

$unresolvedCount = ($report | Where-Object { $_.SiteUrl -like "UNKNOWN*" }).Count
if ($unresolvedCount -gt 0) {
    Write-Host "$unresolvedCount site(s) could not be resolved to a URL - worth checking manually, these could be deleted sites still lingering in the usage report." -ForegroundColor Yellow
}

Write-Host "Cross-reference against Get-SPOSiteActivityAudit.ps1's output (by SiteUrl) for the fuller picture. Sites flagged as long-term inactive there but showing a recent Last Activity Date here are likely reference material still being read, not edited, worth leaving alone rather than archiving." -ForegroundColor Cyan