Enrichment¶
match()¶
Enrich log events with data from a dictionary. Each matching log row gets additional columns from the dictionary lookup.
* | match(dict="threat_intel", field=src_ip, column=ip, include=[threat_score,category])
Parameters¶
| Parameter | Required | Description |
|---|---|---|
dict |
Yes | Name of the context list (created in the Context tab) |
field |
Yes | Log field to use as the lookup key |
column |
Yes | Dictionary column to match against |
include |
Yes | Dictionary columns to add to results: include=[col1,col2] |
require |
No | When true, only return rows that have a match in the dictionary. Default: false |
By default non-matching rows are kept, with empty strings for the included columns. With require=true, non-matching rows are filtered out entirely.
Examples¶
Enrich logs with threat intelligence data:
* | match(dict="threat_intel", field=src_ip, column=ip, include=[threat_score,category])
Only keep logs that match the dictionary:
* | match(dict="threat_intel", field=src_ip, column=ip, include=[threat_score,category], require=true)
Combine with other pipeline stages:
* | match(dict="asset_inventory", field=hostname, column=name, include=[owner,department])
| groupBy(department, function=count())
List kinds¶
A context list is one of three kinds, chosen when it is created. match() is written the same
way for all of them: the list's kind decides how the key is compared.
| Kind | Keys hold | A lookup matches |
|---|---|---|
| Values | names, hashes, indicators | the key, byte for byte |
| Networks | CIDR ranges | an address to the narrowest range holding it |
| Patterns | regular expressions | the first expression matching the value |
Networks¶
A Networks list is looked up with an address rather than a string, so 10.1.2.3 finds a row
keyed 10.1.0.0/16 in preference to one keyed 10.0.0.0/8:
* | match(dict="corp_ranges", field=src_ip, column=network, include=[site,owner])
IPv4 and IPv6 ranges live in the same list. A field carrying something that is not an address misses rather than failing the query, and a row whose key is not a CIDR range is refused when it is saved.
Asking for the key column reports membership: it returns the looked-up value on a hit and an
empty string on a miss, so require=true keeps only the rows inside one of the ranges.
* | match(dict="corp_ranges", field=src_ip, column=network, include=[network], require=true)
Patterns¶
A Patterns list holds regular expressions, matched against the value rather than compared to it:
* | match(dict="tooling", field=commandline, column=pattern, include=[tool,risk])
Expressions are tried in the order the rows are listed and the first one that matches answers,
so put the specific expressions above the general ones. A list holding mimikatz above
\.exe$ reports mimikatz.exe as Mimikatz; reverse the two and it reports it as an
executable.
Matching is case-sensitive. Write (?i) at the front of an expression that should ignore case:
(?i)powershell matches POWERSHELL.EXE.
Asking for the key column reports membership here too, and does so correctly even when the matched row's other columns are empty.
* | match(dict="tooling", field=commandline, column=pattern, include=[pattern], require=true)
An expression that does not compile is refused when the row is saved, because a list holding one fails to load and takes every lookup against it with it.
Ignoring case¶
Ignoring case is a Values list setting, and turning it on for another kind is refused. A CIDR
range has no casing, and a Patterns list says it per expression with (?i); lowering the value
a pattern is matched against would stop every expression carrying an upper-case letter from
matching at all.
modelLookup()¶
Enrich rows with the baseline an analytics model has built, so a query can score each event against learned history.
* | modelLookup(model="rare_parent_child", key=[parent_image, image])
Parameters¶
| Parameter | Required | Description |
|---|---|---|
model |
Yes | Name of an active model in this fractal |
key |
Yes | Log fields matched against the model's key, in order |
require |
No | true (default) returns only rows the model scored; false keeps the rest with empty enrichment |
The key shape depends on the model type:
| Model type | key= |
|---|---|
rarity |
[partition_key, value_key] |
first_seen |
[entity] |
volume_baseline |
[entity] |
beacon, long_connection |
[src_ip, dst_ip, dst_port] |
Enrichment columns can be filtered and aggregated like any other field.
Position in the pipeline¶
Placement relative to an aggregation (groupby, stats functions, chain()) decides what gets enriched:
modelLookup()before the aggregation enriches rows first. Model columns can then be group keys, aggregation inputs, row filters ahead of the aggregation, and step conditions insidechain().modelLookup()after the aggregation enriches the aggregated results instead, so the key fields must be among the group columns.
Count events by whether the model has seen the user before:
* | modelLookup(model="known_users", key=[user]) | groupby(is_new)
Sequence a first-ever-seen user straight into process execution:
* | modelLookup(model="known_users", key=[user]) | chain(user, within=10m) {
is_new="1";
bifract_category="process_creation"
}
require¶
By default a row the model never scored is dropped. The model's key set is pushed into the log scan, so the query reads only the logs that can match rather than every log in the range and discarding the rest after the join.
require=false keeps unscored rows with their enrichment columns at ClickHouse's type defaults (0 for a score, empty for a date). Those defaults compare like real values, so a threshold that looks for something small matches every unscored row:
* | modelLookup(model="rare_images", key=[computer_name, image], require=false) | percent < 0.1
That query returns every log the model has no entry for. Use require=false only to see which rows went unscored, and test the enrichment column for emptiness rather than thresholding it.
A model scores forward from its creation (plus whatever its backfill covered), so under the default a query over a window older than the model returns nothing.
comment()¶
Filter logs to only those that have comments. Optionally narrow by tag labels or keyword search in comment text.
* | comment()
Parameters¶
| Parameter | Required | Description |
|---|---|---|
tags |
No | One or more tag labels (OR logic, case sensitive): tags=[a,b] |
keyword |
No | Search term matched against comment text (case insensitive) |
With no arguments, returns all logs that have at least one comment.
Tag Filtering¶
* | comment(tags=[security])
* | comment(tags=[security,critical])
Multiple tags use OR logic. This matches logs with comments tagged security OR critical.
Brackets match every other list parameter in BQL (include=[...], key=[...], values=[...]). The unbracketed tags=security,critical is still accepted so existing saved queries keep working.
Keyword Filtering¶
* | comment(keyword="timeout")
Matches logs with comments containing "timeout" (case insensitive).
Combined¶
* | comment(keyword="error", tags=[security])
Keyword AND at least one matching tag.
Pipeline Usage¶
comment() can be combined with other pipeline commands:
* | comment(tags=[incident]) | groupby(src_ip, function=count())
* | comment() | table(timestamp, norm_log, src_ip)
tlsh()¶
Filter logs to those whose fuzzy-hash digest is similar to one or more known digests. TLSH survives recompilation and repacking, so it matches variants of a file that a SHA-256 comparison would miss.
event_id=1 | tlsh(field=tlsh, dict="known_bad", threshold=30)
Parameters¶
| Parameter | Required | Description |
|---|---|---|
field |
Yes | Log field holding the TLSH digest. Must be a stored field, not one produced by an earlier command. |
hash |
One of | Literal digest to compare against: hash=[d1,d2] for several. |
dict |
One of | Dictionary whose key column holds the digests to compare against. |
threshold |
No | Maximum TLSH distance. Defaults to 30. |
Supply either hash or dict, not both.
Distance runs from 0 (identical) upward. The convention is <=30 for similar and <=50 for loosely similar; the maximum accepted is 200.
Digests are accepted as 70 hex characters, with or without the T1 version prefix, in either case. Producers differ: the Go implementation behind Velociraptor's tlsh_hash() writes bare lowercase, while MalwareBazaar publishes the uppercase prefixed form.
Requires a TLSH index¶
tlsh() reads a tlsh analytics model on the same field, which indexes the distinct digests present in the fractal. With nothing in scope indexed, the query fails and names the model to create, because an empty result would read as "no matches" rather than "not searched".
The index is what makes this affordable. Digests repeat heavily across rows, so the comparison runs over the number of distinct digests rather than the number of rows, which is why the filter works over billions of logs. The model's view admits only well-formed digests, so the empty and truncated values that producers emit for inputs under 50 bytes never enter the index. That matters: two empty digests compare at distance 0, which would otherwise match everything.
Create the model under Analytics Models, then run a backfill to cover existing history.
In a prism, each member fractal is read from its own index. A member with no index is skipped, not scanned: the query still runs over the indexed members and returns a partial coverage notice naming what was left out. So a fractal that holds no digests for that field needs no model, and one member missing an index never blocks the whole prism.
Added columns¶
| Column | Description |
|---|---|
tlsh_distance |
Distance to the closest matching needle |
tlsh_match |
The needle that matched: the dictionary key, or the literal digest |
event_id=1 | tlsh(field=tlsh, dict="known_bad") | sort(tlsh_distance)
Pulling other dictionary columns onto a hit¶
A needle dictionary can carry more than the digest: a family name, a source, a reference. Chain match() keyed on tlsh_match to bring those columns onto the matching rows.
event_id=1
| tlsh(field=tlsh, dict="known_bad")
| match(dict="known_bad", field=tlsh_match, column=digest, include=[name])
| table(timestamp, image, tlsh_distance, name)
column= names your dictionary's own key column (digest above); substitute whatever yours is.
Key on tlsh_match, never on the log field itself. The log row's digest is only similar to the needle, not equal to it, so a lookup keyed on the log field would search for a key the dictionary does not contain and enrich nothing. tlsh_match holds the needle that actually matched, which is a real dictionary key.
Position in the pipeline¶
tlsh() filters log rows, so it must come before groupby() or any aggregation. Placing it after one is an error rather than a silent re-interpretation as a filter on the pre-aggregation rows.
event_id=1 | tlsh(field=tlsh, dict="known_bad") | groupby(computer_name, function=count())
Cost¶
Work scales with distinct digests multiplied by needle count. Aim to keep that product under roughly 10^9 for interactive hunting and 10^8 for alerting, which means curated, family-representative digest lists rather than corpus dumps.
Three limits are enforced, each with an error naming the numbers involved:
| Limit | Value |
|---|---|
| Distinct digests in the index | 500,000 |
Digests in a dict |
50,000 |
| Distinct digests x needles | 2 x 10^9 |
tlsh() works in alerts as well as in search. The index probe runs on each evaluation, which is cheap because it reads the model rather than the log table.