Office Activity Teams Phishing Campaign

Query

let query_frequency = 15m;
let query_period = 30m;
let threshold = 5;
let _ExpectedDomains = toscalar(
    _GetWatchlist("UUID-EntraIdTenantIds")
    | where Notes has "[ChatCreated]"
    | summarize make_list(TenantId)
);
OfficeActivity
| where TimeGenerated > ago(query_period)
| where Operation == "ChatCreated" and CommunicationType == "OneOnOne"
| extend
    SenderOrganizationId = tostring(Members[0]["OrganizationId"]),
    SenderDisplayName = tostring(Members[0]["DisplayName"]),
    SenderUPN = tostring(Members[0]["UPN"]),
    RecipientOrganizationId = tostring(Members[1]["OrganizationId"]),
    RecipientDisplayName = tostring(Members[1]["DisplayName"]),
    RecipientUPN = tostring(Members[1]["UPN"])
| where isnotempty(SenderOrganizationId) and not(SenderOrganizationId in (_ExpectedDomains))
| extend SenderOrganizationDomain = tostring(split(SenderUPN, "@")[-1])
| as _Events
| join kind=leftsemi (
    _Events
    // query_period should be 2 * query_frequency
    | evaluate activity_counts_metrics(Type, TimeGenerated, ago(query_period), now(), query_frequency, SenderOrganizationId)
    | summarize
        arg_min(PreviousTimeGenerated = TimeGenerated, PreviousCount = ["count"]),
        arg_max(CurrentTimeGenerated = TimeGenerated, CurrentCount = ["count"])
        by SenderOrganizationId
    | where CurrentTimeGenerated > ago(query_period)
    | extend PreviousCount = iff(PreviousTimeGenerated == CurrentTimeGenerated, 0, PreviousCount)
    | where (not(PreviousCount > threshold) and CurrentCount > threshold)
        or ((CurrentCount - PreviousCount) > threshold)
    ) on SenderOrganizationId
| join kind=leftouter (
    OfficeActivity
    | where TimeGenerated > ago(query_period)
    | where Operation == "MessageSent"
    | where UserId in (toscalar(_Events | summarize make_set(UserId)))
    | summarize SenderIPAddress = make_set(ClientIP) by UserId
    ) on UserId
| join kind=leftouter (
    OfficeActivity
    | where TimeGenerated > ago(query_period)
    | where Operation in ("MemberAdded", "MemberRemoved")
    | summarize
        MemberAdded = tobool(binary_all_or(tolong(Operation == "MemberAdded"))),
        MemberRemoved = tobool(binary_all_or(tolong(Operation == "MemberRemoved")))
        by ChatThreadId, UserId
    ) on ChatThreadId, $left.RecipientUPN == $right.UserId
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    SenderOrganizationDomain = array_sort_asc(make_set(SenderOrganizationDomain)),
    SenderDisplayNames = array_sort_asc(make_set(SenderDisplayName)),
    SenderUPNs = array_sort_asc(make_set(SenderUPN)),
    UserIds = array_sort_asc(make_set(UserId)),
    SenderIPAddresses = make_set_if(SenderIPAddress, isnotempty(SenderIPAddress)),
    RecipientOrganizationIds = array_sort_asc(make_set(RecipientOrganizationId)),
    RecipientUPNs = array_sort_asc(make_set(RecipientUPN)),
    RecipientCount = count_distinct(RecipientUPN),
    RecipientAcceptedChat = array_sort_asc(make_set_if(RecipientUPN, MemberAdded)),
    RecipientRemovedChat = array_sort_asc(make_set_if(RecipientUPN, MemberRemoved)),
    ChatNames = array_sort_asc(make_set(ChatName)),
    take_any(OfficeWorkload, RecordType, Operation)
    by SenderOrganizationId
| where RecipientCount > threshold
| project
    StartTime,
    EndTime,
    OfficeWorkload,
    RecordType,
    Operation,
    ChatNames,
    SenderOrganizationDomain,
    SenderUPNs,
    UserIds,
    SenderIPAddresses,
    SenderDisplayNames,
    SenderOrganizationId,
    RecipientCount,
    RecipientUPNs,
    RecipientAcceptedChat,
    RecipientRemovedChat,
    RecipientOrganizationIds

Explanation

This query is designed to monitor and identify unusual activity related to the creation of one-on-one chats in an Office environment. Here's a simplified breakdown of what the query does:

Setup and Definitions:
- It defines some parameters like query_frequency (15 minutes), query_period (30 minutes), and a threshold (5).
- It retrieves a list of expected domains from a watchlist that are associated with chat creation.
Filter and Extract Data:
- It looks at recent Office activities (within the last 30 minutes) specifically for "ChatCreated" operations that are one-on-one.
- It extracts details about the sender and recipient of these chats, such as their organization IDs, display names, and user principal names (UPNs).
Identify Unusual Activity:
- It focuses on senders whose organization IDs are not in the expected list of domains.
- It calculates activity metrics over time to identify senders whose chat creation activity has increased significantly (more than the defined threshold).
Join Additional Information:
- It gathers additional data about these senders, such as their IP addresses and any related "MessageSent" operations.
- It also checks for any "MemberAdded" or "MemberRemoved" operations in the chats to see if recipients were added or removed.
Summarize Results:
- It compiles a summary of the findings, including the time range of the activity, sender and recipient details, and any chat names.
- It filters the results to only include cases where the number of unique recipients exceeds the threshold.
Output:
- The final output includes details like the start and end times of the activity, sender and recipient information, and any changes in chat membership.

In essence, this query is used to detect and report on potentially suspicious or unexpected chat creation activities by monitoring for significant increases in activity from unrecognized domains.

Details

Jose Sebastián Canós

Released: August 19, 2025

Tables

OfficeActivity

Keywords

OfficeActivityMembersSenderOrganizationIdSenderDisplayNameSenderUPNRecipientOrganizationIdRecipientDisplayNameRecipientUPNUserIdClientIPChatThreadIdChatNameOfficeWorkloadRecordTypeOperation

Operators

lettoscalarhassummarizemake_listwhereagoextendtostringsplitisnotemptyinasjoinkindevaluateactivity_counts_metricsarg_minarg_maxiffnotontoboolbinary_all_ortolongmake_setmake_set_ifcount_distincttake_anybyprojectarray_sort_asc

Actions

GitHub

KQL Search