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.
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 designDetection 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.
| Kind | Type | Example (generic; DET-0001's own values are in the table below) | What it tunes |
|---|---|---|---|
| threshold | integer or decimal | min_failed_logons, default 10, bounds 5 to 50 | How much of the behavior has to happen before it is worth an alert. |
| window | interval | lookback_minutes, default 60, bounds 15 to 1440 | How far back the query reads. It sets both the sensitivity and the warehouse bill. |
| allowlist | list of strings, or a reference to a table | allowed_service_accounts, default empty, sourced from a governed table | The named exclusions. Empty by default, so an exclusion is always a visible act. |
| scope | list of strings | in_scope_domains, default every domain the log source covers | Which part of the estate the detection runs against, for staged rollout and for OT segments that need their own thresholds. |
| severity_cutoff | integer | page_above, default 3, bounds 1 to 5 | What reaches a human immediately against what waits in a queue. |
These requirements keep variables reviewable:
- 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.
- The default carries one line of evidence, the same standard the six scoring inputs carry.
- 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.
- 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.
- Allowlists are variables too, and they are the only sanctioned way to exclude a known source of noise.
- A variable changed three times in a quarter is a sign to review the hypothesis and the detection.
DET-0001's variables
| Variable | Kind | Default | Bounds | Evidence for the default |
|---|---|---|---|---|
lookback_minutes | window | 60 | [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_services | threshold | 3 | [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_accounts | allowlist | SECURITY.DETECT.ALLOWLIST_KERBEROAST | - | empty at deploy; every row needs a ticket id and an owner, and the table is reviewed each quarter |
in_scope_domains | scope | ['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.