UnifiedEmailData: A KQL function for simplified email hunting in Microsoft Defender XDR

The Problem

Email telemetry in Microsoft Defender XDR Advanced Hunting is spread across four tables:

EmailEvents - one row per message and recipient
EmailAttachmentInfo - one row per attachment
EmailUrlInfo - one row per URL
EmailPostDeliveryEvents - one row per post-delivery action (ZAP, admin remediation)

For me personally, this fragmentation is a constant annoyance during an investigation. My quick fix has always been to just join everything together which has one major problem:

Row explosion. An email with three attachments and five URLs becomes fifteen rows. Good luck reading that and understanding who received how many mails at a glance.

My new UnifiedEmailData function solves this annoyance: it returns one row per message/recipient pair with attachments, URLs and post-delivery actions nested as JSON arrays, and it pushes all filtering into the function so the expensive joins only ever run on a pre-reduced dataset.

Design Decisions

Nesting instead of joining flat

Attachments, URLs and post-delivery events are aggregated with make_list(bag_pack(...)) before joining. Each email row carries its related records as dynamic arrays:

"Attachments": [
  { "FileName": "invoice.pdf", "SHA256": "ab12...", "FileType": "pdf", ... },
  { "FileName": "payload.html", "SHA256": "cd34...", "FileType": "html", ... }
]

One email, one row regardless of how many attachments or URLs it has. In the Advanced Hunting UI the dynamic arrays are interactive and can be expanded to have a beautiful overview. If you need the flat view back for a specific hunt, a single mv-expand Attachments gets you there.

Semi-join pre-filtering

The interesting part is how the attachment and URL filters work. Say you hunt for a file hash. Instead of joining everything and then apply the filters, the function resolves content filters first with a cheap lookup that only collects matching NetworkMessageIds:

let AttachmentMatchIds = toscalar(
    EmailAttachmentInfo
    | where AttachmentFilterActive
    | where Timestamp between (_Start .. _End)
    | where isempty(SHA256_eq) or SHA256 =~ SHA256_eq
    // ...
    | summarize make_set(NetworkMessageId, 1000000)
);

EmailEvents is then immediately restricted to those messages, and the heavy join/aggregation only runs on that small subset.

Importantly, filtering for FileName_contains="invoice" returns every email that has at least one matching attachment, but the nested Attachments array still contains all attachments of that email so you’ll always get the full picture.

The same concept applies to URL filters.

Time window as a parameter

In order to further reduce processing resources, I’ve included the option to filter for certain lookback times or time-ranges directly within the function.

However, the EmailPostDeliveryEvents lookup is only capped at the start time. Remediation actions can happen days after delivery. This way you can see that an email which was delivered within your set time window was zapped even if the zap itself happened after your set End-Time. This is consistent with the decision made above to always keep full context of the email.

It is therefore crucial to use the built-in time filters instead of the time filter in the UI, because the latter would also restrict the lookup within the post-delivery events.

The Function

The following is the complete query. Paste it into Advanced Hunting and run it as-is to try the function out. The last line, UnifiedEmailData(), is the invocation: put your filter parameters between the parentheses (see Usage Examples).

let UnifiedEmailData = (
    Lookback: timespan = 30d,
    Start: datetime = datetime(null),
    End: datetime = datetime(null),
    NetworkMessageId_eq: string = "",
    InternetMessageId_eq: string = "",
    Subject_contains: string = "",
    SenderDisplayName_contains: string = "",
    SenderFromAddress_eq: string = "",
    SenderFromAddress_in: dynamic = dynamic([]),
    SenderFromDomain_eq: string = "",
    SenderFromDomain_in: dynamic = dynamic([]),
    RecipientEmailAddress_eq: string = "",
    EmailDirection_eq: string = "",
    DeliveryAction_eq: string = "",
    DeliveryLocation_eq: string = "",
    ThreatTypes_has: string = "",
    AttachmentCount_ge: long = -1,
    AttachmentCount_le: long = -1,
    UrlCount_ge: long = -1,
    UrlCount_le: long = -1,
    FileName_contains: string = "",
    FileType_eq: string = "",
    SHA256_eq: string = "",
    SHA256_in: dynamic = dynamic([]),
    Url_contains: string = "",
    UrlDomain_eq: string = "",
    UrlDomain_in: dynamic = dynamic([])
) {
    let _Start = iff(isnull(Start), ago(Lookback), Start);
    let _End = iff(isnull(End), now(), End);
    let AttachmentFilterActive = isnotempty(FileName_contains) or isnotempty(FileType_eq) or isnotempty(SHA256_eq) or array_length(SHA256_in) > 0;
    let UrlFilterActive = isnotempty(Url_contains) or isnotempty(UrlDomain_eq) or array_length(UrlDomain_in) > 0;
    let AttachmentMatchIds = toscalar(
        EmailAttachmentInfo
        | where AttachmentFilterActive
        | where Timestamp between (_Start .. _End)
        | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
        | where isempty(FileName_contains) or FileName contains FileName_contains
        | where isempty(FileType_eq) or FileType =~ FileType_eq
        | where isempty(SHA256_eq) or SHA256 =~ SHA256_eq
        | where array_length(SHA256_in) == 0 or SHA256 in~ (SHA256_in)
        | summarize make_set(NetworkMessageId, 1000000)
    );
    let UrlMatchIds = toscalar(
        EmailUrlInfo
        | where UrlFilterActive
        | where Timestamp between (_Start .. _End)
        | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
        | where isempty(Url_contains) or Url contains Url_contains
        | where isempty(UrlDomain_eq) or UrlDomain =~ UrlDomain_eq
        | where array_length(UrlDomain_in) == 0 or UrlDomain in~ (UrlDomain_in)
        | summarize make_set(NetworkMessageId, 1000000)
    );
    EmailEvents
    | where Timestamp between (_Start .. _End)
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | where isempty(InternetMessageId_eq) or InternetMessageId =~ InternetMessageId_eq
    | where isempty(Subject_contains) or Subject contains Subject_contains
    | where isempty(SenderDisplayName_contains) or SenderDisplayName contains SenderDisplayName_contains
    | where isempty(SenderFromAddress_eq) or SenderFromAddress =~ SenderFromAddress_eq
    | where array_length(SenderFromAddress_in) == 0 or SenderFromAddress in~ (SenderFromAddress_in)
    | where isempty(SenderFromDomain_eq) or SenderFromDomain =~ SenderFromDomain_eq
    | where array_length(SenderFromDomain_in) == 0 or SenderFromDomain in~ (SenderFromDomain_in)
    | where isempty(RecipientEmailAddress_eq) or RecipientEmailAddress =~ RecipientEmailAddress_eq
    | where isempty(EmailDirection_eq) or EmailDirection =~ EmailDirection_eq
    | where isempty(DeliveryAction_eq) or DeliveryAction =~ DeliveryAction_eq
    | where isempty(DeliveryLocation_eq) or DeliveryLocation =~ DeliveryLocation_eq
    | where isempty(ThreatTypes_has) or ThreatTypes has ThreatTypes_has
    | where AttachmentCount_ge < 0 or AttachmentCount >= AttachmentCount_ge
    | where AttachmentCount_le < 0 or AttachmentCount <= AttachmentCount_le
    | where UrlCount_ge < 0 or UrlCount >= UrlCount_ge
    | where UrlCount_le < 0 or UrlCount <= UrlCount_le
    | where not(AttachmentFilterActive) or set_has_element(AttachmentMatchIds, NetworkMessageId)
    | where not(UrlFilterActive) or set_has_element(UrlMatchIds, NetworkMessageId)
    | join kind=leftouter (
        EmailAttachmentInfo
        | where Timestamp between (_Start .. _End)
        | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
        | where not(AttachmentFilterActive) or set_has_element(AttachmentMatchIds, NetworkMessageId)
        | summarize arg_max(Timestamp, FileType, FileSize, FileExtension, ThreatTypes, ThreatNames, DetectionMethods, ReportId)
            by NetworkMessageId, SenderFromAddress, RecipientEmailAddress, FileName, SHA256
        | summarize 
            Attachments = make_list(
                bag_pack(
                    "FileName", FileName,
                    "SHA256", SHA256,
                    "FileType", FileType,
                    "FileSize", FileSize,
                    "FileExtension", FileExtension,
                    "ThreatTypes", ThreatTypes,
                    "ThreatNames", ThreatNames,
                    "DetectionMethods", DetectionMethods,
                    "ReportId", ReportId
                )
            )
        by NetworkMessageId, SenderFromAddress, RecipientEmailAddress
    ) on NetworkMessageId, SenderFromAddress, RecipientEmailAddress
    | join kind=leftouter (
        EmailUrlInfo
        | where Timestamp between (_Start .. _End)
        | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
        | where not(UrlFilterActive) or set_has_element(UrlMatchIds, NetworkMessageId)
        | summarize
            Urls = make_list(
                bag_pack(
                    "Url", Url,
                    "UrlDomain", UrlDomain,
                    "UrlLocation", UrlLocation,
                    "ReportId", ReportId,
                    "UrlChainId", UrlChainId,
                    "UrlChainPosition", UrlChainPosition
                )
            )
        by NetworkMessageId
    ) on NetworkMessageId
    | join kind=leftouter (
        EmailPostDeliveryEvents
        | where Timestamp > _Start
        | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
        | summarize
            PostDeliveryEvents = make_list(
                bag_pack(
                    "Timestamp", Timestamp,
                    "Action", Action,
                    "ActionType", ActionType,
                    "ActionTrigger", ActionTrigger,
                    "ActionResult", ActionResult,
                    "DeliveryLocation", DeliveryLocation,
                    "ReportId", ReportId
                )
            )
        by NetworkMessageId, RecipientEmailAddress
    ) on NetworkMessageId, RecipientEmailAddress
    | extend
        Attachments = coalesce(Attachments, dynamic([])),
        Urls = coalesce(Urls, dynamic([])),
        PostDeliveryEvents = coalesce(PostDeliveryEvents, dynamic([]))
    | project-away NetworkMessageId1, SenderFromAddress1, RecipientEmailAddress1, NetworkMessageId2, NetworkMessageId3, RecipientEmailAddress2
    | project-reorder 
        Timestamp,
        Subject,
        SenderDisplayName,
        SenderFromAddress,
        RecipientEmailAddress,
        EmailDirection,
        AttachmentCount,
        Attachments,
        UrlCount,
        Urls,
        PostDeliveryEvents,
        EmailAction,
        DeliveryAction,
        DeliveryLocation,
        LatestDeliveryAction,
        LatestDeliveryLocation,
        ThreatTypes,
        SenderFromDomain,
        SenderMailFromAddress,
        SenderMailFromDomain,
        SenderIPv4,
        SenderIPv6,
        SenderObjectId,
        RecipientDomain,
        RecipientObjectId,
        EmailActionPolicy,
        EmailActionPolicyGuid,
        ThreatClassification,
        ThreatNames,
        DetectionMethods,
        ConfidenceLevel,
        BulkComplaintLevel,
        AuthenticationDetails,
        To,
        Cc,
        EmailSize,
        EmailLanguage,
        DistributionList,
        ForwardingInformation,
        IsFirstContact,
        Context,
        Connectors,
        OrgLevelAction,
        OrgLevelPolicy,
        UserLevelAction,
        UserLevelPolicy,
        ExchangeTransportRule,
        NetworkMessageId,
        InternetMessageId,
        EmailClusterId,
        ReportId,
        AdditionalFields
};
UnifiedEmailData()

Saving it as a function

If you decide this is something you want to use regularly, save it as a function so that everybody in your tenant can call UnifiedEmailData(...) by name from any query, without ever pasting the definition again.

Open a new query in Advanced Hunting and paste the following snippet:

let _Start = iff(isnull(Start), ago(Lookback), Start);
let _End = iff(isnull(End), now(), End);
let AttachmentFilterActive = isnotempty(FileName_contains) or isnotempty(FileType_eq) or isnotempty(SHA256_eq) or array_length(SHA256_in) > 0;
let UrlFilterActive = isnotempty(Url_contains) or isnotempty(UrlDomain_eq) or array_length(UrlDomain_in) > 0;
let AttachmentMatchIds = toscalar(
    EmailAttachmentInfo
    | where AttachmentFilterActive
    | where Timestamp between (_Start .. _End)
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | where isempty(FileName_contains) or FileName contains FileName_contains
    | where isempty(FileType_eq) or FileType =~ FileType_eq
    | where isempty(SHA256_eq) or SHA256 =~ SHA256_eq
    | where array_length(SHA256_in) == 0 or SHA256 in~ (SHA256_in)
    | summarize make_set(NetworkMessageId, 1000000)
);
let UrlMatchIds = toscalar(
    EmailUrlInfo
    | where UrlFilterActive
    | where Timestamp between (_Start .. _End)
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | where isempty(Url_contains) or Url contains Url_contains
    | where isempty(UrlDomain_eq) or UrlDomain =~ UrlDomain_eq
    | where array_length(UrlDomain_in) == 0 or UrlDomain in~ (UrlDomain_in)
    | summarize make_set(NetworkMessageId, 1000000)
);
EmailEvents
| where Timestamp between (_Start .. _End)
| where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
| where isempty(InternetMessageId_eq) or InternetMessageId =~ InternetMessageId_eq
| where isempty(Subject_contains) or Subject contains Subject_contains
| where isempty(SenderDisplayName_contains) or SenderDisplayName contains SenderDisplayName_contains
| where isempty(SenderFromAddress_eq) or SenderFromAddress =~ SenderFromAddress_eq
| where array_length(SenderFromAddress_in) == 0 or SenderFromAddress in~ (SenderFromAddress_in)
| where isempty(SenderFromDomain_eq) or SenderFromDomain =~ SenderFromDomain_eq
| where array_length(SenderFromDomain_in) == 0 or SenderFromDomain in~ (SenderFromDomain_in)
| where isempty(RecipientEmailAddress_eq) or RecipientEmailAddress =~ RecipientEmailAddress_eq
| where isempty(EmailDirection_eq) or EmailDirection =~ EmailDirection_eq
| where isempty(DeliveryAction_eq) or DeliveryAction =~ DeliveryAction_eq
| where isempty(DeliveryLocation_eq) or DeliveryLocation =~ DeliveryLocation_eq
| where isempty(ThreatTypes_has) or ThreatTypes has ThreatTypes_has
| where AttachmentCount_ge < 0 or AttachmentCount >= AttachmentCount_ge
| where AttachmentCount_le < 0 or AttachmentCount <= AttachmentCount_le
| where UrlCount_ge < 0 or UrlCount >= UrlCount_ge
| where UrlCount_le < 0 or UrlCount <= UrlCount_le
| where not(AttachmentFilterActive) or set_has_element(AttachmentMatchIds, NetworkMessageId)
| where not(UrlFilterActive) or set_has_element(UrlMatchIds, NetworkMessageId)
| join kind=leftouter (
    EmailAttachmentInfo
    | where Timestamp between (_Start .. _End)
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | where not(AttachmentFilterActive) or set_has_element(AttachmentMatchIds, NetworkMessageId)
    | summarize arg_max(Timestamp, FileType, FileSize, FileExtension, ThreatTypes, ThreatNames, DetectionMethods, ReportId)
        by NetworkMessageId, SenderFromAddress, RecipientEmailAddress, FileName, SHA256
    | summarize
        Attachments = make_list(
            bag_pack(
                "FileName", FileName,
                "SHA256", SHA256,
                "FileType", FileType,
                "FileSize", FileSize,
                "FileExtension", FileExtension,
                "ThreatTypes", ThreatTypes,
                "ThreatNames", ThreatNames,
                "DetectionMethods", DetectionMethods,
                "ReportId", ReportId
            )
        )
    by NetworkMessageId, SenderFromAddress, RecipientEmailAddress
) on NetworkMessageId, SenderFromAddress, RecipientEmailAddress
| join kind=leftouter (
    EmailUrlInfo
    | where Timestamp between (_Start .. _End)
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | where not(UrlFilterActive) or set_has_element(UrlMatchIds, NetworkMessageId)
    | summarize
        Urls = make_list(
            bag_pack(
                "Url", Url,
                "UrlDomain", UrlDomain,
                "UrlLocation", UrlLocation,
                "ReportId", ReportId,
                "UrlChainId", UrlChainId,
                "UrlChainPosition", UrlChainPosition
            )
        )
    by NetworkMessageId
) on NetworkMessageId
| join kind=leftouter (
    EmailPostDeliveryEvents
    | where Timestamp > _Start
    | where isempty(NetworkMessageId_eq) or NetworkMessageId == NetworkMessageId_eq
    | summarize
        PostDeliveryEvents = make_list(
            bag_pack(
                "Timestamp", Timestamp,
                "Action", Action,
                "ActionType", ActionType,
                "ActionTrigger", ActionTrigger,
                "ActionResult", ActionResult,
                "DeliveryLocation", DeliveryLocation,
                "ReportId", ReportId
            )
        )
    by NetworkMessageId, RecipientEmailAddress
) on NetworkMessageId, RecipientEmailAddress
| extend
    Attachments = coalesce(Attachments, dynamic([])),
    Urls = coalesce(Urls, dynamic([])),
    PostDeliveryEvents = coalesce(PostDeliveryEvents, dynamic([]))
| project-away NetworkMessageId1, SenderFromAddress1, RecipientEmailAddress1, NetworkMessageId2, NetworkMessageId3, RecipientEmailAddress2
| project-reorder
    Timestamp,
    Subject,
    SenderDisplayName,
    SenderFromAddress,
    RecipientEmailAddress,
    EmailDirection,
    AttachmentCount,
    Attachments,
    UrlCount,
    Urls,
    PostDeliveryEvents,
    EmailAction,
    DeliveryAction,
    DeliveryLocation,
    LatestDeliveryAction,
    LatestDeliveryLocation,
    ThreatTypes,
    SenderFromDomain,
    SenderMailFromAddress,
    SenderMailFromDomain,
    SenderIPv4,
    SenderIPv6,
    SenderObjectId,
    RecipientDomain,
    RecipientObjectId,
    EmailActionPolicy,
    EmailActionPolicyGuid,
    ThreatClassification,
    ThreatNames,
    DetectionMethods,
    ConfidenceLevel,
    BulkComplaintLevel,
    AuthenticationDetails,
    To,
    Cc,
    EmailSize,
    EmailLanguage,
    DistributionList,
    ForwardingInformation,
    IsFirstContact,
    Context,
    Connectors,
    OrgLevelAction,
    OrgLevelPolicy,
    UserLevelAction,
    UserLevelPolicy,
    ExchangeTransportRule,
    NetworkMessageId,
    InternetMessageId,
    EmailClusterId,
    ReportId,
    AdditionalFields

Choose Save > Save as function and fill in the form:

Name: UnifiedEmailData
Location: Shared functions - so your whole team can use it
Description: Returns one row per email with all attachments, URLs and post-delivery actions nested as arrays. All parameters are optional filters.

Then declare the parameters. Yes, with this many parameters that is tedious but it is a one-time setup for your entire tenant: one person clicks through this form, and from then on everybody can simply call UnifiedEmailData(...) from any query. Copy them step by step from this table (every parameter needs its default value, otherwise it becomes required):

Type	Name	Default value
`timespan`	`Lookback`	`30d`
`datetime`	`Start`	`datetime(null)`
`datetime`	`End`	`datetime(null)`
`string`	`NetworkMessageId_eq`	`""`
`string`	`InternetMessageId_eq`	`""`
`string`	`Subject_contains`	`""`
`string`	`SenderDisplayName_contains`	`""`
`string`	`SenderFromAddress_eq`	`""`
`dynamic`	`SenderFromAddress_in`	`dynamic([])`
`string`	`SenderFromDomain_eq`	`""`
`dynamic`	`SenderFromDomain_in`	`dynamic([])`
`string`	`RecipientEmailAddress_eq`	`""`
`string`	`EmailDirection_eq`	`""`
`string`	`DeliveryAction_eq`	`""`
`string`	`DeliveryLocation_eq`	`""`
`string`	`ThreatTypes_has`	`""`
`long`	`AttachmentCount_ge`	`long(-1)`
`long`	`AttachmentCount_le`	`long(-1)`
`long`	`UrlCount_ge`	`long(-1)`
`long`	`UrlCount_le`	`long(-1)`
`string`	`FileName_contains`	`""`
`string`	`FileType_eq`	`""`
`string`	`SHA256_eq`	`""`
`dynamic`	`SHA256_in`	`dynamic([])`
`string`	`Url_contains`	`""`
`string`	`UrlDomain_eq`	`""`
`dynamic`	`UrlDomain_in`	`dynamic([])`

Alternatively, if you want to skip the parameter form entirely, you can also save the complete query from above as a regular query. The downside being that can’t use the function by name in other queries.

Usage Examples

All parameters are optional, however restricting at least the lookback window or time range is strongly recommended for large tenants:

// Search for a specific network message ID
UnifiedEmailData(NetworkMessageId_eq="<id>")

// Search for specific attachments received in the last 14 days
UnifiedEmailData(
    Lookback=14d,
    SHA256_in=dynamic(["<hash1>", "<hash2>", "<hash3>"])
)

// All inbound phish-classified mail during a specific timeframe
UnifiedEmailData(
    Start=datetime(2026-07-01 08:00:00), End=datetime(2026-07-15 18:30:00),
    EmailDirection_eq="Inbound",
    ThreatTypes_has="Phish"
)

// Which mails with links to these domains actually landed?
UnifiedEmailData(
    UrlDomain_in=dynamic(["<domain1.tld>", "<domain2.tld>", "<domain3.tld>"]),
    DeliveryLocation_eq="Inbox/folder"
)

Parameter Reference

Parameter	Type	Behavior
`Lookback`	`timespan`	Default `30d`
`Start` / `End`	`datetime`	Overrides `Lookback`; accepts time of day, interpreted as UTC
`NetworkMessageId_eq`, `InternetMessageId_eq`	`string`
`Subject_contains`, `SenderDisplayName_contains`	`string`
`SenderFromAddress_eq` / `_in`, `SenderFromDomain_eq` / `_in`	`string` / `dynamic`
`RecipientEmailAddress_eq`	`string`
`EmailDirection_eq`	`string`	`"Inbound"`, `"Outbound"`, `"Intra-org"`, `"Unknown"`
`DeliveryAction_eq`	`string`	`"Delivered"`, `"Junked"`, `"Blocked"`, `"Replaced"`
`DeliveryLocation_eq`	`string`	`"Inbox/folder"`, `"On-premises/external"`, `"Junk"`, `"Quarantine"`, `"Failed"`, `"Dropped"`, `"Deleted items"`
`ThreatTypes_has`	`string`	`"Phish"`, `"Malware"`, `"Spam"` (a mail can carry multiple types; `has` matches any one of them)
`AttachmentCount_ge/_le`, `UrlCount_ge/_le`	`long`
`FileName_contains`, `FileType_eq`, `SHA256_eq` / `_in`	`string` / `dynamic`	Matching mails return with all their attachments nested
`Url_contains`, `UrlDomain_eq` / `_in`	`string` / `dynamic`	Matching mails return with all their URLs nested

Caveats

Pre-lookup set size: The make_set(NetworkMessageId, 1000000) in the pre-lookups caps out at one million distinct messages (near the engine’s hard limit of 1,048,576). If a very broad filter - say FileType_eq="pdf" over 30 days in a large tenant - matches more messages than that, results are silently truncated. Shorten the Lookback or combine it with another filter.

One row per recipient, not per message: EmailEvents is recipient-scoped, and the function keeps that granularity deliberately - delivery actions, ZAP results and mailbox locations differ per recipient. A mail to five recipients yields five rows, each with identical nested arrays and network message IDs.

Wrap-Up

Save the function as a query in Advanced Hunting and email triage becomes a one-liner: paste a NetworkMessageId from any alert and you instantly see the message, its attachments, its URLs, and whether ZAP already took care of it. All in a single row.

Happy hunting.