DETECT hunt Build detections

ProductionBuild, Validate

Detections in SQL

A detection that runs as a scheduled query against a data warehouse keeps the same lifecycle. Tuning moves into declared variables, and each run costs warehouse compute.

ExampleFramework v0.136.0. DET-0001, the Kerberoasting detection from section 7, as it runs in Snowflake.

Remember

  • Filter on the event time column first, so the warehouse reads fewer files.
  • Declare thresholds, windows, allowlists, and scope as variables, each with a default, bounds, and evidence.
  • Keep allowlists in a table with a ticket id, an owner, and an expiry date.
  • Count warehouse compute in run cost. A lookback twice as long scans twice the data on every run.
  • The task schedule sets the latency floor. A detection that runs every 15 minutes waits 7.5 minutes on average before it can alert.
  • Handle late-arriving logs with a lookback longer than the schedule, and skip an entity that already has an alert inside the window.
On this page

One detection, three views

The Sigma SQL backend emits the predicate. Everything around it is dialect and scheduling. Every tunable number is a declared variable.

The Snowflake version changes one behavior on purpose. The Sigma rule and the portable query alert on every RC4 ticket request. The Snowflake version alerts when one account requests RC4 tickets for min_distinct_services (3 by default) or more distinct services inside the lookback window. The threshold trades single-request sensitivity for fewer alerts per batch run, and its evidence line is in the variables tab.

-- DET-0001 Kerberoasting, as a scheduled Snowflake detection.
-- The Sigma SQL backend emits the predicate; everything around it is the warehouse dialect,
-- and every tunable is a variable declared on the record rather than a number in the logic.
-- A task runs in its own session, so session variables set here would not reach it. Substitute
-- each $name below from variables.yml before you run this file; the kit has no warehouse deploy step yet.

-- $lookback_minutes               window,    default 60, bounds 15 to 1440
-- $min_distinct_services          threshold, default 3, bounds 2 to 5
-- $allowed_service_accounts       allowlist, a governed table
-- $in_scope_domains               scope,     default ["CORP","OT"]

CREATE OR REPLACE VIEW SECURITY.DETECT.DET_0001_KERBEROASTING AS
WITH events AS (
    SELECT
        event_time,
        raw:EventID::INT                    AS event_id,
        raw:TargetUserName::STRING          AS target_user,
        raw:ServiceName::STRING             AS service_name,
        raw:IpAddress::STRING               AS src_ip,
        raw:TicketEncryptionType::STRING    AS ticket_encryption_type,
        raw:Status::STRING                  AS status,
        raw:TargetDomainName::STRING        AS domain
    FROM SECURITY.RAW.WINDOWS_SECURITY          -- VARIANT column, so the shape is read at query time
    WHERE event_time >= DATEADD('minute', -$lookback_minutes, CURRENT_TIMESTAMP())
      AND raw:EventID::INT = 4769                -- partition pruning happens here, before anything is flattened
),
scoped AS (
    SELECT * FROM events
    WHERE ARRAY_CONTAINS(domain::VARIANT, PARSE_JSON('$in_scope_domains'))
),
matched AS (
    -- the part that comes out of the Sigma SQL backend, field names mapped by the pipeline
    SELECT * FROM scoped
    WHERE ticket_encryption_type = '0x17'
      AND status = '0x0'
      AND NOT (service_name LIKE '%$' OR service_name = 'krbtgt')
),
allowed AS (
    SELECT service_name FROM $allowed_service_accounts WHERE expires_on > CURRENT_DATE()
)
SELECT
    target_user,
    src_ip,
    COUNT(DISTINCT service_name)            AS services_requested,
    MIN(event_time)                         AS first_seen,
    MAX(event_time)                         AS last_seen,
    ARRAY_AGG(DISTINCT service_name)        AS service_names
FROM matched
WHERE service_name NOT IN (SELECT service_name FROM allowed)
GROUP BY target_user, src_ip
HAVING COUNT(DISTINCT service_name) >= $min_distinct_services
QUALIFY ROW_NUMBER() OVER (PARTITION BY target_user ORDER BY last_seen DESC) = 1;

-- Scheduling and cost. The schedule sets the floor under detection_latency_median,
-- and the warehouse size sets the half of run_cost the analysts never see.
-- At the default window the task runs four times inside it, so the insert skips an entity
-- that already has an alert from this detection inside the window.
CREATE OR REPLACE TASK SECURITY.DETECT.T_DET_0001
    WAREHOUSE = DETECT_XS
    SCHEDULE  = 'USING CRON */15 * * * * UTC'
AS
    INSERT INTO SECURITY.DETECT.ALERTS (detection_id, fired_at, entity, context)
    SELECT 'DET-0001', CURRENT_TIMESTAMP(), v.target_user,
           OBJECT_CONSTRUCT('src_ip', v.src_ip, 'services', v.service_names, 'count', v.services_requested)
    FROM SECURITY.DETECT.DET_0001_KERBEROASTING v
    WHERE NOT EXISTS (
        SELECT 1 FROM SECURITY.DETECT.ALERTS a
        WHERE a.detection_id = 'DET-0001'
          AND a.entity = v.target_user
          AND a.fired_at >= DATEADD('minute', -$lookback_minutes, CURRENT_TIMESTAMP())
    );
-- What the Sigma SQL backend emits for DET-0001, unchanged, against the fixture table.
-- This is the predicate the warehouse version wraps: same logic, no dialect, no variables.
SELECT * FROM detect_fixtures
WHERE (EventID=4769 AND TicketEncryptionType='0x17' AND Status='0x0')
  AND (NOT (ServiceName LIKE '%$' ESCAPE '\' OR ServiceName='krbtgt'))
# Declared on the detection record, and lint.py checks each declaration. No kit tool substitutes them
# into the warehouse query yet, and the warehouse version has no replay engine, so a person checks each
# bound against the fixtures at review.
variables:
  lookback_minutes:
    kind: window
    type: integer minutes
    default: 60
    bounds: [15, 1440]
    evidence: the task runs every 15 minutes, so a 60-minute window covers four runs and an event up to 45 minutes late still lands in one
  min_distinct_services:
    kind: threshold
    type: integer
    default: 3
    bounds: [2, 5]
    evidence: the recorded run, SDWIN-201022042947, asked for RC4 tickets for 5 user-run services, so the upper bound still fires on it. Set the default from your own 7-day count
  allowed_service_accounts:
    kind: allowlist
    type: table
    default: SECURITY.DETECT.ALLOWLIST_KERBEROAST
    evidence: empty at deploy; every row needs a ticket id and an owner, and the table is reviewed each quarter
  in_scope_domains:
    kind: scope
    type: list
    default: ["CORP", "OT"]
    evidence: both forests send 4769 events; the lab forest is excluded because its accounts are shared by design

Detection variables

Most tuning changes a number: a threshold moves, a lookback widens, or an account joins an allowlist. Written inline in the query, each of those is a logic change. You re-review it, re-test it, and re-convert it for every target, and the reason for the old value gets lost. Declared on the record, each value keeps its evidence and its history. The kit's lint checks every declaration today. It doesn't substitute the values into a query or replay at the bounds yet, so a tuning change edits the rule and variables.yml in the same pull request.

KindTypeExample (generic; DET-0001's own values are in the table below)What it tunes
thresholdinteger or decimalmin_failed_logons, default 10, bounds 5 to 50How much of the behavior has to happen before it is worth an alert.
windowintervallookback_minutes, default 60, bounds 15 to 1440How far back the query reads. It sets both the sensitivity and the warehouse bill.
allowlistlist of strings, or a reference to a tableallowed_service_accounts, default empty, sourced from a governed tableThe named exclusions. Empty by default, so an exclusion is always a visible act.
scopelist of stringsin_scope_domains, default every domain the log source coversWhich part of the estate the detection runs against, for staged rollout and for OT segments that need their own thresholds.
severity_cutoffintegerpage_above, default 3, bounds 1 to 5What reaches a human immediately against what waits in a queue.

These requirements keep variables reviewable:

  1. Every variable is declared once on the record with a type, a default, and, for thresholds and windows, bounds. Nothing reads a bare number out of the query.
  2. The default carries one line of evidence, the same standard the six scoring inputs carry.
  3. A variable change is a pull request against the record. Nobody edits it in the SIEM, so every threshold change stays in the detection's history.
  4. A threshold needs one fixture that still fires at the upper bound and one that stays silent at the lower bound, so a value that works only at its default fails. The kit doesn't replay at the bounds yet, so the reviewer checks each bound against the fixtures.
  5. Allowlists are variables too, and they are the only sanctioned way to exclude a known source of noise.
  6. A variable changed three times in a quarter is a sign to review the hypothesis and the detection.

DET-0001's variables

VariableKindDefaultBoundsEvidence for the default
lookback_minuteswindow60[15, 1440]the task runs every 15 minutes, so a 60-minute window covers four runs and an event up to 45 minutes late still lands in one
min_distinct_servicesthreshold3[2, 5]the recorded run, SDWIN-201022042947, asked for RC4 tickets for 5 user-run services, so the upper bound still fires on it. Set the default from your own 7-day count
allowed_service_accountsallowlistSECURITY.DETECT.ALLOWLIST_KERBEROAST-empty at deploy; every row needs a ticket id and an owner, and the table is reviewed each quarter
in_scope_domainsscope['CORP', 'OT']-both forests send 4769 events; the lab forest is excluded because its accounts are shared by design

Changes in a warehouse

A detection that runs as scheduled SQL carries a second run cost. The warehouse bills for a query that scans a day of events every five minutes. Score run cost as analyst minutes plus warehouse spend, and record both in the evidence line.

Latency and run cost land differently here. The schedule on the task is the floor under detection_latency_median. A detection running every 15 minutes averages seven and a half minutes of latency before an analyst sees anything, whatever the query does. The lookback_minutes variable sets the bill as well as the sensitivity, because a window twice as wide scans twice the data on every run. Widening it is a costed decision, so declare it and the decision stays visible.

Log forwarders buffer events, so a query whose lookback equals its schedule misses any event that arrives after the run whose window covers it. DET-0001's task runs every 15 minutes over a 60-minute lookback, so an event up to 45 minutes late still falls inside a run. The insert skips an entity that already has an alert inside the window, so the overlapping runs raise one alert per account.

Everything else stays the same: the record carries every section, the score comes from the same inputs, and the precision floor decides whether the detection stays in production. The kit has no replay engine for a warehouse yet, so a reviewer runs this version against the fixtures by hand.

Next step Query languages