|

List Every SharePoint Site with Owner, Storage and Last Activity

ByG Vijai Kumar

This time around, I am going to demonstrate listing all sites from the tenant together with their owner, storage, and last activity date, and why it is more difficult than one can think to list the owners. Script below.

Tested on a tenant with 37 sites using PnP.PowerShell 2.12.0

The basic call

Connect-PnPOnline -Url "https://contoso-admin.sharepoint.com" -ClientId "1111...-5555" -Interactive
Get-PnPTenantSite
Sites returned : 37
Took           : 1.5s

Note 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 : 42

Five 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 difference

Not 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. These are the ones I use.

Url  Title  Owner  Template  GroupId
StorageUsageCurrent  StorageQuota  LastContentModifiedDate
SharingCapability  LockState  SensitivityLabel  IsHubSite  WebsCount

There are newer ones worth knowing about too. They are ArchiveStatus, RestrictedAccessControl, RestrictContentOrgWideSearch, EnableAutoExpirationVersionTrim and ExpireVersionsAfterDays. Print the lot with this.

Get-PnPTenantSite | Select-Object -First 1 | Get-Member -MemberType Properties

StorageUsageCurrent 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 : 0

Three 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. There are no exceptions to this. And the logic is pretty clear when one thinks about it. A Teams site or an M365 group site is owned by the group and not by the site collection administrator, so there is nothing to put into this field by SharePoint.

What does this mean? Quite simply, a “find sites with no owner” script using Owner field showed 22 ownerless sites on my tenant. The truth is 7. 15 out of these were group sites with perfectly legitimate group owners.

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.com

The point to be made here is about the cost. This is an individual request per group, and the measurements came to 1557 ms each. 15 groups take 23 seconds. 500 groups will take 13 minutes in addition to all that, hence the option of making it optional.

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-PnPSiteCollectionAdmin
Attempted to perform an unauthorized operation.

I am a Tenant Admin. This does not imply that I am a site collection admin for each and every site, nor does SharePoint suggest that. It requires that I be an administrator for a particular site to read the administrators of the site, which in itself is a change to the site, in a report which I assumed to be read-only.

Instead, execute it in the admin connection and see what you get.

Title                 LoginName
-----                 ---------
Company Administrator c:0t.c|tenant|90a53517-cc9a-405c-a4cb-...

These administrators are those of the administration center and not of the particular site itself.

There is also no cost-effective way of obtaining an administrator for a site that you don’t have administrative permissions for. Group owners are the best solution for group sites, while the Owner is the most suitable for the others.

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-PnPOnline

Save 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.csv
Enumerating 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.csv

Without -ResolveGroupOwners, it will say (group connected – please re-run with -ResolveGroupOwners) instead of putting a blank there. Having nothing in that spot means “there is no owner on this site,” and in my tenant, that would have been incorrect 15 out of 22 times.

Related posts

Similar Posts