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 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"]),
    RuleId = tostring(Data["drid"]),
    RuleName = tostring(Data["drn"]),
    ProtectionActions = split(tostring(Data["dact"]), ", "),
    ObjectName = tostring(Data["von"]),
    SenderFromAddress = tostring(Data["mfrm"]),
    RecipientEmailAddress = split(tostring(Data["to"]), ","),
    CCEmailAddress = split(tostring(Data["cc"]), ","),
    BCCEmailAddress = split(tostring(Data["bcc"]), ",")
| where Workload == "Exchange" and tostring(RawEventData["Category"]) == "DataLossPrevention"
| project
    Timestamp,
    Application,
    ActionType,
    AlertId,
    UserPrincipalName,
    Severity,
    Workload,
    SensitiveInformationContentType,
    SensitiveInformationTypeMatchInfo,
    PolicyId,
    PolicyName,
    RuleId,
    RuleName,
    ProtectionActions,
    Subject = ObjectName,
    SenderFromAddress,
    RecipientEmailAddress,
    CCEmailAddress,
    BCCEmailAddress,
    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 (
    EmailEvents
    | where Timestamp > ago(query_period)
    | where NetworkMessageId == tostring(network_message_ids[0])
    | summarize FinalRecipientEmailAddress = array_sort_asc(make_list(RecipientEmailAddress)) by NetworkMessageId, DeliveryAction
    | summarize FinalRecipientEmailAddress = make_bag(bag_pack(DeliveryAction, FinalRecipientEmailAddress)) by NetworkMessageId, DeliveryAction
    ) on NetworkMessageId
| 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