Office Activity Office Mail Forwarding

Query

let query_frequency = 1h;
let query_period = 14d;
let _Users =
    IdentityInfo
    | where TimeGenerated > ago(query_period)
    | summarize arg_max(TimeGenerated, Department, CompanyName, JobTitle, MailAddress) by AccountObjectId, AccountSID
;
OfficeActivity
| where TimeGenerated > ago(query_period)
// | where OfficeWorkload == "Exchange"
// | where Operation in ("New-InboxRule", "Set-InboxRule", "Set-Mailbox")
| where Parameters has_any ("ForwardTo", "RedirectTo", "ForwardingSmtpAddress")
| extend
    UserIdValues = extract_all(@'Microsoft Exchange Hosted Organizations\/(?P<Actor>[^\"]+)\"\s.+\s\"\w.+?Microsoft Exchange Hosted Organizations\/(?P<Target>[^\"]+)\"', dynamic(["Actor", "Target"]), UserId)[0]
| extend
    ActorAccount = iff(isnotempty(UserIdValues), tostring(UserIdValues[0]), UserId),
    TargetAccount = tolower(iff(isnotempty(UserIdValues), tostring(UserIdValues[1]), UserId))
| project-away UserIdValues
| mv-apply Parameter = todynamic(Parameters) on (
    summarize ParametersDict = make_bag(bag_pack(tostring(Parameter["Name"]), tostring(Parameter["Value"])))
    )
| extend
    ForwardTo = iff(isnotempty(ParametersDict["ForwardTo"]), split(ParametersDict["ForwardTo"], ";"), dynamic([])),
    RedirectTo = iff(isnotempty(ParametersDict["RedirectTo"]), split(ParametersDict["RedirectTo"], ";"), dynamic([])),
    ForwardingSmtpAddress = iff(isnotempty(ParametersDict["ForwardingSmtpAddress"]), pack_array(trim_start(@"smtp:", tostring(ParametersDict["ForwardingSmtpAddress"]))), dynamic([]))
| mv-expand DestinationEmailAddress = array_concat(ForwardTo, RedirectTo, ForwardingSmtpAddress) to typeof(string)
| project-away ForwardTo, RedirectTo, ForwardingSmtpAddress
| extend DestinationEmailAddress = tolower(DestinationEmailAddress)
| where not(TargetAccount == DestinationEmailAddress)
| as _Events
| join kind=inner (
    _Events
    | join kind=leftouter (
        _Users
        | project
            Target_CompanyName = CompanyName,
            Target_Department = Department,
            Target_JobTitle = JobTitle,
            Target_MailAddress = MailAddress
        ) on $left.TargetAccount == $right.Target_MailAddress
    | summarize
        EndTime = max(TimeGenerated),
        TargetCount = count_distinct(TargetAccount),
        TargetBackgrounds = make_set_if(strcat(Target_CompanyName, Target_Department, Target_JobTitle), isnotempty(Target_Department) and isnotempty(Target_JobTitle) and isnotempty(Target_CompanyName)),
        take_any(Target_CompanyName, Target_Department)
        by DestinationEmailAddress
    | where TargetCount > 1 and EndTime > ago(query_frequency)
    | join kind=leftouter (
        _Users
        | project
            Destination_CompanyName = CompanyName,
            Destination_Department = Department,
            Destination_MailAddress = MailAddress
        ) on $left.DestinationEmailAddress == $right.Destination_MailAddress
    | where not(array_length(TargetBackgrounds) == 1 and Target_CompanyName == Destination_CompanyName and Target_Department == Destination_Department)
    | project-away TargetCount, EndTime
    ) on DestinationEmailAddress
| project-away DestinationEmailAddress1
| extend ClientIPValues = extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)|[^\]]+)\]?([-:](?P<Port>\d+))?', dynamic(["IPAddress", "Port"]), ClientIP)[0]
| extend
    IPAddress = tostring(ClientIPValues[0])//,
    //Port = tostring(ClientIPValues[1])
| sort by DestinationEmailAddress asc, TimeGenerated asc
| project
    TimeGenerated,
    OfficeWorkload,
    UserId,
    IPAddress,
    ActorAccount,
    Operation,
    ResultStatus,
    TargetAccount,
    DestinationEmailAddress,
    OfficeObjectId,
    Parameters,
    AppAccessContext

Explanation

This query is designed to analyze and identify potentially suspicious email forwarding activities within an organization over the past 14 days. Here's a simplified breakdown of what the query does:

Define Time Periods: The query sets a time frame of 14 days (query_period) and a frequency of 1 hour (query_frequency) for checking recent activities.
Retrieve User Information: It extracts the most recent details (like department, company name, job title, and email address) for each user from the IdentityInfo table, filtering data from the last 14 days.
Filter Office Activities: The query looks at the OfficeActivity table for activities within the last 14 days that involve email forwarding or redirection parameters (ForwardTo, RedirectTo, ForwardingSmtpAddress).
Extract and Process User IDs: It extracts and processes user IDs to identify the actor (initiator) and target (recipient) accounts involved in these activities.
Identify Forwarding Destinations: The query expands and processes the forwarding addresses to identify where emails are being forwarded or redirected.
Filter Out Self-Forwarding: It excludes cases where the target account is the same as the forwarding destination, focusing on potentially unauthorized forwarding.
Join with User Information: The query joins the forwarding events with user information to enrich the data with details about the target and destination accounts.
Identify Anomalies: It identifies cases where multiple accounts are forwarding emails to the same destination and checks if these accounts belong to different departments or companies, which could indicate unusual behavior.
Extract Client IP: The query extracts the IP address from which the forwarding activity was initiated.
Sort and Project Results: Finally, it sorts the results by destination email address and time, projecting relevant details like the time of the event, user ID, IP address, operation performed, and other contextual information.

Overall, this query helps in detecting and investigating unusual email forwarding activities that might indicate security incidents or policy violations.

Details

Jose Sebastián Canós

Released: August 12, 2025

Tables

IdentityInfoOfficeActivity

Keywords

IdentityInfoOfficeActivityParametersUserIdActorAccountTargetAccountDestinationEmailAddressCompanyNameDepartmentJobTitleMailAddressClientIPOfficeWorkloadOperationResultStatusOfficeObjectIdAppAccessContext

Operators

letwheresummarizearg_maxagohas_anyextendextract_alliffisnotemptytostringtolowerproject-awaymv-applytodynamicmake_bagbag_packsplitdynamicpack_arraytrim_startmv-expandarray_concattypeofasjoinkindprojectmaxcount_distinctmake_set_ifstrcattake_anybynotarray_lengthsortasc

Actions

GitHub

KQL Search