Get-SPOAlertInventory

PowerShell Script

<#
.SYNOPSIS
    Tenant-wide inventory of classic SharePoint Alerts, ahead of Microsoft's
    July 2026 retirement of the feature. Writes results directly into the
    SPOAlertInventory SharePoint list (see New-SPOAlertInventoryList.ps1),
    with one item per site, even when a site has zero alerts, so the list
    doubles as proof of scan coverage.

.DESCRIPTION
    Get-PnPAlert -AllUsers requires the caller to be a site owner (or Full
    Control) on the target site to see other users' alerts. Being a tenant
    SharePoint Administrator does not grant this automatically, it is a
    deliberate Microsoft security boundary.
      1. Adding the specified admin account as a Site Collection Admin
         (not the visible Owners group) via Set-PnPTenantSite -Owners,
         which works from tenant admin rights alone.
      2. Connecting to the site and running Get-PnPAlert -AllUsers.
      3. Removing that Site Collection Admin flag again immediately after,
         regardless of whether the scan succeeded or failed.

    For every site scanned:
      - Zero alerts found  -> one list item written, ScanStatus "OK",
        AlertTitle left blank, so the item itself proves the site was
        checked and came back clean.
      - One or more alerts -> one list item per alert.
      - Scan failed        -> one list item, ScanStatus "ERROR", with the
        failure reason in Scan Status Detail.
      - Elevation could not be reverted -> an additional item, ScanStatus
        "CLEANUP_NEEDED", flagging manual follow-up.

    The elevate/scan/revert sequence for each site is wrapped in a
    try/finally block, so a failure partway through a site's scan cannot
    leave the admin account elevated there.

#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [string]$AdminUrl = "",

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

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

    [Parameter(Mandatory = $false)]
    [string]$ListName = "SPOAlertInventory", # --- or change if you use a different name ---

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

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

    [Parameter(Mandatory = $false)]
    [switch]$AlsoExportCsv,

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

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

$scanDate = Get-Date

# --- Connect to the list site (separate, persistent connection for writing results) ---
Write-Host "Connecting to list site $ListSiteUrl ..." -ForegroundColor Cyan
$listConnection = Connect-PnPOnline -Url $ListSiteUrl -Interactive -ClientId $ClientId -ReturnConnection

# --- Connect to the SPO admin site (kept open throughout, for elevate/revert calls) ---
Write-Host "Connecting to $AdminUrl ..." -ForegroundColor Cyan
$adminConnection = Connect-PnPOnline -Url $AdminUrl -Interactive -ClientId $ClientId -ReturnConnection

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

Write-Host "Found $($sites.Count) sites to scan." -ForegroundColor Cyan
Write-Host "Admin account used for temporary elevation: $AdminUpn" -ForegroundColor Cyan

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

# --- Helper: write one list item ---
function Write-InventoryItem {
    param(
        [Parameter(Mandatory)] [string]$SiteUrl,
        [string]$SiteTitle,
        [string]$AlertTitle,
        [string]$CreatedForLogin,
        [string]$AlertListUrl,
        [string]$EventType,
        [string]$AlertFilter,
        [string]$Frequency,
        [string]$DeliveryChannel,
        [Nullable[datetime]]$AlertTime,
        [Parameter(Mandatory)] [string]$ScanStatus,
        [string]$ScanStatusDetail
    )

    $values = @{
        "Title"            = if ($AlertTitle) { $AlertTitle } else { "(no alerts found)" }
        "SiteUrl"          = "$SiteUrl, $SiteTitle"
        "SiteTitle"        = $SiteTitle
        "CreatedForLogin"  = $CreatedForLogin
        "EventType"        = $EventType
        "DeliveryChannel"  = $DeliveryChannel
        "ScanDate"         = $scanDate.ToString("yyyy-MM-ddTHH:mm:ssK", [System.Globalization.CultureInfo]::InvariantCulture)
        "ScanStatus"       = $ScanStatus
        "ScanStatusDetail" = $ScanStatusDetail
        "Reviewed"         = $false
    }

    if ($AlertListUrl)              { $values["AlertListUrl"] = "$AlertListUrl, List" }
    if ($AlertFilter)                { $values["AlertFilter"] = $AlertFilter }
    if ($Frequency)                  { $values["Frequency"] = $Frequency }
    if ($AlertTime)                  { $values["AlertTime"] = $AlertTime.Value.ToString("yyyy-MM-ddTHH:mm:ssK", [System.Globalization.CultureInfo]::InvariantCulture) }
    if ($CreatedForLogin) {
        # Person field: pass the login name directly, PnP.PowerShell resolves it
        try { $values["CreatedFor"] = $CreatedForLogin } catch { }
    }

    Add-PnPListItem -Connection $listConnection -List $ListName -Values $values -ErrorAction Stop | Out-Null
}

foreach ($site in $sites) {
    $counter++
    Write-Progress -Activity "Scanning for SharePoint Alerts" -Status $site.Url -PercentComplete (($counter / $sites.Count) * 100)

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

    $elevated = $false
    $siteAlerts = $null
    $scanError = $null

    try {
        # --- Elevate: add as Site Collection Admin (not the Owners group) ---
        Set-PnPTenantSite -Connection $adminConnection -Url $site.Url -Owners $AdminUpn -ErrorAction Stop
        $elevated = $true

        # --- Connect to the site itself and scan ---
        $siteConnection = Connect-PnPOnline -Url $site.Url -Interactive -ClientId $ClientId -ReturnConnection -WarningAction SilentlyContinue
        $siteAlerts = Get-PnPAlert -AllUsers -Connection $siteConnection -ErrorAction Stop
    }
    catch {
        $scanError = $_.Exception.Message
    }
    finally {
        # --- Revert: always attempt this, even if the scan above failed ---
        if ($elevated) {
            try {
                Remove-PnPSiteCollectionAdmin -Connection $adminConnection -Owners $AdminUpn -ErrorAction Stop
            }
            catch {
                Write-Host "WARNING: Could not remove elevation on $($site.Url). Manual cleanup required for $AdminUpn." -ForegroundColor Red
                Write-InventoryItem -SiteUrl $site.Url -SiteTitle $site.Title -ScanStatus "CLEANUP_NEEDED" `
                    -ScanStatusDetail "Elevation could not be automatically removed: $($_.Exception.Message)"
            }
        }
    }

    # --- Write results for this site ---
    if ($scanError) {
        Write-InventoryItem -SiteUrl $site.Url -SiteTitle $site.Title -ScanStatus "ERROR" `
            -ScanStatusDetail "Could not scan: $scanError"

        $csvReport.Add([PSCustomObject]@{ SiteUrl = $site.Url; Status = "ERROR: $scanError" })
    }
    elseif ($siteAlerts.Count -eq 0) {
        # Zero alerts: still write one item, proving the site was scanned
        Write-InventoryItem -SiteUrl $site.Url -SiteTitle $site.Title -ScanStatus "OK" `
            -ScanStatusDetail "No alerts found on this site."

        $csvReport.Add([PSCustomObject]@{ SiteUrl = $site.Url; Status = "OK, no alerts" })
    }
    else {
        foreach ($alert in $siteAlerts) {
            $totalAlertsFound++
            Write-InventoryItem -SiteUrl $site.Url -SiteTitle $site.Title `
                -AlertTitle $alert.Title `
                -CreatedForLogin $alert.User.LoginName `
                -AlertListUrl $alert.ListUrl `
                -EventType ([string]$alert.EventType) `
                -AlertFilter ([string]$alert.Filter) `
                -Frequency ([string]$alert.AlertFrequency) `
                -DeliveryChannel ($alert.DeliveryChannels -join "; ") `
                -AlertTime $alert.AlertTime `
                -ScanStatus "OK"

            $csvReport.Add([PSCustomObject]@{
                SiteUrl         = $site.Url
                AlertTitle      = $alert.Title
                CreatedForLogin = $alert.User.LoginName
                Frequency       = $alert.AlertFrequency
                Status          = "OK"
            })
        }
    }

    Start-Sleep -Milliseconds $ThrottleDelayMs
}

Write-Progress -Activity "Scanning for SharePoint Alerts" -Completed

if ($AlsoExportCsv) {
    $csvReport | Sort-Object SiteUrl | Export-Csv -Path $CsvOutputPath -NoTypeInformation -Encoding UTF8
    Write-Host "`nCSV backup written to $CsvOutputPath" -ForegroundColor Green
}

Write-Host "`nScan complete. $totalAlertsFound alert(s) found across $($sites.Count) site(s) scanned." -ForegroundColor Green
Write-Host "Results written to the '$ListName' list at $ListSiteUrl" -ForegroundColor Green
Write-Host "Review any 'ERROR' or 'CLEANUP_NEEDED' items in the list before considering the sweep complete." -ForegroundColor Yellow