Multiple Zapped Emails With Possibly Malicious Entities Unchecked

Query

let query_frequency = 1h;
let query_period = 14d;
EmailPostDeliveryEvents
| where TimeGenerated > ago(query_frequency)
| where DetectionMethods has_any ("Phish", "Malware")
| as _Auxiliar
| project NetworkMessageId, ThreatTypes, DetectionMethods, ActionType
| join kind=inner (
    EmailEvents
    | where TimeGenerated > ago(query_period)
    | where NetworkMessageId in (toscalar(_Auxiliar | summarize make_set(NetworkMessageId)))
    | project-away ThreatTypes, DetectionMethods
    ) on NetworkMessageId
| project-away NetworkMessageId1
| as _RetroactiveEmails
| union (
    EmailEvents
    | where TimeGenerated > ago(query_period)
    | where DetectionMethods has_any ("Phish", "Malware") and not(DeliveryAction == "Blocked")
    | where not(NetworkMessageId in (toscalar(_RetroactiveEmails | summarize make_set(NetworkMessageId))))
    )
| where not(OrgLevelPolicy in ("Phishing simulation", "SecOps Mailbox"))
| summarize arg_max(TimeGenerated, *) by NetworkMessageId, ReportId
| mv-apply Phish = todynamic(DetectionMethods)["Phish"] to typeof(string), Malware = todynamic(DetectionMethods)["Malware"] to typeof(string) on (
    where not(Phish has_any ("Spoof", "impersonation", "Unknown", "filter", "detonation reputation", "Fingerprint")) or isnotempty(Malware) or ActionType in ("Phish ZAP", "Malware ZAP")
    )
| project-away Phish, Malware
| as _Emails
| join kind=leftouter (
    EmailAttachmentInfo
    | where TimeGenerated > ago(query_period)
    | where NetworkMessageId in (toscalar(_Emails | summarize make_set(NetworkMessageId)))
    | sort by FileType asc, FileName asc
    | summarize
        AttachedFiles = make_set(bag_pack_columns(FileName, FileType))
        by NetworkMessageId
    ) on NetworkMessageId
| project-away NetworkMessageId1
| join kind=leftouter (
    EmailUrlInfo
    | where TimeGenerated > ago(query_period)
    | where NetworkMessageId in (toscalar(_Emails | summarize make_set(NetworkMessageId)))
    | where not(UrlLocation == "Header" and Url contains "unsub")
    | sort by Url asc
    | summarize
        Urls = make_set(bag_pack_columns(Url, UrlLocation)),
        UrlLocations = array_sort_asc(make_set(UrlLocation))
        by NetworkMessageId
    ) on NetworkMessageId
| project-away NetworkMessageId1
| join kind=leftouter (
    UrlClickEvents
    | where TimeGenerated > ago(query_period)
    | where NetworkMessageId in (toscalar(_Emails | summarize make_set(NetworkMessageId)))
    | where isnotempty(Url)
    | sort by Url asc
    | summarize
        Clicks = make_set(bag_pack_columns(Url, ActionType, AccountUpn, IPAddress, ThreatTypes, DetectionMethods)),
        ClickActionTypes = array_sort_asc(make_set(ActionType)),
        AllowedClickUrls = array_sort_asc(make_set_if(Url, ActionType == "ClickAllowed"))
        by NetworkMessageId
    ) on NetworkMessageId
| project-away NetworkMessageId1
| join kind=leftouter (
    AlertEvidence
    | where TimeGenerated > ago(query_period)
    | where NetworkMessageId in (toscalar(_Emails | summarize make_set(NetworkMessageId)))
    | distinct NetworkMessageId, AlertId
    | join kind=inner (
        AlertEvidence
        | where TimeGenerated > ago(query_period)
        | where isnotempty(RemoteUrl)
        | project
            AlertId,
            Title,
            RemoteUrl,
            //AdditionalFields,
            SourceEntityType = tostring(todynamic(AdditionalFields)["SourceEntityType"]),
            LastVerdict = tostring(todynamic(AdditionalFields)["LastVerdict"])
        ) on AlertId
    | project-away AlertId1
    | summarize
        AlertIds = make_set(AlertId)
        by NetworkMessageId, RemoteUrl, SourceEntityType, LastVerdict, Title
    | summarize
        Alerts = make_bag(bag_pack(Title, AlertIds))
        by NetworkMessageId, RemoteUrl, SourceEntityType, LastVerdict
    | summarize
        AlertedUrls = make_set(bag_pack(RemoteUrl, Alerts)),
        MaliciousUrls = array_sort_asc(make_set_if(RemoteUrl, SourceEntityType == "MaliciousUrl" or LastVerdict == "Malicious" or bag_keys(Alerts) has_any ("malicious URL")))
        by NetworkMessageId
    ) on NetworkMessageId
| project-away NetworkMessageId1
| where not(
    isempty(AttachedFiles) and AttachmentCount == 0
    and (isempty(ClickActionTypes) or (array_length(ClickActionTypes) == 1 and tostring(ClickActionTypes[0]) == "ClickBlocked"))
    and array_length(UrlLocations) == 1 and tostring(UrlLocations[0]) == "Body"
    and not(DeliveryAction == "Blocked"))
| extend AlertSeverity = case(
    isempty(AttachedFiles) and array_length(Urls) == 1 and array_length(ClickActionTypes) == 1 and tostring(ClickActionTypes[0]) == "ClickAllowed", "High",
    "Medium"
    )
| project
    TimeGenerated,
    Timestamp,
    NetworkMessageId,
    InternetMessageId,
    EmailDirection,
    EmailClusterId,
    SenderMailFromAddress,
    SenderFromAddress,
    SenderDisplayName,
    SenderObjectId,
    SenderIPv4,
    SenderIPv6,
    To,
    Cc,
    DistributionList,
    RecipientEmailAddress,
    RecipientObjectId,
    Subject,
    AuthenticationDetails,
    ExchangeTransportRule,
    OrgLevelPolicy,
    OrgLevelAction,
    EmailActionPolicy,
    EmailAction,
    UserLevelPolicy,
    UserLevelAction,
    DeliveryAction,
    DeliveryLocation,
    IsFirstContact,
    EmailLanguage,
    ThreatTypes,
    DetectionMethods,
    ConfidenceLevel,
    ThreatNames,
    EmailSize,
    AttachmentCount,
    AttachedFiles,
    UrlCount,
    Urls,
    UrlLocations,
    Clicks,
    ClickActionTypes,
    AllowedClickUrls,
    AlertedUrls,
    MaliciousUrls,
    ReportId,
    SenderFromDomain,
    SenderMailFromDomain,
    RecipientDomain,
    AlertSeverity

Explanation

This query is designed to analyze email events over a specified period to identify potential threats such as phishing or malware. Here's a simplified breakdown of what the query does:

Set Parameters: It defines two time periods: query_frequency (1 hour) and query_period (14 days).
Filter Recent Threats: It looks at email post-delivery events from the last hour to find emails detected with phishing or malware.
Join with Historical Data: It matches these recent threats with email events from the last 14 days to gather more context, excluding certain threat types and actions.
Exclude Simulations: It filters out emails related to phishing simulations or specific mailboxes used by security operations.
Summarize and Filter: It summarizes the data to keep only the most recent event for each email and applies additional filters to exclude certain types of phishing detections unless specific conditions are met.
Gather Attachments and URLs: It collects information about email attachments and URLs, excluding unsubscribing links in headers.
Track URL Clicks: It gathers data on URL clicks within the emails, including the actions taken (e.g., allowed or blocked clicks).
Integrate Alert Data: It joins with alert evidence to identify any URLs flagged as malicious and summarizes alert information.
Filter Out Non-Threatening Emails: It removes emails that don't have attachments, clicks, or URLs in the body unless they were blocked.
Determine Alert Severity: It assigns a severity level to each email based on the presence of attachments, URLs, and click actions.
Project Final Data: Finally, it selects and organizes a wide range of email attributes and threat information for further analysis or reporting.

Overall, this query is a comprehensive analysis tool for identifying and understanding email threats within an organization over a two-week period.

Details

Jose Sebastián Canós

Released: June 8, 2026

Tables

EmailPostDeliveryEventsEmailEventsEmailAttachmentInfoEmailUrlInfoUrlClickEventsAlertEvidence

Keywords

EmailPostDeliveryEventsAttachmentInfoUrlClickAlertEvidenceNetworkMessageIdThreatTypesDetectionMethodsActionTypeTimeGeneratedFileNameLocationAccountUpnIPAddressTitleRemoteSourceEntityLastVerdictAdditionalFieldsAlertsMaliciousUrlsSeverityTimestampInternetDirectionClusterSenderMailFromDisplayObjectIPv4IPv6ToCcDistributionListRecipientSubjectAuthenticationDetailsExchangeTransportRuleOrgLevelPolicyUserIsFirstContactLanguageConfidenceNamesSizeCountAttachedFilesLocationsClicksAllowedAlertedReportDomain

Operators

lethas_anyasprojectjoinkindintoscalarsummarizemake_setproject-awayunionandnotwhereisnotemptymv-applytodynamictotypeofarray_sort_ascmake_set_ifbag_pack_columnsbag_packmake_bagbag_keysdistinctcaseisemptyarray_lengthextend

Actions

GitHub

KQL Search