Write detection rules

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 scheduled detection rule runs on a SQL warehouse you provide. 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:

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(<event_time>)
  AND activity_id = 1
  AND endswith(lower(process.name), '\\hashcat.exe')

Replace <event_time> with the TIMESTAMP column or expression to filter.

IN_TIMERANGE(<event_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. This avoids sharing an endpoint between non-overlapping windows. A nonzero extend_lookback intentionally makes consecutive windows overlap. Signal deduplication controls repeated signals from matching rows. You don't write the start and end times into the SQL yourself. Instead, Lakewatch supplies the range based on how the query runs:

• For a scheduled run, the base window starts at the previous scheduled fire time and ends at the current scheduled fire time. This time window is unaffected by scheduling delays. To account for ingestion lag, see Extend the window with extend_lookback.
• 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(<event_time>) 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 current scheduled fire 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:

Overlapping runs can select the same row multiple times. Keep signal deduplication enabled and choose a deduplication window that covers the period during which a row can be selected again. A longer lookback does not automatically lengthen the deduplication window. Avoid adding changing values such as CURRENT_TIMESTAMP() to the SQL result solely for bookkeeping, because changed result rows are not duplicates. See Signal deduplication.

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.

To set extend_lookback in the UI, enter a duration in Extend lookback, next to the detection SQL. Use ordered day, hour, minute, and second values, such as 15m or 1h30m. Leave it blank to use the 15-minute default, or enter 0s to turn off the extension. The setting has no effect unless your SQL uses IN_TIMERANGE.

Choose the right time column

An event-time column 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:

• Missed or double-counted 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, so a delayed run doesn't leave a gap. Overlap between scheduled runs comes from extend_lookback. See Extend the window with extend_lookback.
• Schedule changes: If you change a rule from every 15 minutes to every 30 minutes, a hardcoded INTERVAL 15 MINUTES is wrong until you remember to edit it. With IN_TIMERANGE, the window follows the schedule.
• Re-windowing: 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.
• Reproducibility: Given the same bounds, IN_TIMERANGE scans the same rows on every run, so results are repeatable. A query tied to NOW() 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 scheduled fire 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:

These predicates use fixed offsets from TIMERANGE_NOW(). Changing the schedule does not change their interval literals. extend_lookback does not widen these manually specified windows, because it changes only the managed start bound used by IN_TIMERANGE.

This table summarizes when to use each function: