Squashing ‘Teams Sprawl’ with PowerShell – How to get better usage insights for Groups and Teams sites
How to manage Teams Sprawl: The programmatic solution
Last year, we covered the issue of Teams ‘sprawl’ and outlined some of the measures an organization can take to combat it. In this article, we will expand on that front by reviewing some script-based approaches for reporting on the spread and usage of Teams.
The need for a programmatic solution
First of all, why would we need to use a script for this task? Well, while Microsoft has provided us with sufficient tools to manage Teams in the organization when it comes to reporting and analytics things are still lacking. You can certainly get a list of all the Teams in the organization if you open the Teams Admin Center, but there’s no way to export this information. We do have an export functionality in the Analytics & Reports section of the admin center, however, the report experience leaves a lot to be desired still. As an example, often the number of Team objects you will see detailed as part of the report experience will not match the actual number of Teams in the tenant, as only recently active Teams are shown. On the screenshot below, we have illustrated this by overlaying the Teams usage report for my tenant, the information visible on the Manage teams page in the Teams admin center and the recently introduced in-client Analytics.
In effect, we have an incomplete and even conflicting picture. If you want to get the accurate number of Teams in the organization, you need to use the Manage Teams blade in the Teams Admin Center, which unfortunately does not provide any export functionality. On the other hand, the Teams usage report found under Analytics & Reports can be exported, however, it only lists Teams that have been active in the given timeframe (28 days at most), and only shows limited information about them (no total number of users, no number of owners, no total number of channels, no total number of Guests, etc). Lastly, we have the information presented as part of the in-client analytics, which again are only presented for a preset timeframe, cannot be exported (or accessed by any programmatic method for that matter) and do not give us the complete set of Teams, or all the important details for a given Team.
Since Microsoft Teams is built on top of the underlying Office 365 Groups, we can potentially use other endpoints, such as the Office 365 Admin Center or the SharePoint Online Admin Center to get (or in some cases export) additional information. All these come with their own UI and features, so your results will vary and the experience overall is quite fragmented. Most importantly, no admin endpoint will give us details about things such as the last time a given Team or channel was used, or even the existence of Private channels. The Office 365 Group activity report in the Office 365 Admin Center can fill in some of the gaps, but it doesn’t show Teams activity. So, if we want to include such information, we must rely on the Teams tools.
Creating the script
Now that we have done a quick overview of the built-in tools, let’s turn to use the different programmatic methods to fetch information about Teams and their usage. The Teams PowerShell module seems like a natural starting point and can indeed be used to fetch a list of all Teams in the tenant, including information about their settings, number of channels, number of owners, users and Guests added. Some data, unfortunately, is not exposed via the Teams cmdlets, such as creation date of the Team, expiration or renewal (if such is configured), or even information about Private channel members (coming in the next module version). In effect, preparing a report requires the use of additional modules, if we want to expose all the relevant information that is. For example, many additional details can be retrieved using the Get-UnifiedGroup cmdlet, run against the corresponding Office 365 Group object. Crawling the Unified Audit log can give us other relevant details such as the actual Team creation date (which can differ from the Office 365 Group) creation date, as well as information about usage. Usage and analytics can also be fetched from the corresponding reports, some of which we can obtain programmatically.
Yet another problem that you certainly want to address when combating Teams sprawl is orphaned sites left behind after the deletion of a Team. To recover those, you need yet another PowerShell module, namely the SharePoint Online one. Since each Team/Office 365 Group has a corresponding site collection and one of a particular type (template GROUP#0), we can quickly fetch those. Private channels are also backed by a site collection, so we can include those by filtering on the TEAMCHANNEL#0 template. As an added benefit, the Get-SPOSite cmdlet exposes the LastContentModifiedDate property, which is certainly useful for our purposes (although it’s not very reliable).
If we combine all the building blocks mentioned above, we can create a script that effectively generates a report of all Office 365 Groups and gives some basic information about their usage, membership and creation date. We can also add an attribute to reveal which Group is enabled for Teams. A sample script that does just that has been made available by Microsoft MVP and author Tony Redmond over at the Office 365 for IT Pros site.
As with most PowerShell snippets available online, the code can be further improved. In our case, we will want to include information about private channels, membership information, expiration date and so on. Also, the code can be adapted to run queries against the Microsoft Graph API instead of relying on several different PowerShell modules. This approach also isn’t perfect, as the Graph still doesn’t have an easy way to list all available site collections, let alone filter them by template. Once the list of site collections is obtained via the SharePoint Online module though, the rest of the script can be run as queries against the Graph.
Running the script
After this long introduction, let’s focus on the actual script, which you can download over at GitHub. As noted above, we will use the SharePoint Online module to fetch the list of site collections, so you need to make sure the module is available on the system. Additional calls will be performed via the Microsoft Graph, and for them to succeed you need a valid token with all the corresponding permissions (Group.Read.All should suffice). To obtain such a token, you need to have an application registered in your Azure AD tenant, the details for which you will have to fill in the script. Alternatively, you can use some of the available “wrapper” modules, such as the Microsoft Graph PowerShell.
If all the prerequisites are met, you can run the script and wait for it to generate the report. After retrieving all SharePoint Online site collections matching the Group or private channel template, the script will gather additional details about each of them, try to find a matching Office 365 Group, get its properties including membership, and check for matching Teams. The output will then be exported to a CSV file, which you can filter on various properties. For example, running the script in my tenant reveals the following:
So, a total of 25 site collections and no less than 10 of them are orphaned. Only 7 entries have the TeamEnabled property set to True, with one of those actually representing a private channel. Using the different filters, you can focus on the orphaned entries, or those that haven’t been used in a while, have just a single member, or whatever other criteria makes sense in your organization. Additional properties can easily be added to the script, as long as they are exposed in the Graph. For example, you might want to include the number of channels, sharing settings, classification, number of files and so on. Probably the most useful type of property you can add is the “Activity” one, which you can obtain from the Unified Audit log (sample script). This can also be achieved by querying the Office 365 Management Activity API, via a similar Graph query syntax. Similarly, you can fetch the Office 365 usage reports and correlate them with the Group/Site in question. But that’s an exercise for another time!
Want the comprehensive guide to dealing with Teams Sprawl? Download How to manage Teams Sprawl. This practical how-to guide brings together our previous article on the business impact of Teams Sprawl, and reasons why it’s so important to avoid it, along with the tips and scripts outlined by Vasil above. Download it here