All About the Microsoft 365 Groups and Teams Activity Report

Posted on by Tony Redmond

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.

HTML version of the Microsoft 365 Groups and Teams activity report
Figure 1: HTML version of the Microsoft 365 Groups and Teams activity report

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 it 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:

  1. Go to the Usage Reports section under Analytics & Reports.
  2. Select the Teams usage report.
  3. Select 90 days as the period.
  4. Click Run report.
  5. When the report completes, select the Export to Excel option (Figure 2).
  6. When the CSV file is ready, download from the Downloads tab.
  7. 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.

Generating a Teams usag
Figure 2: Generating a Teams usage data file

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.

37 Replies to “All About the Microsoft 365 Groups and Teams Activity Report”

  1. Hi,

    What we should change if I want to get report of specific teams only. I have 10k teams, but i want report for 100 teams only.

    Thanks

    Reply

    1. Instead of fetching all Teams to report, use another method to report just selected teams. For example, you could apply a filter to find specific teams, or you could read in the details of teams to report from a CSV file. It’s really up to you. The report operates on whatever teams it’s given to process.

      Reply

  2. Hi Tony,

    Thank you for this great script.
    But I have a issue :

    My “Last SPO Activity”, “SPO Storage Used”, and “Number of SPO Files” columns are always blank, “N/A” and 0 for each of the columns.

    Did you know why?

    Thanks

    Reply

    1. Did the script fetch the usage data from SharePoint Online?

      Can you run this code to fetch the data? You’ll need to do after authenticating with an app that has the Reports.Read.All permission.

      Write-Host “Retrieving SharePoint Online site usage data…”
      $SPOUsageReportsURI = “https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period=’D180′)”
      $SPOUsage = (Invoke-RestMethod -Uri $SPOUsageReportsURI -Headers $Headers -Method Get -ContentType “application/json”) -Replace “…Report Refresh Date”, “Report Refresh Date” | ConvertFrom-Csv

      Reply

  3. Hi Tony.

    When trying to run the script, I get this error message (I am Global Admin). Any suggestions for what might cause this?

    Teams and Groups Activity Report V5.8 starting up…
    Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.
    at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
    at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()
    At C:\Users\XXX\Scripts\TeamsGroupsActivityReportV5.PS1:129 char:21
    + [array]$TeamsData = Get-GraphData -Uri $Uri -AccessToken $Token
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-GraphData

    Reply

    1. The app you’re using doesn’t have the necessary permissions to read the usage data. Does it have Reports.Read.All? And it’s an application and not a delegate permission?

      Reply

  4. Hi,

    All of the 10k groups it is reporting, all are showing as Team Enabled False, although i have 7k Teams.

    Also some are missing owner information and saying no owners found although they have owner.

    Any Idea

    Reply

      1. I have # V5.8 15-Sep-2022 Improved check for groups with no owners

        Downloaded it in October

      2. I have tried updated version and it goes till few thousands and then start throwing exceptions:

        Line 339: $groupmembercount get-graphdata -accessToken $token -uri $uri

        Response status code does not indicate success: 401 (unauthorized)

      3. OK. That looks like your access token has expired (it lasts 59 minutes).

        In V5.9 of the script (Jan 5, 2023), I added a function to check if the access token needed renewal after processing each group. This was done because someone else had the same problem running the script against 14,000 groups and the fix worked. Make sure that you have V5.9 (download from https://github.com/12Knocksinna/Office365itpros/blob/master/TeamsGroupsActivityReportV5.PS1).

      4. Hi,

        I ran the v5.9 as well after you asked about version. Still getting that error.

        It starts fine and doe

        Processing group test-group 5/11000
        .
        .
        .
        but later at some point throw error.

      5. Is the error still at: $groupmembercount get-graphdata -accessToken $token -uri $uri

        If so, and it’s a 401 error, dump the $token and cut and paste it into jwt.io to see if it has expired (https://office365itpros.com/2022/02/17/understanding-azure-ad-access-token/). Permissions etc. are working if other groups are being processed.

        Also, you could change this code to update the 57 minutes allowed for token refresh to 30. You’re much less likely to encounter an expired access token then.

        Function CheckAccessToken {
        # Function to check if the access token needs to be refreshed. If it does, request a new token
        # This happens when the script processes more than a few thousands groups

        $TimeNow = (Get-Date)
        if($TimeNow -ge $TokenExpiredDate) {
        $Global:Token = GetAccessToken
        $Global:TokenExpiredDate = (Get-Date).AddMinutes(30)
        Write-Host “Requested new access token – expiration at” $TokenExpiredDate }

        Return $Token
        }

      6. I did 30 minutes and run it. Still same error. I ran transcript this time and I am seeing this :

        TerminatingError(Invoke-RestMethod): “{“error”:{“code”:”unauthenticated”,”message”:”Token contains invalid signature.”,”innerError”:{“code”:”invalidSignature”,”date”:”2023-01-20T16:21:01″,”request-id”:”0845b3b7-1652-43d2-9f56-*******”,”client-request-id”:”0845b3b7-1652-43d2-9f56-********”}}}”

        Write-Error: C:\Teams\TeamsReport.ps1:308
        Line |
        308 | $GroupData = Get-GraphData -AccessToken $Token -Uri $uri
        | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        | Response status code does not indicate success: 401 (Unauthorized).

      7. What i noticed is:

        Token renewed at

        Requested new access token – expiration at 1/20/2023 11:43:01 AM (16:43 UTC)

        Token expired 2023-01-20T16:21:01

        Any idea why it expired before time?

      9. i am in USA… but script show utc time so i converted time to utc to show u. In short (16:43 UTC) was token expiration duration but Token expired 16:21:01 utc Any idea why it expired before time?

        I set the token renewal to 10 minutes and still error

      10. No idea. It sounds like an old token was in use.

        For testing, I added a line to the GetAccessToken function:

        $Global:Token = ($tokenRequest.Content | ConvertFrom-Json).access_token

        Write-Host (“Retrieved new access token at {0}” -f (Get-Date))

        Maybe you can do the same and see what access tokens are generated and when they are generated.

    2. I am running it again now with same 57 minute refresh. I do see message

      Requested new access token – expiration at…….

      I will try 30 minutes if this fail as well. I will keep you posted.

      Reply

    3. Hey Tony,

      Your last updated script, that did the trick. It went fine and processed all 11k groups/teams.

      $Global:TokenExpiredDate = (Get-Date).AddMinutes($TimeToRefreshToken)

      I think this did the trick. It is working perfectly now. Thanks so much, this is great help.

      Reply

  5. Exception calling “Add” with “2” argument(s): “Item has already been added. Key in dictionary:
    ‘00000000-0000-0000-0000-000000000000’ Key being added: ‘00000000-0000-0000-0000-000000000000′”
    At C:\Users\\TeamsGroupsActivityReportV5.ps1:179 char:4
    + $TeamsDataHash.Add([string]$Team.TeamId, $DataLine)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException
    —–
    I get this error, any idea what I am doing wrong?

    Reply

      1. I did run it from scratch. The script still runs. At the moment it is at the “Analyzing and reporting group” stage. So no idea yet if the error has any impact yet.

      2. I did get an export, but I think because of above error I got a lot of “Issues” and “Fail” statuses in the “Overall Result” column.

        Any idea how to avoid that error? I ran the script without any parameters or something. I only edited the App Registration details in the script. (AppID, TenantID, client secret)
        And I gave the App Registration the permissions that was stated in the description block.

      3. What is the “above error” that you refer to? I can’t really say why you get Issues and Fails in the Overall Result without sight of the data…

  6. This error:
    Exception calling “Add” with “2” argument(s): “Item has already been added. Key in dictionary:
    ‘00000000-0000-0000-0000-000000000000’ Key being added: ‘00000000-0000-0000-0000-000000000000′”
    At C:\Users\\TeamsGroupsActivityReportV5.ps1:179 char:4
    + $TeamsDataHash.Add([string]$Team.TeamId, $DataLine)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException

    Reply

    1. To find out what’s happening, you’ll need to examine the data that the script is attempting to add to the hash table ($Team.TeamId and $Dataline) and the hash table. There appears to be a duplicate team identifier and that shouldn’t happen.

      But this is PowerShell, you have the code, so you can debug what’s happening. I can’t see your data…

      Reply

      1. I exported the data it wants to add it does not retrieve an ID seems like and it is only zero’s:
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=1E483029F4F3C488F289C1F9E7C1140E; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=20-Jan-2023; DaysSinceActive=3}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=1A4341F129EA96F347BD27B9DFED43B1; Privacy=Private; Posts=542; Replies=389; Messages=931; LastActivity=23-Jan-2023; DaysSinceActive=0}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=F2224FF9C58944A0FD3293814F3C01B1; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=23-Jan-2023; DaysSinceActive=0}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=7E3FCEB10594A451E0741D4C536646FB; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=30-Oct-2022; DaysSinceActive=85}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=999FB23A5FA2D2CBED4D1556E7319D9D; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=More than 90 days ago; DaysSinceActive=> 90}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=CB0FF699BEEB29C2A8B9BAD9E794EEB1; Privacy=Private; Posts=3; Replies=3; Messages=6; LastActivity=18-Jan-2023; DaysSinceActive=5}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=964F96B15367E46CFC7A91B2DFA2862C; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=17-Oct-2022; DaysSinceActive=98}
        00000000-0000-0000-0000-000000000000, @{Id=00000000-0000-0000-0000-000000000000; DisplayName=5BBD9479D9F72CCA9B2E9FD042F12C7F; Privacy=Private; Posts=0; Replies=0; Messages=0; LastActivity=More than 90 days ago; DaysSinceActive=> 90}

        What am I doing wrong?
        I have granted these Application permission on the App Registration:
        Directory.Read.All
        Group.Read.All
        GroupMember.Read.All
        Organization.Read.All
        Reports.Read.All
        Sites.Read.All
        User.Read.All

  7. What I am actually missing in this report is the group primary email adres so you can use that for bulk actions. Since Team group names can have duplicates.

    Reply

  8. Hey! Any way you could add Department of the Owner? Would be interesting to see some statistics about where we have Teams in our organization. Also UPN/EmailAddress of the owner would be nice.

    Reply

    1. Sure. Go ahead. It’s PowerShell… Seriously, the point of writing this kind of report in PowerShell is that people can customize the code to meet their needs. Not everyone would want what you’ve requested.

      Reply

      1. Ok i understand, thanks for quick answer and also thanks for this awesome script!

  9. Hello Tony,
    Errors are gone after unchecked. Thank you. I was able to get Members and groupmail to my report but struggling with Owners count
    #$Uri = “https://graph.microsoft.com/v1.0/groups/” + $Group.Id + “/owners/?$count=true & ConsistencyLevel:eventual&$select=id”
    #$Uri = “https://graph.microsoft.com/v1.0/groups/” + $Group.Id + “/owners/’$count?'”
    #$OwnersData = Get-GraphData -AccessToken $Token -Uri $uri
    Will you please help me?

    Thank you so much

    Reply

  10. Hi Tony!

    Great article as always! I have one question though.

    I am trying to authenticate using certificate instead of client secret and I can not figure out how this section should like like:

    # Define the values applicable for the application used to connect to the Graph – These values will not work
    $AppId = “328e1143-88e3-492b-bf82-24c4a47ada63”
    $TenantId = “a662313f-14fc-43a2-9a7a-d2e27f4f3478”
    $AppSecret = ‘ei_7Q~mY8SLKxKJHkY.x-WTWT0ncfaqu8ETtS’

    # Construct URI and body needed for authentication
    $Uri = “https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token”
    $Body = @{
    client_id = $AppId
    scope = “https://graph.microsoft.com/.default”
    client_secret = $AppSecret
    grant_type = “client_credentials”
    }

    Any advise would be much appreciated.

    Regards,
    Marcin

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.