Search and SQL Hunt

FreeDR lets you query every retained event on the host, hot (recent, in DuckDB) and cold (archived Parquet files), as if it were one table. There are two front ends over the same engine:

Page	What you type	Endpoint
Search → Event search	A WHERE expression only (for example event_type = 'process.start') plus a time range	POST /api/v1/hunt/query, POST /api/v1/events/histogram
Search → SQL Hunt	A full read-only SELECT statement	POST /api/v1/hunt/validate, POST /api/v1/hunt/query, POST /api/v1/hunt/export

Both are local-only and require a signed-in console session (see Security).

Query language

The Hunt language is DuckDB SQL, version 2 (ADR-0050). Requests carry "version": 2; other versions are rejected with unsupported Hunt SQL version.

Hunt SQL v1 is retired

The original narrow "Hunt SQL v1" grammar (ADR-0005) is no longer the query language. Old saved searches and Hunt queries without sql_version: 2 stay on disk but are not listed or run.

What this means in practice:

• You write one ordinary DuckDB SELECT (CTEs, joins, subqueries, GROUP BY, HAVING, ORDER BY, window functions and DuckDB functions such as lower, ends_with, regexp_matches, coalesce, to_json are accepted). The schema page lists example functions; the list is not an allowlist.
• LIKE is case-sensitive in DuckDB. Use ILIKE (or lower(...)) for case-insensitive matching.
• Your text is wrapped as a subquery: WITH events AS (...) SELECT * FROM (<your SQL>) LIMIT <max+1>. Anything that cannot be a subquery (multiple statements, PRAGMA, ATTACH, COPY, INSERT/UPDATE/DELETE, DDL) fails.
• Errors come from DuckDB itself.

Relations you can query

Relation	Contents
events	Every retained event from every source, one row per event, 56 typed columns (below). Columns that do not apply to a source are NULL.
event_log	Windows Event Log, Sysmon and Task Scheduler rows only: all events columns for that source plus every named field of the Event Log slot map (about 230 columns, for example threat_name, detection_guid, service_name, image_path, account, logon_type, rule_name, action).

event_log is only built when your SQL names it outside a string literal, so it costs nothing otherwise. Physical storage tables and the typed detection sources (kernel_process_etw, registry_etw, ...) are not Hunt relations: queries naming them fail validation.

SELECT * FROM event_log fails

It exceeds the 64-column result limit. Name the columns you want.

events columns

Times (occurred_at, written_at, *_start_at) are integers: microseconds since the Unix epoch (UTC). event_id is a UUID string. Types shown are as served by GET /api/v1/hunt/schema.

Group	Columns
Common	event_id (text), event_type (text), occurred_at (integer), written_at (integer)
Process (process.start, process.exit, image.load)	is_rundown (boolean), pid, parent_pid, image_base, exit_code, parent_start_at (integer), session_id (integer), image_path, command_line, user_sid, user_name, parent_image_path, parent_command_line, grandparent_image_path (text)
Acting process (file, registry, event log)	actor_pid, actor_start_at (integer), actor_image_path, actor_command_line, actor_user_sid, actor_user_name (text)
File ETW (file.create_open, file.write, file.read, file.rename, file.delete, file.set_info)	created, byte_count, byte_offset, volume_serial (integer), path, old_path, file_id (text)
Registry (registry.key_created, registry.key_delete, registry.value_set, registry.value_delete, registry.key_security_set)	key_path, value_name, value_data (text), value_type (integer)
Windows Event Log (event_log.*)	task_name, task_content, task_action, task_result_code, script_block_text, event_payload (JSON text of the retained list entries, including Sysmon name/value pairs)
USN journal (usn.change)	journal_id, usn, reason (plus volume_serial, file_id, path)
Artifact / file inventory (artifact.content_version)	file_size, created_at, modified_at, sha256, authenticode_status, signer, ads, pe_image_size, pe_checksum, pe_timestamp, pe_preferred_base, acquisition_reason

The SQL Hunt page shows each column with its type, description, source and an example, and has an Insert button per field.

Limits and guardrails

Limit	Value	Notes
Query text	16 KiB	hunt query exceeds 16384 bytes
Result columns	1 to 64, unique names (case-insensitive)
Result types	boolean, integer, floating point, text	Other DuckDB types (lists, structs, timestamps) need CAST(... AS VARCHAR) or to_json(...)
Interactive rows (Run)	1,000
Interactive result size	16 MiB of cell data
Export rows (CSV / NDJSON)	100,000
Export result size	64 MiB of cell data, 64 MiB file
Concurrent evidence reads	2 (shared with all console reads)	Further requests wait
Concurrent exports	1	A second export gets 503 export busy
Search filter text	12,000 characters	Search page only

Row limits fail, they do not truncate

If a query would return more rows than its limit (1,000 for Run, 100,000 for export), it fails with hunt result exceeds output-byte limit instead of returning a partial result. Always end interactive queries with LIMIT 1000 or less, or aggregate.

No time-based query limit applies to console Hunt and Search reads (ADR-0048): a slow query keeps running until it finishes or you cancel it (Cancel, or navigating away, interrupts DuckDB). The former input-event scan budget (ADR-0029) and grouped-query pre-count (ADR-0030) were removed: each Hunt request runs as exactly one DuckDB query.

Other guardrails on the evidence reader:

• The DuckDB connection is opened read-only.
• External access, extension auto-install and auto-load are disabled; file access is limited to FreeDR's own evidence and temp directories. read_csv, httpfs and similar cannot reach other files or the network.
• Cold files that are missing, corrupt or fail integrity checks produce an explicit error, not an empty result.
• Validate binds the query with DuckDB against an empty, correctly typed events (and event_log) relation and returns {"valid": true} or {"valid": false, "error": "..."}. It never opens or scans evidence. Run validates first automatically.
• CSV export prefixes cells that begin with =, +, - or @ with ' to block spreadsheet formula injection.

Keep queries bounded

Broad queries over all retained evidence can be slow and memory-hungry. Practical habits:

• Add a time bound with integer literals. For a simple read of one relation (events or event_log, no CTE, join or subquery), occurred_at comparisons with literal numbers in the WHERE clause select which cold files are read and filter every tier. Expressions such as epoch_us(now()) - ... still work but read all files.
• Pin event_id with event_id = '<uuid>' or event_id IN (...) at the top level of the WHERE when you know it; this is also pushed into every tier.
• Always use LIMIT, and prefer count(*)/GROUP BY summaries over row dumps.
• Use the Search page's time range (default Last 24 hours) rather than All time when you can.

Example queries

Recent encoded PowerShell command lines:

SELECT occurred_at, pid, image_path, command_line
FROM events
WHERE event_type = 'process.start'
  AND image_path ILIKE '%\powershell.exe'
  AND command_line ILIKE '%-enc%'
ORDER BY occurred_at DESC
LIMIT 100

Event volume by type for one UTC day (time literals enable cold-file pruning):

SELECT event_type, count(*) AS events
FROM events
WHERE occurred_at >= 1791936000000000   -- 2026-10-14 00:00 UTC
  AND occurred_at <  1792022400000000   -- 2026-10-15 00:00 UTC
GROUP BY event_type
ORDER BY events DESC
LIMIT 100

Run-key writes and the process that made them:

SELECT occurred_at, key_path, value_name, value_data, actor_image_path
FROM events
WHERE event_type = 'registry.value_set'
  AND key_path ILIKE '%\CurrentVersion\Run%'
ORDER BY occurred_at DESC
LIMIT 200

Defender detections by name (named Event Log fields):

SELECT occurred_at, event_type, threat_name, image_path, detection_guid
FROM event_log
WHERE event_type LIKE 'event_log.defender.%'
  AND threat_name IS NOT NULL
ORDER BY occurred_at DESC
LIMIT 50

Newly installed services:

SELECT occurred_at, service_name, image_path, account
FROM event_log
WHERE event_type = 'event_log.service.installed'
ORDER BY occurred_at DESC
LIMIT 50

Readable timestamps (convert microseconds, then cast to text because timestamp results are not accepted):

SELECT CAST(make_timestamp(CAST(occurred_at AS BIGINT)) AS VARCHAR) AS occurred_utc,
       event_type, path
FROM events
WHERE event_type = 'file.rename'
LIMIT 20

Search page filters are only the WHERE part, for example event_type = 'process.start' AND image_path ILIKE '%powershell%'.

Search page

• Query mode takes a WHERE expression; Builder mode adds field/operator/value conditions joined with AND/OR into the same expression.
• Time ranges: last 15 minutes, hour, 24 hours (default), 7 days, custom, or all time.
• Field add/exclude actions edit the query text directly; there is no hidden filter state.
• Column picker, sort, field facets and statistics panels.
• Export (CSV) first counts matches; it refuses more than 100,000 matching events or more than 64 visible columns.

Histogram

The Search event-flow chart comes from one request, POST /api/v1/events/histogram (ADR-0047), with {version: 2, filter, start_us, end_us}. It returns exactly 12 equal-width bins of exact counts over hot and cold evidence, and no event data. The span may be at most 3,660 days; the filter is the same WHERE expression (at most 12,000 characters). It shares the two evidence read slots and is cancelled when superseded.

SQL Hunt page

• Editor with Validate, Run, Cancel, Export CSV and Export NDJSON (freedr-hunt.csv / freedr-hunt.ndjson).
• Field reference with search, types, examples and insert buttons.
• Describe what to find asks the configured AI provider to draft a query (POST /api/v1/hunt/ai-draft). Requires AI to be enabled in Settings; the description is sent to that provider. The draft is placed in the editor, not run.
• Pivot evidence to Hunt (from an event) prefills a query for that event.
• A Hunt query can be used as the starting point for a custom alert rule. Rules use their own typed-source SQL rules, described on the detection pages.

Results show status (complete), the columns with types, and the rows. counters.accepted_events is not reported for direct SQL.

Saved queries

Saved searches and Hunt queries live in FreeDR's local control database, not in the browser (ADR-0039), so they are shared across browser profiles on the host.

Property	Value
Kinds	search (a saved Search view) and hunt (a SQL Hunt query)
Identity	(kind, name); saving the same name again overwrites it
Capacity	50 entries in total (saved query library is full)
Name	1 to 80 bytes, no leading/trailing spaces
Data	JSON object, at most 16 KiB, must contain "sql_version": 2
Favorite	Per-entry flag, toggled from the library

API: GET /api/v1/queries[?kind=search|hunt], PUT /api/v1/queries (upsert), DELETE /api/v1/queries. Writes require the CSRF operation token. Saved queries are inert: opening one loads it into the editor; you run it explicitly. They are not rules and are never scheduled.

Not covered here

• POST /api/v1/hunt/live-mft/query is a separate, API-only exact live NTFS lookup (by exact path, file reference, or a batch of up to 64 keys). It is not SQL, does not read stored evidence and has no console page.