Getting your Office 365 tenant’s SharePoint site structure with PowerShell

Update 12 June 2018: Updated cmdlets to follow the newer PnP naming standards (e.g. Connect-PnPOnline instead of Connect-SPOnline).

Introduction

From time to time you may want to get an overview of the structure of your SharePoint Online environment. That is, you want to know the number of site collections and subsites, and know how they are organised.

While you could review the existing site collections either in the UI (not very convenient once you have more than 20) or via PowerShell, both approaches don’t provide you with details about a site collection’s structure itself. How many subsites are there, how are they organised? How many levels deep do they go, or are there dozens of subsites located directly underneath a specific site collection?

Preparation

Just like in my recent post, what you need are the OfficeDev PnP PowerShell cmdlets available at https://github.com/OfficeDev/PnP-PowerShell (review the installation instructions on that page if you haven’t installed those awesome cmdlets yet) and an account which has been assigned either the “SharePoint administrator” or “Global administrator” role in your tenant. Additionally, this account needs to have access to all SharePoint Online site collections – this is where the script to set site collection administrators on all site collections comes in very handy.

The Script

What the script does is the following: it first fetches the list of all site collections (apply a filter if necessary) and then collects the structure for each site collection by going through all subsites. Lastly, it writes the collected information into a CSV file for further manual analysis, e.g. in Excel.

#Config Variables - update these as required
$tenant = "mytenant"
$ReportPath = "SPOStructure.csv"

# Note: If you run this script regularly, please have a look at
# the following site to see how you can store credentials securely in Windows
# https://github.com/OfficeDev/PnP-PowerShell#settings-up-credentials
$cred = Get-Credential
Connect-PnPOnline -Url "https://$($tenant).sharepoint.com" -Credentials $cred

write-host "Getting all sites"
#Note: we do not make use of the IncludeOneDriveSites parameter here,
# which would include personal sites as well
#You could also filter here to get only specific sites, or use
# the -Url parameter for the Get-PnPTenantSite to get the structure of a single site collection only
$tenantSites = Get-PnPTenantSite

function Get-SPOSubWebs($Context, $RootWeb){ 
	$arrWebs = @()
        $Webs = $RootWeb.Webs 
        $Context.Load($Webs) 
        $Context.ExecuteQuery()  
        ForEach ($sWeb in $Webs) 
        { 
	    $arrWebs  += $sWeb.Url
            $arrWebs += Get-SPOSubWebs -RootWeb $sWeb -Context $Context 
        } 
	return $arrWebs
    } 

$allWebs = @()
foreach($site in $tenantSites) {
    write-host "Connecting to $($site.Url)"
    Connect-PnPOnline -Url $site.Url -Credentials $cred
    $allWebs += $site.Url
    $allWebs += Get-SPOSubWebs (Get-PnPContext) (Get-PnPWeb)
}
write-host "Finished"

$allWebs | Out-File -FilePath $ReportPath

Here’s the script in action:

Supply values for the following parameters:
Credential
Getting all sites
Connecting to https://mytenant.sharepoint.com/ 
Connecting to https://mytenant.sharepoint.com/portals/hub 
Connecting to https://mytenant.sharepoint.com/search 
Connecting to https://mytenant.sharepoint.com/sites/a-datum-corporation-inc 
Connecting to https://mytenant.sharepoint.com/sites/CompliancePolicyCenter 
Connecting to https://mytenant.sharepoint.com/sites/proseware-inc 
Connecting to https://mytenant-my.sharepoint.com/ 
Finished

And the result written to the CSV file:

https://mytenant.sharepoint.com/
https://mytenant.sharepoint.com/paris
https://mytenant.sharepoint.com/portals/hub
https://mytenant.sharepoint.com/search
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd/it
https://mytenant.sharepoint.com/sites/a-datum-corporation-inc/amsterd/it/financi
https://mytenant.sharepoint.com/sites/CompliancePolicyCenter
https://mytenant.sharepoint.com/sites/proseware-inc
https://mytenant-my.sharepoint.com/