Sync-SPOServiceDeskSiteList-Graph

PowerShell Script

<#
.SYNOPSIS
    Service Desk Site Inventory, Graph-only version (no classic
    Microsoft.Online.SharePoint.PowerShell module required)

.DESCRIPTION
    Same end result as the classic-module version of this script (uses
    Get-SPOSite's IsTeamsConnected/IsTeamsChannelConnected), but detects
    Teams-connected sites via Microsoft Graph instead. This exists specifically
    so the whole thing can run on a single PowerShell 7.4 runtime in an Azure
    Automation runbook

    Detection logic (Teams-connected count and Owner field) validated
    end-to-end against devintranet, confirmed matching the classic-module
    version's output.

    Detection logic:
      1. Communication sites (Template "SITEPAGEPUBLISHING*") and private/shared
         Teams channel sites (Template "TEAMCHANNEL*") are excluded outright.
      2. Sites with no GroupId at all can't be Teams-connected (Teams always
         requires an underlying M365 Group) - included automatically.
      3. Sites WITH a GroupId are group-connected, but that doesn't guarantee
         a Team exists - a group can exist without Teams ever being enabled on
         it. Each group's resourceProvisioningOptions is checked via Graph;
         if it contains "Team", the site is excluded.

    This is deliberately more Graph calls than the classic-module version (one
    extra call per group-connected site), but keeps everything on one PnP.PowerShell
    connection and one PowerShell runtime - worth it for the runbook, and the
    Graph call volume is trivial for ~50 sites.

    List sync behavior (same as the classic-module version):
      - Sites already in the list are updated in place. Comments, which the
        service desk fills in manually, is never touched.
      - Sites no longer in scope are flagged Status = "Removed" rather than
        being deleted from the list.
      - New sites are added fresh.

.NOTES
    Stage:  Detection logic validated end-to-end (manual/interactive, workstation).
            List-sync logic is untested in this file specifically - reused
            verbatim from the classic-module version, but run once fully
            before treating it as confirmed.
    Target: devintranet (swap $AdminUrl and $ListSiteUrl before running against
            anything else)
    Next:   this is the version that becomes the runbook, since it runs
            entirely on PnP.PowerShell 3.x / PowerShell 7.4 with no classic
            module dependency. Auth needs swapping from -Interactive to
            cert-based app-only for unattended execution.
#>

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

$ListSiteUrl = "https://tenantName.sharepoint.com/sites/siteName"
$ListName    = "listName"
#endregion

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

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

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

Write-Host "Filtering out Teams-connected sites via Graph..." -ForegroundColor Cyan
$NonTeamsSites = New-Object System.Collections.Generic.List[Object]
$groupCheckCount = 0

foreach ($Site in $AllSites) {

    # Private/shared Teams channel sites get their own site collection tied to
    # the channel, separate from the parent team's main GroupId.
    if ($Site.Template -like "TEAMCHANNEL*") { continue }

    # Communication sites aren't Teams-connected but also aren't the kind of
    # site the service desk needs to track here.
    if ($Site.Template -like "SITEPAGEPUBLISHING*") { continue }

    # No M365 Group at all - can't be Teams-connected regardless.
    if (-not $Site.GroupId -or $Site.GroupId -eq [Guid]::Empty) {
        $NonTeamsSites.Add($Site)
        continue
    }

    # Group-connected: still need to check whether THIS group actually has a
    # Team provisioned on it, since a group can exist without Teams enabled.
    $isTeamsConnected = $false
    try {
        $groupCheckCount++
        $Group = Invoke-PnPGraphMethod -Url "groups/$($Site.GroupId)?`$select=resourceProvisioningOptions" -Method Get -ErrorAction Stop
        if ($Group.resourceProvisioningOptions -contains "Team") {
            $isTeamsConnected = $true
        }
    }
    catch {
        Write-Warning "  Could not check Teams status for group $($Site.GroupId) (site $($Site.Url)): $($_.Exception.Message)"
        # Treat lookup failures as Unknown rather than silently including or
        # excluding - worth reviewing manually rather than guessing either way.
        continue
    }

    if (-not $isTeamsConnected) { $NonTeamsSites.Add($Site) }
}

Write-Host "$groupCheckCount group-connected sites required a Graph check." -ForegroundColor DarkGray
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): Site URL in this report is unreliable
# even with anonymization off, so we join on Site Id instead (see the classic-
# module DEV script's notes for the full writeup - same workaround applies here).
$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
        $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"
    }

    [PSCustomObject]@{
        Url          = $Site.Url
        Title        = $Site.Title
        UsageGB      = [math]::Round($Site.StorageUsageCurrent / 1024, 2)
        LastActivity = $LastActivityDate
        Owner        = $Site.Owner     # NOTE: verify this field is populated - Get-PnPTenantSite
                                        # may expose owner info under a different property name
                                        # than Get-SPOSite did. Check the results table below.
        Comments     = ""
        Status       = $Status
    }
}

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

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

#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). 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 - nothing to flag.
            }
        }
    }
}

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