KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 04 M365 DLP Violations Per User 30d

Query

// Hunt    : M365 - DLP Violations and Overrides per User (30d)
// Purpose : Rank users by total DLP violations and overrides over 30 days,
//           surfacing policy names, override justifications, and workloads.
//           Identifies repeat offenders and data types most at risk.
// Tables  : OfficeActivity
// Period  : P30D
//==========================================================================================

let LookbackDays = 30d;

OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where RecordType in (
    "ComplianceDLPExchange",
    "ComplianceDLPSharePoint",
    "ComplianceDLPSharePointClassification")
| extend
    PolicyName         = coalesce(tostring(SRPolicyName), extract('"PolicyName":"([^"]+)"', 1, tostring(Parameters))),
    RuleName           = tostring(SRRuleMatchDetails[0].RuleName),
    Action             = extract('"Actions":"([^"]+)"', 1, tostring(Parameters)),
    SensitiveInfoTypes = tostring(SRRuleMatchDetails[0].SensitiveInfoTypes),
    Justification      = extract('"OverrideJustification":"([^"]+)"', 1, tostring(Parameters))
| extend
    IsOverride    = Action has "Override" or isnotempty(Justification),
    IsBlocked     = Action has_any ("BlockAccess", "BlockWithOverride"),
    Workload      = OfficeWorkload
| summarize
    TotalViolations    = count(),
    Overrides          = countif(IsOverride),
    Blocks             = countif(IsBlocked),
    Policies           = make_set(PolicyName, 10),
    SensitiveTypes     = make_set(SensitiveInfoTypes, 5),
    Workloads          = make_set(Workload, 5),
    LastViolation      = max(TimeGenerated),
    FirstViolation     = min(TimeGenerated)
    by UserId
| sort by TotalViolations desc
| project
    UserId,
    TotalViolations,
    Overrides,
    Blocks,
    Policies,
    SensitiveTypes,
    Workloads,
    FirstViolation,
    LastViolation

Explanation

This query is designed to analyze and rank users based on their Data Loss Prevention (DLP) violations and overrides within the last 30 days. Here's a simplified breakdown:

  1. Data Source: It examines records from the OfficeActivity table, focusing on specific types of compliance-related activities.

  2. Time Frame: The analysis is limited to activities that occurred in the past 30 days.

  3. Data Extraction: For each record, it extracts relevant details such as the policy name, rule name, action taken, sensitive information types involved, and any justification provided for overrides.

  4. Classification:

    • Overrides: Identifies if an action was overridden by checking for specific actions or justifications.
    • Blocks: Identifies actions that were blocked or required an override.

  5. Aggregation:

    • Counts the total number of violations, overrides, and blocks per user.
    • Collects a set of unique policy names, sensitive information types, and workloads associated with each user.
    • Records the first and last violation dates for each user.

  6. Ranking: Users are sorted by the total number of violations in descending order, highlighting those with the most frequent violations.

  7. Output: The final result includes each user's ID, total violations, number of overrides and blocks, associated policies, sensitive information types, workloads, and the dates of their first and last violations. This helps identify repeat offenders and the types of data most at risk.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

OfficeActivity

Keywords

OfficeActivityComplianceDLPExchangeComplianceDLPSharePointComplianceDLPSharePointClassificationPolicyNameRuleNameActionSensitiveInfoTypesJustificationOverrideBlockAccessBlockWithOverrideWorkloadUserId

Operators

letagoincoalescetostringextracthasisnotemptyhas_anysummarizecountcountifmake_setmaxminbysortdescproject

Actions