pySigma SQLite Backend

Converts Sigma rules into SQLite queries. The package sigma.backends.sqlite provides sqliteBackend, registered as the sigma-cli target sqlite. It is the converter behind Zircolite, and its output runs on any SQLite table of flattened events.

Maintained by wagga.

Install

pip install pysigma-backend-sqlite

Requires Python 3.11+ and pySigma 2.0.1 or newer. Correlation output needs SQLite 3.38 or newer. sigma-cli 3.1 still requires pySigma 1.x, so alongside it pip installs backend 2.0.0, which has no keyword search.

Quick start

from sigma.collection import SigmaCollection
from sigma.backends.sqlite import sqliteBackend

rules = SigmaCollection.from_yaml(r"""
title: Whoami execution
status: test
logsource:
    category: process_creation
    product: windows
detection:
    selection:
        Image|endswith: '\whoami.exe'
    condition: selection
""")

print(sqliteBackend().convert(rules)[0])
# SELECT * FROM logs WHERE Image LIKE '%\\whoami.exe' ESCAPE '\'

Processing pipelines map a logsource to concrete fields. With sqliteBackend(sysmon_pipeline() + windows_logsource_pipeline()) the same rule becomes:

SELECT * FROM logs WHERE Channel='Microsoft-Windows-Sysmon/Operational' AND (EventID=1 AND Image LIKE '%\\whoami.exe' ESCAPE '\')

The sysmon and windows pipelines still declare pysigma<2; install them with pip install --no-deps pysigma-pipeline-sysmon pysigma-pipeline-windows. With sigma-cli:

sigma convert -t sqlite -f zircolite -p sysmon -p windows-logsources rules/

Output formats

Format	Output
default	One SELECT * FROM logs WHERE ... statement per rule
zircolite	A JSON list with one entry per rule: Sigma metadata, the SQL, and what a consumer needs to run it. See output format

Options

Pass options as constructor keywords, or from sigma-cli with -O name=value. Set them at construction; changing attributes afterwards is unsupported.

Option	Default	Purpose
table	logs	Table to query. A pipeline setState on table takes precedence
collate_nocase	false	Emit field='value' COLLATE NOCASE for tables without NOCASE columns
max_flat_operands	100	Longer AND/OR chains are nested to stay under SQLite's expression depth limit
fulltext_field	none	Column of searchable event text; enables keyword search
timestamp_field	timestamp	Event time column used by correlations
timestamp_format	iso	iso, unix, unix_ms or unix_us
timestamp_seconds_expression	ISO parser	SQL turning {field} into Unix seconds; overrides timestamp_format
event_id_field	rowid	Non-NULL, per-table unique event identifier for correlation evidence
observation_end	latest event	Unix seconds up to which absence windows can expire

Generated SQL

Modifier	SQL
contains, startswith, endswith	LIKE '%value%' ESCAPE '\' and its prefix/suffix forms
all	AND of the values
re	REGEXP (needs a registered function, see Limitations)
cidr	= or LIKE prefixes of the expanded network
cased	GLOB; literal *, ?, [ become [*], [?], [[]
fieldref	field1=field2, or LIKE with the second field's % and _ escaped
exists	IS NOT NULL / IS NULL
gt, gte, lt, lte, neq	>, >=, <, <=, negated equality
minute, hour, day, week, month, year	strftime() part
base64, base64offset, wide, utf16*, windash	Expanded by pySigma into ordinary matches

The SQL keeps Sigma's meaning without help from the caller:

• Negation. Sigma reads a condition on a missing field as false; SQLite reads it as NULL, and NOT NULL drops the row. Every NOT therefore wraps its operand: EventID=1 AND (NOT COALESCE((CommandLine LIKE '%foo%' ESCAPE '\'), 0)).
• Case. LIKE ignores case; = does only on NOCASE columns (Zircolite's layout), otherwise set collate_nocase.
• Booleans. Flag: true matches text or integer storage: (Flag='true' OR Flag=1).
• Field names. SQLite keywords and names with characters other than letters, digits and _ are quoted with backticks. field: null becomes field IS NULL.

Correlations

All Sigma correlation types run in SQLite, including chains, aliases and extended temporal conditions. See correlations for their semantics and the migration guide for upgrading from backend 1.x.

Keyword search

Keyword (fieldless) rules need fulltext_field, a column holding each event's searchable text. See keyword search.

Limitations

• REGEXP. SQLite has no built-in REGEXP. Register one that is false for NULL: connection.create_function("regexp", 2, lambda p, v: v is not None and re.search(p, str(v)) is not None). pySigma validates |re with RE2 by default, rejecting lookarounds and backreferences. For trusted rules, pass policy=TrustedPolicy (from sigma.policy.profiles) to SigmaCollection.from_yaml(); the patterns must then suit Python's re.
• CIDR. SQLite has no network type. Prefix patterns match IPv6 addresses only in the spellings pySigma expands to.
• Case folding (NOCASE, LIKE) is ASCII only.
• Timestamp parts use strftime() and need ISO-8601 text; a numeric epoch is read as a Julian day. %W can return week 0.
• Field existence. SQL NULL cannot tell a missing field from an explicit null.
• Multi-field value_count (a list in condition.field) is rejected.
• Unknown columns. A query naming a column the table lacks fails to prepare. Add missing columns as NULL first; see output format.

Development

poetry install
poetry run pytest
SQLITE_MIN_BINARY=/path/to/sqlite-3.38 poetry run pytest tests/test_sqlite_minimum.py

tools/audit_corpus.py converts and prepares a SigmaHQ checkout per profile (see coverage). tools/benchmark_correlations.py times sparse and dense correlation windows.