KQL Search

Query Details

HomeAI ToolsDevice Query

Quarantined Emails

Query

let query_frequency = 7d;
let query_period = 30d;
let query_wait = 1h;
let _PostDelivery_MovedToQuarantine =
    EmailPostDeliveryEvents
    | where Timestamp between (ago(query_frequency + query_wait) .. ago(query_wait))
    | where Action == "Moved to quarantine" or (ActionType has "ZAP" and not(Action == "Add message info only"))
    | join kind=rightsemi (
        EmailPostDeliveryEvents
        | where Timestamp > ago(query_period)
        ) on NetworkMessageId
    | summarize arg_max(Timestamp, *) by NetworkMessageId
    | where DeliveryLocation == "Quarantine"
    | project
        NetworkMessageId,
        InternetMessageId,
        PostDelivery_DeliveryLocation = DeliveryLocation,
        PostDelivery_ActionTrigger = ActionTrigger,
        PostDelivery_ActionType = ActionType,
        PostDelivery_Action = Action,
        PostDelivery_ThreatTypes = ThreatTypes,
        PostDelivery_DetectionMethods = DetectionMethods,
        PostDelivery_ActionResult = ActionResult,
        PostDelivery_SenderFromAddress = SenderFromAddress,
        PostDelivery_RecipientEmailAddress = RecipientEmailAddress
    | as _AuxiliarEvents
    | join kind=leftouter (
            EmailEvents
            | where Timestamp > ago(query_period)
            | where NetworkMessageId in (toscalar(_AuxiliarEvents | summarize make_set(NetworkMessageId)))
            | summarize arg_max(Timestamp, *) by NetworkMessageId, InternetMessageId, SenderFromAddress, RecipientEmailAddress
        ) on NetworkMessageId
    | where isnotempty(DeliveryLocation) and isnotempty(LatestDeliveryLocation) and DeliveryLocation != LatestDeliveryLocation
    | project-away NetworkMessageId1, InternetMessageId1
;
let _EmailEvents_Quarantined =
    EmailEvents
    | where Timestamp between (ago(query_frequency + query_wait) .. ago(query_wait))
    | where DeliveryLocation == "Quarantine" or LatestDeliveryLocation == "Quarantine" or DeliveryAction in ("Blocked", "Moved to quarantine") or LatestDeliveryAction in ("Blocked", "Moved to quarantine") or EmailAction has "Send to quarantine"
    | where not(LatestDeliveryAction == "Quarantine release")
    | where not(DeliveryAction == "Blocked" and LatestDeliveryAction == "Blocked" and ((DeliveryLocation == "Dropped" and LatestDeliveryLocation == "Dropped") or (DeliveryLocation == "Failed" and LatestDeliveryLocation == "Failed")))
    | where not(NetworkMessageId in (toscalar(_PostDelivery_MovedToQuarantine | summarize make_set(NetworkMessageId))))
    // | where not(LatestDeliveryLocation == "Deleted items")
    | project
        Timestamp,
        NetworkMessageId,
        InternetMessageId,
        SenderMailFromAddress,
        SenderMailFromDomain,
        SenderFromAddress,
        SenderFromDomain,
        SenderDisplayName,
        SenderIPv4,
        SenderIPv6,
        AuthenticationDetails,
        RecipientEmailAddress,
        RecipientObjectId,
        Subject,
        EmailClusterId,
        EmailDirection,
        EmailAction,
        EmailActionPolicy,
        EmailActionPolicyGuid,
        BulkComplaintLevel,
        DeliveryAction,
        DeliveryLocation,
        LatestDeliveryAction,
        LatestDeliveryLocation,
        ThreatTypes,
        ThreatNames,
        ThreatClassification,
        DetectionMethods,
        ConfidenceLevel,
        EmailLanguage,
        AttachmentCount,
        UrlCount,
        Connectors,
        OrgLevelPolicy,
        OrgLevelAction,
        ExchangeTransportRule,
        ReportId,
        AdditionalFields
;
union _EmailEvents_Quarantined, _PostDelivery_MovedToQuarantine

Explanation

This KQL (Kusto Query Language) query is designed to analyze email events, specifically focusing on emails that have been moved to quarantine. Here's a simplified breakdown of what the query does:

  1. Define Time Periods:

    • query_frequency is set to 7 days, query_period to 30 days, and query_wait to 1 hour. These variables define the time frames for filtering the data.

  2. Identify Post-Delivery Quarantined Emails:

    • The _PostDelivery_MovedToQuarantine part of the query filters EmailPostDeliveryEvents to find emails that were moved to quarantine or had a specific action type ("ZAP") within the last 7 days (excluding the last hour).
    • It joins these results with events from the last 30 days to ensure they are recent.
    • It further filters to ensure the delivery location is "Quarantine" and selects relevant fields for analysis.

  3. Identify Quarantined Emails from Email Events:

    • The _EmailEvents_Quarantined part of the query filters EmailEvents to find emails that were delivered to quarantine or had actions indicating they were blocked or moved to quarantine within the last 7 days (excluding the last hour).
    • It excludes emails that were released from quarantine or have certain blocked statuses.
    • It also excludes emails already identified in the _PostDelivery_MovedToQuarantine set.

  4. Combine Results:

    • The final step combines (union) the results from both _PostDelivery_MovedToQuarantine and _EmailEvents_Quarantined to create a comprehensive list of emails that have been quarantined, either post-delivery or during initial processing.

In summary, this query is designed to provide a detailed list of emails that have been moved to quarantine, either during initial delivery or after delivery, within a specified recent time frame. It helps in identifying and analyzing potentially harmful or unwanted emails that were quarantined for security reasons.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: June 4, 2025

Tables

EmailPostDeliveryEventsEmailEvents

Keywords

EmailEvents

Operators

letbetweenagowhereorhasnotjoinkind=rightsemionsummarizearg_maxbyprojectaskind=leftouterintoscalarmake_setisnotempty!=project-awayunion

Actions