Write detection rules
Lakewatch is in Private Preview. To request access, reach out to your Databricks account team.
A detection rule's logic is the SQL that defines what it searches for. This page explains how rules run, how to choose an approach for common detection patterns, and how to scope a rule to the right window of data. For the UI steps to create a rule, see Create and manage detection rules.
How detection rules run
Every detection rule in Lakewatch runs on a schedule. When a rule runs, it executes your detection SQL over a time window and generates a signal for each match. You write the full query, including the FROM clause, so a rule can join tables, scan a whole table, or compute aggregates. A rule runs on a SQL warehouse you provide, or on serverless compute; see Set up a detection warehouse.
You set a rule's schedule and compute when you create it. See Create and manage detection rules.
Choosing an approach
This table maps common detection patterns to how you author each one:
Detection pattern | Example | How to author it |
|---|---|---|
Signature or IOC match | A process hash in a known-bad list; a connection to a flagged IP | Match events against a list of known-bad values |
Atomic signal | A new admin account; an audit log cleared; a mailbox forwarding rule added | Match a single event that is itself noteworthy |
Short-window threshold | More than 5 failed logins for a user in 1 minute; one IP failing 20 accounts in 10 minutes | Aggregate and threshold on a short schedule |
Behavioral baseline or anomaly | A user accessing far more servers than their 30-day norm | Compare against a baseline table |
Rare or first-seen | A process hash never seen before; a first-time add to a privileged group | Compare against a seen-list table |
Cross-source correlation | Login, then file-share access, then exfiltration, joined across sources | Join across sources on an hourly or shorter schedule |
Scope rules to a time range
Whenever possible, scope a rule to a relevant time range rather than scanning an entire table. Use the IN_TIMERANGE() function to express that window, and let the run supply the bounds:
SELECT *
FROM process_activity
WHERE IN_TIMERANGE(time)
AND activity_id = 1
AND endswith(lower(process.name), '\\hashcat.exe')
IN_TIMERANGE(time) returns true when an event falls within the effective time range of the current run. The range is half-open: an event whose timestamp equals the window's start is included, but one that equals the end is excluded, so adjacent runs never double-count the boundary. You don't write the start and end times into the SQL yourself. Instead, Lakewatch supplies the range based on how the query runs:
- In a detection rule, the window is derived from the rule's schedule and ends at the run's trigger time. A rule that runs every 15 minutes covers a 15-minute slice. You can widen that slice with
extend_lookbackto include late-arriving logs. - On the query page (which you can use to develop a rule), you select the window using the date/time picker. Because SQL written on the query page does not itself contain the time range, it can be deployed as a detection rule unchanged.
Extend the window with extend_lookback
IN_TIMERANGE() filters on event time, but logs often reach the table minutes after the event occurs. A row can land after the run whose window covered its event time has already finished, and later runs scan newer event times, creating a coverage gap. extend_lookback, an optional per-rule setting, moves the window's start earlier so each run re-scans a slice of older event time and catches rows that arrived since the last run. It shifts only the earliest bound; the latest bound stays at the run's trigger time. Set it to a duration such as 30s, 10m, or 2h. The window a scheduled rule scans is its schedule cadence plus extend_lookback:
| Schedule | Time range scanned (ending at the trigger time) |
|---|---|---|
Unset (default 15m) | Every 5 minutes | The previous 20 minutes |
| Every 5 minutes | The previous 5 minutes |
| Every 30 minutes | The previous 42 minutes |
| Every 10 minutes | The previous 5 hours and 10 minutes |
If you don't set extend_lookback, Lakewatch applies a default of 15 minutes. Most enterprise sources deliver within a few minutes, but few guarantee an upper bound, so the default absorbs typical late arrivals without any tuning.
extend_lookback has no dedicated form field. Set it by editing the rule as YAML (see Create and manage detection rules) and adding a top-level timerange block:
timerange:
extend_lookback: 30m
Choose the right time column
Event time (time) paired with extend_lookback is the right approach for nearly every rule. Estimate your source's ingestion lag and set extend_lookback to cover it, or rely on the 15-minute default for sources that deliver promptly.
If a source's ingestion latency is so variable that no single extend_lookback reliably covers it, you can instead scope the rule on ingestion time. On tables produced by a Lakewatch datasource, the lw_meta.ingestion_time column records when Lakewatch wrote each row after ingestion, so a window over it is unaffected by delivery delays:
WHERE IN_TIMERANGE(lw_meta.ingestion_time)
Reach for this only when event time plus extend_lookback can't do the job. Each run then covers a fixed slice of ingestion time rather than event time, so an event's own timestamp can fall outside the run's nominal window, which can skew time-bucketed aggregations. Try to solve it with event time first.
Avoid anchoring to NOW() or CURRENT_TIMESTAMP()
A common pattern hand-writes the window against the current wall-clock time:
-- Avoid this
WHERE time BETWEEN CURRENT_TIMESTAMP() - INTERVAL 24 HOUR AND CURRENT_TIMESTAMP()
This works for a routine run, but can create problems:
- It can miss or double-count data. A window like "the last hour" is anchored to when the run fires, not the slice it was meant to cover. If a 3:00 run is delayed to 3:12, it scans 2:12–3:12, leaves a 2:00–2:12 gap, and may overlap the next run.
IN_TIMERANGE()derives the window from the scheduled slice, eliminating that gap-and-overlap risk. - It doesn't adapt to schedule changes. If you change a rule from every 15 minutes to every 30 minutes, a hardcoded
INTERVAL 15 MINUTESis wrong until you remember to edit it. WithIN_TIMERANGE(), the window follows the schedule. - It can't be re-windowed. Backfilling a missed period, replaying after a data fix, or previewing a new rule against older data all run the same rule over a different window. A query locked to
NOW()can't do that without manual edits. - It's harder to reproduce. Given the same bounds,
IN_TIMERANGE()scans the same rows on every run, so results are repeatable. A query tied toNOW()shifts with each execution.
Reserve NOW() and CURRENT_TIMESTAMP() for cases when you need to reference the real wall-clock time, such as computing how old an event is relative to the present moment.
Reference the run's window with TIMERANGE_NOW()
When you need a "now" value that respects the run's window rather than the wall clock, use TIMERANGE_NOW(). It returns the run's trigger time, and falls back to the actual current time when no window is set (for example, on the query page). Use it to hand-build a window that IN_TIMERANGE() can't express, such as offsetting the window or scanning two non-adjacent periods in a single run:
Schedule | Predicate | Result |
|---|---|---|
Every 10 minutes |
| Shifts the 10-minute window 7 minutes into the past |
Every day |
| Matches events from today and from the same day last week |
This table summarizes when to use each function:
Goal | Use |
|---|---|
Scope a rule to its time window |
|
Reference "now" that respects the run's window |
|