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.
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:
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.
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.
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.
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
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?
I have # V5.8 15-Sep-2022 Improved check for groups with no owners
Downloaded it in October
Loading...
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)
Loading...
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).
Loading...
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.
Loading...
Is the error still at: $groupmembercount get-graphdata -accessToken $token -uri $uri
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
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).
Loading...
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?
Loading...
What country is this in? The local culture setting for PowerShell might be an issue.
Loading...
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
Loading...
No idea. It sounds like an old token was in use.
For testing, I added a line to the GetAccessToken function:
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?
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.
Loading...
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.
Loading...
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…
Loading...
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
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…
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
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.
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.
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.
Ok i understand, thanks for quick answer and also thanks for this awesome script!
Loading...
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?
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”
}
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
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
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.
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
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
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
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?
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
Are you using the latest version of the script (from GitHub)?
I have # V5.8 15-Sep-2022 Improved check for groups with no owners
Downloaded it in October
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)
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).
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.
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
}
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).
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?
What country is this in? The local culture setting for PowerShell might be an issue.
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
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.
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.
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.
Happy to help!
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?
The hash table already has an entry in it for a team. Did you run the script from scratch or are you running sections at a time?
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.
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.
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…
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
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…
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
I found my solution here: https://blog.atwork.at/post/Get-the-latest-Teams-activity-with-Graph
See “Turn off obfuscation”
Obfuscation of Microsoft 365 report data has been in place since 2020. It’s something you need to check before running reports because it screws up a lot of things. https://office365itpros.com/2021/04/16/microsoft-obfuscates-teams-usage-data/
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.
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.
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.
Ok i understand, thanks for quick answer and also thanks for this awesome script!
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
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