Get SharePoint Sites and related hub sites using PowerShell

If you’re looking to extract a comprehensive list of all site collections and their associated hub sites in SharePoint, then you’ve come to the right place! With this PowerShell script, you can easily obtain a report with all the relevant information you need.
To begin with, it’s important to note that this script specifically requires the SharePoint Online Management Shell to run successfully. Once you have that set up, you can proceed with executing the script. This script filters out any sites that do not have a related hub to make it run faster.
The final output of this script is a CSV file, which you can open in Microsoft Excel. From there, you can apply filters, and perform further analysis with the data that can help you make informed decisions about your SharePoint environment.

Import-Module Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url https://tenant-admin.sharepoint.com
# Output location for CSV
$ExportLocation = "C:Codepower_shell_OutputsAll_Sites_And_Their_Connected_HubSites.csv"  # Create array object to hold all individual ReportEntry objects
$Report =@()
# Get all the hubsites
$HubSites = Get-SPOHubSite
# Get all sites, exluding sites without a connected hub by filtering them out client side
$Sites = Get-SPOSite -Limit All | ? {$_.HubSiteID.GUID -ne "00000000-0000-0000-0000-000000000000"}
# Iterate through each hub
ForEach($Hub in $HubSites)
{
    # Get the ID of the Hubsite
    $HubSiteId = $Hub.ID.GUID
    # Iterate through each site collection
    ForEach($Site in $Sites)
    {
        # Get the Associated Hubsite ID for this site
        $AssociatedHubSiteID = $Site.HubSiteID.GUID
        # If the associated HubSite ID of this site equals the hubsite id of the hub we are currently checking
        # AND the site url of this site does not equal the hubsite url of the hub we are currently checking
        # then we can create an entry.
        If ( ($AssociatedHubSiteID -eq $HubSiteId) -and ($Hub.SiteUrl -ne $Site.Url) )
        {
            $ReportEntry = "" | Select-Object Hub,SiteUrl # Initialize an empty ReportEntry object, add Hub and SiteURL properties to the object
            $ReportEntry.Hub = $Hub.SiteUrl #Add the hub URL to the Hub property of the object
            $ReportEntry.SiteUrl = $Site.Url #Add the Site URL to the SiteUrl property
            $Report += $ReportEntry # Add the ReportEntry object to the Report array
        }
    }
}
$Report | Export-Csv -Path $ExportLocation -NoTypeInformation # Pipe the Report array to the Export-CSV command to create report
Write-Debug "Finished"