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. 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.
I’ve been trying to run this script but I am getting 403 forbiden errors. Yet I should have all the right permissions. App registration has the following application permissions for MsGraph :
AuditLog.Read.All
Directory.Read.All
User.Read.All
The default delegation User.Read was kept
Is there a config I need to edit in the script apart from the CSV output TenantID AppID and Secret? Or I am missing something in my tenant in order to access the data?
Thank you
The code says:
# Needs the Reports.Read.All permission to get user data
# Needs the AuditLog.Read.All and Directory.Read.All permissions to read user signin data
Yup, missed that after passing initial configurations and never re-read the script comments. Works perfectly now and thank you for this wonderfull script
I’ve added all these permissions but I’m still getting 403 forbidden errors… what could be the problem?
Does the signed in account have any administrative permissions?
That’s exactly what I needed today! Everything worked great, thanks for the work! Greetings from Germany
This is exactly what I have been looking for when it comes to lastsignin data. The script is running successfully, but the license data does not seem to populate for the majority of my licensed users.
You mean the license data returned by the usage API? If you look at records in the $TeamUserData array, do you see license data there?
Here’s an example:
$TeamsUserData[4]
Report Refresh Date : 2022-12-14
User Id : c814b6e2-d4c2-431d-b82c-e059b152c96c
User Principal Name : Jeff.Brown@office365itpros.com
Last Activity Date :
Is Deleted : False
Deleted Date :
Assigned Products : OFFICE 365 E3
Team Chat Message Count : 0
Private Chat Message Count : 0
Call Count : 0
Meeting Count : 0
Meetings Organized Count : 0
Meetings Attended Count : 0
Ad Hoc Meetings Organized Count : 0
Ad Hoc Meetings Attended Count : 0
Scheduled One-time Meetings Organized Count : 0
Scheduled One-time Meetings Attended Count : 0
Scheduled Recurring Meetings Organized Count : 0
Scheduled Recurring Meetings Attended Count : 0
Audio Duration : PT0S
Video Duration : PT0S
Screen Share Duration : PT0S
Audio Duration In Seconds : 0
Video Duration In Seconds : 0
Screen Share Duration In Seconds : 0
Has Other Action : No
Urgent Messages : 0
Post Messages : 0
Tenant Display Name : REDMOND & ASSOCIATES
Shared Channel Tenant Display Names :
Reply Messages : 0
Is Licensed : Yes
Report Period : 180
For anyone looking for the option for “Display concealed user, group, and site names in all reports”
Go to the Microsoft 365 admin center. Go to Settings > Org Settings > Services. Select Reports. Clear Display concealed user, group, and site names in all reports, and then select Save
*Edit* For anyone looking for the option for “Display concealed user, group, and site names in all reports”
Go to the Microsoft 365 admin center. Go to Settings > Org Settings > Services. Select Reports.
fantastic report! Thanks for this, wondering if anyone has found a way to turn this into reporting days of the week, meaning M-T-W-th-f-sa-su rather than date ranges etc. End goal would be to show that users WFH/office,etc in a hybrid are statistically as busy, more busy, or less busy certain days of the week than others.
I think you might get into difficulties with unions/worker councils if you were to do such a thing…
In any case, the minimum period for the usage reports API is 7 days. Without 1-day granularity, it’s not feasible to use this data for such a purpose.