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.13. 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!!
  • V5.13 handles the issue caused when the usage API cannot include the site URL for SharePoint sites in its output.

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.

81 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

      1. Hi Tony,
        Thanks a lot for all the great work.
        While running the script, the SharePoint data is not captured. The graph application has been given all needed permission. Any help will be greatly appreciated.

        Thanks,
        Abhishek

      2. Have you tried running “https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period=’D180′)” in another script or interactively?

  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

  11. I get the “Last Teams message” in this fomat in the report: “14-Apr-2023” all other dates are correctly formated as dd.mm.yyyy (german date format). Would it be possible to append the report to convert this one date to the same format as the others in the report?

    Reply

    1. Try changing this line:

      $LastItemAddedToTeams = $ThisTeamData.LastActivity

      to

      $LastItemAddedToTeams = Get-Date ($ThisTeamData.LastActivity) -format g

      and see if that fixes the problem.

      Reply

  12. Hi Tony,
    Im also seeing the ” “Last SPO Activity”, “SPO Storage Used”, and “Number of SPO Files” columns are always blank, “N/A” and 0 for each of the columns.” issues. The code you posted to the original report runs but returns nothing. Any ideas?

    Thanks
    Jim

    Reply

    1. Can you extract and run the code to fetch SPO usage information on its own? I can’t see your tenant so I am afraid you’ll have to debug this on your own…

      Reply

      1. yes, the code on its own returns nothing. oddly, the SP report in the admin centre currently says There is no data. Maybe its a backend issue? i’ll check in tomorrow and see if we have any data.

      2. Could be a backend issue. The report depends on whatever data the Graph Usage Reporting API provides, and that’s the same as visible in the Reports section of the Microsoft 365 admin center.

    1. You mean the Azure AD group identifier? Sure… just add it to the set of properties included in the command that creates the $ReportLine variable that’s written into the report.

      Reply

      1. Thanks for the reply!

        But no I’m after the GroupID that’s associated to the SharePoint site.
        Report we get has numerous duplicate names, I mean lots (but different URL’s)
        Trying to work out a way to delete via scripting the groups that show no owners and no users

        Trying to do it via Deleting SPO sites, but comes up error that there’s a M365 group connected so won’t let you (although I want the group deleted to).

        Deleting the Group will also delete the site (and Team if connected) so great, but… when there are duplicates it fails, not knowing which one to delete.

        The only unique way would be GroupID? not sure, I though of a way to delete the Group (and SPO site) via URL, which I have not worked out yet.

        So thus why looking at a way to to get the GroupID od the M365 group itself.

      2. If it’s the Azure AD group identifier (the GUID) you want to include in the report, change this code to add the last line to include $G.ObjectId in the output. I think that should do the job:

        $ReportLine = [PSCustomObject][Ordered]@{
        GroupName = $DisplayName
        ManagedBy = $G.ManagedBy
        ContactEmail = $G.GroupContact
        Visibility = $G.Visibility
        Members = $G.GroupMembers
        “External Guests” = $G.GuestMembers
        Description = $G.Description
        “Sensitivity Label” = $G.Label
        “Team Enabled” = $TeamsEnabled
        “Last Teams message” = $LastItemAddedtoTeams
        “Number Teams messages” = $NumberOfChats
        “Last Email Inbox” = $LastConversation
        “Number Email Inbox” = $NumberConversations
        “Last SPO Activity” = $SPOLastActivityDate
        “SPO Storage Used (GB)” = $SPOStorageUsed
        “Number SPO Files” = $SPOFileCount
        “SPO Site URL” = $G.SharePointURL
        “Date Created” = $G.WhenCreated
        “Days Old” = $GroupAge
        NumberWarnings = $NumberWarnings
        Status = $Status
        “Overall Result” = $OverallStatus
        GroupdId = $G.ObjectId }
        $Report.Add($ReportLine)

  14. @Tony Thanks very much, that’s all it was GroupdId = $G.ObjectId
    Was trying all sorts of things, even GroupID = $Group.id… doh…

    Reply

  15. Hi Tony, and thank you for a superb script. Question: when running this script, in the beginning I get many rows with the following:

    Teams and Groups Activity Report V5.10 starting up…
    Retrieved new access token at 2023-10-27 15:46:04
    Exception calling “Add” with “2” argument(s): “Objektet har redan lagts till. Nyckel i uppslagslistan: 00000000-0000-0000-0000-000000000000. Nyckel som läggs till: 00000000-0000-0000-0000-00
    0000000000.”
    At line:190 char:4
    + $TeamsDataHash.Add([string]$Team.TeamId, $DataLine)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException

    and also this:

    Retrieving SharePoint Online site usage data…
    Couldn’t add details for site D9A693A886C2B528626DC30CEDE56E1A to the DataTable – continuing

    Sorry for swedish, it says “the object has already been added. Key in th lookup list” – “Key thats added” …hopefully it’s quite understandable.

    Why do I get this? Can it be ignored?

    Reply

    1. This error is in the GetTeamStats function. Did the Graph API request to fetch Teams activity data retrieve any information?

      $Uri = “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”
      [array]$TeamsData = Get-GraphData -Uri $Uri -AccessToken $Token

      Reply

      1. I get this:

        PS C:\Windows\system32> $Uri = “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”
        [array]$TeamsData = Get-GraphData -Uri $Uri -AccessToken $Token
        Get-GraphData : System.Net.WebException: Fjärrservern returnerade ett fel: (400) Felaktig begäran.
        vid Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
        vid Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()
        At line:2 char:21
        + [array]$TeamsData = Get-GraphData -Uri $Uri -AccessToken $Token
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
        + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-GraphData

      2. What version of the script are you using?

        In any case, the code you posted has single quotation marks in the period

        “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”

        Whereas it should be plain single quotation marks:

        $uri = “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”

      3. PS C:\Windows\system32> $TeamsData

        reportRefreshDate : 2023-10-30
        teamId : 00000000-0000-0000-0000-000000000000
        teamName :
        lastActivityDate : 2023-10-11
        teamType : Public
        isDeleted : False
        details : {@{activeSharedChannels=0; activeExternalUsers=0; reportPeriod=180; activeUsers=0; activeChannels=0; channelMessages=0; reactions=0; meetingsOrganized=0; postMessages=0;
        replyMessages=0; urgentMessages=0; mentions=0; guests=0}}

  16. Ah, quotes fixed!

    Anyways, when I run the above, it does not return anything. I have the 5.10 version.

    Reply

    1. Well, I ran the command here (using the Graph SDK) and it worked just fine. Maybe your permissions are not correct?

      $Uri = “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”
      $Data = Invoke-MgGraphRequest -Uri $Uri -Method get
      $Data.Value[0]

      Name Value
      —- —–
      teamType Private
      reportRefreshDate 2023-10-30
      teamId 06fc846d-141d-4e38-8b29-964903b9013d
      isDeleted False
      details {System.Collections.Hashtable}
      teamName Windows File Server Replacement Project
      lastActivityDate 2022-04-11

      Reply

      1. Hmm weird, I’m getting this:

        PS C:\Windows\system32> $Uri = “https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=’D180′)?`$format=application/json”
        $Data = Invoke-MgGraphRequest -Uri $Uri -Method get
        $Data.Value[0]
        Invoke-MgGraphRequest : GET https://graph.microsoft.com/beta/reports/getTeamsTeamActivityDetail(period=%E2%80%99D180%E2%80%B2)?$format=application/json
        HTTP/1.1 400 Bad Request
        Transfer-Encoding: chunked
        Vary: Accept-Encoding
        Strict-Transport-Security: max-age=31536000
        request-id: 9269cd1a-0103-46ae-a402-ade93ce3a16a
        client-request-id: 0af6fdd3-3074-446a-958d-ffe5b6bdc3ea
        x-ms-ags-diagnostic: {“ServerInfo”:{“DataCenter”:”Sweden Central”,”Slice”:”E”,”Ring”:”5″,”ScaleUnit”:”002″,”RoleInstance”:”GV3PEPF00002BC2″}}
        Date: Wed, 01 Nov 2023 11:59:46 GMT
        Content-Encoding: gzip
        Content-Type: application/json
        {“error”:{“code”:”BadRequest”,”message”:”Syntax error: character ‘’’ is not valid at position 7 in ‘period=’D180′’.”,”innerError”:{“date”:”2023-11-01T11:59:46″,”request-id”:”9269cd1a-0103-46
        ae-a402-ade93ce3a16a”,”client-request-id”:”0af6fdd3-3074-446a-958d-ffe5b6bdc3ea”}}}
        At line:2 char:9
        + $Data = Invoke-MgGraphRequest -Uri $Uri -Method get
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (Method: GET, Re…ffe5b6bdc3ea
        }:HttpRequestMessage) [Invoke-MgGraphRequest], HttpResponseException
        + FullyQualifiedErrorId : InvokeGraphHttpResponseException,Microsoft.Graph.PowerShell.Authentication.Cmdlets.InvokeMgGraphRequest
        Cannot index into a null array.
        At line:3 char:1
        + $Data.Value[0]
        + ~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : NullArray

  18. Hi Tony,

    MSFT is having an issue for the SharePoint Online reports:
    https://admin.microsoft.com/#/servicehealth/:/alerts/SP676147

    This is why many users (including me) are having issues with the graph version of the script not showing any SPO data.

    When fetching getSharePointSiteUsageDetail, SiteURL is empty:

    e.g.
    Report Refresh Date : 2023-12-03
    Site Id : 00000000-0000-0000-0000-000000000000
    Site URL :
    Owner Display Name : 7AE794F40611155890313F6BF783647E
    Is Deleted : False
    Last Activity Date : 2023-10-28
    File Count : 2616
    Active File Count : 7
    Page View Count : 1
    Visited Page Count : 1
    Storage Used (Byte) : 1186223991
    Storage Allocated (Byte) : 27487790694400
    Root Web Template : Group
    Owner Principal Name : 08EFCCAD5D8BCCEB99FBA7A9E3A7D632
    Report Period : 180

    This leads to this error during script execution
    Couldn’t add details for site 465643B1ED6FB75CDF89F1B233BC5274 to the DataTable – continuing

    Reply

      1. Would I be able to add it into the existing script easily? Looking at line 507 I’m wondering if something like this would work. I don’t need the number of chats is lower than 20 in my scenario.
        If ($LastItemAddedToTeams -gt 90) {
        $NumberWarnings++
        $TeamsStatus = “No recent teams chats” }

      2. You’d need to do something like:

        Channels = Get-MgTeamChannel
        ForEach Channel
        Get-MgTeamChannelMessage -Top 1
        DaysSinceLastItem = NewTimeSpan DateForChannelMessage
        If DaysSinceLastItem > 90 Issue warning

        This is pseudo-code… Not PowerShell

      3. Sorry for the misleading replies… I was thinking about how I might attack the problem from scratch, but of course the Teams usage data is available in the report. I added some lines to check if the last activity in a team is more than 90 days old and report it. You can download the script from GitHub to pick up the change. Let me know if it does what you need.

      4. Looks like that worked, A couple interesting things. If the last teams chat message date is null the script throws an error. Doesn’t seem to affect to report. The more curious thing I’m seeing is with “Group Inbox Not Recently Used” I see that’s pulling from $LastConversation -le $WarningEmailDate I changed the WarningEmailDate to -90 from -365 and the results are very inconsistent. It put that some groups that had emails within the last month hadn’t been used recently and other groups where last conversation was well over the 90 day mark weren’t included at all and status cameback with MBX: Ok

      5. The fix for the null date is easy. I should have put it in when I made the change. It’s there now.

        I don’t have time to look at the Outlook group inbox issue. I took a quick look and don’t see anything obvious.

  20. Thanks Tony, Looks like it’s still erroring on the null date but it’s not a big deal as the report seems to be fine. I’ll look through and see if I can figure out why the email one isn’t reporting correctly.

    Reply

  21. Im currently trying to run the script as a Teams Admin but get the following error:

    “Invoke-WebRequest : {“error”:”invalid_request”,”error_description”:”AADSTS90002: Tenant
    ‘a662313f-14fc-43a2-9a7a-d2e27f4f3478’ not found. Check to make sure you have the correct tenant ID and are signing
    into the correct cloud. Check with your subscription administrator, this may happen if there are no active
    subscriptions for the tenant. Trace ID: 98577248-95e8-43ff-b6d2-12035a958500 Correlation ID:”

    Presumably the script needs Tenant ID updated, but what Client/App ID or secret need to be specified? Is this for the connection to MS Graph? It would be good to know what permissions are required or what parameters in the script need localising? Is there another way to run it with minimal customisation?

    Thank you,
    Rich.

    Reply

    1. You need an Entra ID app created in your tenant with a secret or certificate. You’re getting an error because you’re trying to use the dummy tenant id I include in the Github code.

      The required permissions are listed in the code.

      Reply

  22. Hey Tony 🙂
    I’m im the early steps of trying to manage the Teams platform.
    I’ve tried to run the script in both the PowerShell 5 and PowerShell 7 Version, but regardless which one I use I’m getting an error:
    Which Version of PowerShell should I use? – couldn’t find anything about it.
    I think I’ve got the right IDs needed for authentication, so I don’t know where the problem is rooted.

    Could you provide me a helping hand @Tony?
    Thanks
    Pascal

    Here are the error codes:
    Powershell 5:

    Teams and Groups Activity Report V5.13 starting up…
    Invoke-WebRequest : Der Remoteserver hat einen Fehler zurückgegeben: (404) Nicht gefunden.
    In C:\Users\Jens\Downloads\TeamsGroupsActivityReportV5.PS1:124 Zeichen:17
    + … enRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType “ap …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

    ConvertFrom-Json : Das Argument kann nicht an den Parameter “InputObject” gebunden werden, da es NULL ist.
    In C:\Users\Jens\Downloads\TeamsGroupsActivityReportV5.PS1:126 Zeichen:35
    + $Token = ($tokenRequest.Content | ConvertFrom-Json).access_token
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

    Retrieved new access token at 12.06.2024 13:14:10
    Can’t get a valid access token from Entra ID – exiting

    Powershell 7:

    Teams and Groups Activity Report V5.13 starting up…
    Invoke-WebRequest: C:\Users\Jens\Downloads\TeamsGroupsActivityReportV5.PS1:124
    Line |
    124 | … enRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType “ap …
    | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    | Response status code does not indicate success: 404 (Not Found).
    ConvertFrom-Json: C:\Users\Jens\Downloads\TeamsGroupsActivityReportV5.PS1:126
    Line |
    126 | $Token = ($tokenRequest.Content | ConvertFrom-Json).access_token
    | ~~~~~~~~~~~~~~~~
    | Cannot bind argument to parameter ‘InputObject’ because it is null.
    Retrieved new access token at 12.06.2024 13:18:27
    Can’t get a valid access token from Entra ID – exiting

    Reply

    1. The script should run under PowerShell 5.1 or 7.4 (tested for both).

      Your problem is that you didn’t get an access token from Entra ID. To do this, you need a registered Entra ID, the application (client) identifier of the app, the tenant identifier, and an app secret. The app also needs consent to use the set of application permissions detailed at the top of the script. If you don’t get an access token and that token doesn’t confirm that the app can use the permissions, you cannot use Graph API requests to retrieve data.

      Reply

  23. Hi Tony, The script starts asking for creds after approximately after 45 mins and wont stop. How can I fix that?

    Reply

    1. Sounds like the refresh of the access token isn’t working after 53 minutes. What’s the last message output on screen before the prompt happens?

      Every time the access token refreshes (needed to access the Graph), you should see a message saying something like:

      Retrieved new access token at 03/07/2024 11:28:46

      Reply

      1. I got this error

        Processed 207 groups in 2787.93 – Currently processing at 13.47 seconds per group
        Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a receive.
        At line:124 char:17
        + … enRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType “ap …
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
        + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

        ConvertFrom-Json : Cannot bind argument to parameter ‘InputObject’ because it is null.
        At line:126 char:35
        + $Token = ($tokenRequest.Content | ConvertFrom-Json).access_token
        + ~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

        Also

        Error reading team details for Kaizen Event- Prospecting from a Distance (c95bd044-b5f8-4efe-a85b-d79a92513ccd)
        Check the Teams admin center for this team – it might be deleted but still shows up in the teams list

      2. The problem you ran into is a failure to acquire an access token from Entra ID. It seems like a transient error.

        What version of the code are you running? It’s performing very slowly to process groups at 13.47 seconds. I’d expect 3 seconds or less.

Leave a Reply

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