Speeding Up the Groups and Teams Activity Report by Replacing PowerShell with Graph API Calls

Grappling with PowerShell

Sometimes I hate PowerShell. Not the language itself, just my ineptitude, or my inability to remember how to do things, or the speed of some cmdlets which deal with objects like mailboxes and groups. It’s not that the cmdlets are inefficient. They do a lot of work to retrieve information about objects, so they are slow.

This is fine for ad-hoc queries or where you only need to process a couple of hundred mailboxes or groups. The problem is accentuated as numbers grow, and once the need exists to process thousands of objects, some significant time is spent waiting for cmdlets to complete, meaning that scripts can take hours to run.

Microsoft has made significant progress in the Exchange Online PowerShell module to introduce faster cmdlets like Get-ExoMailbox and Get-ExoMailboxStatistics. These REST-based cmdlets are faster and more robust than their remote PowerShell cousins and these improvements are ample justification for the work needed to revisit and upgrade scripts. The module also supports automatic renewal of sessions to Exchange Online and the Security and Compliance endpoints, so it’s all good.

The Sloth of Get-UnifiedGroup

Things aren’t so impressive with Get-UnifiedGroup, which retrieves details about Microsoft 365 Groups. Reflecting the use of Microsoft 365 groups, Get-UnifiedGroup is a complex cmdlet which assembles details from Azure AD, Exchange Online, and SharePoint Online to give a full picture of group settings. Running Get-UnifiedGroup to fetch details of 200 groups is a slow business; running the cmdlet to fetch details of 10,000 groups is a day-long task. The Get-Team cmdlet is no speedster either. In their defense, Microsoft designed these cmdlets for general-purpose interaction with Groups and Teams and not to be the foundation for reporting thousands of objects over a short period.

If you only need a list of Microsoft 365 Groups, it’s also possible to create the list using the Get-Recipient cmdlet.

Get-Recipient -RecipientTypeDetails GroupMailbox -ResultSize Unlimited

Creating a list of groups with Get-Recipient is usually much faster than creating it with Get-UnifiedGroup. However, although you end up with a list of groups, Get-Recipient doesn’t return any group-related properties, so you usually end up running Get-UnifiedGroup to retrieve settings for an individual group before you can process it. Still, that overhead can be spread out over the processing of a script and might only be needed for some but not all groups.

The Graph is Quicker

Which brings me to the Microsoft Graph API for Groups. As I’ve pointed out for some years, using Graph APIs with PowerShell is a nice way to leverage the approachability of PowerShell and the power of the Graph. The script to create a user activity report from Graph data covering Exchange, SharePoint, OneDrive, Teams, and Yammer is a good example of how accessible the Graph is when you get over the initial learning curve.

Three years ago, I wrote about a script to find obsolete Teams and Groups based on the amount of activity observed in a group across Exchange Online, SharePoint Online, and Teams. In turn, that script was based on an earlier script which processed only Office 365 Groups. Since then, I have tweaked the script in response to comments and feedback and everything worked well. Except that is, once the script ran in large environments supporting thousands of groups. The code worked, but it was slow, and prone to time-outs and failures.

Speeding Up the Report

The solution was to dump as many PowerShell cmdlets as possible and replace them with Graph calls. The script (downloadable from GitHub) now uses the Graph to retrieve:

• SharePoint Online site usage data for the last 90 days.
• A list of Microsoft 365 Groups and a list of team-enabled groups.
• The owners of a group.
• The display name of the owners (because the first call returns their Azure AD identifier).
• Some extended properties of a group not fetched when the group list is returned.
• Counts for group members and guests (easier to do since Microsoft added advanced queries for directory objects in October 2020).
• Archived status for teams.

The result is that the script is much faster than before and can deal with thousands of groups in a reasonable period. Fetching the group list still takes time as does fetching all the bits that Get-UnifiedGroup returns automatically. On a good day when the service is lightly loaded, the script takes about six seconds per group. On a bad day, it could be eight seconds. Even so, the report (Figure 1) is generated about three times faster.

Results - Teams and Microsoft 365 Groups Activity Report V5.1
--------------------------------------------------------------   
Number of Microsoft 365 Groups scanned                          : 199    
Potentially obsolete groups (based on document library activity): 121    
Potentially obsolete groups (based on conversation activity)    : 130      
Number of Teams-enabled groups                                  : 72    
Percentage of Teams-enabled groups                              : 36.18%

Total Elapsed time:  1257.03 seconds
Summary report in c:\temp\GroupsActivityReport.html and CSV in c:\temp\GroupsActivityReport.csv

The only remaining use of an “expensive” cmdlet in the script is when Get-ExoMailboxFolderStatistics fetches information about compliance items for Teams stored in Exchange Online mailboxes. The need for this call might disappear soon when Microsoft eventually ships the Teams usage report described in message center notification MC234381 (no sign so far despite a promised delivery of late February). Hopefully, that report will include an update to the Teams usage report API to allow fetching of team activity data like the number of conversations over a period. If this happens, I can eliminate calling Get-ExoMailboxFolderStatistics and gain a further speed boost.

Some Extra Work but Not Onerous

The downsides of using the Graph with PowerShell are that you need to register an app in Azure Active Directory and make sure that the app has the required permissions to access the data. This soon becomes second nature, and once done, being able to process data faster than is possible using the general-purpose Get-UnifiedGroup and Get-Team cmdlets is a big benefit when the time comes to process more than a few groups at one time.