Query Details
HUNT 13 M365 Cross Workload User Activity Timeline 30d
Query
// Hunt : M365 - Cross-Workload User Activity Timeline (30d)
// Purpose : Build a unified per-user activity timeline across Teams, Exchange,
// SharePoint, and OneDrive for the past 30 days. Enables holistic
// investigation of a specific user's M365 footprint during an incident.
// Tables : OfficeActivity
// Usage : Set TargetUser to a specific UPN to scope to one user, or leave blank ("") for all users.
// Period : P30D
//==========================================================================================
let LookbackDays = 30d;
// Set to a specific UPN (e.g. "[email protected]") to scope to one user.
// Leave blank ("") to surface all users — results are capped at 500 rows by event count.
let TargetUser = "";
OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where isempty(TargetUser) or UserId =~ TargetUser
| extend
Workload = OfficeWorkload,
ActionCategory = case(
Operation has_any ("Download", "Sync"), "Download",
Operation has_any ("Upload", "Create"), "Upload/Create",
Operation has_any ("Delete", "Remove"), "Deletion",
Operation has_any ("Share", "Invite"), "Sharing",
Operation has_any ("Mail", "Message"), "Messaging",
Operation has_any ("Rule", "Policy"), "Policy/Rule Change",
Operation has_any ("Permission", "Admin"), "Permission Change",
Operation has_any ("Label", "Sensitivity"), "Label Change",
"Other")
| summarize
Operations = make_set(Operation, 50),
FileOrObjects = make_set(SourceFileName, 20),
Sites = make_set(Site_Url, 10),
ClientIPs = make_set(ClientIP, 5),
EventCount = count(),
FirstEvent = min(TimeGenerated),
LastEvent = max(TimeGenerated)
by UserId, Workload, ActionCategory
| sort by EventCount desc, UserId asc, Workload asc
| take 500
| project
UserId,
Workload,
ActionCategory,
EventCount,
Operations,
FileOrObjects,
Sites,
ClientIPs,
FirstEvent,
LastEvent
Explanation
This query is designed to create a timeline of user activities across various Microsoft 365 services (Teams, Exchange, SharePoint, and OneDrive) over the past 30 days. It helps in investigating a user's activities during a security incident. Here's a simplified breakdown of the query:
-
Lookback Period: The query examines activities from the last 30 days.
-
Target User: You can specify a particular user by entering their User Principal Name (UPN). If left blank, the query will include all users, but the results will be limited to 500 rows.
-
Data Source: The query pulls data from the
OfficeActivitytable. -
Filtering: It filters activities based on the specified time period and the target user (if any).
-
Categorization: Activities are categorized into different action types such as Download, Upload/Create, Deletion, Sharing, Messaging, Policy/Rule Change, Permission Change, Label Change, or Other.
-
Summarization: For each user and workload, it summarizes:
- The types of operations performed.
- Files or objects involved.
- Sites accessed.
- Client IP addresses used.
- Total number of events.
- The first and last occurrence of events.
-
Sorting and Limiting: Results are sorted by the number of events, and only the top 500 entries are displayed.
-
Output: The final output includes details like User ID, Workload, Action Category, Event Count, Operations, Files/Objects, Sites, Client IPs, and the timestamps of the first and last events.
This query is useful for gaining a comprehensive view of a user's activity across different Microsoft 365 services, aiding in security investigations.
Details
David Alonso
Released: March 18, 2026
Tables
Keywords
Operators