Query Details
Office Activity Office Mail Forwarding
Query
let query_frequency = 1h;
let query_period = 14d;
OfficeActivity
| where TimeGenerated > ago(query_period)
//| where OfficeWorkload == "Exchange"
| where Operation in ("Set-Mailbox", "New-InboxRule", "Set-InboxRule")
| 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
Actor = iff(isnotempty(UserIdValues), tostring(UserIdValues[0]), UserId),
Target = iff(isnotempty(UserIdValues), tostring(UserIdValues[1]), UserId)
| mv-apply Parameter = todynamic(Parameters) on (
summarize ParametersDict = make_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
| as _Events
| lookup kind=inner (
_Events
| summarize TargetCount = dcount(Target), MaxTimeGenerated = max(TimeGenerated) by DestinationEmailAddress
| where TargetCount > 1 and MaxTimeGenerated > ago(query_frequency)
| project-away TargetCount, MaxTimeGenerated
) on DestinationEmailAddress
| 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,
Actor,
Operation,
ResultStatus,
Target,
DestinationEmailAddress,
OfficeObjectId,
Parameters
Explanation
This query is designed to analyze Office Activity data over a period of 14 days. It filters for specific operations ("Set-Mailbox", "New-InboxRule", "Set-InboxRule") and extracts user ID values, actor and target information. It also processes parameters to identify forwarding and redirection email addresses.
The query then expands the destination email addresses and removes unnecessary columns. It performs a lookup to find events where the same target email address has been used more than once within an hour.
The query also extracts client IP address values and sorts the data by destination email address and time generated. Finally, it projects selected columns to display in the output.
In simple terms, this query is used to track and analyze specific email operations and their associated details such as user IDs, IP addresses, and destination email addresses over a two-week period.
Details
Jose Sebastián Canós
Released: March 13, 2023
Tables
Keywords
Operators