KQL Search

let alert_id = "<<<>>>";
let query_period = 1d;
let internet_message_ids = toscalar(
    AlertEvidence
    | where Timestamp > ago(query_period)
    | where AlertId == strcat("dl", alert_id) and EntityType == "MailMessage"
    | extend AdditionalFields = todynamic(AdditionalFields)
    | summarize make_set(AdditionalFields["InternetMessageId"])
);
let network_message_ids = toscalar(
    AlertEvidence
    | where Timestamp > ago(query_period)
    | where AlertId == strcat("dl", alert_id) and EntityType == "MailMessage"
    | summarize make_set(NetworkMessageId)
);
CloudAppEvents
| where Timestamp > ago(query_period)
| where ActionType == "AlertTriggered" and tostring(RawEventData["AlertId"]) == alert_id
| 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"]), ";")
| where Workload == "Exchange" and tostring(RawEventData["Category"]) == "DataLossPrevention"
| project
    Timestamp,
    Application,
    ActionType,
    AlertId,
    UserPrincipalName,
    Severity,
    Workload,
    SensitiveInformationContentType,
    SensitiveInformationTypeMatchInfo,
    AlertTriggered_RawEventData = RawEventData,
    AlertTriggered_ReportId = ReportId,
    // We will assume there is only 1 email associated to an alert
    InternetMessageId = tostring(internet_message_ids[0]),
    NetworkMessageId = tostring(network_message_ids[0])
| lookup kind=leftouter (
    EmailUrlInfo
    | where Timestamp > ago(query_period)
    | where NetworkMessageId == tostring(network_message_ids[0])
    | summarize EmailUrlInfo = make_list(bag_pack("Url", Url, "UrlDomain", UrlDomain, "UrlLocation", UrlLocation)) by NetworkMessageId
    ) on NetworkMessageId
| lookup kind=leftouter (
    EmailAttachmentInfo
    | where Timestamp > ago(query_period)
    | where NetworkMessageId == tostring(network_message_ids[0])
    | distinct NetworkMessageId, FileName, FileType, SHA256, FileSize
    | summarize EmailAttachmentInfo = make_list(bag_pack("FileName", FileName, "FileType", FileType, "FileSize", FileSize, "SHA256", SHA256)) by NetworkMessageId
    ) on NetworkMessageId
| lookup kind=leftouter (
    CloudAppEvents
    | where Timestamp > ago(query_period)
    | where ActionType == "AlertEntityGenerated" and tostring(RawEventData["AlertId"]) == alert_id
    | extend
        AlertId = tostring(RawEventData["AlertId"]),
        EntityType = tostring(RawEventData["EntityType"]),
        Data = todynamic(tostring(RawEventData["Data"]))
    | where EntityType == "DlpRuleMatch"
    | extend
        PolicyId = coalesce(tostring(Data["cid"]), tostring(Data["dpid"])),
        PolicyName = tostring(Data["dpn"]),
        ManagementRuleId = tostring(Data["dmrid"]),
        RuleId = tostring(Data["drid"]),
        RuleName = tostring(Data["drn"]),
        ProtectionActions = split(tostring(Data["dact"]), ", "),
        ObjectName = tostring(Data["von"]),
        DMC = split(tostring(Data["dmc"]), ","),
        SenderFromAddress = tostring(Data["mfrm"]),
        RecipientEmailAddress = split(tostring(Data["to"]), ","),
        CCEmailAddress = split(tostring(Data["cc"]), ","),
        BCCEmailAddress = split(tostring(Data["bcc"]), ",")
    | project
        AlertId,
        PolicyId,
        PolicyName,
        ManagementRuleId,
        RuleId,
        RuleName,
        DMC,
        ProtectionActions,
        Subject = ObjectName,
        SenderFromAddress,
        RecipientEmailAddress,
        CCEmailAddress,
        BCCEmailAddress,
        AlertEntityGenerated_DlpRuleMatch_RawEventData = RawEventData,
        AlertEntityGenerated_DlpRuleMatch_ReportId = ReportId
    ) on AlertId