An Ongoing PowerShell Project
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.
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.4. You can download it from GitHub. I’ll update this post when new versions appear.
Because it’s much slower, I don’t develop the pure PowerShell version anymore. The latest version is 4.8. You can download it from GitHub.
Teams Usage Report
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.