Get the list all sites and subsites in SharePoint online using Powershell

SR VSP 1,251

Guys,

I'm looking for a script to get the list of all sites and subsites in SharePoint online using PowerShell and export the file in to CSV any advise here ?

Tried with PNP but unable authorize my admin account so any other methods would be helpful.

SR VSP 1,251

Tried this but how to export in to CSV ?

Import-Module Microsoft.Online.SharePoint.Powershell
 
#Config Parameters
$AdminSiteURL="https://contoso-admin.sharepoint.com"
 
#Get Credentials to connect to SharePoint Admin Center
$Cred = Get-Credential
 
#Function to get all subsites of a site
Function Get-SPOWeb($WebURL)
{
    #Setup credentials to connect
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
    #Get Web information and subsites
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($webURL)
    $Context.Credentials = $Credentials
    $Web = $context.Web
    $Context.Load($web)
    $Context.Load($web.Webs)
    $Context.executeQuery()
  
    #Iterate through each subsite in the current web
    foreach ($Subweb in $web.Webs)
    {
        #Get the web object
        $Subweb
 
        #Call the function recursively to process all subsites underneath the current web
        Get-SPOWeb($Subweb.url)
    }
}
 
#Connect to SharePoint Online Admin Center
Connect-SPOService -Url $AdminSiteURL -Credential $Cred
 
#Get All site collections
$SiteCollections = Get-SPOSite -Limit All
 
#Traverse through each site collection and get their subsits
Foreach ($Site in $SiteCollections)
{
    Write-Host $Site.Url    
    $AllWebs = Get-SPOWeb $Site.Url
    $AllWebs | ForEach-Object { Write-Host $_.URL }
}

SR VSP 1,251 Reputation points

2023-10-04T02:20:14.1966667+00:00

@RaytheonXie_MSFT any help with below script to export in to CSV file
Pfeiffer Andreas - UCI 195 Reputation points

2024-07-25T10:14:23.26+00:00

The simpliest way is to create an application in azure with a self-signed certificate and the needed scopes. Then you connect this way to get access on all sharepoints site content in your tenant :

Connect-PnPOnline -Url <site url> -ClientId <client id> -Tenant <your tenant> -Thumbprint <thumbprint of the certificate>

2 answers

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-03T02:50:00.44+00:00
Hi @SR VSP，

During my investigation, I found a post with the same needs as yours. The following code is for your reference:

Please run following PowerShell as administrator.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0 Start-SPAssignment –Global $spSites = Get-SPSite -Limit All $spWAs = Get-SPWebApplication -IncludeCentralAdministration $csvs = @() #Get Web Application Data Write-Host("Getting Web Application Data") $spWAs | select Name, DisplayName, Id, Url, ApplicationPool, @{Expression={$_.ApplicationPool.Name}}, @{Expression={$_.ApplicationPool.UserName}}, DefaultQuotaTemplate | Export-CSV $bkdir\SPWebApplication.csv $csvs += C:\SPWebApplication.csv #Get Site Collection Data Write-Host("Getting Site Collection Data") $spSites | select Id, HostName, Url, Owner,WebApplication, ContentDatabase, RootWeb, LastContentModifiedDate, @{Expression={$_.Usage.Storage}} | Export-CSV $bkdir\SPSite.csv $csvs += C:\SPSite.csv #Get Site Data Write-Host("Getting Site Data") $spSites | Get-SPWeb -Limit All | select ID, Title, Author, Url, WebTemplate, LastItemModifiedDate, IsRootWeb | Export-CSV $bkdir\SPWeb.csv $csvs += C:\SPWeb.csv Stop-SPAssignment –Global

Reference:

https://learn.microsoft.com/en-us/answers/questions/1187774/script-to-get-all-site-collections-and-subsites-fr

https://gist.github.com/ciphertxt/52287bc26b8bc95a89d0

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng
Please sign in to rate this answer.
SR VSP 1,251 Reputation points

2023-10-03T12:40:38.0966667+00:00

Cheng - I'm looking in SharePoint Online.

@Emily Du-MSFT

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-04T08:58:55.11+00:00

Hi @SR VSP，

Please try:

$AdminSiteURL = "https://yoursite-admin.sharepoint.com/" # Get SharePoint Online admin center credentials $Cred = Get-Credential # Connect to SharePoint Online admin center Connect-SPOService -Url $AdminSiteURL -Credential $Cred # Initialize an array to store site information $SiteInfoArray = @() # Get all site collections $SiteCollections = Get-SPOSite -Limit All # Loop through each site collection foreach ($Site in $SiteCollections) { Write-Host "Site Collection URL: $($Site.Url)" # Create an object to store the site information for the main site $SiteInfo = New-Object PSObject -Property @{ "SiteCollectionURL" = $Site.Url "SubsiteURL" = $null # Set to null as there are no subsites } # Add the main site information object to the array $SiteInfoArray += $SiteInfo # Get all subsites of the current site collection $SubSites = Get-SPOSite -Limit All -Filter "Url -like '$($Site.Url)/*'" # Loop through each subsite (if any) foreach ($SubSite in $SubSites) { Write-Host "Subsite URL: $($SubSite.Url)" # Create an object to store the site information for the subsite $SiteInfo = New-Object PSObject -Property @{ "SiteCollectionURL" = $Site.Url "SubsiteURL" = $SubSite.Url } # Add the subsite information object to the array $SiteInfoArray += $SiteInfo } } Write-Host "Total items in the array: $($SiteInfoArray.Count)" Start-Sleep -Seconds 5 # Export the site information to a CSV file $SiteInfoArray | Export-Csv -Path "C:\Temp\SharePointSites.csv" -NoTypeInformation # Disconnect from SharePoint Online Disconnect-SPOService

Here is test result:

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

SR VSP 1,251 Reputation points

2023-10-04T13:51:18.1833333+00:00

cheng - Thank you for your response but in the final output of the CSV file there are no subsites.

Ex: SiteA has 3 subsites like Sub1,Sub2,Sub3 these are not showing in the output file

https://Contoso.sharepoint.com/sites/Master

https://Contoso.sharepoint.com/sites/Master/Sub1

https://Contoso.sharepoint.com/sites/Master/Sub2

https://Contoso.sharepoint.com/sites/Master/Sub3

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-05T06:24:22.44+00:00

Hi @SR VSP，

Sorry, I seem to be complicating the issue.

Maybe you just need to create an array to collect site and subsite URLs and then export to CSV

You can define array to collect and then import csv

# Initialize an array to store site information $SiteInfoArray = @() $SubSite

Import csv after final collection

# Export the site information to a CSV file $SiteInfoArray | Export-Csv -Path "C:\Temp\SharePointSites.csv" -NoTypeInformation

I made modifications based on the code you provided

# Import the SharePoint Online Management Shell module Import-Module Microsoft.Online.SharePoint.Powershell # Configuration Parameters $AdminSiteURL = "https://your-admin.sharepoint.com" $CSVFilePath = "C:\Temp\SharePointSites.csv" # Specify the path for the CSV file # Get Credentials to connect to SharePoint Admin Center $Cred = Get-Credential # Initialize an array to store site information $SiteInfoArray = @() # Function to get all subsites of a site Function Get-SPOWeb($WebURL) { # Setup credentials to connect $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password) # Get Web information and subsites $Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebURL) $Context.Credentials = $Credentials $Web = $Context.Web $Context.Load($Web) $Context.Load($Web.Webs) $Context.executeQuery() # Iterate through each subsite in the current web foreach ($Subweb in $Web.Webs) { # Get the web object $Subweb # Create an object to store the site information $SiteInfo = New-Object PSObject -Property @{ "SiteURL" = $Web.Url "SubsiteURL" = $Subweb.Url } # Add the site information object to the array $SiteInfoArray += $SiteInfo # Call the function recursively to process all subsites underneath the current web Get-SPOWeb $Subweb.Url } } # Connect to SharePoint Online Admin Center Connect-SPOService -Url $AdminSiteURL -Credential $Cred # Get All site collections $SiteCollections = Get-SPOSite -Limit All # Traverse through each site collection and get their subsites Foreach ($Site in $SiteCollections) { Write-Host "Processing Site Collection: $($Site.Url)" $AllWebs = Get-SPOWeb $Site.Url } # Output the array to CSV file $SiteInfoArray | Export-Csv -Path $CSVFilePath -NoTypeInformation # Disconnect from SharePoint Online Disconnect-SPOService

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-10T06:57:27.3366667+00:00

Hi @SR VSP，

I am kindly following up to see if you have any remaining questions or concerns for this issue. Please feel free to reach us if any questions or concerns you may have. If the answer is helpful, please click "Accept Answer" and kindly upvote it.By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Rod Rosado 0 Reputation points

2023-10-17T13:38:41.85+00:00

I'll respond if no one else is. I tried this on a large installation and it seemed to process several site collections, however, the csv contained no data after the script ended. the only errors I saw were these (which I had assumed were empty site collections:

Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (401) Unauthorized." At line:26 char:5 + $Context.executeQuery() + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : WebException The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested. At line:29 char:14 + foreach ($Subweb in $Web.Webs) + ~~~~~~~ + CategoryInfo : OperationStopped: (:) [], CollectionNotInitializedException + FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-30T06:11:07.6466667+00:00

Hi @Rod Rosado，

When working with SharePoint Online using PowerShell, you may encounter the error “The remote server returned an error (401) Unauthorized.” This error can occur when the script is not properly authenticated to access the SharePoint Online site

Read more: https://www.sharepointdiary.com/2019/06/sharepoint-online-remote-server-returned-an-error-401-unauthorized.html#ixzz8HbJfkwxa

Hi @SR VSP，

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

This will help more users who have the same questions as you to retrieve answers faster

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

Rod Rosado 0 Reputation points

2023-10-30T13:31:15.7266667+00:00

Cheng, then this would not affect why the csv had no data in it, correct? then why would the csv be empty after the completed run?

ChengFeng - MSFT 5,030 Reputation points Microsoft Vendor

2023-10-31T05:47:24.5666667+00:00

Hi @Rod Rosado

Unfortunately, if your authority is insufficient, you cannot obtain the site information of other personnel under the organizational structure. (If you have no permissions, you can access other personnel site information, which actually has very large security risks). And I use the code site in the code to obtain all site information through the administrator permissions.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

Rod Rosado 0 Reputation points

2023-10-31T13:08:40.08+00:00

The account I'm using is a global administrator and a SharePoint Admin just for good measure, so what's missing?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Alvaro Avila 80 Reputation points

2024-07-25T13:48:34.03+00:00

You can use this app: https://github.com/Barbarur/NovaPoint

Free, open-source and easy to use. You can get a report of all your sites and subsites easily and many other reports from your SharePoint Online.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Get the list all sites and subsites in SharePoint online using Powershell

2 answers

Your answer