KQL Search

Query Details

X 2 Purview DLP X Office Activity Downloads

Query

// X-2 - Purview DLP match ⨯ OfficeActivity SharePoint download by same user (±30 min)
// Run in: security.microsoft.com -> Hunting -> Advanced hunting (UNIFIED PORTAL)
let dlpMatches =
    MicrosoftPurviewInformationProtection
    | where TimeGenerated > ago(1d)
    | where isnotempty(ExecutionRuleName)
    | project DlpTime=TimeGenerated, UserId, ItemName, PolicyName, ExecutionRuleName;
let spDownloads =
    OfficeActivity
    | where TimeGenerated > ago(1d)
    | where Operation in ("FileDownloaded","FileSyncDownloadedFull","FileAccessed")
    | project SpTime=TimeGenerated, UserId, OfficeObjectId, ClientIP, UserAgent;
dlpMatches
| join kind=inner spDownloads on UserId
| where SpTime between (DlpTime - 30m .. DlpTime + 30m)
| summarize Hits=count(),
            DlpRules=make_set(ExecutionRuleName, 10),
            Files=make_set(ItemName, 20),
            SpObjects=make_set(OfficeObjectId, 20),
            FirstSeen=min(DlpTime), LastSeen=max(DlpTime)
        by UserId, ClientIP
| where Hits >= 5
| order by Hits desc

Explanation

This query is designed to identify users who have both triggered a Data Loss Prevention (DLP) rule and downloaded files from SharePoint within a 30-minute window. Here's a simplified breakdown:

  1. Data Collection:

    • The query first collects DLP matches from the MicrosoftPurviewInformationProtection table, focusing on events from the last day where a DLP rule was executed.
    • It also gathers SharePoint download activities from the OfficeActivity table, again from the last day, specifically looking for file download or access operations.

  2. Data Matching:

    • It then joins these two datasets based on the UserId, meaning it looks for instances where the same user appears in both datasets.

  3. Time Correlation:

    • The query filters the joined data to find cases where the SharePoint download activity occurred within 30 minutes before or after the DLP match.

  4. Summarization:

    • For each user, it counts the number of such matched events (Hits).
    • It compiles lists of unique DLP rules triggered, file names involved, and SharePoint object IDs accessed.
    • It also records the first and last time these events were seen for each user.

  5. Filtering and Ordering:

    • It only keeps records where a user has at least 5 such matched events.
    • Finally, it orders the results by the number of hits in descending order, highlighting users with the most frequent occurrences of this behavior.

In essence, this query helps identify users who might be involved in suspicious activities by correlating DLP rule triggers with SharePoint downloads in a short time frame.

Details

David Alonso profile picture

David Alonso

Released: May 25, 2026

Tables

MicrosoftPurviewInformationProtectionOfficeActivity

Keywords

MicrosoftPurviewInformationProtectionOfficeActivityUserIdItemNamePolicyNameExecutionRuleNameTimeGeneratedOperationOfficeObjectIdClientIPUserAgent

Operators

let|whereisnotemptyprojectinjoinonbetweensummarizecountmake_setminmaxbyorder bydesc

Actions

GitHub