Upgrading the Microsoft 365 User Activity Report with a 180-day Lookback Period

Generating a Composite View of User Activity

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. 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.