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. #############################################################################################################################################################
  9. ###################variables section###################################
  10. $userName = "[email protected] - <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
  17. Connect-PnPOnline -Url $siteURL -Credentials $cred
  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
  27. $associatedSites = @()
  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
  34. if($oneSite.hubsiteid -eq $hubSiteId)
  35. {
  37. write-host "Associated Site URL: " $oneSite.url
  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
  46. #Add the object with property to an Array
  47. $associatedSites += $assocatedSiteObject
  49. }
  50. }
  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
  56. ######The below script will list down all hub sites and their associated connected sites in the tenant.##################
  57. write-host "------------------------------------------------------------------------------------------------------"
  59. write-host " #####Generating master hub sites along with connected sites report for the tenant. ######:" -BackgroundColor DarkGreen
  61. $hubSites=Get-PnPHubSite
  62. $associatedSites = @()
  63. foreach($oneHubSite in $hubSites)
  64. {
  66. $test=$oneHubSite;
  67. write-host "Hub Site URL: " $oneHubSite.SiteUrl
  69. $hubSite = Get-PnPTenantSite $oneHubSite.SiteUrl;
  70. $hubSiteId = $hubSite.HubSiteId
  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
  76. if($oneSite.hubsiteid -eq $hubSiteId)
  77. {
  78. write-host "Associated Site URL: " $oneSite.url
  79. $assocatedSiteObject = New-Object PSObject
  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
  87. #Add the object with property to an Array
  88. $associatedSites += $assocatedSiteObject
  90. }
  91. }
  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
  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 = "[email protected] - <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.