pySigma SQLite Backend

This is the SQLite backend for pySigma. It provides the package sigma.backends.sqlite with the sqliteBackend class.

This backend also aims to be compatible with Zircolite which uses pure SQLite queries to perform SIGMA-based detection on EVTX, Auditd, Sysmon for linux, XML or JSONL/NDJSON Logs.

It supports the following output formats:

• default: plain SQLite queries
• zircolite : SQLite queries in JSON format for Zircolite

This backend is currently maintained by:

• wagga

Requirements

• Python 3.10 or later
• pySigma >= 1.0.2, < 2.0 (tested with 1.4.0)

Supported Features

Sigma Modifiers

Note on cased: SQLite's GLOB is a native 2-argument operator with no ESCAPE clause, so queries are emitted as field GLOB 'pattern' and backslashes are kept literal (e.g. Windows paths match as-is). Because GLOB has no escape mechanism, a literal *, ? or [ inside a cased value is still interpreted as a glob metacharacter; escaping such literals ([*]) is not currently supported.

Correlation Rules

The backend supports Sigma correlation rules with the following types:

The following correlation types are not supported: temporal_extended, temporal_ordered_extended.

Correlation rules support group-by for grouping results and timespan for temporal constraints.

SQLite Requirements for Correlation

For correlation rules to work properly, your SQLite database must meet the following requirements:

Configurable Parameters:

The backend provides configurable parameters for correlation queries:

Example usage with custom parameters:

backend = sqliteBackend(correlation_methods=["default"])
backend.timestamp_field = "event_time"

Notes:

• The timestamp field is used with julianday() for time difference calculations in temporal correlations
• For multi-rule correlations (temporal, temporal_ordered), the backend automatically adds a sigma_rule_id column to identify which rule matched each event
• Timespan values are converted to seconds internally for comparison

Other Features

• NULL value handling: field: null → field IS NULL
• Boolean values: true/false support
• Field name quoting: Special characters in field names are quoted with backticks
• Wildcard escaping: Proper escaping of % and _ characters in values

Known issues/limitations

• Full text search support will need some work and is not a priority since it needs virtual tables on SQLite side

Quick Start

Example script (default output) with sysmon pipeline

Add pipelines

poetry add pysigma-pipeline-sysmon
poetry add pysigma-pipeline-windows

Convert a rule

from sigma.collection import SigmaCollection
from sigma.backends.sqlite import sqliteBackend
from sigma.pipelines.sysmon import sysmon_pipeline
from sigma.pipelines.windows import windows_logsource_pipeline

# Combine pipelines to map both Channel and EventID:
# 1. sysmon_pipeline: maps category (e.g., process_creation) -> EventID (e.g., 1)
#                     and changes logsource to service=sysmon
# 2. windows_logsource_pipeline: maps service=sysmon -> Channel
#
# For process_creation/windows, this produces:
#   Channel='Microsoft-Windows-Sysmon/Operational' AND EventID=1
combined_pipeline = sysmon_pipeline() + windows_logsource_pipeline()
sqlite_backend = sqliteBackend(combined_pipeline)
# Set the table name for the generated SQL queries
sqlite_backend.table = "logs"


rule = SigmaCollection.from_yaml(
r"""
    title: Test
    status: test
    logsource:
        category: test_category
        product: test_product
    detection:
        sel:
            fieldA: valueA
            fieldB: valueB
        condition: sel
""")

print(sqlite_backend.convert(rule)[0])

Running

poetry run python3 example.py