GSA Enriched Office 365 - Office Mail Forwarding - Hunting Version

let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  
  // Enriched Logs Query for forwarding rule operations
  let EnrichedForwardRules = EnrichedMicrosoft365AuditLogs
      | where TimeGenerated between (starttime .. endtime)
      | where Workload == "Exchange"
      | where (Operation == "Set-Mailbox" and tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardingSmtpAddress') 
          or (Operation in ('New-InboxRule', 'Set-InboxRule') and (tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardTo' or tostring(parse_json(tostring(AdditionalProperties))) contains 'RedirectTo'))
      | extend parsed = parse_json(tostring(AdditionalProperties))
      | extend fwdingDestination_initial = iif(Operation == "Set-Mailbox", tostring(parsed.ForwardingSmtpAddress), coalesce(tostring(parsed.ForwardTo), tostring(parsed.RedirectTo)))
      | where isnotempty(fwdingDestination_initial)
      | extend fwdingDestination = iff(fwdingDestination_initial has "smtp", (split(fwdingDestination_initial, ":")[1]), fwdingDestination_initial)
      | parse fwdingDestination with * '@' ForwardedtoDomain 
      | parse UserId with * '@' UserDomain
      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.'))[0])
      | where ForwardedtoDomain !contains subDomain
      | extend Result = iff(ForwardedtoDomain != UserDomain, "Mailbox rule created to forward to External Domain", "Forward rule for Internal domain")
      | extend ClientIPAddress = case(ClientIp has ".", tostring(split(ClientIp, ":")[0]), ClientIp has "[", tostring(trim_start(@'[[]', tostring(split(ClientIp, "]")[0]))), ClientIp)
      | extend Port = case(ClientIp has ".", (split(ClientIp, ":")[1]), ClientIp has "[", tostring(split(ClientIp, "]:")[1]), ClientIp)
      | extend Host = tostring(parse_json(tostring(AdditionalProperties)).OriginatingServer)
      | extend HostName = tostring(split(Host, ".")[0])
      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))
      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, HostName, DnsDomain
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;
  // Office Activity Query for forwarding rule operations
  let OfficeForwardRules = OfficeActivity
      | where TimeGenerated between (starttime .. endtime)
      | where OfficeWorkload == "Exchange"
      | where (Operation =~ "Set-Mailbox" and Parameters contains 'ForwardingSmtpAddress') 
          or (Operation in~ ('New-InboxRule', 'Set-InboxRule') and (Parameters contains 'ForwardTo' or Parameters contains 'RedirectTo'))
      | extend parsed = parse_json(Parameters)
      | extend fwdingDestination_initial = (iif(Operation =~ "Set-Mailbox", tostring(parsed[1].Value), tostring(parsed[2].Value)))
      | where isnotempty(fwdingDestination_initial)
      | extend fwdingDestination = iff(fwdingDestination_initial has "smtp", (split(fwdingDestination_initial, ":")[1]), fwdingDestination_initial)
      | parse fwdingDestination with * '@' ForwardedtoDomain 
      | parse UserId with * '@' UserDomain
      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.') [0]))
      | where ForwardedtoDomain !contains subDomain
      | extend Result = iff(ForwardedtoDomain != UserDomain, "Mailbox rule created to forward to External Domain", "Forward rule for Internal domain")
      | extend ClientIPAddress = case(ClientIP has ".", tostring(split(ClientIP, ":")[0]), ClientIP has "[", tostring(trim_start(@'[[]', tostring(split(ClientIP, "]")[0]))), ClientIP)
      | extend Port = case(ClientIP has ".", (split(ClientIP, ":")[1]), ClientIP has "[", tostring(split(ClientIP, "]:")[1]), ClientIP)
      | extend Host = tostring(split(OriginatingServer, " (")[0])
      | extend HostName = tostring(split(Host, ".")[0])
      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))
      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, HostName, DnsDomain
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;
  // Combine the results from both Enriched and Office Activity logs
  let CombinedForwardRules = EnrichedForwardRules
      | union OfficeForwardRules
      | summarize arg_min(TimeGenerated, *) by UserId, ForwardedtoDomain, Operation
      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, Host_0_HostName, Host_0_DnsDomain, IP_0_Address, Account_0_Name, Account_0_UPNSuffix;  
  // Final output
  CombinedForwardRules
      | order by TimeGenerated desc;

OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/OfficeMailForwarding_hunting.yaml
relevantTechniques:
- T1114
- T1020
name: GSA Enriched Office 365 - Office Mail Forwarding - Hunting Version
query: "let starttime = todatetime('{{StartTimeISO}}');\n  let endtime = todatetime('{{EndTimeISO}}');\n  \n  // Enriched Logs Query for forwarding rule operations\n  let EnrichedForwardRules = EnrichedMicrosoft365AuditLogs\n      | where TimeGenerated between (starttime .. endtime)\n      | where Workload == \"Exchange\"\n      | where (Operation == \"Set-Mailbox\" and tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardingSmtpAddress') \n          or (Operation in ('New-InboxRule', 'Set-InboxRule') and (tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardTo' or tostring(parse_json(tostring(AdditionalProperties))) contains 'RedirectTo'))\n      | extend parsed = parse_json(tostring(AdditionalProperties))\n      | extend fwdingDestination_initial = iif(Operation == \"Set-Mailbox\", tostring(parsed.ForwardingSmtpAddress), coalesce(tostring(parsed.ForwardTo), tostring(parsed.RedirectTo)))\n      | where isnotempty(fwdingDestination_initial)\n      | extend fwdingDestination = iff(fwdingDestination_initial has \"smtp\", (split(fwdingDestination_initial, \":\")[1]), fwdingDestination_initial)\n      | parse fwdingDestination with * '@' ForwardedtoDomain \n      | parse UserId with * '@' UserDomain\n      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.'))[0])\n      | where ForwardedtoDomain !contains subDomain\n      | extend Result = iff(ForwardedtoDomain != UserDomain, \"Mailbox rule created to forward to External Domain\", \"Forward rule for Internal domain\")\n      | extend ClientIPAddress = case(ClientIp has \".\", tostring(split(ClientIp, \":\")[0]), ClientIp has \"[\", tostring(trim_start(@'[[]', tostring(split(ClientIp, \"]\")[0]))), ClientIp)\n      | extend Port = case(ClientIp has \".\", (split(ClientIp, \":\")[1]), ClientIp has \"[\", tostring(split(ClientIp, \"]:\")[1]), ClientIp)\n      | extend Host = tostring(parse_json(tostring(AdditionalProperties)).OriginatingServer)\n      | extend HostName = tostring(split(Host, \".\")[0])\n      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, HostName, DnsDomain\n      | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;\n  // Office Activity Query for forwarding rule operations\n  let OfficeForwardRules = OfficeActivity\n      | where TimeGenerated between (starttime .. endtime)\n      | where OfficeWorkload == \"Exchange\"\n      | where (Operation =~ \"Set-Mailbox\" and Parameters contains 'ForwardingSmtpAddress') \n          or (Operation in~ ('New-InboxRule', 'Set-InboxRule') and (Parameters contains 'ForwardTo' or Parameters contains 'RedirectTo'))\n      | extend parsed = parse_json(Parameters)\n      | extend fwdingDestination_initial = (iif(Operation =~ \"Set-Mailbox\", tostring(parsed[1].Value), tostring(parsed[2].Value)))\n      | where isnotempty(fwdingDestination_initial)\n      | extend fwdingDestination = iff(fwdingDestination_initial has \"smtp\", (split(fwdingDestination_initial, \":\")[1]), fwdingDestination_initial)\n      | parse fwdingDestination with * '@' ForwardedtoDomain \n      | parse UserId with * '@' UserDomain\n      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.') [0]))\n      | where ForwardedtoDomain !contains subDomain\n      | extend Result = iff(ForwardedtoDomain != UserDomain, \"Mailbox rule created to forward to External Domain\", \"Forward rule for Internal domain\")\n      | extend ClientIPAddress = case(ClientIP has \".\", tostring(split(ClientIP, \":\")[0]), ClientIP has \"[\", tostring(trim_start(@'[[]', tostring(split(ClientIP, \"]\")[0]))), ClientIP)\n      | extend Port = case(ClientIP has \".\", (split(ClientIP, \":\")[1]), ClientIP has \"[\", tostring(split(ClientIP, \"]:\")[1]), ClientIP)\n      | extend Host = tostring(split(OriginatingServer, \" (\")[0])\n      | extend HostName = tostring(split(Host, \".\")[0])\n      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, HostName, DnsDomain\n      | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;\n  // Combine the results from both Enriched and Office Activity logs\n  let CombinedForwardRules = EnrichedForwardRules\n      | union OfficeForwardRules\n      | summarize arg_min(TimeGenerated, *) by UserId, ForwardedtoDomain, Operation\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, Host_0_HostName, Host_0_DnsDomain, IP_0_Address, Account_0_Name, Account_0_UPNSuffix;  \n  // Final output\n  CombinedForwardRules\n      | order by TimeGenerated desc;\n"
id: d49fc965-aef3-49f6-89ad-10cc4697eb5b
entityMappings:
- fieldMappings:
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: ClientIPAddress
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Host_0_HostName
    identifier: HostName
  - columnName: Host_0_DnsDomain
    identifier: DnsDomain
  entityType: Host
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - EnrichedMicrosoft365AuditLogs
description-detailed: |
  Adversaries often abuse email-forwarding rules to monitor activities of a victim, steal information and further gain intelligence on
  victim or victim's organization. This query over Office Activity data highlights cases where user mail is being forwarded and shows if 
  it is being forwarded to external domains as well.  
description: |
    Adversaries often abuse email-forwarding rules to monitor victim activities, steal information, and gain intelligence on the victim or their organization. This query highlights cases where user mail is being forwarded, including to external domains.
kind: Scheduled
tactics:
- Collection
- Exfiltration
version: 2.0.2

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/d49fc965-aef3-49f6-89ad-10cc4697eb5b')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/d49fc965-aef3-49f6-89ad-10cc4697eb5b')]",
      "properties": {
        "alertRuleTemplateName": "d49fc965-aef3-49f6-89ad-10cc4697eb5b",
        "customDetails": null,
        "description": "Adversaries often abuse email-forwarding rules to monitor victim activities, steal information, and gain intelligence on the victim or their organization. This query highlights cases where user mail is being forwarded, including to external domains.\n",
        "description-detailed": "Adversaries often abuse email-forwarding rules to monitor activities of a victim, steal information and further gain intelligence on\nvictim or victim's organization. This query over Office Activity data highlights cases where user mail is being forwarded and shows if \nit is being forwarded to external domains as well.\n",
        "displayName": "GSA Enriched Office 365 - Office Mail Forwarding - Hunting Version",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIPAddress",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "Host_0_HostName",
                "identifier": "HostName"
              },
              {
                "columnName": "Host_0_DnsDomain",
                "identifier": "DnsDomain"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/OfficeMailForwarding_hunting.yaml",
        "query": "let starttime = todatetime('{{StartTimeISO}}');\n  let endtime = todatetime('{{EndTimeISO}}');\n  \n  // Enriched Logs Query for forwarding rule operations\n  let EnrichedForwardRules = EnrichedMicrosoft365AuditLogs\n      | where TimeGenerated between (starttime .. endtime)\n      | where Workload == \"Exchange\"\n      | where (Operation == \"Set-Mailbox\" and tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardingSmtpAddress') \n          or (Operation in ('New-InboxRule', 'Set-InboxRule') and (tostring(parse_json(tostring(AdditionalProperties))) contains 'ForwardTo' or tostring(parse_json(tostring(AdditionalProperties))) contains 'RedirectTo'))\n      | extend parsed = parse_json(tostring(AdditionalProperties))\n      | extend fwdingDestination_initial = iif(Operation == \"Set-Mailbox\", tostring(parsed.ForwardingSmtpAddress), coalesce(tostring(parsed.ForwardTo), tostring(parsed.RedirectTo)))\n      | where isnotempty(fwdingDestination_initial)\n      | extend fwdingDestination = iff(fwdingDestination_initial has \"smtp\", (split(fwdingDestination_initial, \":\")[1]), fwdingDestination_initial)\n      | parse fwdingDestination with * '@' ForwardedtoDomain \n      | parse UserId with * '@' UserDomain\n      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.'))[0])\n      | where ForwardedtoDomain !contains subDomain\n      | extend Result = iff(ForwardedtoDomain != UserDomain, \"Mailbox rule created to forward to External Domain\", \"Forward rule for Internal domain\")\n      | extend ClientIPAddress = case(ClientIp has \".\", tostring(split(ClientIp, \":\")[0]), ClientIp has \"[\", tostring(trim_start(@'[[]', tostring(split(ClientIp, \"]\")[0]))), ClientIp)\n      | extend Port = case(ClientIp has \".\", (split(ClientIp, \":\")[1]), ClientIp has \"[\", tostring(split(ClientIp, \"]:\")[1]), ClientIp)\n      | extend Host = tostring(parse_json(tostring(AdditionalProperties)).OriginatingServer)\n      | extend HostName = tostring(split(Host, \".\")[0])\n      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, HostName, DnsDomain\n      | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;\n  // Office Activity Query for forwarding rule operations\n  let OfficeForwardRules = OfficeActivity\n      | where TimeGenerated between (starttime .. endtime)\n      | where OfficeWorkload == \"Exchange\"\n      | where (Operation =~ \"Set-Mailbox\" and Parameters contains 'ForwardingSmtpAddress') \n          or (Operation in~ ('New-InboxRule', 'Set-InboxRule') and (Parameters contains 'ForwardTo' or Parameters contains 'RedirectTo'))\n      | extend parsed = parse_json(Parameters)\n      | extend fwdingDestination_initial = (iif(Operation =~ \"Set-Mailbox\", tostring(parsed[1].Value), tostring(parsed[2].Value)))\n      | where isnotempty(fwdingDestination_initial)\n      | extend fwdingDestination = iff(fwdingDestination_initial has \"smtp\", (split(fwdingDestination_initial, \":\")[1]), fwdingDestination_initial)\n      | parse fwdingDestination with * '@' ForwardedtoDomain \n      | parse UserId with * '@' UserDomain\n      | extend subDomain = ((split(strcat(tostring(split(UserDomain, '.')[-2]), '.', tostring(split(UserDomain, '.')[-1])), '.') [0]))\n      | where ForwardedtoDomain !contains subDomain\n      | extend Result = iff(ForwardedtoDomain != UserDomain, \"Mailbox rule created to forward to External Domain\", \"Forward rule for Internal domain\")\n      | extend ClientIPAddress = case(ClientIP has \".\", tostring(split(ClientIP, \":\")[0]), ClientIP has \"[\", tostring(trim_start(@'[[]', tostring(split(ClientIP, \"]\")[0]))), ClientIP)\n      | extend Port = case(ClientIP has \".\", (split(ClientIP, \":\")[1]), ClientIP has \"[\", tostring(split(ClientIP, \"]:\")[1]), ClientIP)\n      | extend Host = tostring(split(OriginatingServer, \" (\")[0])\n      | extend HostName = tostring(split(Host, \".\")[0])\n      | extend DnsDomain = tostring(strcat_array(array_slice(split(Host, '.'), 1, -1), '.'))\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, HostName, DnsDomain\n      | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n      | extend IP_0_Address = ClientIPAddress, Host_0_HostName = HostName, Host_0_DnsDomain = DnsDomain, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix;\n  // Combine the results from both Enriched and Office Activity logs\n  let CombinedForwardRules = EnrichedForwardRules\n      | union OfficeForwardRules\n      | summarize arg_min(TimeGenerated, *) by UserId, ForwardedtoDomain, Operation\n      | project TimeGenerated, UserId, UserDomain, subDomain, Operation, ForwardedtoDomain, ClientIPAddress, Result, Port, ObjectId, fwdingDestination, Host_0_HostName, Host_0_DnsDomain, IP_0_Address, Account_0_Name, Account_0_UPNSuffix;  \n  // Final output\n  CombinedForwardRules\n      | order by TimeGenerated desc;\n",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Collection",
          "Exfiltration"
        ],
        "techniques": [
          "T1020",
          "T1114"
        ],
        "templateVersion": "2.0.2"
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}