Rule Conversion🔗

rsigma backend convert translates Sigma rules into queries for a specific log analytics backend. Instead of evaluating rules against live events, conversion produces query strings that you can run against an existing log store: PostgreSQL/TimescaleDB, LynxDB, and any future backend that implements the Backend trait. This is the right path for historical threat hunting and for retroactive coverage testing against months of already-collected logs.

This page covers the two production backends, their output formats and backend options, multi-table correlation, and the workflow for integrating converted queries into Grafana, dashboards, or SOAR playbooks.

When to convert instead of evaluate🔗

The output of conversion is plain text on stdout, one query per rule. Pipe it into psql, save it to a versioned .sql file, or wrap it in a deployment pipeline.

Backends and targets🔗

List what is available:

rsigma backend targets

Available conversion targets:
  postgres  - PostgreSQL/TimescaleDB (aliases: postgresql, pg)
  lynxdb    - LynxDB log analytics engine
  test      - Backend-neutral test backend

The test backend produces backend-neutral text queries and is mainly used by the test suite, but it is handy for seeing how a rule lowers to a generic boolean expression.

Each backend supports one or more output formats:

rsigma backend formats postgres

Available formats for 'postgres':
  default  - Plain PostgreSQL SQL
  view  - CREATE OR REPLACE VIEW for each rule
  timescaledb  - TimescaleDB-optimized queries with time_bucket()
  continuous_aggregate  - CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous)
  sliding_window  - Correlation queries using window functions for per-row sliding detection

PostgreSQL and TimescaleDB🔗

The PostgreSQL backend is the most fully featured. It leverages native operators that map cleanly to Sigma modifiers:

Basic conversion🔗

rsigma backend convert rules/ -t postgres

For a single-detection rule:

SELECT * FROM security_events WHERE "CommandLine" ILIKE '%whoami%'

Backend options🔗

-O key=value passes options into the PostgreSQL backend. The most useful ones:

Combine options for production schemas:

rsigma backend convert rules/ -t postgres \
    -O table=okta_events \
    -O json_field=data \
    -O timestamp_field=time

JSONB mode🔗

When events live as a JSONB column ({"data": {"actor": {"name": "..."}}, "time": "..."}), set json_field and Sigma field references become JSONB extraction expressions:

-- Sigma field: eventType
data->>'eventType'

-- Sigma field: securityContext.isProxy
data->'securityContext'->>'isProxy'

-- Sigma field: actor.detail.alternateId
data->'actor'->'detail'->>'alternateId'

Intermediate path segments use -> (returns jsonb) and the last segment uses ->> (returns text), which mirrors how rsigma-eval walks nested JSON during streaming evaluation.

Output formats🔗

default🔗

Plain SELECT * FROM table WHERE ... queries, one per rule. Use this for ad-hoc execution in psql or for embedding into application code.

view🔗

Wraps each rule's query in CREATE OR REPLACE VIEW sigma_<rule-id> AS SELECT .... Useful for dashboards: each rule becomes a named view that downstream tools can query without parsing SQL.

rsigma backend convert rules/ -t postgres -f view

timescaledb🔗

Adds time_bucket() clauses and other TimescaleDB-specific optimizations. Use this when your events sit on a TimescaleDB hypertable.

continuous_aggregate🔗

Wraps each base detection rule in CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) AS ... WITH NO DATA. For a Sigma EventID: 4625 rule, you get:

CREATE MATERIALIZED VIEW sigma_9d2e7c48_4a3b_4f99_93c9_1c5f7c8b1a2b
    WITH (timescaledb.continuous) AS
    SELECT time_bucket('1 hour', time) AS bucket, *
    FROM security_events WHERE "EventID" = 4625
    WITH NO DATA

TimescaleDB then refreshes the aggregate in the background and your dashboards query the materialised result instead of the raw hypertable. Convert the base detection rules separately (or pass --skip-unsupported) and skip the event_count/value_count correlation rules; the materialised view above is the queryable surface you want.

sliding_window🔗

Uses SQL window functions for event_count correlations, producing a per-row sliding window that emits every event that crosses the threshold. Only the correlation rule itself converts under this format; base detection rules return unknown output format: sliding_window, so pair the conversion with --skip-unsupported:

rsigma backend convert rules/ -t postgres -f sliding_window --skip-unsupported

WITH source AS (
    SELECT * FROM security_events
    WHERE time >= NOW() - INTERVAL '300 seconds'
),
event_counts AS (
    SELECT *, COUNT(*) OVER (
        PARTITION BY "User"
        ORDER BY time
        RANGE BETWEEN INTERVAL '300 seconds' PRECEDING AND CURRENT ROW
    ) AS correlation_event_count
    FROM source
)
SELECT * FROM event_counts WHERE correlation_event_count >= 5

This is the right format when you want per-event explanations of why a brute-force correlation fired, rather than a single aggregate row.

Multi-table temporal correlations🔗

When a temporal correlation references detection rules that target different tables (via per-logsource pipeline routing or the postgres.table custom attribute), the backend automatically generates a UNION ALL CTE:

WITH matched AS (
    SELECT *, 'process_rule' AS rule_name FROM process_events
        WHERE time >= NOW() - INTERVAL '300 seconds'
    UNION ALL
    SELECT *, 'network_rule' AS rule_name FROM network_events
        WHERE time >= NOW() - INTERVAL '300 seconds'
)
SELECT "User", COUNT(DISTINCT rule_name) AS distinct_rules,
    MIN(time) AS first_seen, MAX(time) AS last_seen
FROM matched
GROUP BY "User"
HAVING COUNT(DISTINCT rule_name) >= 2

When every referenced rule targets the same table, the backend emits the simpler single-table form. The multi-table form expects compatible column layouts (same columns in each SELECT *). If your tables differ, normalize them through pipeline field mappings or use a single-table approach with a discriminator column.

OCSF pipelines🔗

Two pipelines are included for OCSF-style schemas:

# Single-table: every event class goes to security_events
rsigma backend convert rules/ -t postgres -p pipelines/ocsf_postgres.yml

# Multi-table: per-logsource routing to process_events, network_events, etc.
rsigma backend convert rules/ -t postgres -p pipelines/ocsf_postgres_multi_table.yml

Both are bundled in the repository at crates/rsigma-convert/pipelines/. They are good starting points; copy and customise them for your schema.

Custom table per rule🔗

Three layers of precedence control the target table, schema, and database, in this order:

1. Rule-level custom_attributes (postgres.table, postgres.schema, postgres.database).
2. Pipeline state (set_state with key: table, key: schema).
3. CLI backend options (-O table=...).
4. Backend defaults (security_events).

Rule-level wins. This lets you keep most rules on the default table while routing exceptions:

title: Process Creation
logsource:
    category: process_creation
detection:
    selection:
        CommandLine|contains: 'whoami'
    condition: selection
custom_attributes:
    postgres.table: process_events
    postgres.schema: siem

LynxDB🔗

The LynxDB backend produces SPL2-compatible queries. Translation favours the native search syntax and falls back to | where pipeline stages for features that LynxDB's parser does not support directly (regex, CIDR, single-character wildcards).

"Deferred" means the feature does not translate to a native LynxDB search term and is instead emitted as an SPL2 pipeline stage downstream of search.

rsigma backend convert rules/ -t lynxdb

Two output formats: default produces a full query with index prefix (FROM main | search ...), and minimal produces just the search expression for use as an API q parameter.

The target index defaults to main. Override it via pipeline state:

transformations:
  - type: set_state
    key: index
    value: security_logs

rsigma backend convert rules/ -t lynxdb -p pipeline.yml
# Output: FROM security_logs | search ...

Selecting columns with fields:🔗

When a Sigma rule lists fields:, the backend emits SELECT field1, field2, ... instead of SELECT *. Function calls (e.g. count(*)) and field as alias are preserved. This gives you control over what each generated query returns without writing the SELECT clause by hand.

title: Sad Puppy in Dog Supply Line
detection:
    selection:
        status: "sad"
    condition: selection
fields:
    - dog_name
    - dog_breed
    - status as current_state

SELECT "dog_name", "dog_breed", "status" AS "current_state"
FROM security_events
WHERE "status" ILIKE 'sad'

Skipping unsupported rules🔗

Not every rule in a large ruleset translates cleanly to every backend. Use --skip-unsupported to drop those rules silently and continue:

rsigma backend convert rules/ -t postgres --skip-unsupported

Without the flag, the first unsupported rule fails the run with exit code 2 (rule error). Use the rsigma rule fields command beforehand to audit which fields each rule depends on, before discovering at conversion time that one is unsupported by your pipeline.

Saving output🔗

-o path/to/output.sql writes to a file instead of stdout. Combine with --skip-unsupported for a one-shot pipeline build:

rsigma backend convert rules/ -t postgres -f view \
    -p pipelines/ocsf_postgres.yml \
    --skip-unsupported \
    -o /var/lib/rsigma/sql/views.sql
psql -f /var/lib/rsigma/sql/views.sql

Workflow: from rules to a Grafana dashboard🔗

A typical detection-engineering loop with the PostgreSQL backend:

1. Author rules in a Git-tracked directory.
2. CI runs rsigma rule lint and rsigma rule validate (see Linting Rules and CI/CD).
3. On merge to main, CI runs rsigma backend convert -t postgres -f view -p pipelines/ocsf_postgres.yml and commits the SQL into a deployment repository.
4. A Terraform or Atlas migration applies the generated CREATE VIEW statements to your PostgreSQL.
5. Grafana panels query the resulting views.
6. Alerting rules in Grafana Managed Alerting or in Prometheus query the same views (or the underlying tables) at scheduled intervals.

This avoids the impedance mismatch of running Sigma rules through pySigma at every alert evaluation. The conversion happens once per rule change, and your alerting infrastructure speaks plain SQL.

See also🔗

• CLI reference: backend convert for the full flag table.
• Backends reference: PostgreSQL/TimescaleDB for every option, modifier mapping, and edge case.
• Backends reference: LynxDB for SPL2 specifics.
• Sigma rules on LynxDB for LynxDB-side guides covering compatibility, pipelines, the SPL2 mapping, and operational tutorials.
• Processing Pipelines for field mapping (essential for any non-default schema).
• Linting Rules for catching authoring mistakes before conversion.