Find obsolete SharePoint Online sites

Connect-MgGraph -AppId $AppId -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint

param(
    [string] $TenantId = "",
    [string] $AppId = ""
)
 
Function Get-Microsoft365GroupOwners([String]$SiteURL) {
   # Function to return the owners of an Microsoft 365 Group identified by the group GUID
   $Owners = $Null; $DeletedGroup = $False; $SiteOwners = $Null
   # Get the site properties. We need a separate call here because Get-SPOSite doesn't return all properties when it fetches a set of sites
   $Group = (Get-SPOSite -Identity $SiteURL)
   If ($Group.Template -eq  "TEAMCHANNEL#0" -or $GroupId.Template -eq "TEAMCHANNEL#1") { # If Teams private or shared channel, we use the Related Group Id
      $SiteOwners = "Channel owners"
   } Else { # And for all other group-enabled sites, we use the GroupId
      $GroupId = $Group | Select-Object -ExpandProperty GroupId
   }
 
   If ($GroupId.Guid -eq "00000000-0000-0000-0000-000000000000") { # Null group id stored in site
      $SiteOwners = "Deleted group"; $DeletedGroup = $True }
   If ($DeletedGroup -eq $False) {
      Try {
         [string]$GroupId = $GroupId.Guid
         [array]$Owners = Get-MgGroupOwner -GroupId $GroupId -ErrorAction Stop
      } Catch {
         $SiteOwners = "Possibly deleted Microsoft 365 Group"; $DeletedGroup = $True
      }
   }
 
   If ($Null -eq $Owners) { # Got nothing back, maybe because of an error
        $SiteOwners = "Possibly deleted Microsoft 365 Group"
   } Else { # We have some owners, now format them
        $SiteOwners = $Owners.additionalProperties.displayName -Join ", "
   }
 
Return $SiteOwners
}
 
# Check that we are connected to Exchange Online and SharePoint Online
$ModulesLoaded = Get-Module | Select-Object Name
If (!($ModulesLoaded -match "Microsoft.Online.Sharepoint.PowerShell")) {
   Write-Host "Please connect to the SharePoint Online Management module and then restart the script"; break
}
# Connect to the Microsoft Graph in app-only mode to read group owner information, SPO report information, and
# Change these values for your tenant and app registration. The app registration needs to have the Group.Read.All and Reports.Read.All application permissions in order for the script to work.
$CertificateThumbprint = "8CD4AD070C7447BA767EAB5DA659A02E6411BB80"
Connect-MgGraph -AppId $AppId -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint
 
# Get all SPO sites
Clear-Host
Write-Host "Fetching information about SharePoint Online sites..."
[array]$Sites = Get-SPOSite -Limit All | Select-Object Title, URL, StorageQuota, StorageUsageCurrent, ArchiveStatus, CreatedTime, Template | `
   Sort-Object StorageUsageCurrent -Descending
If ($Sites.Count -eq 0) {
   Write-Host "No SharePoint Online sites found.... exiting..." ; break
}
$Sites = $Sites | Where-Object { $_.ArchiveStatus -eq "NotArchived" } # Remove archived sites
# Remove sites used for Teams private/shared channels. Comment this out if you want to include the channel sites in the report.
$Sites = $Sites | Where-Object {$_.Template -ne "TEAMCHANNEL#0" -and $_.Template -ne "TEAMCHANNEL#1"}
# Remove redirect sites. These are not really sites, but just placeholders that point to the real site, and they don't have any activity or storage usage themselves, so we can remove them from the report. Comment this out if you want to include the redirect sites in the report.
$Sites = $Sites | Where-Object {$_.Template -ne "REDIRECTSITE#0"}
 
# Check whether the obfuscation setting for reports is set and if so, reset it to allow clear values to be obtained
$ObfuscationChanged = $false
If ((Get-MgAdminReportSetting).DisplayConcealedNames -eq $True) {
   $Parameters = @{ displayConcealedNames = $False }
   Update-MgAdminReportSetting -BodyParameter $Parameters
   $ObfuscationChanged = $true
}
 
Write-Host "Fetching SharePoint Online site activity information from the Microsoft Graph..."
$TempDownLoadFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\download.csv"
$URI = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D180')"
Invoke-MgGraphRequest -Uri $Uri -Method GET -OutputFilePath $TempDownloadFile
[array]$SitesActivityData = Import-CSV $TempDownloadFile
 
Write-Host "Processing site activity data..."
$SiteActivityHash = @{}
ForEach ($Site in $SitesActivityData) {
    $SiteId = $Site.'Site Id'
    Try {
        $SiteInfo = Get-MgSite -SiteId $SiteId -ErrorAction Stop
        If ($Site.'Last Activity Date') {
            $SiteActivityHash.Add($SiteInfo.WebURL, $Site)
        } Else {
            $SiteActivityHash.Add($SiteInfo.WebURL, "No activity")
        }
    } Catch {
        Write-Host "Failed to get site information for SiteId: $SiteId"
        Continue
    }
}
 
Clear-Host
 
$ProgressDelta = 100/($Sites.count); $PercentComplete = 0; $SiteNumber = 0
$TotalSPOStorageUsed = [Math]::Round(($Sites.StorageUsageCurrent | Measure-Object -Sum).Sum /1024,2)
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($Site in $Sites) {
   $SiteOwners = $Null ; $Process = $True; $SiteType = $null
   $SiteNumber++
   $SiteStatus = $Site.Title + " ["+ $SiteNumber +"/" + $Sites.Count + "]"
   Write-Progress -Activity "Processing site" -Status $SiteStatus -PercentComplete $PercentComplete
   $PercentComplete += $ProgressDelta
   $NoCheckGroup = $False
   Switch ($Site.Template.toUpper()) {  #Figure out the type of site and if we should process it - this might not be an exhaustive set of site templates
      "GROUP#0"                   {$SiteType = "Group-enabled team site"}
      "TEAMCHANNEL#0"             {$SiteType = "Teams Private or Shared Channel"}
      "TEAMCHANNEL#1"             {$SiteType = "Teams Shared or Private Channel"}
      "STS#0"                     {$SiteType = "Team Site"; $NoCheckGroup = $True; $SiteOwners = "System"}
      "REVIEWCTR#0"               {$SiteType = "Review Center"; $Process = $False}
      "APPCATALOG#0"              {$SiteType = "App Catalog"; $Process = $False}
      "STS#3"                     {$SiteType = "Team Site"; $NoCheckGroup = $True; $SiteOwners = "System"}
      "SPSMSITEHOST#0"            {$SiteType = "Unknown"; $Process = $False}
      "SRCHCEN#0"                 {$SiteType = "Search Center"; $Process = $False}
      "EHS#1"                     {$SiteType = "Team Site - SPO Configuration"; $NoCheckGroup = $True; $SiteOwners = "System"}
      "EDISC#0"                   {$SiteType = "eDiscovery Center"; $Process = $False}
      "SITEPAGEPUBLISHING#0"      {$SiteType = "Site page"; $NoCheckGroup = $True; $SiteOwners = "System"}
      "POINTPUBLISHINGHUB#0"      {$SiteType = "Communications Site"; $NoCheckGroup = $True; $SiteOwners = "System" }
      "POINTPUBLISHINGPERSONAL#0" {$SiteType = "OneDrive for Business"; $Process = $False}
      "POINTPUBLISHINGTOPIC#0"    {$SiteType = "Office 365 Video"; $NoCheckGroup = $True; $SiteOwners = "System"}
      "REDIRECTSITE#0"            {$SiteType = "Redirect site"; $Process = $False; $SiteOwners }
      Default                     {$SiteType = "Other"}
      }
   If ($NoCheckGroup -eq $False) { # Get owner information if it's a Microsoft 365 Group
      $SiteOwners = Get-Microsoft365GroupOwners($Site.URL)
   }
 
   $UsedGB = [Math]::Round($Site.StorageUsageCurrent/1024,2)
   If ($Site.StorageUsageCurrent -gt 0) {
      $PercentTenant = ([Math]::Round($Site.StorageUsageCurrent/1024,4)/$TotalSPOStorageUsed).tostring("P")
   } Else {
      $PercentTenant = "0%"
   }
 
   # Calculate how long it's been since the last activity date from the usage report data, if we have one. This helps us
   # understand if the site is still active or not, and can be used to filter the report if necessary
   $LastActivityInfo = $SiteActivityHash[$Site.URL]
   $LastActivityDate = $LastActivityInfo.'Last Activity Date'
   If ($LastActivityDate -and $LastActivityDate -ne "No activity") {
      $LastActivityDate = Get-Date $LastActivityDate -Format 'dd-MMM-yyyy'
      $DaysSinceActivity = (New-TimeSpan -Start $LastActivityDate -End (Get-Date)).Days
   } Else {
      $LastActivityDate = "No activity"
      $DaysSinceActivity = "N/A"
   }
 
# Calculate obsolete status for the site
# If less than seven days old, continue
# If no activity, and more than 90 days old, obsolete
# If activity, but last activity more than 180 days ago, obsolete
# If the nummber of files is less than 20 and no activity in the last 180 days, obsolete. This is to catch sites that have had some activity, but are essentially empty and not being used.
   If ((New-TimeSpan -Start $Site.CreatedTime -End (Get-Date)).Days -lt 7) {
      $SiteObsolete = $False
   } ElseIf ($LastActivityDate -eq "No activity" -and (New-TimeSpan -Start $Site.CreatedTime -End (Get-Date)).Days -gt 90) {
      $SiteObsolete = $True
   } ElseIf ($LastActivityDate -ne "No activity" -and $DaysSinceActivity -gt 180) {
      $SiteObsolete = $True
   } ElseIf ($LastActivityDate -ne "No activity" -and $DaysSinceActivity -gt 180 -and $LastActivityInfo.'File Count' -lt 20) {
      $SiteObsolete = $True
   } Else {
      $SiteObsolete = $False
   }
 
# And write out the information about the site
   If ($Process -eq $True) {
      $ReportLine = [PSCustomObject]@{
         URL               = $Site.URL
         SiteName          = $Site.Title
         Owner             = $SiteOwners
         Template          = $SiteType
         QuotaGB           = [Math]::Round($Site.StorageQuota/1024,0)
         UsedGB            = $UsedGB
         Files             = $LastActivityInfo.'File Count'
         PercentUsed       = ([Math]::Round(($Site.StorageUsageCurrent/$Site.StorageQuota),4).ToString("P"))
         PercentTenant     = $PercentTenant
         'Last Activity'   = $LastActivityDate
         DaysSinceActivity = $DaysSinceActivity
         CreatedTime       = $Site.CreatedTime
         SiteObsolete      = $SiteObsolete
      }
      $Report.Add($ReportLine)
   }
}
 
$Report = $Report | Sort-Object DaysSinceActivity -Descending
 
# Generate the report in either Excel worksheet or CSV format, depending on if the ImportExcel module is available
If (Get-Module ImportExcel -ListAvailable) {
    $ExcelGenerated = $True
    Import-Module ImportExcel -ErrorAction SilentlyContinue
    $ExcelOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO Site List.xlsx"
    $Report | Export-Excel -Path $ExcelOutputFile -WorksheetName "SPO Sites" -Title ("SPO Sites {0}" -f (Get-Date -format 'dd-MMM-yyyy')) -TitleBold -TableName "SPOSites"
} Else {
    $CSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPO Site List.CSV"
    $Report | Export-Csv -Path $CSVOutputFile -NoTypeInformation -Encoding Utf8
}
 
Write-Host ("{0} sites processed. Current SharePoint Online storage consumption is {1} GB." -f $Report.count, $TotalSPOStorageUsed)
 
If ($ExcelGenerated -eq $true) {
    Write-Host ("SPO sites report is available in Excel workbook {0}" -f $ExcelOutputFile)
} Else {
    Write-Host ("SPO sites report is available in CSV file {0}" -f $CSVOutputFile)
}
 
# If necessary, reset tenant obfuscation settings to True
If ($ObfuscationChanged) {
    If ((Get-MgAdminReportSetting).DisplayConcealedNames -eq $False) {
       $Parameters = @{ displayConcealedNames = $True }
       Update-MgAdminReportSetting -BodyParameter $Parameters
    }
}