Red Sift - New email with URL from previously unseen sender

Status: available
Severity: medium
Time window: 14d
Group by: EmailFrom
Source: github.com/Azure/Azure-Sentinel

'Detects email forensics events that contain one or more URLs where the sender in the from field has not been seen in the previous 14 days, which may indicate phishing activity or a newly observed sender.'

MITRE ATT&CK coverage

Tactic	Techniques
Initial Access	`T1566` Phishing

Rule body kusto

id: 6e0b70d4-0ab8-480e-9707-8ad45fc21a65
name: Red Sift - New email with URL from previously unseen sender
description: |
  'Detects email forensics events that contain one or more URLs where the sender in the from field has not been seen in the previous 14 days, which may indicate phishing activity or a newly observed sender.'
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: RedSiftPush
    dataTypes:
      - RedSiftEmailForensics_CL
queryFrequency: 1h
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let lookback = 14d;
  let recentWindow = 1h;
  let historicalSenders = RedSiftEmailForensics_CL
  | extend
      EmailFrom = tostring(column_ifexists("EmailFrom", ""))
  | where TimeGenerated between (ago(lookback) .. ago(recentWindow))
  | where isnotempty(EmailFrom)
  | summarize by EmailFrom;
  RedSiftEmailForensics_CL
  | extend
      EmailFrom = tostring(column_ifexists("EmailFrom", "")),
      EmailSubject = tostring(column_ifexists("EmailSubject", "")),
      EmailReturnPath = tostring(column_ifexists("EmailReturnPath", "")),
      EmailMessageUid = tostring(column_ifexists("EmailMessageUid", "")),
      SrcIp = tostring(column_ifexists("SrcIp", "")),
      DstHostname = tostring(column_ifexists("DstHostname", "")),
      Severity = tostring(column_ifexists("Severity", "")),
      Message = tostring(column_ifexists("Message", "")),
      CorrelationUid = tostring(column_ifexists("CorrelationUid", "")),
      EmailUrls = todynamic(column_ifexists("EmailUrls", "[]"))
  | where TimeGenerated >= ago(recentWindow)
  | where isnotempty(EmailFrom)
  | extend UrlCount = array_length(EmailUrls)
  | where UrlCount > 0
  | mv-apply Url = EmailUrls on (
      summarize UrlSet = make_set(tostring(Url.url_string), 50)
    )
  | extend UrlList = strcat_array(UrlSet, ", ")
  | join kind=leftanti (historicalSenders) on EmailFrom
  | project
      TimeGenerated,
      EmailFrom,
      EmailSubject,
      EmailReturnPath,
      EmailMessageUid,
      SrcIp,
      DstHostname,
      UrlCount,
      UrlList,
      Severity,
      Message,
      CorrelationUid
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: EmailFrom
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SrcIp
  - entityType: DNS
    fieldMappings:
      - identifier: DomainName
        columnName: DstHostname
customDetails:
  EmailSubject: EmailSubject
  ReturnPath: EmailReturnPath
  UrlCount: UrlCount
  UrlList: UrlList
  CorrelationUid: CorrelationUid
alertDetailsOverride:
  alertDisplayNameFormat: "RedSift - New URL-bearing sender {{EmailFrom}}"
  alertDescriptionFormat: "Email from previously unseen sender {{EmailFrom}} contains {{UrlCount}} URL(s)."
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: P1D
    matchingMethod: Selected
    groupByEntities:
      - Account
    groupByCustomDetails:
      - EmailSubject
eventGroupingSettings:
  aggregationKind: AlertPerResult
suppressionEnabled: false
suppressionDuration: PT1H
version: 1.0.0
kind: Scheduled

Stages and Predicates

Parameters

let lookback = 14d;
let recentWindow = 1h;

Let binding: `historicalSenders`

let historicalSenders = RedSiftEmailForensics_CL
| extend
    EmailFrom = tostring(column_ifexists("EmailFrom", ""))
| where TimeGenerated between (ago(lookback) .. ago(recentWindow))
| where isnotempty(EmailFrom)
| summarize by EmailFrom;

Derived from lookback, recentWindow.

Stage 1: `source`

RedSiftEmailForensics_CL

Stage 2: `extend`

| extend
    EmailFrom = tostring(column_ifexists("EmailFrom", "")),
    EmailSubject = tostring(column_ifexists("EmailSubject", "")),
    EmailReturnPath = tostring(column_ifexists("EmailReturnPath", "")),
    EmailMessageUid = tostring(column_ifexists("EmailMessageUid", "")),
    SrcIp = tostring(column_ifexists("SrcIp", "")),
    DstHostname = tostring(column_ifexists("DstHostname", "")),
    Severity = tostring(column_ifexists("Severity", "")),
    Message = tostring(column_ifexists("Message", "")),
    CorrelationUid = tostring(column_ifexists("CorrelationUid", "")),
    EmailUrls = todynamic(column_ifexists("EmailUrls", "[]"))

Stage 3: `where`

| where TimeGenerated >= ago(recentWindow)

Stage 4: `where`

| where isnotempty(EmailFrom)

Stage 5: `extend`

| extend UrlCount = array_length(EmailUrls)

Stage 6: `where`

| where UrlCount > 0

Stage 7: `kusto:mv-apply`

| mv-apply Url = EmailUrls on (
    summarize UrlSet = make_set(tostring(Url.url_string), 50)
  )

Stage 8: `extend`

| extend UrlList = strcat_array(UrlSet, ", ")

Stage 9: `join` (negated)

| join kind=leftanti (historicalSenders) on EmailFrom

Stage 10: `project`

| project
    TimeGenerated,
    EmailFrom,
    EmailSubject,
    EmailReturnPath,
    EmailMessageUid,
    SrcIp,
    DstHostname,
    UrlCount,
    UrlList,
    Severity,
    Message,
    CorrelationUid

Stage 11: `summarize`

summarize

Stage 12: `summarize`

summarize by EmailFrom

Exclusions

Top-level NOT(...) conjuncts: predicates this rule actively suppresses.

Field	Kind	Excluded values
`EmailFrom`	is_not_null	(no value, null check)

Indicators

Each row is a field, operator, and value that the rule matches. The corpus column counts how many other rules in the catalog look for the same combination: high numbers point to widely-used, community-vetted indicators. Blank or 1 shows that the indicator is specific to this rule.

Field	Kind	Values
`EmailFrom`	is_not_null	(no value, null check)
`UrlCount`	gt	`0` transforms: `cased`

Output fields

Fields the rule emits when it matches. Chronicle authors list these in the outcome block; they appear on the detection and $risk_score drives alerting. Sentinel / Defender XDR rules build them up through project / summarize / extend stages. Sentinel maps these into alert fields via entityMappings and customDetails; Defender XDR custom detections surface them as alert fields directly.

Field	Source
`EmailFrom`	`summarize`

`j` / `k`	Scroll down / up
`d` / `u`	Half-page down / up
`gg` / `G`	Top / bottom
`h` / `l`	History back / forward
`f`	Follow link (`Shift` = new tab)
`/`	Focus search
`?`	Toggle this help
`↑` / `↓`	Navigate search results
`Enter`	Open highlighted result
`Esc`	Close results / dialog

`type:`	`events` / `rules` / `providers`
`vendor:`	`sigma` / `elastic` / `splunk` / `kusto` / `chronicle` (vendor name alone also works: `sigma:`, `kql:`, `secops:`…)
`tactic:`	TA-id, slug, or name: `credential_access`, `TA0006`
`technique:`	technique or sub-technique ID: `T1003`, `T1003.001` (alias `tech:`)
`severity:`	`critical` / `high` / `medium` / `low` / `informational` (alias `sev:`)
`risk_score`	Numeric comparison on the Elastic risk score (0 to 100): `risk_score>50`, `risk_score<=20`, `risk_score=99` (alias `risk`; Elastic rules only)
`stages:`	Rules with exactly N pipeline stages
`correlation:`	`single_event` / `sequence` / `alternatives` / `alternatives_cross_log` / `all_required` / `correlated`
`with:`	Co-occurrence event-id; stacks (`with:4624 with:4769`) to require all, while a comma list in one occurrence (`with:4624,4769`) is an either-or group. Implies multi-event
`like:`	Structural neighbors of a rule slug (equivalents + subsumption stricter / broader): `like:comsvcs_lsass_memory_dump-splunk-sysmon`
`groupby:`	Entity-grouping substring match against `group_by_keys`: `groupby:user`, `groupby:host`
`uses:`	Rules whose predicate tree touches the field (any kind, any value): `uses:CommandLine`
`excludes:`	Rules with top-level `not()` clauses on the field (FP whitelists): `excludes:ParentImage`
`field:` / `value:`	Predicate search; narrows rule cards to those with a matching leaf and drives the indicator tier. Unquoted = substring, wildcards allowed (`value:mimikatz`)
`indicator:`	Shorthand for `field:F value:V`: `indicator:Image=*\powershell.exe`
`kind:`	Filter by predicate kind. Narrows rule cards to those carrying a matching predicate leaf (`vendor:elastic kind:cidr_match`) and drives the indicator tier: `contains` / `starts_with` / `ends_with` / `regex` / `cidr` / `eq` / `in` … (operator aliases `op:`/`match:`)
`has:` / `no:`	`sample`, `field`, `notes`, `refs`, `trace`, `thirdparty`, `rule`, `pattern`, `timewindow`, `threshold`, `newterms`, `sigma`/`elastic`/`splunk`/`kusto`/`chronicle`
`-op:val`	Exclude matches; works on most operators but not `type:`/`like:`/`has:`/`no:` (use `no:<flag>` to exclude a rule flag): `tactic:execution -vendor:splunk`. Standalone `-kind:`/`-field:`/`-value:` drop every rule carrying a matching predicate leaf (`type:rules -kind:is_null`)
`field:"…"` / `value:"…"`	Quoted value = anchored exact match (also allows spaces): `value:"net user"`
`a,b`	Comma = OR inside one operator (`vendor:sigma,elastic`, `severity:high,critical`); repeating a facet merges the same way. `field:`/`value:` never split (literal commas)
`vendors:` / `stage:`	Singular and plural spellings fold to the canonical operator and value: `tactics:` = `tactic:`, `type:event` = `type:events`, `correlation:sequences` = `correlation:sequence`, `has:thresholds` = `has:threshold`
`"quoted phrase"`	Exact-match a multi-word phrase (free text)

Red Sift - New email with URL from previously unseen sender

MITRE ATT&CK coverage

Rule body kusto

Stages and Predicates

Parameters

Let binding: `historicalSenders`

Stage 1: `source`

Stage 2: `extend`

Stage 3: `where`

Stage 4: `where`

Stage 5: `extend`

Stage 6: `where`

Stage 7: `kusto:mv-apply`

Stage 8: `extend`

Stage 9: `join` (negated)

Stage 10: `project`

Stage 11: `summarize`

Stage 12: `summarize`

Exclusions

Indicators

Output fields

Keyboard shortcuts

Search operators

Red Sift - New email with URL from previously unseen sender

MITRE ATT&CK coverage

Rule body kusto

Stages and Predicates

Parameters

Let binding: historicalSenders

Stage 1: source

Stage 2: extend

Stage 3: where

Stage 4: where

Stage 5: extend

Stage 6: where

Stage 7: kusto:mv-apply

Stage 8: extend

Stage 9: join (negated)

Stage 10: project

Stage 11: summarize

Stage 12: summarize

Exclusions

Indicators

Output fields

Let binding: `historicalSenders`

Stage 1: `source`

Stage 2: `extend`

Stage 3: `where`

Stage 4: `where`

Stage 5: `extend`

Stage 6: `where`

Stage 7: `kusto:mv-apply`

Stage 8: `extend`

Stage 9: `join` (negated)

Stage 10: `project`

Stage 11: `summarize`

Stage 12: `summarize`