Query Details
HUNT 09 M365 One Drive Top Downloaders 30d
Query
// Hunt : M365 - Top File Downloaders and Uploaders to OneDrive (30d)
// Purpose : Rank users by total volume of file downloads and uploads in OneDrive
// and SharePoint over 30 days. Surfaces outliers and high-volume movers
// for insider threat, data hoarding, and exfiltration investigations.
// Tables : OfficeActivity
// Period : P30D
//==========================================================================================
let LookbackDays = 30d;
OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where RecordType in ("SharePoint", "OneDrive", "SharePointFileOperation", "OneDriveFileOperation")
| where Operation in (
"FileDownloaded", "FileSyncDownloadedFull", "FileSyncDownloadedPartial",
"FileUploaded", "FileSyncUploadedFull",
"FileCopied", "FileMoved")
| extend ActivityType = case(
Operation has "Download" or Operation has "Sync" and Operation has "Downloaded", "Download",
Operation has "Upload" or Operation has "Sync" and Operation has "Uploaded", "Upload",
Operation has "Copie", "Copy",
"Move")
| summarize
TotalOps = count(),
Downloads = countif(ActivityType == "Download"),
Uploads = countif(ActivityType == "Upload"),
Copies = countif(ActivityType == "Copy"),
UniqueFiles = dcount(SourceFileName),
SiteURLs = make_set(Site_Url, 5),
ClientIPs = make_set(ClientIP, 5),
LastActivity = max(TimeGenerated)
by UserId
| sort by Downloads desc
| extend DownloadUploadRatio = round(todouble(Downloads) / (Uploads + 1), 2)
| project
UserId,
TotalOps,
Downloads,
Uploads,
Copies,
DownloadUploadRatio,
UniqueFiles,
SiteURLs,
ClientIPs,
LastActivity
Explanation
This query is designed to identify and rank users based on their file activity in OneDrive and SharePoint over the past 30 days. It focuses on users who download and upload large volumes of files, which can help in detecting unusual behavior or potential insider threats. Here's a simple breakdown of what the query does:
-
Time Frame: It looks at data from the last 30 days.
-
Data Source: It uses the
OfficeActivitytable, specifically filtering for activities related to SharePoint and OneDrive. -
Activity Types: It considers file operations such as downloads, uploads, copies, and moves.
-
User Activity Summary:
- Counts the total number of operations each user performed.
- Separately counts downloads, uploads, and copies.
- Counts the number of unique files each user interacted with.
- Collects up to 5 unique site URLs and client IPs associated with each user's activities.
- Records the last activity time for each user.
-
Ranking and Metrics:
- Sorts users by the number of downloads in descending order.
- Calculates a download-to-upload ratio for each user.
-
Output: The final result includes each user's ID, total operations, counts of downloads, uploads, copies, the download-upload ratio, number of unique files, a list of site URLs and client IPs, and the time of their last activity.
This query helps identify users with high file movement activity, which could indicate data hoarding or potential data exfiltration risks.
Details
David Alonso
Released: March 18, 2026
Tables
Keywords
Operators