Sync-SPOServiceDeskSiteList

PowerShell Script

<#
.SYNOPSIS
    Service Desk Site Inventory - syncs non-Teams-connected, non-Communication
    SharePoint sites (with usage and last-activity data) into a SharePoint list
    the service desk has access to.

.DESCRIPTION
    Manual/interactive version, run from a workstation - validated end-to-end
    against SiteName. Pulls the tenant's full site list, filters out
    Teams-connected and Communication sites, gets each remaining site with
    storage usage and genuine last-activity data, then upserts the result into
    a SharePoint list.

    List sync behavior:
      - Sites already in the list are updated in place. The Comments column,
        which the service desk fills in manually, is never touched.
      - Sites no longer in scope (deleted, or now Teams-connected) are flagged
        Status = "Removed" rather than being deleted from the list, so there's
        an audit trail and no accidental loss of service desk annotations.
      - New sites are added fresh.

    Requires two modules side by side:
      - PnP.PowerShell 3.x
            -> everything: site enumeration, the Graph usage report via
               Invoke-PnPGraphMethod (reuses PnP's own connection/token, so no
               separate Microsoft.Graph module and no MSAL clash - same approach
               as Get-SPOSiteUsageReport.ps1), and all list read/write operations.
      - Microsoft.Online.SharePoint.PowerShell (classic SPO Management Shell),
        loaded via -UseWindowsPowerShell compatibility mode
            -> ONLY used for Get-SPOSite's IsTeamsConnected / IsTeamsChannelConnected
               properties, which Get-PnPTenantSite still doesn't expose (open PnP
               issue #2636). Everything else stays on PnP.

    Last Activity Date comes from Microsoft Graph's getSharePointSiteUsageDetail
    report - reads + writes combined, genuine history, not limited to a rolling
    window (see Get-SPOSiteUsageReport.ps1 for the fuller writeup on this). Site
    URLs are resolved via a separate per-site Graph Site ID lookup, working
    around a known Microsoft bug where the report's own Site URL column is
    unreliable.

.NOTES
    Stage:  Validated manual/interactive version, run from a workstation.
    Target: Site (swap $AdminUrl and $ListSiteUrl before running against
            anything else)

#>

#region Config - EDIT THESE BEFORE RUNNING
$AdminUrl                  = "https://tenantName-admin.sharepoint.com"   # <-- confirm this is right for tenant
$ClientId                  = ""
$InactivityThresholdMonths = 12
$UsagePeriod                = "D180"   # only affects PageViewCount/VisitedPageCount, not LastActivityDate

$ListSiteUrl = "https://tenantName.sharepoint.com/sites/siteName"   # <-- confirm this matches Your LIST SITE AND NAME
$ListName    = "listName"
#endregion

# If this ever throws "module not found" on a different machine,
# the fix is a one-time manual install in real Windows PowerShell, not a
# scripted re-install.
try {
    Import-Module Microsoft.Online.SharePoint.PowerShell -UseWindowsPowerShell -ErrorAction Stop
}
catch {
    Write-Host "Could not load Microsoft.Online.SharePoint.PowerShell: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "Try: powershell.exe -NoProfile -Command `"Get-Module -ListAvailable Microsoft.Online.SharePoint.PowerShell`" to confirm it's still visible from this session." -ForegroundColor Yellow
    exit 1
}
#endregion

#region Connect
Write-Host "Connecting to SharePoint Online (PnP)..." -ForegroundColor Cyan
Connect-PnPOnline -Url $AdminUrl -Interactive -ClientId $ClientId

Write-Host "Connecting to SharePoint Online (classic - Teams-connected flag only)..." -ForegroundColor Cyan
Connect-SPOService -Url $AdminUrl
#endregion

$InactivityCutoff = (Get-Date).AddMonths(-$InactivityThresholdMonths)

Write-Host "`nPulling all sites from SPO (this can take a minute on large tenants)..." -ForegroundColor Cyan
$AllSites = Get-SPOSite -Limit All

Write-Host "Filtering out Teams-connected sites and Communication sites..." -ForegroundColor Cyan
$NonTeamsSites = $AllSites | Where-Object {
    -not $_.IsTeamsConnected -and -not $_.IsTeamsChannelConnected -and $_.Template -notlike "SITEPAGEPUBLISHING*"
}

Write-Host "$($NonTeamsSites.Count) non-Teams-connected sites found (of $($AllSites.Count) total)`n" -ForegroundColor Green

Write-Host "Pulling Graph site usage report (Last Activity Date, reads + writes)..." -ForegroundColor Cyan
try {
    $rawCsv = Invoke-PnPGraphMethod -Url "reports/getSharePointSiteUsageDetail(period='$UsagePeriod')" -Method Get -Raw -ErrorAction Stop
}
catch {
    Write-Host "Failed to retrieve the usage report: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "Usually means the connecting account is missing Reports.Read.All, or admin consent hasn't been granted yet." -ForegroundColor Yellow
    exit 1
}
$UsageData = $rawCsv | ConvertFrom-Csv
Write-Host "Retrieved usage data for $($UsageData.Count) sites from the Graph report." -ForegroundColor Cyan

# KNOWN MICROSOFT BUG (advisory SP676147, unresolved since 2023): the "Site URL"
# column in this report comes back empty even with anonymization off. Site Id IS
# populated correctly though, so we resolve each site's Graph Site ID separately
# and join on that instead of URL. Adds one Graph call per site - fine for a
# weekly batch of ~50 sites, would need batching for a much larger tenant.
$UsageLookupById = @{}
foreach ($row in $UsageData) {
    if ($row.'Site Id') { $UsageLookupById[$row.'Site Id'] = $row }
}

$Results = foreach ($Site in $NonTeamsSites) {

    $Uri      = [Uri]$Site.Url
    $HostName = $Uri.Host
    $SitePath = $Uri.AbsolutePath.TrimEnd('/')

    $SiteId = $null
    try {
        $GraphSite = Invoke-PnPGraphMethod -Url "sites/${HostName}:${SitePath}?`$select=id" -Method Get -ErrorAction Stop
        # Graph's composite id is "hostname,siteIdGuid,webIdGuid" - the usage
        # report's Site Id column is just the middle GUID.
        $SiteId = ($GraphSite.id -split ',')[1]
    }
    catch {
        Write-Warning "  Could not resolve Graph Site ID for $($Site.Url): $($_.Exception.Message)"
    }

    $UsageRow = if ($SiteId) { $UsageLookupById[$SiteId] } else { $null }

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

    $Status = if ($LastActivityDate -and $LastActivityDate -ge $InactivityCutoff) {
        "Active"
    } elseif ($LastActivityDate) {
        "Inactive"
    } else {
        "Unknown"   # Graph Site ID lookup failed, or no match in usage report - review manually
    }

    [PSCustomObject]@{
        Url          = $Site.Url
        Title        = $Site.Title
        UsageGB      = [math]::Round($Site.StorageUsageCurrent / 1024, 2)   # StorageUsageCurrent is in MB
        LastActivity = $LastActivityDate
        Owner        = $Site.Owner
        Comments     = ""     # manual field for service desk - stays blank here, preserved on future upsert
        Status       = $Status
    }
}

Write-Host "`n--- Results ---`n" -ForegroundColor Cyan
$Results | Sort-Object LastActivity | Format-Table -AutoSize

$ExportPath = Join-Path $PSScriptRoot "ServiceDeskSiteInventory_$(Get-Date -Format 'yyyyMMdd').csv"
$Results | Export-Csv -Path $ExportPath -NoTypeInformation
Write-Host "`nExported to $ExportPath" -ForegroundColor Green

$unknownCount = ($Results | Where-Object { $_.Status -eq "Unknown" }).Count
if ($unknownCount -gt 0) {
    Write-Host "$unknownCount site(s) had no match in the usage report - worth checking manually, these could be very new sites or ones with genuinely zero recorded activity." -ForegroundColor Yellow
}

#region Write to the SharePoint list (upsert - keyed on Site URL)
# Sites already in the list: fields are updated EXCEPT Comments, which the service
# desk owns and this script never touches.
# Sites in the list but no longer in $Results (removed, or now Teams-connected):
# SiteStatus is set to "Removed" rather than deleting the row.
# Sites not yet in the list: added fresh, Comments left blank.

Write-Host "`nConnecting to $ListSiteUrl to sync the list..." -ForegroundColor Cyan
Connect-PnPOnline -Url $ListSiteUrl -Interactive -ClientId $ClientId

Write-Host "Reading existing list items..." -ForegroundColor Cyan
$ExistingItems = Get-PnPListItem -List $ListName -PageSize 500
$ExistingLookup = @{}
foreach ($item in $ExistingItems) {
    $key = $item["SiteUrl"]
    if ($key) { $ExistingLookup[$key] = $item }
}

$CurrentUrls = @{}
$addedCount   = 0
$updatedCount = 0

foreach ($Row in $Results) {
    $CurrentUrls[$Row.Url] = $true

    $fieldValues = @{
        Title        = $Row.Title
        SiteUrl      = $Row.Url
        UsageGB      = $Row.UsageGB
        LastActivity = $Row.LastActivity
        SiteOwner    = $Row.Owner
        SiteStatus   = $Row.Status
        # Comments intentionally omitted - never overwritten by this script
    }

    if ($ExistingLookup.ContainsKey($Row.Url)) {
        try {
            Set-PnPListItem -List $ListName -Identity $ExistingLookup[$Row.Url].Id -Values $fieldValues -ErrorAction Stop | Out-Null
            $updatedCount++
        }
        catch {
            # Item existed when the list was read moments ago but is gone now
            # (manual deletion, or SharePoint consistency lag on a bulk delete).
            # Fall back to adding it fresh rather than failing the whole run.
            Add-PnPListItem -List $ListName -Values $fieldValues | Out-Null
            $addedCount++
        }
    }
    else {
        Add-PnPListItem -List $ListName -Values $fieldValues | Out-Null
        $addedCount++
    }
}

# Flag rows that exist in the list but weren't in this run's results
$removedCount = 0
foreach ($url in $ExistingLookup.Keys) {
    if (-not $CurrentUrls.ContainsKey($url)) {
        $existingStatus = $ExistingLookup[$url]["SiteStatus"]
        if ($existingStatus -ne "Removed") {
            try {
                Set-PnPListItem -List $ListName -Identity $ExistingLookup[$url].Id -Values @{ SiteStatus = "Removed" } -ErrorAction Stop | Out-Null
                $removedCount++
            }
            catch {
                # Already gone (deleted manually, or consistency lag) - nothing to flag.
            }
        }
    }
}

Write-Host "`nList sync complete: $addedCount added, $updatedCount updated, $removedCount flagged as Removed." -ForegroundColor Green
#endregion