Filtering out log patterns in ClickStack

Teams moving from Kibana often work like this: open Discover → Patterns, then click Filter out pattern on the 10–30 noise patterns they already know, until only the unusual logs are left. ClickStack has Event Patterns but no such button. This lab shows how far you get today, and where a Lucene exclusion quietly removes logs you still needed.

Verified on ClickStack all-in-one 2.39.1 (HyperDX 2.39.1, ClickHouse 26.8.7.19), local _base stack, 2026-11-02: bin/check.sh 11/11. HyperDX behaviour below is read from the @hyperdx/app@2.39.1 source; the UI itself was not driven in this run, and every claim is checked with SQL.

UI check, 2026-11-04 (same ClickStack all-in-one 2.39.1 image, ClickHouse 26.8.7.19, after bin/check.sh 11/11, headless Chromium): Event Patterns on the lab rows showed the nine templates with ~ counts and no include or exclude action; the five stacked Lucene phrases gave 10,000 results with the … order batch … pattern gone; the five NOT match(Body, patternToRegex(…)) conditions in SQL mode gave 20,000 results with the three patterns to keep and the signal left.

What HyperDX 2.39.1 does under Event Patterns

Read from packages/app/src/hooks/usePatterns.tsx and components/PatternTable.tsx:

• It takes 10,000 rows of the current search (ORDER BY rand() LIMIT 10000) and mines them in the browser with drain3 0.9.11 (Pyodide), default config plus every ASCII punctuation character as a delimiter.
• A variable token shows as <*>. The template is rebuilt over the last row's original text, so separators like [", = and . stay as they were.
• Counts are the sample count scaled to the total, hence ~72. A pattern rarer than about one row in 10,000 may not show at all.
• The pattern table has no include or exclude action.

bin/mine_patterns.py repeats those steps outside the UI, so the lab can check them with SQL.

The data

sql/01-seed.sql writes 200,000 rows into ClickStack's own default.otel_logs (tagged ResourceAttributes['lab'], spread over the last 10 minutes), nine patterns in fixed shares:

	Pattern (as Event Patterns shows it)	Rows
noise N1	payment capture skipped for order["<*>"] token="<*>.<*>"	40,000
noise N2	cache miss for key user:<*>	40,000
noise N3	GET /api/cart/<*> completed in <*> ms	50,000
noise N4	health check ok for upstream 10.0.<*>.<*> in <*> ms	30,000
noise N5	retrying publish to topic orders-<*> attempt <*>	20,000
keep K1	payment capture skipped for order batch <*> after <*> retries — same opening words as N1	10,000
keep K2	payment capture failed for order["<*>"] code=<*>	8,000
keep K3	cache miss for key config:<*>.<*> fallback to defaults — same opening words as N2	1,800
signal S1	inventory reservation timed out for sku <*> after <*> ms	200

Removing the five noise patterns should leave exactly 20,000 rows: K1–K3 and S1.

Run it

cd _base && docker compose up -d && cd ..
cd labs/log-pattern-exclusion
bin/check.sh              # seed, mine, run every scenario; leaves the rows for the UI
bin/check.sh --cleanup    # same, then removes the lab rows, lab_patterns and patternToRegex

Needs curl, python3 and uv (for drain3==0.9.11). With CH_URL unset the scripts use the published local defaults in _base/.env.example. To look at the same rows in HyperDX: open http://localhost:8080, the Logs source, Last 15 minutes, then Event Patterns.

Step 1 — Lucene exclusions and the SQL they become

- goes directly in front of the word or phrase, with no space; a lone - is a syntax error. Terms separated by spaces are ANDed. For the implicit field (Body), with ClickStack's default INDEX idx_lower_body lower(Body) TYPE text(tokenizer = 'splitByNonAlpha'), the 2.39.1 parser emits (sql/03-lucene.sql runs each one):

Lucene	Excludes	SQL (2.39.1)	Rows left
-payment	Body has the word	NOT hasAllTokens(lower(Body), lower('payment'))	142,000
-"payment capture skipped for order"	Body contains the phrase	NOT (hasAllTokens(lower(Body), lower('…')) AND lower(Body) LIKE lower('%…%'))	150,000
-payment*	Body starts with	lower(Body) NOT LIKE lower('payment%')	142,000
-*skipped*	Body contains	lower(Body) NOT LIKE lower('%skipped%')	150,000

A field term without quotes, -ServiceName:pay, is a substring test (ServiceName NOT ILIKE '%pay%'); with quotes, -ServiceName:"payment", it is an exact value. * only works on an unquoted single word. There is no regex in the Lucene bar.

Step 2 — Why stacked phrases are not enough

The best you can do in Lucene is one fixed phrase per noise pattern:

-"payment capture skipped for order" -"cache miss for key user" -"completed in"
-"health check ok for upstream" -"retrying publish to topic"

That leaves 10,000 rows, not 20,000. The phrase for N1 also matches K1 (payment capture skipped for order batch …), so a pattern you wanted to keep disappears with the noise and nothing tells you (checks C5, C6). With real logs, where the same words run through many messages, this is the normal case, not an edge case.

Step 3 — One regex per pattern

A template is precise enough to become a regex: <*> stands for one [A-Za-z0-9]+ run and everything else is literal. sql/02-functions.sql defines that conversion once:

SELECT patternToRegex('payment capture skipped for order["<*>"] token="<*>.<*>"');
-- ^payment\s+capture\s+skipped\s+for\s+order\["[A-Za-z0-9]+"\]\s+token="[A-Za-z0-9]+\.[A-Za-z0-9]+"$

Anchored at both ends, it matches N1 and not K1. Five of them stacked (sql/04-regex-exclusion.sql) leave exactly 20,000 rows (C7). In HyperDX, switch the search bar from Lucene to SQL and paste the condition, e.g. NOT match(Body, patternToRegex('cache miss for key user:<*>')).

Step 4 — A stored noise list

Typing 10–30 conditions per incident is the actual pain. Keep the templates in a table instead and test them all in one multiMatchAny (Hyperscan) call:

NOT multiMatchAny(Body, (SELECT groupArray(regex) FROM lab_patterns.noise))

Same 20,000 rows (C8). With a second list of patterns that are expected but worth keeping, sql/05-new-patterns.sql counts per five minutes what is in neither list — here only the 200 signal rows (C10) — which is the query an alert on "a new pattern appeared" would run.

Cost

bin/cost.sh, 2,000,000 lab rows (10 minutes of data), local all-in-one 2.39.1 on Docker Desktop (12 CPUs, 16 GiB), 2026-11-02, count() over the whole range, best of 5 after one warm-up:

Exclusions	Lucene phrases	NOT match() each	one multiMatchAny	Rows left (correct: 200,000)
none	17 ms
5	40 ms	36 ms	47 ms	Lucene 100,000 · regex 200,000
30 (25 match nothing)	71 ms	62 ms	54 ms	Lucene 100,000 · regex 200,000

At this size the cost does not decide anything; the row count does. Exclusions cannot skip granules, so time grows with the rows in the chosen time range. Not measured on ClickHouse Cloud.

What the checks cover

Check	What
C0	200,000 rows seeded
C1–C2	the miner finds 9 patterns, and the noise templates in 02-functions.sql are exactly what it shows
C3	patternToRegex() in SQL equals template_to_regex() in Python for every mined template — breaking the escaping makes it fail (tried 2026-11-02: 4 of 9 differ)
C4	every row matches exactly one template regex
C5–C6	stacked Lucene phrases leave 10,000 rows; the 10,000 lost rows are K1
C7–C9	stacked NOT match() and multiMatchAny both leave 20,000 rows: K1–K3 and S1
C10	rows in neither list = the 200 signal rows

Limits

• No one-click exclude. Templates are copied from Event Patterns by hand; this lab only makes the condition exact and reusable.
• ASCII templates only were checked. reconstructTemplate treats anything outside [A-Za-z0-9] as a separator, so a template over Korean or other non-ASCII text may not line up with drain's tokens. Not tested.
• A template is as wide as the sample. 10.0.<*>.<*> came from three upstream hosts; a host in 10.1.x.x would not match and would surface as new.
• multiMatchAny needs Hyperscan (allow_hyperscan, on by default); match() does not.
• Cleanup uses a lightweight DELETE that does not wait (sql/99-cleanup.sql says why).

Files

Path	What
sql/01-seed.sql	the nine patterns, {rows:UInt64}
sql/02-functions.sql	patternToRegex(), lab_patterns.noise, lab_patterns.known
sql/03-lucene.sql	Lucene exclusions as 2.39.1 SQL
sql/04-regex-exclusion.sql	per-pattern regex and the stored list
sql/05-new-patterns.sql	rows in neither list, per five minutes
sql/99-cleanup.sql	removes the lab rows and objects
bin/mine_patterns.py	Event Patterns outside the UI, with the regex for each template
bin/check.sh · bin/cost.sh · bin/sql.sh	checks, cost, SQL runner

cd _base && docker compose up -d && cd ..
cd labs/log-pattern-exclusion
bin/check.sh              # 넣기, 패턴 찾기, 모든 시나리오 실행. UI에서 보도록 행은 남김
bin/check.sh --cleanup    # 같은 실행 뒤 실습 행, lab_patterns, patternToRegex를 지움

-"payment capture skipped for order" -"cache miss for key user" -"completed in"
-"health check ok for upstream" -"retrying publish to topic"

SELECT patternToRegex('payment capture skipped for order["<*>"] token="<*>.<*>"');
-- ^payment\s+capture\s+skipped\s+for\s+order\["[A-Za-z0-9]+"\]\s+token="[A-Za-z0-9]+\.[A-Za-z0-9]+"$

NOT multiMatchAny(Body, (SELECT groupArray(regex) FROM lab_patterns.noise))