RunReveal Detection Engine: SQL, Sigma, and What the Workspace Actually Shows

2 days ago

6 min read

Detection-as-Code, For Real

A detection in RunReveal is a scheduled query with metadata attached — severity, risk score, MITRE ATT&CK mapping, which sources it applies to.

The query is the detection: version-controllable, diffable, reviewable in a pull request, because it's SQL or Sigma text, not a form full of dropdowns serialized into some proprietary format.

Everything below this point is Live tested, real detection objects, real query results, real run history. Nothing here is a hypothetical.

Two Detection Types

SQL detections

Full ClickHouse SQL — joins, custom views, historical lookback, whatever the query language supports. Typically scheduled every five minutes via a cron expression, though one real detection in the test workspace uses a shorthand form instead: schedule: "@1m" runs identically to schedule: "* * * * *".

Neither the console nor the API docs mention the @-shorthand exists; it was only visible by reading a real detection object back through the API.

The windowing gotcha: The documented, correct way to filter a time window in a scheduled SQL detection is receivedAt >= {from:DateTime} AND receivedAt < {to:DateTime} — RunReveal auto-injects from/to on every scheduled run. now() - INTERVAL X MINUTE runs without erroring, but it is not the supported pattern, and one real detection in this workspace still uses it (see below) rather than the documented one.

Sigma detections

Real-time streaming pattern matching using the industry-standard Sigma format. Simpler than SQL, portable across tools that also support Sigma, but no joins and no custom views.

Pulling a real Sigma detection back through the API shows something the docs don't spell out: the top-level query field is an empty string.

The actual rule lives one level down, as raw YAML, inside settings.rule:

{
  "type": "sigma",
  "query": "",
  "settings": {
    "rule": "title: Google Alert Center Activity Rule Test\n
              id: 11111111-1111-4111-8111-111111111111\n
              status: test\n
              logsource:\n
                product: google_workspace\n
              detection:\n
                selection:\n
                  eventName: Activity Rule\n
                condition: selection\n
              level: medium"
  }
}

If you're building anything that reads detections back programmatically — a CI check, a backup script, an MCP tool — checking query for Sigma rules will silently find nothing. You have to branch on type and parse settings.rule as YAML instead.

The Escalation Ladder, With Real Numbers Behind It

A match doesn't automatically page anyone. It climbs a three-step ladder:

Detections — every single match gets recorded here, quietly, with no notification.
Signals — matches that RunReveal considers worth surfacing, still with no notification channel attached.
Alerts — the same match, once a notification channel is actually attached to the detection.

That's the documented model. Querying the workspace's own detections and alerts tables directly — both queryable exactly like any other table in RunReveal, which is a genuinely nice property — shows what it looks like in practice, not in theory.

Across every detection that has ever run in this workspace, exactly one alert has ever fired, ever. One. Everything else that ran — and plenty has, including a detection that’s been executing every five minutes for over a week straight — has stayed at the "detections" tier: recorded, queryable, and completely silent.

The escalation ladder isn’t a marketing description of intended behavior; it’s really how quiet the system stays until something is deliberately wired to notify.

A structural quirk the docs never mention: detections is per-match, not per-run

One real detection returned 11 matching rows on a single scheduled run. Querying the detections table for that detection’s history back:

SELECT id, scheduledRunID, recordsReturned
FROM detections
WHERE detectionName = '<redacted-detection-name>'

came back as 11 separate rows, each with a distinct id but the identical scheduledRunID and the identical recordsReturned: 11 value copied onto every row.

The table isn't one row per scheduled execution — it's one row per matched record, with scheduledRunID as the join key back to the run that produced them.

recordsReturned tells you the run's total match count, duplicated across every row it produced, not that row's own count (which is always 1). If you're aggregating this table for a dashboard or a report, count(DISTINCT scheduledRunID) is "how many times this rule fired" — count(*) is "how many individual matches came back," and confusing the two will silently 11x your numbers for any rule that returns multiple rows per run.

Case Study: A Detection That Ran Clean for a Week and Found Nothing — For the Wrong Reason

This is the finding worth the most attention in this article, because the failure mode it demonstrates isn't RunReveal-specific — it's a trap any SQL-based detection engine leaves open.

A real test detection in this workspace flags repeated failed admin logins:

SELECT actor['email'] AS user, srcIP, count() AS failures
FROM logs
WHERE sourceType = 'google-workspace-alerts'
  AND eventName ILIKE '%fail%login%'
  AND receivedAt > now() - INTERVAL 5 MINUTE
GROUP BY user, srcIP
HAVING failures >= 5

It's enabled, on a five-minute schedule, and has been executing without error for over a week. It has zero rows in the detections table for its entire run history — no matches, ever. Read on its own, that looks like exactly what you want from a tuned detection: no false positives, quietly waiting for a real brute-force attempt.

Querying the real event vocabulary this source actually produces tells a different story:

SELECT DISTINCT eventName FROM logs WHERE sourceType = 'google-workspace-alerts'

→ "Admin password reset"
→ "Suspicious login"
→ "Activity Rule"

None of those three values contain the substring the detection is filtering for. eventName ILIKE '%fail%login%' cannot match anything this source will ever emit — the real event for a failed login here is literally named "Suspicious login," with no "fail" in it anywhere. This detection is not quiet because the environment is clean. It's quiet because its WHERE clause and the source's real data never had any chance of intersecting, and it will stay silent forever regardless of what actually happens, because RunReveal has no mechanism to warn you a filter doesn't match anything the source produces — a rule that's structurally guaranteed to never fire looks, from the outside, identical to a healthy, well-tuned one.

The practical takeaway: Before trusting silence from any new detection as "nothing bad happened," run SELECT DISTINCT eventName FROM logs WHERE sourceType = '<source>' against the real data first and check your filter actually intersects with it. Writing the WHERE clause from what you assume the event names should be, instead of what they actually are, is an easy way to ship a detection that will never once fire.

API Gotchas Worth Knowing Before You Automate Anything

I built an MCP server against this API, which meant hitting every endpoint by hand before trusting it enough to wrap in a tool. A few things the documentation doesn't mention:

name is required on creation but not marked as required in the documented schema — omit it and you get a generic validation error that doesn’t obviously point at the missing field.
The enabled field on /detections/enabled/update wants a string, literally "false", not a JSON boolean. Send a real boolean and it 500s.
/detections/get takes the detection’s name as a query parameter, not an ID in a POST body.
Deleting a detection through the API doesn’t work. It returns success and hands back the full detection list, with your target detection still sitting in it, completely unchanged.

That last one isn’t a one-time observation — it’s still verifiably true. Four test detections created during earlier hands-on testing for this series are still sitting in the workspace today, weeks later, disabled rather than deleted because that was the only way to actually neutralize them.

A fun contradiction, if you read the Agents piece in this series: RunReveal's Agents API wants enabled as a real boolean and rejects a string outright — the exact opposite convention from detections. And Agents' delete endpoint actually works, unlike detections. Don't assume any two endpoints on this API follow the same rules, even when they look structurally identical.

A Real Timing Trap

Checking whether a scheduled detection found anything immediately after its run time updates is a trap. I hit zero results checking within roughly 15 to 90 seconds of a run, even when I could prove with a manual query that the underlying data clearly matched the detection's WHERE clause.

Minutes later, the same broader query found the matches sitting at their original timestamps.

There's real read-after-write lag between a detection run completing and its results being reliably queryable.

Don't trust an immediate zero-result check as proof a rule doesn't work.

------------------------------------------------------Dean-----------------------------------

Next Part follows the thread from here — what happens once an alert actually fires and an investigation gets opened.