Hub Sites Report in SharePoint Online using PnP

Introduction

Often times, as a SharePoint online administrator we must generate a report of all hub sites and their associated sites or all associated sites for a particular hub site. Dealing with this manually is very time consuming and there is no straight screen using which can generate this. Here in this article, we will see how we can get all hub sites and their all associated sites for a particular hub site using PnP Powershell. Also, we will see how we can generate this report using a SharePoint online admin center URL.

PnP PowerShell script to get all sites connected to a hub site

Using the below PnP PowerShell code, we can get the noted types of reports:

Get all associated or connected sites to a particular hub site.
Get a master report for all sites and their hub sites from the SharePoint online tenant.

CLS
#############################################################################################################################################################
#Description: Using this script we can generate all associated sites for a partuclar hub site and also all hub sites and their associated sites from a tenant.
#Created By: Habibur Rahaman
#Date: 12-22-2019
#############################################################################################################################################################
###################variables section###################################
$userName = "[email protected] - <Your User name>"
$passWord = "YourPassWord"
$siteURL="https://globalsharepoint2019-admin.sharepoint.com/ <Your sharepoint admin url>"
###################variables section ends here###################################
$encPassWord = convertto-securestring -String $passWord -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userName, $encPassWord
Connect-PnPOnline -Url $siteURL -Credentials $cred
#Getting the hub site id for which we want to generate the report - those are connected to this hub site.
$hubSiteURL="https://globalsharepoint2019.sharepoint.com/sites/SPHubSite"
$hubSite = Get-PnPTenantSite $hubSiteURL
$hubSiteId = $hubSite.HubSiteId
write-host " #####Generating sites connected a single hub site report######: " -BackgroundColor DarkGreen
write-host "Hub Site URL: " $hubSiteURL
$associatedSites = @()
#Get all sites associated to the hub site(in the above hub site)
$sitesTenant = Get-PnPTenantSite -Detailed
$sitesTenant | select url | % {$oneSite = Get-PnPTenantSite $_.url
if($oneSite.hubsiteid -eq $hubSiteId)
{
write-host "Associated Site URL: " $oneSite.url
$assocatedSiteObject = New-Object PSObject
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site URL" -value $hubSiteURL
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site ID" -value $hubSiteId
$assocatedSiteObject | Add-Member -MemberType NoteProperty -Name "Associated Site URL" -value $oneSite.Url
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Associated Site Status" -value $oneSite.Status
#Add the object with property to an Array
$associatedSites += $assocatedSiteObject
}
}
#Export the site array collection to a CSV file
$associatedSites | Export-CSV "C:\Temp\GetAllSitesAssociatedInHubSites\SitesConnectedToSingleHubSiteReprot.csv" -NoTypeInformation
write-host " #####Generating sites connected a single hub site report- ends here######: " -BackgroundColor DarkYellow
######The below script will list down all hub sites and their associated connected sites in the tenant.##################
write-host "------------------------------------------------------------------------------------------------------"
write-host " #####Generating master hub sites along with connected sites report for the tenant. ######:" -BackgroundColor DarkGreen
$hubSites=Get-PnPHubSite
$associatedSites = @()
foreach($oneHubSite in $hubSites)
{
$test=$oneHubSite;
write-host "Hub Site URL: " $oneHubSite.SiteUrl
$hubSite = Get-PnPTenantSite $oneHubSite.SiteUrl;
$hubSiteId = $hubSite.HubSiteId
#Get all sites associated to the hub site(in the above hub site)
$sitesTenant = Get-PnPTenantSite -Detailed
$sitesTenant | select url | % {$oneSite = Get-PnPTenantSite $_.url
if($oneSite.hubsiteid -eq $hubSiteId)
{
write-host "Associated Site URL: " $oneSite.url
$assocatedSiteObject = New-Object PSObject
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site URL" -value $oneHubSite.SiteUrl
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site ID" -value $oneHubSite.ID
$assocatedSiteObject | Add-Member -MemberType NoteProperty -Name "Associated Site URL" -value $oneSite.Url
$assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Associated Site Status" -value $oneSite.Status
#Add the object with property to an Array
$associatedSites += $assocatedSiteObject
}
}
}
#Export the site array collection to a CSV file
$associatedSites | Export-CSV "C:\Temp\GetAllSitesAssociatedInHubSites\SitesConnectedToHubSiteReprotForTenant.csv" -NoTypeInformation
write-host "##### Generating master hub sites along with connected sites report for the tenant ends here ######:" -BackgroundColor DarkYellow
######The below script will list down all hub sites and their associated connected sites in the tenant - ends here##################

Explanation about the hub sites inventory code

This script takes a SharePoint online username, password, SharePoint online admin URL and a hub site URL. In order to get all associated sites for a particular hub site, we should know the hub site URL and using the Get-PnPTenantSite command we have to get the id of that hub site which will be used while we are looping thru the all active sites to identify whether that active site of part of this hub site. And finally for whichever active sites "hubsiteid" attribute matches with the "hubsiteid" of hub site what we have passed as a parameter in the beginning - then we export the collection as a CSV file.

For a tenant level, hub sites report, we use the same technique but here we don't pass the hub site URL as a parameter. Rather, we read all hub sites collection using the Get-PnPHubSite command and all active sites collection using the Get-PnPTenantSite command from a tenant. Then we loop thru first all hub sites then inside the for each loop of Get-PnPHubSite, write one more for each loop for Get-PnPTenantSite collection, then find the "hubsiteid" attribute from hub site and tenant site and wherever match we store in an array object - finally we export that into a CSV file, the same as the other one.

Prerequisites for the PnP script

Before executing the above code, we must install the PnP in the machine where we will execute the above code. There are many ways to install this - below is the simplest one.

For PnP PowerShell installation - we need to execute the below command in the PowerShell command window but need to ensure the Powershell version should be more 3.0 in that machine.

(New-Object Net.WebClient).DownloadString('https://raw.githubusercontent.com/sharepoint/PnP-PowerShell/master/Samples/Modules.Install/Install-SharePointPnPPowerShell.ps1')

How to execute this script

In order to run the above code successfully, we need to pass the below parameters,

###################variables section###################################
$userName = "[email protected] - <Your User name>"
$passWord = "YourPassWord"
$siteURL="https://globalsharepoint2019-admin.sharepoint.com/ <Your SharePoint admin url>"
$hubSiteURL="https://globalsharepoint2019.sharepoint.com/sites/SPHubSite<Hub Site URL>"
###################variables section ends here###################################

PnP PowerShell script to get all sites connected to hub site: Test

Now, let's execute the above code.

Hub Sites Report In SharePoint Online Using PnP

Let's see the exported hub sites report – what does it look like?

Now we can see the below master report for all hub sites and their associated sites in the SharePoint online tenant,

And we can see the below report for the scenario when we want to get the all associated sites connected to a single hub site.

View all sites that are connected to a hub site using SharePoint admin center URL

By now we have learned that how we can generate the hub and their associated sites report using PnP - now we will see how we can generate the same report using the thru SharePoint online admin center URL.

Let's follow the below steps to do this.

We need to generate a view to list down the hub site report using the hub site association.

Syntax: https://tenant-admin.sharepoint.com

Example: https://globalsharepoint2019-admin.sharepoint.com/

If we navigate to SharePoint online admin center URL, we will get the below SharePoint admin center page. Then need to click on "Active Sites" link from the left-side panel.

Then from the "Active sites" dashboard click on "Hub" -> Filter by Hub -> then select your hub site – this will list down all sites that are connected to that particular hub site.

For example, we have selected my hub site "SP Hub Site"

Now we can see all sites that are connected to the hub site "SP Hub Site"

How to export the hub sites report in Excel?

Similarly, we can export this report in CSV file and we can filter that site report file from the local excel to have a list of all the sites that are part of the same Hub.

To do this we need to follow the below steps,

From the "Active sites" dashboard click on the "Export" button. This will export all SharePoint online sites in the CSV file to your local download folder.

We will get the active sites report as below – from there we can filter as per our need.

Download

The above code can be downloaded from the below two locations,

Summary

In this article, we learned the below concepts with respect to hub sites report in SharePoint online:

Get all sites associated with a particular hub site using PnP PowerShell and SharePoint online URL.
Get all hub sites and their associated sites from a tenant using PnP PowerShell and SharePoint online URL.
How to use the Get-PnPTenantSite command.
How to use Get-PnPHubSite.
How to export the report in a CSV file using the export-csv command.