KQL Search

Query Details

HomeAI ToolsDevice Query

RULE 25 M365 Risky Sign In File Download Correlation

Query

// Rule    : M365 - Risky AAD Sign-In Correlated with M365 File Download Spike
// Severity: High
// Tactics : InitialAccess, Collection, Exfiltration
// MITRE   : T1078.004 (Valid Accounts: Cloud Accounts),
//           T1530 (Data from Cloud Storage)
// Freq    : PT1H   Period: PT1H
// Description: Correlates Microsoft Entra ID (AAD) risky sign-in events with a
//              simultaneous M365 SharePoint/OneDrive file download spike for the same
//              user. A risky sign-in alone may be a false positive; a download spike
//              immediately following confirms active abuse. The combined signal
//              significantly elevates confidence and severity of the finding.
//
//              Tables : SigninLogs, AADUserRiskEvents, OfficeActivity
//==========================================================================================

let LookbackPeriod    = 1h;
let DownloadThreshold = 20;     // files downloaded in the same hour as risky sign-in

// Step 1: Users with risky sign-ins in the current window
let RiskySignIns = SigninLogs
    | where TimeGenerated > ago(LookbackPeriod)
    | where RiskLevelDuringSignIn in ("high", "medium")
        or RiskState in ("atRisk", "confirmedCompromised")
    | extend UserPrincipalNameLower = tolower(UserPrincipalName)
    | summarize
        RiskLevel       = max(RiskLevelDuringSignIn),
        RiskState       = any(RiskState),
        RiskReasons     = make_set(RiskDetail, 5),
        RiskIPs         = make_set(IPAddress, 5),
        SignInApps      = make_set(AppDisplayName, 5),
        RiskFirstSeen   = min(TimeGenerated),
        RiskLastSeen    = max(TimeGenerated)
        by UserPrincipalNameLower;

// Step 2: Users with download spikes in the same window
let DownloadSpike = OfficeActivity
    | where TimeGenerated > ago(LookbackPeriod)
    | where RecordType in ("SharePoint", "OneDrive")
    | where Operation in (
        "FileDownloaded", "FileSyncDownloadedFull",
        "FileSyncDownloadedPartial", "MailItemsAccessed")
    | extend UserIdLower = tolower(UserId)
    | summarize
        DownloadCount   = count(),
        FileList        = make_set(SourceFileName, 15),
        SiteURLs        = make_set(SiteUrl, 5),
        DownloadIPs     = make_set(ClientIP, 5)
        by UserIdLower
    | where DownloadCount >= DownloadThreshold;

// Step 3: Correlate — same user appears in BOTH datasets
RiskySignIns
| join kind=inner DownloadSpike
    on $left.UserPrincipalNameLower == $right.UserIdLower
| extend
    // Did the risky sign-in IP also appear in the download session?
    SameIPForSignInAndDownload = set_intersect(RiskIPs, DownloadIPs)
| extend AlertSeverity = case(
    RiskState == "confirmedCompromised",             "Critical",
    array_length(SameIPForSignInAndDownload) > 0,    "Critical",
    RiskLevel == "high" and DownloadCount >= 100,    "High",
    RiskLevel == "high",                             "High",
    "Medium")
| project
    TimeGenerated          = RiskLastSeen,
    UserId                 = UserPrincipalNameLower,
    RiskLevel,
    RiskState,
    RiskReasons,
    RiskIPs,
    DownloadCount,
    FileList,
    SiteURLs,
    DownloadIPs,
    SameIPForSignInAndDownload,
    AlertSeverity

Explanation

This query is designed to detect potential security threats by correlating risky sign-in events with unusual file download activity in Microsoft 365 environments. Here's a simplified breakdown of what the query does:

  1. Lookback Period: The query examines data from the past hour.

  2. Risky Sign-Ins: It identifies users who have had risky sign-in attempts within the last hour. These sign-ins are considered risky if they have a high or medium risk level, or if the account is marked as "at risk" or "confirmed compromised." The query collects details like risk level, risk reasons, IP addresses used, and the applications accessed during these sign-ins.

  3. Download Spikes: It looks for users who have downloaded a large number of files (at least 20) from SharePoint or OneDrive within the same hour. It gathers information about the files downloaded, the sites accessed, and the IP addresses used during these downloads.

  4. Correlation: The query then finds users who appear in both the risky sign-in list and the download spike list. This correlation suggests that a risky sign-in was followed by a significant file download activity, which could indicate malicious behavior.

  5. Alert Severity: The query assigns a severity level to each correlated event based on certain conditions:

    • "Critical" if the account is confirmed compromised or if the same IP address was used for both the risky sign-in and the download.
    • "High" if the risk level is high and the download count is 100 or more.
    • "Medium" for other cases.

  6. Output: Finally, it outputs relevant details such as the time of the last risky sign-in, user ID, risk level, reasons for risk, IP addresses, download count, list of files downloaded, site URLs, and the severity of the alert.

In summary, this query helps identify potential security incidents by linking risky sign-ins with unusual file download activities, thereby increasing the confidence in detecting active abuse.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

SigninLogsAADUserRiskEventsOfficeActivity

Keywords

RiskySignInsSigninLogsRiskLevelDuringSignInRiskStateUserPrincipalNameLowerRiskReasonsRiskIPsSignInAppsRiskFirstSeenRiskLastSeenDownloadSpikeOfficeActivityRecordTypeOperationUserIdLowerDownloadCountFileListSiteURLsDownloadIPsSameIPForSignInAndDownloadAlertSeverityUserId

Operators

letagointolowersummarizemaxanymake_setmincountjoinonset_intersectcasearray_lengthproject

Actions