Table of Contents
Fixing Bugs and Applying Workarounds for the Groups and Teams Activity Report
The Microsoft 365 Groups and Teams activity report is generated by a PowerShell script that I’ve been working on years. The first version is listed as created in July 2016, which is soon after Office 365 Groups made their debut.
Some recent changes caused me to refresh the Groups and Teams Activity Report script (the current version is 5.13 and it is available from GitHub). The major issues are listed below.
Continuing Woes with SharePoint Usage Data
The Graph Usage Reports API continues to have a problem with SharePoint Online usage data. The URL for a SharePoint site is not included in the data, which makes it very difficult to match the usage statistics with a site. As a workaround, the script fetches details of all sites in the tenant using the List Sites API to build a list of site URLs, which is then matched up with the SharePoint usage data. Matching is imperfect but works 99% of the time, which is close enough for a workaround that I hope will become unnecessary soon when Microsoft fixes the Usage Reports API.
Disappearing Group Owner Names
Some people noted that groups were being reported with no owners when they absolutely had some owners. The script calls the Groups API to retrieve owner information using GET requests like this:
https://graph.microsoft.com/v1.0/groups/33b07753-efc6-47f5-90b5-13bef01e25a6/owners?
Weirdly, the information retrieved only included the identifier for group owner accounts. The display name needed by the report was blank. I know that Microsoft encourages developers to include a Select statement in Graph queries to limit the number of properties retrieved for objects. This increases performance and reduces the amount of data that must be transferred from the service to an app. I therefore changed the request to:
https://graph.microsoft.com/v1.0/groups/33b07753-efc6-47f5-90b5-13bef01e25a6/owners?$select=id,displayName,mail
Everything worked, which is good, but when I retested with the original call a few days later, all the expected properties were there.
@odata.type : #microsoft.graph.user
id : eff3cd58-1bb8-4899-94de-795f656b4a18
businessPhones : {+353 1 2080705}
displayName : Tony Redmond
givenName : Tony
jobTitle : Chief Executive Officer
mail : Tony.Redmond@office365itpros.com
mobilePhone : +353 86 01629851
officeLocation : Derrigimlagh
preferredLanguage : en-IE
surname : Redmond
userPrincipalName : Tony.Redmond@office365itpros.com
My conclusion is that a bug (or perhaps an attempt to introduce a performance enhancement) suppressed the output of the properties for some period in the recent past. If this is the case and Microsoft reverted to previous behavior, it would explain what happened. But it could be something else, and the learning from the experience is that it is better to be explicit when requesting data from the Graph. Use Select to tell the Graph the set of properties needed by an app and all should be well.
Converting Strings to Dates
The data generated by the usage reports API includes the date when the monitored object was last active. This information is important in terms of knowing if a group or team is active. The date is in string format and must be converted to a datetime object to calculate the number of days since the last activity. Normally, casting the date as a datetime object is enough, but then you run into the problem that date format differs across cultures and the script throws the “string was not recognized as a valid datetime” error.
The script hadn’t had the problem before, but then I had a report that the code to convert the last activity date for a team failed. The date seemed OK (21-Sept-2023) and the code worked perfectly on my workstation, but failed elsewhere when the date format defined for PowerShell in the user’s chosen culture didn’t recognize 21-Sep-2023 as a valid date. The solution is to define the expected input string format for the cast. Here’s the current code:
[datetime]$LastItemAddedToTeams = [datetime]::ParseExact($ThisTeamData.LastActivity, "dd-MMM-yyyy", $null)
Hopefully, this fix will resolve the issue no matter what local culture is chosen for PowerShell. The learning here is that Microsoft 365 and Graph APIs output dates in different formats so some care is needed to handle dates properly, especially if you expect code to run in different countries.
Learnings for the Groups and Teams Activity Report
If I was a professional PowerShell developer, I probably would have taken more care with date objects. However, no one can be blamed when their scripts misbehave due to problems introduced by Microsoft. It’s a warning to keep an eye out for changes – or to build better error handling into scripts.
Speaking of which, I might convert the Groups and Teams Activity Report script to use the Microsoft Graph PowerShell SDK. This would simplify matters because the code wouldn’t have to deal with pagination and renewing access tokens (because the script is used to process reports for tens of thousands of groups, it can take hours to run, and the access token must be renewed hourly). Simpler code is easier to maintain… or so the theory goes.
So much change, all the time. It’s a challenge to stay abreast of all the updates Microsoft makes across the Microsoft 365 ecosystem. Subscribe to the Office 365 for IT Pros eBook to receive monthly insights into what happens, why it happens, and what new features and capabilities mean for your tenant.