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. 
  1. CLS  
  2. #############################################################################################################################################################  
  3. #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.  
  4. #Created By: Habibur Rahaman  
  5. #Date: 12-22-2019  
  6. #############################################################################################################################################################  
  7.   
  8.   
  9. ###################variables section###################################  
  10. $userName = "Global-sharepoint2019@globalsharepoint2019.onmicrosoft.com - <Your User name>"  
  11. $passWord = "YourPassWord"  
  12. $siteURL="https://globalsharepoint2019-admin.sharepoint.com/ <Your sharepoint admin url>"  
  13. ###################variables section ends here###################################  
  14. $encPassWord = convertto-securestring -String $passWord -AsPlainText -Force  
  15. $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userName, $encPassWord  
  16.   
  17. Connect-PnPOnline -Url $siteURL -Credentials $cred  
  18.   
  19. #Getting the hub site id for which we want to generate the report - those are connected to this hub site.  
  20. $hubSiteURL="https://globalsharepoint2019.sharepoint.com/sites/SPHubSite"  
  21. $hubSite = Get-PnPTenantSite $hubSiteURL    
  22. $hubSiteId = $hubSite.HubSiteId  
  23. write-host " #####Generating sites connected a single hub site report######: " -BackgroundColor DarkGreen  
  24. write-host "Hub Site URL: " $hubSiteURL  
  25.   
  26.   
  27. $associatedSites = @()  
  28.   
  29.   
  30. #Get all sites associated to the hub site(in the above hub site)  
  31. $sitesTenant = Get-PnPTenantSite -Detailed   
  32. $sitesTenant | select url | % {$oneSite = Get-PnPTenantSite $_.url   
  33.   
  34.   if($oneSite.hubsiteid -eq $hubSiteId)  
  35.   {  
  36.       
  37.     write-host "Associated Site URL: " $oneSite.url  
  38.   
  39.      $assocatedSiteObject = New-Object PSObject       
  40.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site URL" -value $hubSiteURL  
  41.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site ID" -value $hubSiteId  
  42.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -Name "Associated Site URL" -value $oneSite.Url  
  43.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Associated Site Status" -value $oneSite.Status  
  44.        
  45.   
  46.      #Add the object with property to an Array  
  47.      $associatedSites += $assocatedSiteObject  
  48.   
  49.   }  
  50. }  
  51.   
  52. #Export the site array collection to a CSV file  
  53. $associatedSites | Export-CSV "C:\Temp\GetAllSitesAssociatedInHubSites\SitesConnectedToSingleHubSiteReprot.csv" -NoTypeInformation    
  54. write-host " #####Generating sites connected a single hub site report- ends here######: " -BackgroundColor DarkYellow  
  55.   
  56. ######The below script will list down all hub sites and their associated connected sites in the tenant.##################  
  57. write-host "------------------------------------------------------------------------------------------------------"  
  58.   
  59. write-host " #####Generating master hub sites along with connected sites report for the tenant. ######:" -BackgroundColor DarkGreen  
  60.   
  61. $hubSites=Get-PnPHubSite  
  62. $associatedSites = @()  
  63. foreach($oneHubSite in $hubSites)  
  64. {  
  65.   
  66.    $test=$oneHubSite;  
  67.    write-host "Hub Site URL: " $oneHubSite.SiteUrl  
  68.   
  69.    $hubSite = Get-PnPTenantSite $oneHubSite.SiteUrl;    
  70.    $hubSiteId = $hubSite.HubSiteId  
  71.    
  72. #Get all sites associated to the hub site(in the above hub site)  
  73. $sitesTenant = Get-PnPTenantSite -Detailed   
  74. $sitesTenant | select url | % {$oneSite = Get-PnPTenantSite $_.url   
  75.   
  76.   if($oneSite.hubsiteid -eq $hubSiteId)  
  77.   {  
  78.     write-host "Associated Site URL: " $oneSite.url  
  79.     $assocatedSiteObject = New-Object PSObject  
  80.            
  81.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site URL" -value $oneHubSite.SiteUrl  
  82.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Hub Site ID" -value $oneHubSite.ID  
  83.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -Name "Associated Site URL" -value $oneSite.Url  
  84.      $assocatedSiteObject | Add-Member -MemberType NoteProperty -name "Associated Site Status" -value $oneSite.Status  
  85.        
  86.   
  87.      #Add the object with property to an Array  
  88.      $associatedSites += $assocatedSiteObject  
  89.   
  90.   }  
  91. }  
  92.   
  93.   
  94. }  
  95. #Export the site array collection to a CSV file  
  96. $associatedSites | Export-CSV "C:\Temp\GetAllSitesAssociatedInHubSites\SitesConnectedToHubSiteReprotForTenant.csv" -NoTypeInformation    
  97. write-host "##### Generating master hub sites along with connected sites report for the tenant ends here ######:" -BackgroundColor DarkYellow  
  98.   
  99. ######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.
  1. (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, 
  1. ###################variables section###################################  
  2. $userName = "Global-sharepoint2019@globalsharepoint2019.onmicrosoft.com - <Your User name>"  
  3. $passWord = "YourPassWord"  
  4. $siteURL="https://globalsharepoint2019-admin.sharepoint.com/ <Your SharePoint admin url>"  
  5. $hubSiteURL="https://globalsharepoint2019.sharepoint.com/sites/SPHubSite<Hub Site URL>"  
  6. ###################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,
 
Hub Sites Report In SharePoint Online Using PnP
 
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.
 
Hub Sites Report In SharePoint Online Using PnP
 

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.
 
Login to the SharePoint admin center URL.
 
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.
 
Hub Sites Report In SharePoint Online Using PnP
 
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"
 
Hub Sites Report In SharePoint Online Using PnP
 
Now we can see all sites that are connected to the hub site "SP Hub Site"
 
Hub Sites Report In SharePoint Online Using PnP
 

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.
 
Hub Sites Report In SharePoint Online Using PnP
 
We will get the active sites report as below – from there we can filter as per our need.
 
Hub Sites Report In SharePoint Online Using PnP
 

Download

 

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.