In this post, I am going to show you how to list every site in your tenant with its owner, storage and last activity – and why the owner column is harder than it looks. Full script at the end.
Run against a real tenant of 37 sites on PnP.PowerShell 2.12.0.
The basic call
Connect-PnPOnline -Url "https://contoso-admin.sharepoint.com" -ClientId "1111...-5555" -Interactive
Get-PnPTenantSiteSites returned : 37
Took : 1.5sNote the -admin URL. Tenant-wide cmdlets want the admin centre, not a site.
Two things that are not obvious
OneDrive is excluded. Personal sites are site collections too, but you only get them if you ask:
Without -IncludeOneDriveSites : 37
With -IncludeOneDriveSites : 42Five here. In a real organisation it is one per person, so a 200 site tenant becomes 5,000. Know which number you want before you run a report on it.
-Detailed did nothing. I compared the property sets:
Get-PnPTenantSite 1.5s, 91 properties
Get-PnPTenantSite -Detailed 0.7s, 91 properties - no differenceNot one extra property. Plenty of guides tell you to reach for -Detailed; on this version there is nothing to reach for. Check it on your own version before you pay for it.
91 properties
Far more than the four everyone shows. The ones I use:
Url Title Owner Template GroupId
StorageUsageCurrent StorageQuota LastContentModifiedDate
SharingCapability LockState SensitivityLabel IsHubSite WebsCountThere are newer ones worth knowing about too – ArchiveStatus, RestrictedAccessControl, RestrictContentOrgWideSearch, EnableAutoExpirationVersionTrim, ExpireVersionsAfterDays. Print the lot with:
Get-PnPTenantSite | Select-Object -First 1 | Get-Member -MemberType PropertiesStorageUsageCurrent and StorageQuota are both in MB. A quota of 1048576 is 1TB.
The owner problem
This is the part worth reading. There are four owner-looking properties. Here is how many of my 37 sites had each one populated:
Owner : 15
OwnerName : 0
OwnerEmail : 0
OwnerLoginName : 0Three of them are empty on every single site. Not sparse – zero. If you build a report on OwnerEmail you get a column of blanks and nothing tells you it did not work.
And the one that does work is only populated on 15 of 37. Grouped by template, the blanks look like this:
Count Template
----- --------
15 GROUP#0
1 EHS#1
1 POINTPUBLISHINGHUB#0
1 RedirectSite#0
...Every group-connected site has a blank Owner. No exceptions. Which makes sense once you think about it – a Teams or Microsoft 365 group site is owned by the group, not by a site collection administrator, so there is nothing for SharePoint to put in that field.
The consequence is worth being blunt about. A “find sites with no owner” script that checks Owner reported 22 ownerless sites on my tenant. The real answer is 7. Fifteen of them were group sites with perfectly good owners in the group.
Getting the real owner
For group-connected sites, ask the group. The site object gives you the GroupId:
$owners = Get-PnPMicrosoft365GroupOwner -Identity $site.GroupId
($owners | ForEach-Object { $_.UserPrincipalName }) -join '; 'allcompany : admin@contoso.onmicrosoft.com
cd : admin@contoso.onmicrosoft.com
demohr : admin@contoso.onmicrosoft.comThe important thing to note is the cost. That is a separate call per group, and I measured 1557ms each. Fifteen groups is 23 seconds. Five hundred groups is thirteen minutes on top of everything else, so make it optional rather than something your report always does.
What does not work
The obvious alternative is to ask each site for its administrators:
Connect-PnPOnline -Url "https://contoso.sharepoint.com/sites/Example" -ClientId "..." -Interactive
Get-PnPSiteCollectionAdminAttempted to perform an unauthorized operation.I am a tenant administrator. That is not the same as being a site collection administrator on every site, and SharePoint does not pretend otherwise. To read a site’s administrators you have to be one, which means adding yourself first – and that is a change to the site, on a report you thought was read-only.
Run it from the admin connection instead and it answers a different question entirely:
Title LoginName
----- ---------
Company Administrator c:0t.c|tenant|90a53517-cc9a-405c-a4cb-...That is the admin centre’s own administrators, not the site’s.
So there is no cheap way to get a site collection administrator for a site you do not already administer. Group owners are the practical answer for group sites, and Owner is the practical answer for the rest.
The whole script
[CmdletBinding()]
param(
[Parameter(Mandatory)] [string] $TenantAdminUrl,
[Parameter(Mandatory)] [string] $ClientId,
[string] $OutputCsv,
[switch] $ResolveGroupOwners,
[switch] $IncludeOneDrive,
[int] $StaleDays = 90
)
$ErrorActionPreference = 'Stop'
Connect-PnPOnline -Url $TenantAdminUrl -ClientId $ClientId -Interactive
Write-Host "Enumerating sites..." -ForegroundColor Cyan
$sites = if ($IncludeOneDrive) { Get-PnPTenantSite -IncludeOneDriveSites } else { Get-PnPTenantSite }
Write-Host " $($sites.Count) site(s)" -ForegroundColor Green
$cutoff = (Get-Date).AddDays(-$StaleDays)
$results = [System.Collections.Generic.List[object]]::new()
$number = 0
foreach ($site in $sites) {
$number++
Write-Progress -Activity 'Building site inventory' `
-Status "$number of $($sites.Count): $($site.Url)" `
-PercentComplete (($number / $sites.Count) * 100)
$isGroupSite = $site.GroupId -and $site.GroupId -ne [guid]::Empty
$owner = if ($site.Owner) {
$site.Owner
}
elseif ($isGroupSite -and $ResolveGroupOwners) {
try {
$groupOwners = Get-PnPMicrosoft365GroupOwner -Identity $site.GroupId -ErrorAction Stop
($groupOwners | ForEach-Object { $_.UserPrincipalName }) -join '; '
}
catch { "(group lookup failed: $($_.Exception.Message))" }
}
elseif ($isGroupSite) {
'(group-connected - rerun with -ResolveGroupOwners)'
}
else {
'(none recorded)'
}
$results.Add([pscustomobject]@{
Url = $site.Url
Title = $site.Title
Owner = $owner
GroupConnected = $isGroupSite
Template = $site.Template
StorageUsedMB = [math]::Round($site.StorageUsageCurrent, 1)
StorageQuotaMB = $site.StorageQuota
PercentUsed = if ($site.StorageQuota -gt 0) {
[math]::Round(($site.StorageUsageCurrent / $site.StorageQuota) * 100, 1)
} else { 0 }
LastModified = $site.LastContentModifiedDate
Stale = $site.LastContentModifiedDate -lt $cutoff
Sharing = $site.SharingCapability
SensitivityLabel= $site.SensitivityLabel
LockState = $site.LockState
})
}
Write-Progress -Activity 'Building site inventory' -Completed
$stale = @($results | Where-Object Stale)
$noOwner = @($results | Where-Object { $_.Owner -like '(none*' })
$totalGB = [math]::Round(($results | Measure-Object StorageUsedMB -Sum).Sum / 1024, 2)
Write-Host ""
Write-Host "Sites : $($results.Count)"
Write-Host "Group-connected : $(@($results | Where-Object GroupConnected).Count)"
Write-Host "Storage used : $totalGB GB"
Write-Host "Not modified $StaleDays d : $($stale.Count)"
Write-Host "No owner recorded: $($noOwner.Count)"
if (-not $ResolveGroupOwners -and @($results | Where-Object GroupConnected).Count -gt 0) {
Write-Warning "Group-connected sites have no owner shown. Rerun with -ResolveGroupOwners (about 1.5s each)."
}
if ($OutputCsv) {
$results | Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8
Write-Host "`nWritten to $OutputCsv" -ForegroundColor Green
} else {
$results | Sort-Object StorageUsedMB -Descending |
Format-Table Url, Owner, StorageUsedMB, LastModified, Stale -AutoSize
}
Disconnect-PnPOnlineSave it as Get-SiteInventory.ps1 and run it:
.\Get-SiteInventory.ps1 `
-TenantAdminUrl "https://contoso-admin.sharepoint.com" `
-ClientId "11111111-2222-3333-4444-555555555555" `
-ResolveGroupOwners `
-OutputCsv .\sites.csvEnumerating sites...
37 site(s)
Sites : 37
Group-connected : 15
Storage used : 1.06 GB
Not modified 90 d : 1
No owner recorded : 7
Written to .\sites.csvWithout -ResolveGroupOwners it says (group-connected – rerun with -ResolveGroupOwners) in the owner column rather than leaving it blank. A blank there reads as “this site has no owner”, and on my tenant that would have been wrong fifteen times out of twenty-two 🙂