Table of Contents
Bringing Microsoft 365 User Activity Data Together from Multiple Workloads
I’ve been dabbling with the Microsoft Graph usage report API for a couple of years. This is the API that powers the activity reports available in the Microsoft 365 admin center, Teams admin center, and SharePoint Online admin center, so it’s a good source of hard information.
The output of my labor is the Microsoft 365 user activity report, a PowerShell script that assembles data from SharePoint Online, Exchange Online, Teams, OneDrive for Business, and Yammer activity to build a picture of how active a user account is, with the intention of removing underused or unused accounts to save on licensing costs. The 2020 version of the script introduced a bunch of performance fixes to make it possible to retrieve data quickly and efficiently.
New Version Extends the Usage History to 180 Days
Recently, a reader pointed out that the usage report API now supports a lookback period of 180 days, doubling the previous 90 days. In other words, you can fetch information about the activities performed by an account inside Microsoft 365 for the last 180 days. I don’t know when Microsoft made this change, but it’s a good one.
The usage report API doesn’t capture data about every possible user activity, nor does it cover all workloads. For instance, there’s no usage API covering Stream and Planner activity. However, in the case of Stream, once the transition to OneDrive for Business and SharePoint Online, video activities will show up in the data for those workloads.
In any case, the usage data is sufficient to make a good assessment of just how active an account is. After all, if little or no trace of activity exists over 180 days, the account probably isn’t too active and is a candidate for removal. Measuring usage over 90 days is also a good yardstick of activity but doubling the measurement period makes the assessment even more accurate because it accommodates long absences such as sabbaticals and parental leave. This underlines the need to assess data in a wider context when deciding whether accounts really are inactive.
Example Microsoft 365 User Activity Data
The script works by extracting usage data for the supported workloads and combining them into an overall record per user. Here’s an example of a combined record. Note that usage data is always a couple of days behind real time.
UPN : Tony.Redmond@office365itpros.com DisplayName : Tony Redmond Status : Account in use LastSignIn : 03/08/2022 18:08 DaysSinceSignIn : 0 EXOLastActive : 31-Jul-2022 EXODaysSinceActive : 3 EXOQuotaUsed : 5.91 EXOItems : 34324 EXOSendCount : 2572 EXOReadCount : 4661 EXOReceiveCount : 11158 TeamsLastActive : 01-Aug-2022 TeamsDaysSinceActive : 2 TeamsChannelChat : 362 TeamsPrivateChat : 493 TeamsMeetings : 22 TeamsCalls : 2 SPOLastActive : 31-Jul-2022 SPODaysSinceActive : 3 SPOViewedEditedFiles : 798 SPOSyncedFiles : 575 SPOSharedExtFiles : 17 SPOSharedIntFiles : 34 SPOVisitedPages : 92 OneDriveLastActive : 31-Jul-2022 OneDriveDaysSinceActive : 3 OneDriveFiles : 6183 OneDriveStorage : 27.2147 OneDriveQuota : 1024 YammerLastActive : 19-Jul-2022 YammerDaysSinceActive : 15 YammerPosts : 104 YammerReads : 238 YammerLikes : 1 License : POWER BI (FREE)+ENTERPRISE MOBILITY + SECURITY E5+BUSINESS APPS (FREE)+MICROSOFT POWER AUTOMATE FREE+MICROSOFT VIVA TOPICS+OFFICE 365 E5 OneDriveSite : https://redmondassociates-my.sharepoint.com/personal/tony_redmond_office365itpros_com IsDeleted : False EXOReportDate : 31-Jul-2022 TeamsReportDate : 01-Aug-2022 UsageFigure : 5.2
Very importantly, if your organization chooses to obfuscate usage data (Figure 1), it isn’t possible to generate the report because user principal names provide the match for usage data from the workloads, and the routine that generates the obscured data creates different values for the user principal name in each workload.
Microsoft 365 User Activity Report Output
The output generated by the script is a PowerShell list which can be exported in different formats. Figure 2 shows the output as viewed through the Out-GridView cmdlet. The script also generates a CSV file, but you could also use the ImportExcel module to create a nicely-formatted Excel worksheet or the PSWriteHTML module to generate a HTML report.
You can download the updated Microsoft 365 user activity report script from GitHub. (Note: report script updated to V2.1 on 8-Oct-2025 to improve functionality and make the script based purely on the Microsoft Graph PowerShell SDK).
If you find an enhancement (aka a bug fix), please suggest it in GitHub. It’s always good to have extra eyes review and improve code.
Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the ultimate eBook covering Office 365 and the wider Microsoft 365 ecosystem.