Admin Reports Client Side Object Model (CSOM) CSV Office 365 PnP PowerShell PowerShell SharePoint SharePoint Online SharePoint Online Management Shell Site Collection 

SharePoint Online: Get All Site Collections using PowerShell

Salaudeen Rajack 3 Comments , , , , , , , , , ,

Requirement: Get all site collections using PowerShell in SharePoint Online.

How to Get a List of Site Collections in SharePoint Online?

A site collection is a group of sites, generally organized by department, project, region, function, or business unit. If you’re responsible for managing a SharePoint Online environment, sooner or later, you’ll need to get a list of all the site collections in your tenancy. Thankfully, PowerShell makes gathering information about your SharePoint Online environment easy. This blog post will show you how to use PowerShell to get a list of all the site collections in your tenant. You will also learn how to export the results into a CSV or HTML file for reporting purposes.

View All Sites in SharePoint Online Tenant using SharePoint Admin Center

How to view all site collections in SharePoint Online? Well, If you want to get a list of SharePoint Online site collections, you can go to the SharePoint admin center (https://YourDomain-admin.sharepoint.com) >> Expand “Sites” >> Active Sites.

get all sites in sharepoint online

This gets you the list of all site collections in SharePoint Online. You can export them to CSV by clicking on the “Export” button in the toolbar. Let’s see the SharePoint Online PowerShell to get all site collections.

How to Get a Site Collection in SharePoint Online using PowerShell?

As a SharePoint Online administrator, you need to retrieve information about a specific site collection in your SharePoint environment. To get a site collection in SharePoint Online using PowerShell, you can follow these steps:

  1. Open PowerShell on your local machine.
  2. Connect to your SharePoint Online tenant using the Connect-SPOService cmdlet. You will need to provide your SharePoint Online admin URL and credentials.
  3. Run the Get-SPOSite cmdlet to retrieve the site collection that you want. For example, to retrieve the site collection with the URL “https://crescent.sharepoint.com/sites/marketing”, you would run the following command: Get-SPOSite -Identity https://crescent.sharepoint.com/sites/marketing This will return information about the site collection, including its URL, title, and owner.
  4. If you want to retrieve additional information about the site collection, you can use other PowerShell cmdlets, such as Get-SPOSiteGroup or Get-SPOSiteGroupPermissions.
#Parameters
$AdminSiteURL="https://crescent-admin.sharepoint.com"
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"

#Connect to SharePoint Online from PowerShell
Connect-SPOService -url $AdminSiteURL

#Get the Site Collection
$Site =  Get-SPOSite -Identity $SiteURL

#sharepoint online list all site collections powershell
$Site | Select -Property Template, StorageUsageCurrent, StorageQuota, LastContentModifiedDate, sharingcapability

Please note, To run PowerShell cmdlets for SharePoint Online, and you need to have the SharePoint Administrator role on the tenant and the SharePoint Online Management Shell (or the SharePoint Online PowerShell module) installed on your local machine.

How to Get All Site Collections in SharePoint Online using PowerShell?

Site collections are the primary objects in SharePoint Online. Use the Get-SPOSite PowerShell cmdlet to list all site collections in the tenant. Similar to On-Premises SharePoint Management Shell, You can also use “SharePoint Online Management Shell” to connect to Office 365, and SharePoint Online sites! Here is the PowerShell script to get all site collections in SharePoint Online:

Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

$AdminSiteURL="https://crescent-admin.sharepoint.com"

$Credential = Get-credential 
Connect-SPOService -url $AdminSiteURL -Credential $Credential 

#sharepoint online list all site collections powershell
Get-SPOSite -Limit All | Format-Table Url, Template, StorageUsageCurrent, StorageQuota, LastContentModifiedDate, sharingcapability -AutoSize

By default, the Get-SPOSite cmdlet retrieves 200 site collections. We must specify the “Limit” switch with “All” to get all sites from the tenant. Here is the PowerShell script to get all site collections with detailed info from SharePoint Online and Export All Site Collection inventory to CSV:

Connect-SPOService -url "https://crescent-admin.sharepoint.com" -Credential (Get-credential)
Get-SPOSite -Detailed | Export-CSV -LiteralPath C:\Temp\SitesInventory.csv -NoTypeInformation

This PowerShell lists all site collections in SharePoint Online tenant along with all available site collection details.

get all site collections inventory in sharepoint online using powershell

PowerShell to Get All Site Collections in SharePoint Online

How about connecting to SharePoint Online with saved credentials? Here is the PowerShell to list all SharePoint Online sites using saved credentials (I’m assuming the account you specified in the script doesn’t have the MFA enabled!):

#Load SharePoint Online Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Variables for Processing
$AdminUrl = "https://crescent-admin.sharepoint.com/"
$UserName= "Salaudeen@crescent.com"
$Password = "Password goes here"
 
#Setup Credentials to connect
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName, $SecurePassword

#connect to the service
Connect-SPOService -Url $AdminUrl -Credential $Credentials

#sharepoint online get all site collections PowerShell
$SiteColl = Get-SPOSite 

#sharepoint online PowerShell iterate through all site collections
ForEach($Site in $SiteColl)
{
    Write-host $Site.Url
}

PowerShell to Get All Site Collections, including Modern Team sites and Group Sites:

This PowerShell enumerates site collections in SharePoint Online and gets the URL for each site.

Let’s get the list of all sites, including Modern team sites and group-connected sites in SharePoint Online using PowerShell.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
  
#Get All Site collections from the Tenant- Including Modern Team sites and communication sites
Function Get-SPOSites($AdminSiteURL, $Cred)
{
    #Setup credentials to connect
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
   
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminSiteURL)
    $Ctx.Credentials = $Credentials
  
    #Get the tenant object 
    $Tenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($ctx)
  
    #Get All Site Collections
    $SiteCollections=$Tenant.GetSitePropertiesFromSharePoint(0,$true)
    $Ctx.Load($SiteCollections)
    $Ctx.ExecuteQuery()
 
    #Iterate through Each site collection
    ForEach($Site in $SiteCollections)
    {
        Write-host $Site.URL
    }
}
  
#Set Parameters
$AdminSiteUrl = "https://crescent-admin.sharepoint.com/"
$Cred= Get-Credential

#sharepoint online powershell list all sites
Get-SPOSites -AdminSiteURL $AdminSiteUrl -Cred $Cred

SharePoint Online PowerShell to List All Site Collections

Let’s get all site collections under a specific path: 

Import-Module Microsoft.Online.SharePoint.Powershell

#Config Parameters
$AdminSiteURL="https://crescent-admin.sharepoint.com"

#Get Credentials to connect to the SharePoint Admin Center
$Cred = Get-Credential

#Connect to SharePoint Online Admin Center
Connect-SPOService -Url $AdminSiteURL -Credential $Cred

#Get All site collections which has /sites in its path
Get-SPOSite -Filter { Url -like '*/sites*' } -Limit All

#Get All site collections of Group site template
#Get-SPOSite -Filter { Template -eq "GROUP#0" } -Limit All

You can filter and get all site collections based on some criteria. E.g., let’s get all sites that are using > 1 GB storage.

Get-SPOSite -Limit All | where { $_.StorageUsageCurrent -gt 1024 }

To filter and get collections: E.g., get all site collections with “Project*” in their URL. 

Get-SPOSite -Filter {$_.url -like "https://tenant.sharepoint.com/sites/Project*"}

SharePoint Online PnP PowerShell to Get All Sites

Need to get a list of all the site collections in your SharePoint Online environment? PnP PowerShell to the rescue! This script shows you how to use the PnP PowerShell cmdlet Get-PnPTenantSite to get a list of all site collections in your tenant.

#Set Parameter
$TenantSiteURL="https://crescent.sharepoint.com"

#Connect to the Tenant site
Connect-PnPOnline $TenantSiteURL -Credentials (Get-Credential)

#sharepoint online pnp powershell get all sites
Get-PnPTenantSite

We can extract all site collection details and export them to CSV using the following: 

#Config Variables
$TenantSiteURL =  "https://crescent-admin.sharepoint.com/"
$CSVFilePath = "C:\Temp\AllSitesData.csv"

#Connect to Tenant Admin Site
Connect-PnPOnline -Url $TenantSiteURL -Interactive

#Get All Site collections data and export to CSV
Get-PnPTenantSite -Detailed | Select Title, URL, Owner, LastContentModifiedDate, WebsCount, Template, StorageUsage | Export-Csv -path $CSVFilePath -NoTypeInformation

This gets all site collections, including modern team sites and communication sites. Let’s skip the search center, My site Host, App Catalog, Content Type Hub, eDiscovery and Bot, etc., and get the quota of all sites in the tenant:

#Parameter
$TenantAdminURL = "https://Crescent-admin.sharepoint.com"

#Connect to Admin Center
Connect-PnPOnline -Url $TenantAdminURL -Interactive
   
#Get All Site collections - Exclude: Seach Center, Redirect site, Mysite Host, App Catalog, Content Type Hub, eDiscovery and Bot Sites
$SiteCollections = Get-PnPTenantSite | Where -Property Template -NotIn ("SRCHCEN#0", "REDIRECTSITE#0", "SPSMSITEHOST#0", "APPCATALOG#0", "POINTPUBLISHINGHUB#0", "EDISC#0", "STS#-1")
   
#Loop through each site collection
ForEach($Site in $SiteCollections)
{
    #Get the storage Quota of the site
    Write-host $Site.URL - $Site.StorageQuota
}

You can include OneDrive sites as:

#Get all site collections including OneDrive Sites
Get-PnPTenantSite -IncludeOneDriveSites

Get Site Collections List in an HTML Report format

Let’s get a list of all site collections in a friendly HTML format using PowerShell:

Import-Module Microsoft.Online.SharePoint.Powershell -DisableNameChecking

#Config Parameters
$AdminSiteURL="https://crescent-admin.sharepoint.com"
$ReportOutput="C:\Temp\SitesRpt.html"

#CSS Styles
$HeadTag = @"
<style type="text/css">

table {
 border-collapse: collapse; font-family: verdana,arial,sans-serif;
 font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9;
 border: b1a0c7 0.5pt solid; border-spacing: 1px; border-collapse: separate; /*Sal Table format */  
}

th {
border-width: 1px; padding: 5px; background-color:#8064a2;
border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; 
color: white;  font-size: 11pt;  font-weight: 700;  text-decoration: none;
}

td {
 border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; 
 font-size: 11pt; font-weight: 400; text-decoration: none; 
}

tr:nth-child(even) { background-color: #e4dfec; }
tr:hover { background-color: #694D8C; color:#ffffff }

</style>
"@

$PreContentTag = "<h3> SharePoint Online: Site Collections Inventory Report </h3>"

#Get Credentials to connect to the SharePoint Admin Center
$Cred = Get-Credential

#Connect to SharePoint Online Admin Center
Connect-SPOService -Url $AdminSiteURL -Credential $Cred

#Get All site collections
$SiteCollections = Get-SPOSite -Limit All
Write-Host "Total Number of Site collections Found:"$SiteCollections.count -f Yellow

#Get the site collection details, convert to html and output to the report
$SiteCollections | ConvertTo-HTML -Title "Site Collection Inventory Report" -Property URL, Title,@{Expression={$_.LastContentModifiedDate }; Label="Last Modified"} -Head $HeadTag -PreContent $PreContentTag | Out-File $ReportOutput

And the report looks like this:

sharepoint online site collections html report

Wrapping up

In conclusion, PowerShell provides a powerful and efficient way to retrieve a list of all site collections in SharePoint Online. By using the appropriate PowerShell cmdlets, SharePoint administrators can quickly and easily retrieve a comprehensive list of all site collections in their environment. This information can be used for various management and auditing tasks, such as auditing, troubleshooting issues, tracking usage, and optimizing the SharePoint environment.

Here is another article on using PowerShell with SharePoint Online: How to Use PowerShell with SharePoint Online?

How do I create a SharePoint Online site collection using PowerShell?

You can create new sites in SharePoint Online using SharePoint Online Management Shell – New-SPOSite, PnP PowerShell cmdlet New-PnPTenantSite, or CSOM PowerShell scripts.
More info: PowerShell to Create a new site collection in SharePoint Online

How do I get all subsites in SharePoint Online using PowerShell?

To get all subsites from a SharePoint Online site, use: Get-PnPSubWeb cmdlet in PnP PowerShell. You can also use CSOM PowerShell scripts to load get webs (subsites) of a site collection.
More info: PowerShell to Get All Subsites in a Site Collection in SharePoint Online

How do I get all SharePoint sites and subsites in PowerShell?

To get all site collections and subsites, You have to get the web collection recursively in CSOM. With PnP PowerShell: Get-PnPSubWeb -Recurse
More info: PowerShell to get all site collections and subsites in SharePoint Online

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

3 thoughts on “SharePoint Online: Get All Site Collections using PowerShell”

  • If I have 2 Lakhs Site, using above will take time or gets timed out or it can cause performance issues at Server level.

    Is there a way to get it Batch wise ?

    Reply

  • Hi Sir,

    How can we separate all Modern Team sites and Teams site? Do you have any script for this requirement?

    Could you please help me on this.

    Thanks,
    Srinivas

    Reply

    • Simple! Use: Get-SPOSite -Limit All -Template ‘GROUP#0’ -IncludePersonalSite:$False

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *