How to list/enumerate SharePoint sites in Microsoft Flow

Flow is not only for creating approval workflows or email notifications. Flow can also be a powerful service for us SharePoint Online tenant administrators.

Sooner or later we will need to list, enumerate, some or all our SharePoint sites. We may need to get some specific details for each site or simply see how many sites we have.

There’s unfortunately no trigger or action in Flow to do this at the moment. One alternative would also be to get this information from the Microsoft Graph but we can’t do that neither as of today.

However, there’s a hidden little golden nugget in SharePoint that we can use for this. Something that has been overlooked by most SharePoint Online administrators.

An easily overlooked fact is that the SharePoint Online administration portal is a site collection. A site collection we can connect to from within a Flow for instance.

If you look closely at how the SharePoint Online administration portal list or enumerate SharePoint sites you can actually see that it’s utilizing a hidden list within the admin site collection.

The SharePoint Online Client Browser is one of my favorite tools and by bringing up the admin site collection in there, or do the same thing in PowerShell or similar, we can see two very interesting lists.

Got to be an engineer who came up with those names.

DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS

DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS

These two lists are quite similar, but the content differ a bit. You can easily dig through them in a developer environment to identify the details, but the most important difference is that the list DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS include personal sites for OneDrive.

The lists contain a lot of information that is truly valuable for SharePoint Online tenant administrators. Not all the field contain any information. There are for instance several fields with names that indicate that they were used to store information about site collection administrators while they are empty today.  

As SharePoint Online administrators we can use these lists in our Flows and do things we’re not able to do otherwise.

Below you will find two simple Flows that utilize one of these hidden lists.

Get a notification when a new site is created

The first Flow will post a message in Microsoft Teams whenever a new SharePoint site is created. We use the SharePoint When an item is created trigger on the DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS list to pick up the new site and the action to post the message in Teams using the Post a message action.

Get a weekly report of deleted sites

The second Flow will run on a weekly schedule, gather a report of all site deleted in the past week and post that as a table in a message in a specific channel in Microsoft Teams.

To get the items I use a Filter Query that look for items (site collections) with a TimeDeleted later than one week ago. The formula uses addDays but a negative number to subtract one week from now.

addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),-7)

A word of caution

These lists are not documented by Microsoft. Microsoft make changes to them from time to time and we don’t see those changes in any public road map or documentation. Before you start using these lists in a production environment you should discuss and mitigate the risks within your organisation or together with your customer. Even though these hidden lists are a goldmine for SharePoint Online tenant administrators who want to use Flow for certain tasks related to site governance you really shouldn’t make any updates in these lists.