KQL Search

Query Details

HomeAI ToolsDevice Query

Third Party Phishing Report Malfunction

Query

let query_frequency = 1h;
let query_period = 14d;
let query_wait = 1h;
OfficeActivity
| where OfficeWorkload == "Exchange" and AppId == "<<<>>>" and ClientAppId == "<<<>>>"
| extend
    UniqueTokenId = tostring(AppAccessContext["UniqueTokenId"]),
    InternetMessageId = case(
        Operation == "MailItemsAccessed", tostring(todynamic(Folders)[0]["FolderItems"][0]["InternetMessageId"]),
        Operation == "MoveToDeletedItems", tostring(todynamic(AffectedItems)[0]["InternetMessageId"]),
        Operation == "Create", tostring(todynamic(Item)["InternetMessageId"]),
        Operation == "Send", tostring(todynamic(Item)["InternetMessageId"]),
        ""
    ),
    Path = case(
        Operation == "MailItemsAccessed", tostring(todynamic(Folders)[0]["Path"]),
        Operation == "MoveToDeletedItems", tostring(todynamic(AffectedItems)[0]["Path"]),
        Operation == "Create", tostring(todynamic(Item)["ParentFolder"]["Path"]),
        Operation == "Send", tostring(todynamic(Item)["ParentFolder"]["Path"]),
        ""
    ),
    ImmutableId = case(
        Operation == "MailItemsAccessed", tostring(todynamic(Folders)[0]["FolderItems"][0]["ImmutableId"]),
        Operation == "MoveToDeletedItems", tostring(todynamic(AffectedItems)[0]["ImmutableId"]),
        Operation == "Create", tostring(todynamic(Item)["ImmutableId"]),
        Operation == "Send", tostring(todynamic(Item)["ImmutableId"]),
        ""
    ),
    Attachments = case(
        Operation == "Send", tostring(todynamic(Item)["Attachments"]),
        ""
    ),
    Subject = case(
        Operation == "MoveToDeletedItems", tostring(todynamic(AffectedItems)[0]["Subject"]),
        Operation == "Create", tostring(todynamic(Item)["Subject"]),
        Operation == "Send", tostring(todynamic(Item)["Subject"]),
        ""
    )
| summarize
    StartTime = min(TimeGenerated),
    take_any(MailboxOwnerUPN),
    Operations = make_bag(bag_pack(Operation, bag_pack_columns(InternetMessageId, Path, ImmutableId, Attachments, Subject)))
    by MailboxGuid, ResultStatus, UniqueTokenId
| where StartTime < ago(query_wait)
| extend ExpectedFlow = case(
    // Remove Simulated Phish
    isnotempty(UniqueTokenId) and ResultStatus == "Succeeded"
    and bag_keys(Operations) has_all ("MailItemsAccessed", "MoveToDeletedItems", "Send") //, "Create")
    and Operations["MailItemsAccessed"]["InternetMessageId"] has "<<<>>>" and Operations["MoveToDeletedItems"]["InternetMessageId"] has "<<<>>>"
    // and substring(tostring(Operations["Create"]["ImmutableId"]), 0, 58) == substring(tostring(Operations["Send"]["ImmutableId"]), 0, 58)
    and substring(tostring(Operations["Send"]["ImmutableId"]), 0, 54) == substring(tostring(Operations["MailItemsAccessed"]["ImmutableId"]), 0, 54)
    and Operations["Send"]["Subject"] startswith "Simulated Phish: "// and Operations["Create"]["Subject"] startswith "FW: "
    and Operations["Send"]["Attachments"] has "Headers.txt ",
    true,
    // Expected flow for other phishing reports
    isnotempty(UniqueTokenId) and ResultStatus == "Succeeded"
    and bag_keys(Operations) has_all ("MailItemsAccessed", "Send")
    and not(Operations["MailItemsAccessed"]["InternetMessageId"] has_all ("<<<>>>", "<<<>>>"))
    //and substring(tostring(Operations["Create"]["ImmutableId"]), 0, 57) == substring(tostring(Operations["Send"]["ImmutableId"]), 0, 57)
    and substring(tostring(Operations["Send"]["ImmutableId"]), 0, 54) == substring(tostring(Operations["MailItemsAccessed"]["ImmutableId"]), 0, 54)
    and Operations["Send"]["Subject"] startswith "Potential Phish: "// and Operations["Create"]["Subject"] startswith "FW: "
    and Operations["Send"]["Attachments"] has "Headers.txt ",
    true,
    false
    )
| extend
    Path = tostring(Operations["MailItemsAccessed"]["Path"]),
    InternetMessageId = tostring(Operations["MailItemsAccessed"]["InternetMessageId"]),
    Subject = trim_start(@"FW\: ", tostring(Operations["Create"]["Subject"]))
| project StartTime, MailboxOwnerUPN, MailboxGuid, UniqueTokenId, ExpectedFlow, ResultStatus, Operations, Path, InternetMessageId, Subject
| sort by StartTime desc
| extend RowNumber = row_number()
| summarize
    LastStartTime = max(StartTime),
    FailuresInLastFive = countif(not(ExpectedFlow) and RowNumber <= 5),
    FailuresInLastTen = countif(not(ExpectedFlow) and RowNumber <= 10),
    FailuresInLastTwenty = countif(not(ExpectedFlow) and RowNumber <= 20),
    FailuresInLastFifty = countif(not(ExpectedFlow) and RowNumber <= 50)
| where LastStartTime > ago(query_frequency + query_wait) and FailuresInLastTen >= 9

Explanation

This KQL query is designed to analyze and identify patterns in email activities related to potential phishing attempts within the Exchange workload. Here's a simplified breakdown of what the query does:

  1. Time Parameters:

    • The query looks at data over a period of 14 days (query_period) and runs every hour (query_frequency), with a wait time of 1 hour (query_wait).

  2. Data Filtering:

    • It filters activities from the OfficeActivity table where the workload is "Exchange" and matches specific application IDs (AppId and ClientAppId).

  3. Data Transformation:

    • It extracts and processes various fields like UniqueTokenId, InternetMessageId, Path, ImmutableId, Attachments, and Subject based on different email operations (e.g., "MailItemsAccessed", "MoveToDeletedItems", "Create", "Send").

  4. Data Aggregation:

    • The query summarizes the data by grouping it based on MailboxGuid, ResultStatus, and UniqueTokenId. It collects operations into a structured format (Operations).

  5. Flow Analysis:

    • It checks for expected patterns in email flows to identify simulated or potential phishing emails. This includes checking for specific subjects and attachments, and matching IDs across operations.

  6. Result Filtering:

    • The query filters out results that are older than the specified wait time and checks for expected or unexpected flows.

  7. Failure Counting:

    • It counts the number of unexpected flows (failures) in the last 5, 10, 20, and 50 operations.

  8. Final Filtering:

    • The query only returns results where there have been at least 9 failures in the last 10 operations, and the last operation was recent (within the query frequency plus wait time).

In essence, this query is used to monitor and detect unusual patterns in email activities that might indicate phishing attempts, focusing on specific operations and expected behaviors.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: October 21, 2025

Tables

OfficeActivity

Keywords

OfficeActivity

Operators

let|whereandextendtostring()AppAccessContext[]case()==dynamic()summarizemin()take_any()make_bag()bag_pack()bag_pack_columns()by<ago()isnotempty()has_all()hassubstring()startswithnot()trim_start()projectsort byrow_number()countif()>

Actions