Table of Contents
An Ongoing PowerShell Project
Updated: January 27, 2023
The Microsoft 365 Groups and Teams Activity Report is a longstanding project of mine. I originally wrote the PowerShell script when Office 365 Groups were quite new and then refreshed it to deal with Microsoft Teams. The idea is to report statistics about the activity of groups such as:
- Number of conversations in the group inbox (for Outlook groups).
- Number of files in the group’s SharePoint site and the storage quota used.
- Number of conversations in channels (for team-enabled groups).
With the data, you can see what groups or teams might be inactive and are candidates for archiving or removal.
The output is a report in HTML (Figure 1) and CSV formats. Administrators can slice and dice the data in the CSV file to present it whatever way they want. Some like to import the data into Power BI and visualize it there.
Note: If your group names include non-ASCII characters like é, use the Export-Excel cmdlet from the ImportExcel module to export the report file to Excel. Exporting to a CSV does not include the non-ASCII characters in group names.
Speeding the Script Up
The most recent enhancement discarded many of the calls to “expensive” PowerShell cmdlets like Get-UnifiedGroup and replaced them with Microsoft Graph queries. I did this to increase performance of the script and enable it to run in some large tenants with over 20,000 groups (teams). I’m sure that the script will process more than that number, but I haven’t gone higher. In any case, if you need to process very large numbers of groups, you should probably use a different tool and maybe even split processing up across batches of groups (for instance, A-C, D-E, and so on).
The latest version of the Graph-based script is 5.10. You can download the full script from GitHub. The latest updates include:
- Better error handling.
- Replaced call to Exchange Get-OrganizationConfig cmdlet with Graph API request.
- Updated processing of groups with no owners. This aspect was further improved in 5.8.
- Output more information about script processing.
- Rewrote function to refresh access token for Graph access after 57 minutes. This is to accommodate long-running scripts, like one tenant which runs the report against 40K teams. In V5.9, I added a new function to check the access token and renew it if necessary after processing each group.
- The script automatically downloads the latest Teams usage data from the Graph. This removes the need to manually download the data from the Teams admin center and means that the data used is always the latest available.
- V5.10 addresses a problem where the date and items in folder data returned by the Get-MailboxFolderStatistics and Get-ExoMailboxFolderStatistics cmdlets are arrays!!
I’ll update this post when new versions appear.
Because it’s much slower, I don’t develop the pure PowerShell version anymore. The last version that I worked on is 4.8. The pure PowerShell script lags both the performance and functionality of its Graph counterpart, but you can download it from GitHub.
Teams Usage Report
Update: V5.5 and later versions remove the need to download the Teams usage report from the Teams admin center. The script now does this automatically.
If you’re going to run the report, you can speed things up even more by going to the Analytics & Reports section of the Teams admin center to download a CSV file with Teams usage data. If you don’t download the file, the script will still run. However, instead of being able to check usage data (like the number of channel posts) from the file, the script must check the number of compliance records stored in the team’s group mailbox.
Because checking compliance records uses a call to the Get-ExoMailboxFolderStatistics cmdlet instead of reading a record from a hash table, the operation is much more expensive in performance terms. On average, it takes an extra couple of seconds to process each team-enabled group, which quickly mounts up when the script must process hundreds or thousands of teams. As an example, to process 210 groups (83 teams), the script took 1034 seconds without a teams usage data file. With the file, the elapsed time for the same set reduced to 388 seconds.
On the upside, checking compliance records returns the count of every channel conversation post since the creation of a team (subject to any retention policies in force) whereas checking against the data file gives a snapshot of activity over the last 90 days. Knowing what happened over the last 90 days is usually sufficient to know if a team is active.
To generate the Teams usage data file, do the following:
- Go to the Usage Reports section under Analytics & Reports.
- Select the Teams usage report.
- Select 90 days as the period.
- Click Run report.
- When the report completes, select the Export to Excel option (Figure 2).
- When the CSV file is ready, download from the Downloads tab.
- Rename the downloaded file to match the file used by the script (by default, this is c:\temp\TeamsUsageData.csv. You can change the location and file name in the $TeamsDataCSVFile variable if you wish.
Teams Private and Shared Channels
If you provide the script with a teams usage data file, the data includes messages posted to private channels. It will soon include messages posted to shared channels. If you don’t use a data file, the script only includes messages posted to standard channels because it doesn’t check the mailboxes of private channel members or the special cloud mailboxes used by shared channels.
Use the Script as You Want
I don’t pretend this script is a work of PowerShell art. It could probably do with a complete rewrite. However, it works, and it’s something that tenants can use to create their own version of what they think an activity report should do. After all, it’s just PowerShell, so play with the code and let your imagination run riot!
Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.