SharePoint Online Tutorial: How to obtain counts of all sites by site template

Article
1/17/2024

Introduction

Some useful metrics for SharePoint Online administrators and IT managers are the:

Total number of sites associated with their O365 subscription,
How they are categorized (i.e., by site template) and
the number of sites in each category.

Total number of sites

Knowing the total number of sites provides a sense of the depth of organizational usage of the SharePoint portion of the O365 subscription and knowing the number of categories and how many sites are in each category can help provide a sense of the breadth of that usage. For example, if the SharePoint Admin has enabled the Create site command, it would be informative to know how many Teams and Communication sites users have created. That metric is not currently available in the SharePoint Admin center or in any other admin center.

Delve blogs

If the SharePoint Admin has enabled Office Graph, which in turn enables Delve, it would be informative to know how many staff members have created their own Delve blogs. Again, this metric is not currently available in the SharePoint Admin center or in any other of the O365 admin centers.

OneDrive usage

If your organization has enabled OneDrive, every staff member using his or her OneDrive also has a SharePoint MySite. While the OneDrive Usage report in the O365 Report Center displays a useful chart of OneDrive usage, it doesn't break down OneDrive usage by MySite types, of which there are eleven (SPSPERS#0 - SPSPERS#10). Nor is a listing of these sites provided in the OneDrive or SharePoint Admin centers.

Site types

Lastly, the SharePoint Admin center does not provide a breakdown of the various collaboration, enterprise, publishing and custom site types that may have been created. While site collections created through the SharePoint Admin center interface will be listed at the center, that listing does not identify the type of site. However, this information can be obtained in just a few minutes using the following three tools:

SharePoint Online Management Shell
SharePoint Online PowerShell
Microsoft Excel

The following procedure shows you how.

Procedure

Launch the SharePoint Management Shell. No need to launch it elevated as you would normally for executing on-prem. Just launch it normally.
Once it appears, execute the following command to establish a connection to your SharePoint Online:

Connect-SPOService -Url "https://contoso-admin.sharepoint.com" -Credential "[your email address]"
Now execute this command to pull a listing of sites:

Get-SPOSite -Limit "All" -IncludePersonalSite $True | Export-CSV -Path "C:\temp\SiteListing.csv"
Open the CSV file in Microsoft Excel. Delete the first line, and then save the file as an Excel file. The name on the first spreadsheet tab will be the filename.
Select the Template column. Then, select the Data tab on the ribbon, and then, in the Sort & Filter group, click Advanced and click OK at the popup prompt
Enable Unique records only, and then click OK. The listing will collapse to show a short listing of all templates.
Add another spreadsheet to the workbook. Let's call it Categories. Then, select just the list of templates, and then copy and past as data only to the new spreadsheet (right-click in a cell, and then choose Past Options - Values).

Sort the column. Here's an example template listing we'll use for the rest of this procedure.

Template	Count
APPCATALOG#0
BDR#0
BLANKINTERNET#0
BLOG#0
COMMUNITY#0
COMMUNITYPORTAL#0
EDISC#0
EHS#1
ENTERWIKI#0
GROUP#0
OFFILE#1
POINTPUBLISHINGHUB#0
POINTPUBLISHINGPERSONAL#0
POINTPUBLISHINGTOPIC#0
PROJECTSITE#0
PWA#0
SITEPAGEPUBLISHING#0
SPSMSITEHOST#0
SPSPERS#2
SPSPERS#6
SPSPERS#9
SRCHCEN#0
STS#0
STS#-1
visprus#0

Go back to the first spreadsheet, and then on the Data tab, in the Sort $ Filter group, click Clear.
On the Categories spreadsheet, enter the following in the column cell just to the right of the first template name listed:

=Countif(

now go back to the first tab, and then select all data just in the Template column. Once you select everything, hit ENTER. A message prompt will appear - just click OK and ignore it. You'll automatically return to the Categories spreadsheet and you will see something similar to the following:

=countif(SiteListing!T2:T6898

Then enter a "$" just in front of each cell ID so that you have something like the following:

=countif(SiteListing!$T$2:$T$6898,

Now add a comma "," then click on the cell containing that first template name, and then add a closing parenthesis. Now you will have something like the following:

=countif(SiteListing!$T$2:$T$6898,Categories!A2)
Hit Enter. This counts all the occurrences of the first template name in the column you selected on the first spreadsheet.

Now select the cell you entered the equation into. Hover the cursor over the bottom right corner of the cell until the cursor changes to a cross "+", and then click, hold and drag down. This populates each of the following cells with that equation, updating cell IDs as it goes down. Because you entered "$" just in front of those two other IDs, these will remain fixed as you drag down, meaning that the same range will be used for all of the different template names. And just like that, you get a count of all of the different SharePoint sites have been created in your subscription. Here's an example:

Template	Count
APPCATALOG#0	1
BDR#01	5
BLANKINTERNET#01	2
BLOG#01	2
COMMUNITY#01	18
COMMUNITYPORTAL#0	1
EDISC#0	2
EHS#1	1
ENTERWIKI#0	1
GROUP#0	923
OFFILE#1	10
POINTPUBLISHINGHUB#0	1
POINTPUBLISHINGPERSONAL#0	1045
POINTPUBLISHINGTOPIC#0	1
PROJECTSITE#0	1
PWA#0	15
SITEPAGEPUBLISHING#0	45
SPSMSITEHOST#0	1
SPSPERS#2	678
SPSPERS#6	1252
SPSPERS#9	3723
SRCHCEN#0	1
STS#0	217
visprus#0	1
TOTAL	7947

As a followup, use the SUM function to get a grand total of all of the site type subtotals.

References

Notes

To get a list of site templates, like what I provided here, open the CSV file you get from executing Get-SPOSite. Then select the Template column. On the ribbon, in the Sort & Filter group, click Advanced, enable the Unique records only setting, and then click OK.
Though the OneDrive online UI lacks the usual administration links, append the settings (_layouts/15/settings.aspx) or site contents (/_layouts/15/viewlsts.aspx) paths to get to the MySite's administration interfaces.
To enable user site creation: O365 Admin Center > SharePoint Admin Center > Settings > Site Creation > Show the Create site command.

Share via