-1

I got this pnp PowerShell to retrieve hub sites and associated sites. Can someone please help on how to get the output to a csv with the header? Right now it's writing to the host.

List all sites being a hub site or associate to a hub site

$results = Submit-PnPSearchQuery -Query 'contentclass=sts_site' -RefinementFilters 'departmentid:string("{*",linguistics=off)' -TrimDuplicates $false -SelectProperties @("Title","Path","DepartmentId","SiteId") -All -RelevantResults

Filter out the hub sites

$hubSites = $results |? { $_.DepartmentId.Trim('{','}') -eq $_.SiteId  }

Loop over the hub sites

foreach( $hub in $hubSites ) {
    Write-Host $hub.Title - $hub.Path -ForegroundColor Green
    # Filter out sites associated to the current hub
    $associatedSites = ($results |? { $_.DepartmentId -eq $hub.DepartmentId -and $_.SiteId -ne $hub.SiteId })
    foreach($site in $associatedSites) {
        Write-Host "`t"$site.Title - $site.Path -ForegroundColor Yellow
    }
}
0

Export-csv is your friend , both for exporting listitems and custom objects : https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6

0

A demo about exporting SharePoint list data to CSV using PowerShell for your reference:

 #Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items where Status is "In Progress"
 $list.Items |  Where-Object { $_["Status"] -eq "In Progress"} | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Status" -value $_["Status"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Priority" -value $_["Priority"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.txt" -NoTypeInformation                       

#Dispose the web Object
$web.Dispose()

You can change it as your requirement.

Export SharePoint List Items to CSV using PowerShell

0

Using the below PnP PowerShell script we can generate all hub sites and their associated sites from a tenant and also all associated sites for a particular hub site:

PnP PowerShell script to get all hub sites and their all associated sites inventory from a tenant:

CLS
$userName = "Global-sharepoint2019@globalsharepoint2019.onmicrosoft.com"
$passWord = "YourPassWord"
$encPassWord = convertto-securestring -String $passWord -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userName, $encPassWord

Connect-PnPOnline -Url "https://globalsharepoint2019-admin.sharepoint.com/" -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##################

Output csv for all hub sites and their all associated sites:

enter image description here

Output csv for all associated sites connected to a particular hub site:

enter image description here

For more details please refer the below article:

Retrieve hub sites and associated sites using PnP Powershell

  • Since you are retrieving all the sites in the tenant and then filtering by hubsite url...I think this method can be quite slow on busy tenants, dont you think so? – Alberto Jan 3 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.