KQL Search

Query Details

HomeAI ToolsDevice Query

RULE 08 M365 Share Point Mass File Download

Query

// Rule    : M365 - SharePoint / OneDrive Mass File Download or Sync
// Severity: High
// Tactics : Collection, Exfiltration
// MITRE   : T1530 (Data from Cloud Storage), T1537 (Transfer Data to Cloud Account)
// Freq    : PT1H   Period: PT1H
// Description: Detects anomalous bulk file downloads or OneDrive sync activity
//              from SharePoint. Baseline uses 14-day per-user average; triggers when
//              current hour exceeds 3x baseline or an absolute threshold.
//==========================================================================================

let AbsoluteThreshold = 100;        // files per hour trigger regardless of baseline
let BaselineMultiplier = 3.0;
let LookbackPeriod     = 1h;
let BaselineDays       = 14d;

// Per-user baseline download average per hour
let Baseline = OfficeActivity
    | where TimeGenerated between (ago(BaselineDays) .. ago(LookbackPeriod))
    | where RecordType in ("SharePoint", "OneDrive")
    | where Operation in ("FileDownloaded", "FileSyncDownloadedFull", "FileSyncDownloadedPartial")
    | summarize HourlyEvents = count() by UserId, bin(TimeGenerated, 1h)
    | summarize AvgHourly = avg(HourlyEvents), StdDev = stdev(HourlyEvents) by UserId;

// Current hour
OfficeActivity
| where TimeGenerated > ago(LookbackPeriod)
| where RecordType in ("SharePoint", "OneDrive")
| where Operation in ("FileDownloaded", "FileSyncDownloadedFull", "FileSyncDownloadedPartial")
| summarize
    DownloadCount   = count(),
    FileList        = make_set(SourceFileName, 20),
    SiteURLs        = make_set(SiteUrl, 5),
    ClientIPs       = make_set(ClientIP, 5),
    UserAgents      = make_set(UserAgent, 3)
    by UserId
| join kind=leftouter Baseline on UserId
| extend
    AvgHourly       = coalesce(AvgHourly, 0.0),
    Threshold       = max_of(todouble(AbsoluteThreshold), AvgHourly * BaselineMultiplier)
| where DownloadCount >= Threshold
| extend AlertSeverity = case(
    DownloadCount >= 500, "High",
    DownloadCount >= 200, "Medium",
    "Low")
| project
    TimeGenerated  = now(),
    UserId,
    DownloadCount,
    AvgHourlyBaseline = round(AvgHourly, 1),
    Threshold      = round(Threshold, 0),
    FileList,
    SiteURLs,
    ClientIPs,
    AlertSeverity

Explanation

This query is designed to detect unusual bulk file downloads or synchronization activities from SharePoint or OneDrive, which could indicate potential data exfiltration. Here's a simplified breakdown of what the query does:

  1. Thresholds and Baseline Setup:

    • It sets an absolute threshold of 100 files per hour, which will trigger an alert regardless of the user's normal activity.
    • It calculates a baseline average of file downloads per hour for each user over the past 14 days.

  2. Baseline Calculation:

    • The query looks at historical data from the past 14 days to calculate the average number of files downloaded per hour by each user.
    • It considers activities related to file downloads and syncs from SharePoint and OneDrive.

  3. Current Activity Monitoring:

    • It examines file download activities in the current hour.
    • It counts the number of files downloaded and collects additional information like file names, site URLs, client IPs, and user agents.

  4. Comparison and Alerting:

    • For each user, it compares the current hour's download count against the higher of either the absolute threshold (100 files) or three times their baseline average.
    • If the current download count exceeds this threshold, it triggers an alert.

  5. Alert Severity:

    • The severity of the alert is determined by the number of files downloaded:
      • "High" if 500 or more files are downloaded.
      • "Medium" if between 200 and 499 files are downloaded.
      • "Low" for fewer than 200 files, but still above the threshold.

  6. Output:

    • The query outputs the current time, user ID, download count, baseline average, threshold, list of files, site URLs, client IPs, and alert severity.

In essence, this query helps identify potential security incidents by flagging users who download an unusually large number of files from SharePoint or OneDrive within a short period.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

OfficeActivity

Keywords

SharePointOneDriveFileUserSiteClient

Operators

letbetweeninsummarizebybinavgstdevwhereagocountmake_setjoinkindonextendcoalescemax_oftodoublecaseprojectnowround

Actions