Purview DLP One Drive Alert Info

Query

let alert_id = "<<<>>>";
let query_period = 1d;
let _FilePaths =
    AlertEvidence
    | where Timestamp > ago(query_period)
    | where EntityType == "File" and AlertId == strcat("dl", alert_id)
    | extend AdditionalFields = todynamic(AdditionalFields)
    | distinct SourceFilePath = strcat(AdditionalFields["Directory"], "/", AdditionalFields["Name"]), ObjectName = url_decode(tostring(AdditionalFields["Name"]))
;
CloudAppEvents
| where Timestamp > ago(query_period)
| where ActionType in ("AlertTriggered", "AlertUpdated") and tostring(RawEventData["AlertId"]) == alert_id
| summarize arg_max(Timestamp, *)
| extend
    AlertId = tostring(RawEventData["AlertId"]),
    Data = todynamic(tostring(RawEventData["Data"]))
| extend
    UserPrincipalName = tolower(tostring(Data["f3u"])),
    Severity = tostring(Data["sev"]),
    Workload = tostring(Data["wl"]),
    SensitiveInformationContentType = split(tostring(Data["sict"]), ";"),
    SensitiveInformationTypeMatchInfo = split(tostring(Data["sitmi"]), ";"),
    PolicyId = tostring(Data["dpid"]),
    PolicyName = tostring(Data["dpn"]),
    ManagementRuleId = tostring(Data["drid"]),
    //RuleId = tostring(Data["drid"]),
    RuleName = tostring(Data["drn"]),
    ProtectionActions = split(tostring(Data["dact"]), ", "),
    ObjectName = tostring(Data["von"]),
    DMC = split(tostring(Data["dmc"]), ","),
    FileOwnerName = tolower(tostring(Data["flmb"])), // It might not the same as f3u - UserPrincipalName
    FileOwnerDisplayName = tostring(Data["fo"]),
    USI = tostring(Data["usi"])
| where Workload == "OneDrive" and tostring(RawEventData["Category"]) == "DataLossPrevention"
| lookup kind=leftouter _FilePaths on ObjectName
| project
    Timestamp,
    Application,
    ActionType,
    AlertId,
    UserPrincipalName,
    Severity,
    Workload,
    SensitiveInformationContentType,
    SensitiveInformationTypeMatchInfo,
    PolicyId,
    PolicyName,
    ManagementRuleId,
    //RuleId,
    RuleName,
    DMC,
    ProtectionActions,
    ObjectName,
    SourceFilePath,
    FileOwnerName,
    FileOwnerDisplayName,
    USI,
    AlertTriggered_RawEventData = RawEventData,
    AlertTriggered_ReportId = ReportId
// | summarize arg_max(CreationTime, *) by AlertId, Timestamp, SourceFilePath, ObjectName, ManagementRuleId
| join hint.strategy=shuffle kind=leftouter (
    CloudAppEvents
    | where Timestamp > ago(query_period)
    | where ActionType in ("DLPRuleMatch", "DLPRuleUndo") and Application == "Microsoft OneDrive for Business"
    | mv-expand PolicyDetail = iff(array_length(RawEventData["PolicyDetails"]) > 0, RawEventData["PolicyDetails"], dynamic([""]))
    | mv-expand Rule = iff(array_length(PolicyDetail["Rules"]) > 0, PolicyDetail["Rules"], dynamic([""]))
    | mv-apply SensitiveInformationItem = Rule["ConditionsMatched"]["SensitiveInformation"] on (
        summarize SensitiveInformation = make_list(bag_remove_keys(SensitiveInformationItem, dynamic(["SensitiveInformationDetailedClassificationAttributes"])))
    )
    | project
        CreationTime = todatetime(RawEventData["CreationTime"]),
        ManagementRuleId = tostring(Rule["ManagementRuleId"]),
        ObjectName,
        //IncidentId = tostring(RawEventData["IncidentId"]),
        SensitiveInformation,
        ActionType,
        Reason = tostring(RawEventData["ExceptionInfo"]["Reason"]),
        //SharePointMetaData = RawEventData["SharePointMetaData"],
        SiteCollectionUrl = tostring(RawEventData["SharePointMetaData"]["SiteCollectionUrl"]),
        //IsViewableByExternalUsers = tostring(RawEventData["SharePointMetaData"]["IsViewableByExternalUsers"]),
        //ItemLastSharedTime = todatetime(RawEventData["SharePointMetaData"]["ItemLastSharedTime"]),
        //SharedBy = todatetime(RawEventData["SharePointMetaData"]["SharedBy"]),
        DLPRuleMatch_RawEventData = RawEventData,
        DLPRuleMatch_ReportId = ReportId
    // The same file can be updated, and suffer several "DLPRuleMatch" and "DLPRuleUndo" actions
    | summarize
        EvaluationEventCount = count(),
        arg_min(CreationTime, SensitiveInformation, SiteCollectionUrl, DLPRuleMatch_RawEventData, DLPRuleMatch_ReportId),
        (LastEvaluationTime, LastActionType, LastReason, LastSensitiveInformation) = arg_max(CreationTime, ActionType, Reason, SensitiveInformation)
        by ObjectName, ManagementRuleId
    | extend
        LastEvaluationTime = iff(EvaluationEventCount == 1, datetime(null), LastEvaluationTime),
        LastActionType = iff(EvaluationEventCount == 1, "", LastActionType),
        LastReason = iff(EvaluationEventCount == 1, "", LastReason),
        LastSensitiveInformation = iff(EvaluationEventCount == 1, dynamic(null), LastSensitiveInformation)
    ) on $left.SourceFilePath == $right.ObjectName, ManagementRuleId //, CreationTime does not always match
| project-away ObjectName1, ManagementRuleId1
// Add info about the sharing targets
// Did not have any example to work with, where a DLP alert happened and the file had ActionType in ("AddedToGroup", "AddedToSecureLink", "AddedToSharingLink")
| as hint.materialized=true _Events
| join kind=leftouter (
    CloudAppEvents
    | where Timestamp > ago(query_period)
    | where Application == "Microsoft OneDrive for Business" and ActionType in ("AddedToGroup", "AddedToSecureLink", "AddedToSharingLink") // "GroupAdded", "SecureLinkCreated", "SharingLinkCreated", "SharingSet", "SharingInheritanceBroken"
    | extend
        UserId = tostring(RawEventData["UserId"]),
        CreationTime = todatetime(RawEventData["CreationTime"]),
        SharingOperation = tostring(RawEventData["Operation"]),
        SiteUrl = tostring(RawEventData["SiteUrl"]),
        ListId = tostring(RawEventData["ListId"]),
        ListItemUniqueId = tostring(RawEventData["ListItemUniqueId"]),
        ObjectName = tostring(RawEventData["ObjectId"]),
        ItemType = tostring(RawEventData["ItemType"]),
        Permission = tostring(RawEventData["Permission"]),
        SharingLinkScope = tostring(RawEventData["SharingLinkScope"]),
        EventData = tostring(RawEventData["EventData"]),
        ModifiedProperties = tostring(RawEventData["ModifiedProperties"]),
        TargetUserOrGroupName = tostring(RawEventData["TargetUserOrGroupName"]),
        TargetUserOrGroupType = tostring(RawEventData["TargetUserOrGroupType"]),
        UniqueSharingId = tostring(RawEventData["UniqueSharingId"]),
        CorrelationId = tostring(RawEventData["CorrelationId"]),
        SharingEvent_RawEventData = RawEventData,
        SharingEvent_ReportId = ReportId
    | where tostring(toscalar(_Events | summarize make_set_if(url_decode(SourceFilePath), isnotempty(SourceFilePath)))) has ObjectName
    | summarize
        CreationTime = min(CreationTime),
        TargetUserOrGroupName = array_sort_asc(make_set_if(TargetUserOrGroupName, isnotempty(TargetUserOrGroupName))) // You could exclude targets from the same tenant
        by CorrelationId, SiteUrl, UserId, SharingOperation, UniqueSharingId, ObjectName, ItemType, Permission, SharingLinkScope, EventData, TargetUserOrGroupType
    | summarize
        CreationTime = min(CreationTime),
        TargetUserOrGroup = make_bag(bag_pack(TargetUserOrGroupType, TargetUserOrGroupName))
        by CorrelationId, SiteUrl, UserId, SharingOperation, UniqueSharingId, ObjectName, ItemType, Permission, SharingLinkScope, EventData
    | extend EmptyColumns = array_concat(
        iff(isnotempty(Permission), dynamic(null), dynamic(["Permission"])),
        iff(isnotempty(SharingLinkScope), dynamic(null), dynamic(["SharingLinkScope"])),
        iff(isnotempty(EventData), dynamic(null), dynamic(["EventData"]))
        )
    | summarize
        CreationTime = min(CreationTime),
        Operations = make_bag(bag_pack(SharingOperation, bag_remove_keys(bag_pack_columns(Permission, SharingLinkScope, EventData), EmptyColumns))),
        take_any(TargetUserOrGroup)
        by CorrelationId, SiteUrl, UserId, UniqueSharingId, ObjectName, ItemType, Auxiliar = tostring(TargetUserOrGroup)
    | summarize
        CreationTime = min(CreationTime),
        Objects = make_list(bag_pack_columns(ObjectName, ItemType, Operations)),
        take_any(TargetUserOrGroup)
        by CorrelationId, SiteUrl, UserId, Auxiliar
    | summarize
        SharingTime = min(CreationTime),
        SharingTargets = make_list(bag_pack_columns(TargetUserOrGroup, Objects))
        by CorrelationId, SiteUrl, UserId
    | sort by SharingTime asc
    | summarize
        SharingEvents = make_list(bag_pack_columns(SharingTime, CorrelationId, SharingTargets))
        by SiteUrl, UserId
    | project
        SiteCollectionUrl = SiteUrl,
        UserPrincipalName = UserId,
        SharingEvents
    ) on SiteCollectionUrl, UserPrincipalName
| project-away SiteCollectionUrl1, UserPrincipalName1
| project-reorder
    Timestamp,
    Application,
    ActionType,
    AlertId,
    UserPrincipalName,
    Severity,
    Workload,
    SensitiveInformationContentType,
    SensitiveInformationTypeMatchInfo,
    PolicyId,
    PolicyName,
    ManagementRuleId,
    //RuleId,
    RuleName,
    DMC,
    ProtectionActions,
    ObjectName,
    SourceFilePath,
    FileOwnerName,
    FileOwnerDisplayName,
    USI,
    AlertTriggered_RawEventData,
    AlertTriggered_ReportId,
    CreationTime,
    SensitiveInformation,
    EvaluationEventCount,
    LastEvaluationTime,
    LastActionType,
    LastReason,
    LastSensitiveInformation,
    SiteCollectionUrl,
    DLPRuleMatch_RawEventData,
    DLPRuleMatch_ReportId,
    SharingEvents

Explanation

This KQL query is designed to analyze and summarize data loss prevention (DLP) alerts and related file activities within Microsoft OneDrive for Business over a specified period (1 day in this case). Here's a simplified breakdown of what the query does:

Define Variables:
- alert_id: A placeholder for a specific alert ID.
- query_period: The time frame for the query, set to 1 day.
Extract File Paths:
- From the AlertEvidence table, it retrieves distinct file paths and names associated with the specified alert ID.
Retrieve Alert Events:
- From the CloudAppEvents table, it fetches events related to the specified alert ID, focusing on actions like "AlertTriggered" and "AlertUpdated".
- It extracts various details such as user information, severity, workload, policy details, and file owner information.
Filter for OneDrive DLP Events:
- Filters the events to only include those related to OneDrive and categorized under "DataLossPrevention".
Join with File Paths:
- Joins the alert events with the previously extracted file paths to correlate file activities with alerts.
Analyze DLP Rule Matches:
- Further joins with CloudAppEvents to analyze DLP rule matches and undos, extracting sensitive information and rule evaluation details.
Identify Sharing Events:
- Identifies sharing actions (like adding to a group or creating a sharing link) related to the files in question.
- Summarizes sharing events, including details about the users or groups with whom the files were shared.
Project and Reorder Results:
- Projects the final set of columns to display, including timestamps, user details, alert and policy information, file paths, sensitive information, and sharing events.

Overall, this query is used to track and analyze DLP alerts and related file activities, providing insights into how files are being accessed, shared, and protected within OneDrive for Business.

Details

Jose Sebastián Canós

Released: May 7, 2025

Tables

AlertEvidenceCloudAppEvents

Keywords

AlertEvidenceCloudAppEventsRawEventDataDataLossPreventionOneDriveMicrosoftForBusinessSensitiveInformationPolicyDetailsSharePointMetaData

Operators

let=>agoandstrcatextendtodynamicdistincturl_decodetostringinsummarizearg_maxtolowersplitlookupkind=leftouteronprojectproject-awayjoinhint.strategy=shufflemv-expandiffarray_lengthdynamicmv-applymake_listbag_remove_keysarg_minbydatetimeashint.materialized=truearray_sort_ascmake_set_ifisnotemptymake_bagbag_packbag_pack_columnsarray_concattake_anysortascproject-reorder

Actions

GitHub

KQL Search