Report spo site storage usage

Connect-SPOService -Url $AdminUrl
Connect-ExchangeOnline -ShowBanner:$false

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
   $GroupId = (Get-SPOSite -Identity $SiteURL)
   If ($GroupId.Template -eq  "TEAMCHANNEL#0" -or $GroupId.Template -eq "TEAMCHANNEL#1") { # If Teams private or shared channel, we use the Related Group Id
      #$GroupId = $GroupId | Select-Object -ExpandProperty RelatedGroupId
      $SiteOwners = "Channel owners"
   } Else { # And for all other group-enabled sites, we use the GroupId
      $GroupId = $GroupId | Select-Object -ExpandProperty GroupId
   }
 
   If ($GroupId.Guid -eq [Guid]::Empty ) { # Null group id stored in site
      $SiteOwners = "Deleted group"; $DeletedGroup = $True
   }
   If ($DeletedGroup -eq $False -and $null -eq $SiteOwners) {
      Try {
         $Owners = (Get-UnifiedGroupLinks -Identity $GroupId.Guid -LinkType Owners -ErrorAction SilentlyContinue)
      } 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
      $Owners = $Owners | Select-Object -ExpandProperty DisplayName
      $SiteOwners = $Owners -join ", "
   }
 
Return $SiteOwners
}
 
# Check that we are connected to Exchange Online and SharePoint Online
$ModulesLoaded = Get-Module | Select-Object Name
If (!($ModulesLoaded -match "ExchangeOnlineManagement")) {
   Write-Host "Please connect to the Exchange Online Management module and then restart the script"; break
}
If (!($ModulesLoaded -match "Microsoft.Online.Sharepoint.PowerShell")) {
   Write-Host "Please connect to the SharePoint Online Management module and then restart the script"; break
}
 
# 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, LastContentModifiedDate, ArchiveStatus, 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.toUpper() -ne "TEAMCHANNEL#0" -and $_.Template.toUpper() -ne "TEAMCHANNEL#1"}
 
Clear-Host
$ProgressDelta = 100/($Sites.count); $PercentComplete = 0; $SiteNumber = 0
$Report = [System.Collections.Generic.List[Object]]::new()
$TotalSPOStorageUsed = [Math]::Round(($Sites."Storage Used (Byte)" | Measure-Object -Sum).Sum /1024,2)
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)
   $PercentTenant = ([Math]::Round($Site.StorageUsageCurrent/1024,4)/$TotalSPOStorageUsed).tostring("P")
 
   # Calculate how long it's been since the last conetnt modification date, 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
   If ($Site.LastContentModifiedDate) {
      $LastModifiedDate = Get-Date $Site.LastContentModifiedDate -Format 'dd-MMM-yyyy hh:mm'
      $DaysSinceModified = (New-TimeSpan -Start $Site.LastContentModifiedDate -End (Get-Date)).Days
   } Else {
      $LastModifiedDate = "No content"
   }
 
# 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
         PercentUsed       = ([Math]::Round(($Site.StorageUsageCurrent/$Site.StorageQuota),4).ToString("P"))
         PercentTenant     = $PercentTenant
         LastModified      = $LastModifiedDate
         DaysSinceModified = $DaysSinceModified
      }
      $Report.Add($ReportLine)
   }
}
 
# Now generate the report
$CSVOutputFile =  ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\SPOSiteConsumption.csv"
$Report | Export-CSV -NoTypeInformation $CSVOutputFile -Encoding utf8
Write-Host ("Current SharePoint Online storage consumption is {0} GB. Report is in {1}" -f $TotalSPOStorageUsed, $CSVOutputFile)