Get-SPOSiteActivityAudit

PowerShell Script

# ============================================================
# Get-SPOSiteActivityAudit.ps1
# Tenant-wide SharePoint Online site activity audit. Reports
# genuine last user activity, owners, and storage used, sorted
# longest-idle first, to support outreach about sites that may
# be safe to archive or retire.
# ============================================================

<#
.SYNOPSIS
    Tenant-wide SharePoint Online site activity audit. Reports Name, URL,
    last user activity, site owners, storage used, and a tiered
    inactivity indicator, sorted longest-idle first, to support outreach
    to site owners about sites that may be safe to archive or retire.

.DESCRIPTION
    Uses LastItemUserModifiedDate rather than LastContentModifiedDate or
    LastItemModifiedDate, since those two are known to be bumped by system
    activity (audit logging, Content Type Hub subscriptions, usage
    recalculation, permission changes, feature activation)

    Worth knowing: this still cannot distinguish a real human editing
    content from an unattended Power Automate flow running under a named
    user's delegated connection, both register identically as a user
    modification. Treat "recently modified, but very low storage growth
    and a single owner who's long gone" as a hint worth double-checking,
    not an automatic sign of genuine activity.

.NOTES
    Requires: PnP.PowerShell 3.x
    Author:   Cameron Griffiths

.EXAMPLE
    .\Get-SPOSiteActivityAudit.ps1 -AdminUrl "https://contoso-admin.sharepoint.com" -OutputPath ".\SiteActivityAudit.csv"
#>

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

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

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

    [Parameter(Mandatory = $false)]
    [int]$ThrottleDelayMs = 300
)

# --- Known system-managed site templates to skip ---
$systemTemplates = @(
    "APPCATALOG#0",
    "SRCHCEN#0",
    "SPSMSITEHOST#0",
    "SPSTOC#0",
    "TENANTADMIN#0"
)

$today = Get-Date

# --- Connect to the SPO admin site ---
Write-Host "Connecting to $AdminUrl ..." -ForegroundColor Cyan
Connect-PnPOnline -Url $AdminUrl -Interactive -ClientId $ClientId

# --- Get all sites (excluding OneDrive personal sites) ---
Write-Host "Retrieving tenant site list ..." -ForegroundColor Cyan
$sites = Get-PnPTenantSite -Detailed | Where-Object {
    $_.Template -notlike "SPSPERS*" -and $_.Url -notlike "*-my.sharepoint.com*"
}

Write-Host "Found $($sites.Count) sites to audit." -ForegroundColor Cyan

$report = New-Object System.Collections.Generic.List[Object]
$counter = 0

foreach ($site in $sites) {
    $counter++
    Write-Progress -Activity "Auditing site activity" -Status $site.Url -PercentComplete (($counter / $sites.Count) * 100)

    if ($systemTemplates -contains $site.Template) {
        continue
    }

    try {
        Connect-PnPOnline -Url $site.Url -Interactive -ClientId $ClientId -WarningAction SilentlyContinue

        # --- Genuine last user activity ---
        $web = Get-PnPWeb -Includes LastItemUserModifiedDate
        $lastUserActivity = $web.LastItemUserModifiedDate
        $daysSinceActivity = [math]::Round(($today - $lastUserActivity).TotalDays)

        # --- Owners group membership, not just the primary Owner field ---
        $ownerGroup = Get-PnPGroup -AssociatedOwnerGroup -ErrorAction SilentlyContinue
        $owners = @()
        if ($ownerGroup) {
            $owners = Get-PnPGroupMember -Group $ownerGroup -ErrorAction SilentlyContinue |
                      Where-Object { $_.PrincipalType -eq "User" -and $_.LoginName -notlike "*spo-grid-all-users*" }
        }
        $ownerNames = if ($owners.Count -gt 0) { ($owners.Title -join "; ") } else { "NO OWNERS" }

        # --- Tiered inactivity indicator ---
        $indicator = "Active"
        if ($daysSinceActivity -ge 730) {
            $indicator = "Inactive 2+ years"
        }
        elseif ($daysSinceActivity -ge 365) {
            $indicator = "Inactive 1-2 years"
        }

        $report.Add([PSCustomObject]@{
            Name                = $site.Title
            Url                 = $site.Url
            LastUserActivity    = $lastUserActivity
            DaysSinceActivity   = $daysSinceActivity
            Owners              = $ownerNames
            OwnerCount          = $owners.Count
            StorageUsedMB       = $site.StorageUsageCurrent
            InactivityIndicator = $indicator
        })
    }
    catch {
        $report.Add([PSCustomObject]@{
            Name                = $site.Title
            Url                 = $site.Url
            LastUserActivity    = $null
            DaysSinceActivity   = $null
            Owners              = ""
            OwnerCount          = ""
            StorageUsedMB       = ""
            InactivityIndicator = "UNKNOWN - Could not audit: $($_.Exception.Message)"
        })
    }

    Start-Sleep -Milliseconds $ThrottleDelayMs
}

Write-Progress -Activity "Auditing site activity" -Completed

# --- Output, longest-idle first ---
$report | Sort-Object DaysSinceActivity -Descending | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8

Write-Host "`nAudit complete. Report written to $OutputPath" -ForegroundColor Green

$summary = $report | Group-Object InactivityIndicator | Select-Object Name, Count | Sort-Object Name
Write-Host "`nInactivity summary:" -ForegroundColor Yellow
$summary | Format-Table -AutoSize

$noOwners = ($report | Where-Object { $_.Owners -eq "NO OWNERS" }).Count
if ($noOwners -gt 0) {
    Write-Host "$noOwners site(s) have no owners at all, worth cross-referencing against the orphaned-site-risk audit before attempting outreach on those." -ForegroundColor Yellow
}