Searching logs with SonQL

The query language for Sondar log search, and what it actually does.

Where a command is documented here, it was run against a live instance — not carried over from a document, because the earlier documentation marked commands unsupported that in fact work.

---

The shape of a query

search2 index="app_logs" ERROR
| stats count() as errors by service
| sort by errors desc

A query is a source, then a pipeline of commands separated by |. Each command takes the previous result and passes on a new one.

sort takes a BY clause. sort by errors desc, not Splunk’s sort -errors. The grammar is SORT ((offset COMMA)? size)? BY sort_field_list, so a size limit goes before the by: sort 10 by errors desc.

A . does not break a token, by default. Searching NullPointerException finds nothing in an index full of java.lang.NullPointerException — the dotted identifier is one token. / and = do break, so orders finds path=/api/v1/orders. Splunk treats . as a minor breaker and the shipped default here does not, so it is a live migration trap.

It is configurable per index. The word-breaker list defaults to ,'";=()[]{}?@&<>/: — add . when you create the index and both NullPointerException and java.lang.NullPointerException match, with nothing lost. It applies at write time, so it affects new data only. For data already written, use a wildcard: *NullPointerException, java.lang.*, or | where _raw like "%NullPointerException%".

And it sorts descending by default — the opposite of Splunk, where sort defaults to ascending. | sort by duration_ms gives you the slowest first. Write asc when you want the other direction. Verified on both engines against fields with real variation; the handbook states the same (search1_command_manual: “is sorted by desc by default”). This one is silent when it surprises you — a migrated query returns the reverse order, not an error.

search2 is the log search command. index= selects which index to read.

index and repo. Internally an index is called a repo, and you will see repo= in older material and in some API payloads. In a search predicate the two are interchangeable — index= is aliased to repo=. The alias is search-predicate only: the field is still named repo, so | stats count() by index does not work while | stats count() by repo does.

Filtering

Bare terms are full-text matches against the raw event:

search2 index="app_logs" ERROR
search2 index="app_logs" ERROR "connection refused"

Field predicates compare a specific field:

search2 index="app_logs" status=500
search2 index="app_logs" status>=500 AND service="checkout-api"

AND, OR and NOT combine predicates. Wildcards use *.

AND binds tighter than OR — add parentheses. Two terms side by side are an implicit AND, and SonQL evaluates every AND before any OR, on both engines. So a b OR c means (a AND b) OR c, not a AND (b OR c): the terms before the OR do not apply to what comes after it. The one exception is index=, which picks what is searched and holds for the whole search wherever it is written (measured: (index="_internal" sourcetype="json" GET) OR POST returns only _internal events, though _sondar_log held 464 POST events that day).

you write	SonQL reads it as	events (27 Sep 2026, one day of _internal)
search2 index="_internal" sourcetype="json" GET OR POST	(json AND GET) OR POST	1,711
search2 index="_internal" sourcetype="json" (GET OR POST)	json AND (GET OR POST)	0

The first returns 1,711 events, every one of them sourcetype="_audit" — all the index’s POST events, whatever their sourcetype — and the second returns 0, because no json event contains either word. The parenthesised form is not simply empty: sourcetype="_audit" (GET OR POST) returns 12,255. search gives the same two answers. There is no error either way, so whenever a search mixes OR with anything else, put the OR in parentheses. (Those _audit events were measured in _internal; since the audit log moved to its own index, they are in repo="_audit".) Splunk reads the unparenthesised text the other way round — see Splunk → SonQL, trap 5.

Aggregation

stats is the workhorse:

| stats count() as n by service, status
| stats avg(duration_ms) as avg_ms, max(duration_ms) as peak by endpoint

Aggregation functions are validated: a name the engine does not know is an error, not a silent empty column —

| stats countt() as c
→ Error: no match function [countt()]

Limiting results

limit, not head. SonQL spells it limit:

| limit 20

This is worth knowing if you come from Splunk. head is not a SonQL command. It now errors on both engines — search2 answers unknown command [head] with did you mean 'limit'?, search1 answers do not support spl: head. Until August 2026 search2 silently ignored an unrecognized command, so | head 20 returned unfiltered results and the query looked like it worked (fixed; search1 never had it).

Commands

Split by how strongly each is established, because the distinction is the whole point of this page.

Run against the live engine and confirmed working. Earlier documentation marked all five of these unsupported:

command	what it does	engine notes
top	most frequent values of a field	search2 requires a count: top 5 service. search1 accepts bare top service. The percent column is “of total data volume” — all search-result events — on both engines since 14 Aug 2026. With a by clause the limit is per group and the percent is relative to that group’s events that have the field, on both engines (search2 refused top … by until its September 2026 builds; measured against search1 on a field present on only some events, the two agree row for row). A mixed-version cluster needs a full restart for top/rare (the result wire format changed in 14 Aug builds)
rare	least frequent values	same — rare 5 service on search2
dedup	drop duplicate rows	identical on both
transaction	group related events into one	works on both engines on live indexed data (15 Aug 2026); search2 carries the joined member fields — each event field becomes a multivalue column holding the union of the members’ values, so `transaction client_ip
chart	aggregate into a chart-shaped result	works on both (closed on search2 14 Aug 2026); the over <r> by <c> two-level form is stats by <r>,<c> on search2, one row per combination, not the columnar pivot search1 builds. chart options are rejected on search2 with a hard error — span= and limit= (and any other option, on the row or column split) are parsed but not honored there, so they fail loudly rather than silently changing the result

The two engines are not quite the same language. Of 22 commands run against both as of 14-15 Aug 2026, 15 agreed and 7 did not; the per-command table above is the maintained answer and outranks that count, which is a snapshot and will drift. Where a saved search, dashboard panel or alert does not begin with search or search2, the engine is chosen by the global Default Search Engine setting — so changing that setting can change what those objects return. Name the engine explicitly in anything you save.

Also exercised directly: stats, where, eval, rex, sort, limit, and the rex → where → stats → sort pipeline end to end. timechart and bin joined this list on 5 Sep 2026, measured on a live instance over a 22,572-event corpus: timechart span="1d" count() returned 31 buckets, and bin _time span="1d" | stats count() as n by _time returned three days totalling 22,572.

Quote the span, and put it before the aggregate. span="1d" parses; span=1d does not, on either command — unquoted, 1d is not a value the option grammar accepts, and the query fails outright rather than degrading. timechart additionally wants its options first: timechart span="1d" count(), not timechart count() span="1d". Splunk accepts the opposite order, so this is the one place in the migration table where a query has to be rewritten rather than copied.

Present in the grammar and wired into the engine, but not individually exercised here: eventstats, fields, join, movingavg, lookup, fit.

This list said bucket until 5 Sep 2026, and there is no such command. The lexer rule is BUCKET: 'bin' — the token is named BUCKET but matches only the literal bin, so bucket _time span="1d" fails to parse while bin works. Splunk treats the two as aliases, which is what makes the wrong one plausible. Checked all nine names in this paragraph against the lexer literals rather than the rule names; bucket was the only one that was not a keyword. Each builds a real plan node on search2 — checked 2 Sep 2026 across all 52 command alternatives in search2’s grammar, and enforced from then on by tools/check-spl-operators.mjs. That is a stronger statement than “these are real lexer tokens, so they parse”, which is what this paragraph used to say: parsing alone is compatible with the clause being dropped, which is exactly what accum, makemv and streamstats were doing. It is still weaker than a runtime check, so this page claims no behavior for them.

streamstats — search1 only. On search1 it requires a by clause (without one the engine answers “expected one of ‘by’”). On search2 it is refused with “streamstats is not supported on search2; use search1, or aggregate with stats on search2”. Until 2 Sep 2026 search2 parsed the clause and silently dropped it — streamstats count() by h returned the rows with no aggregate column and no error — so an earlier version of this note, which described only the missing-by message, read as though supplying by made it work. It did not. accum and makemv were in the same state and are refused for the same reason.

mvcombine — on search2, reduce the fields first. search2 … | fields mvhost, mvcode | mvcombine mvcode returns what you expect; search2 … | mvcombine mvcode, with no reduction, returns no rows at all. search1 is unaffected and needs no reduction. Measured on an indexed corpus 12 September 2026, both engines, with the expected answer written down before the data was generated — three hosts carrying 4, 2 and 1 distinct codes; the reduced form returns exactly that on either engine, the bare form returns 3 rows on search1 and 0 on search2.

Reducing first is what Splunk’s own guidance recommends anyway (mvcombine is “most useful after you reduce the set of available fields”), so the working form is also the idiomatic one. If you want a shape that behaves identically on both engines with no caveat, stats values(<field>) by <group> is it.

The reason is worth knowing, because it tells you when the bare form will bite. search2 groups by the searched repo’s columns, and a repo holds many sourcetypes — so the group key can name a column your documents do not have, and grouping by an absent column correctly yields nothing. A | fields clause narrows the key to what you asked for, which is why it works. On a repo holding exactly one sourcetype the bare form is fine; on a mixed repo it is not. Tracked as #428, with the remaining case open.

mvexpand — works on both engines (corrected 14 Aug 2026). On both engines it expands a multi-value field into one row per value. The earlier “returns the input unchanged on search2” observation was not reproducible against the current code — a regression test (SchemaTests. testMvexpandOnValuesOutput) proves stats values(f) as vs | mvexpand vs expands. mvexpand limit=<n> keeps only the first <n> values of each row and works on both engines; both spellings are accepted since 15 Aug 2026 — Splunk’s field-first mvexpand <field> limit=<n> and the options-first mvexpand limit=<n> <field>, on search1 and search2 alike.

lookup runs on both engines since September 2026. On search2 it is a left join against the lookup table: every event is kept, and the ones whose key matches gain the output fields. Two differences from search1: where the table has several rows for one key, search2 takes the first (search1 returns every match as a multivalue field). Where an output name already exists on the events, a matched event takes the table’s value and an unmatched one keeps its own. Measured on a real index: 81 of 1,222 events enriched on both engines, the other 1,141 kept with no value. (Before that month, search2 refused lookup at plan time.)

iplocation — set up IP geolocation once, in Settings. Sondar ships no GeoIP data. iplocation reads a MaxMind City database — the free GeoLite2 City, or a paid GeoIP2 City — under your own MaxMind account, so the data license is between you and MaxMind and Sondar redistributes nothing.

An administrator sets it up under Settings > Advanced Settings > IP Geolocation:

• Download from MaxMind (recommended). Create a free MaxMind account, generate a license key, and enter the Account ID and key. Sondar downloads GeoLite2 City at once and, with Keep it up to date on, checks MaxMind daily and downloads each new build (MaxMind publishes twice a week). The key is encrypted before it leaves the browser and is never shown again; it is sent only to MaxMind.
• Upload a database file, for installs that cannot reach MaxMind: download GeoLite2 City from your MaxMind account — the .tar.gz archive or the .mmdb file — and upload it.

The database is live as soon as the page says Active — no restart. In a cluster deployed with the Helm chart every node reads one shared copy. Operators who manage files themselves can instead set sondar.iplocation.database to a file’s path on every node (in config/sondar.yml, or as an environment variable of that name); that file then wins, and the page shows it read-only.

| iplocation clientip
| iplocation prefix="geo_" allfields=true lang="de" clientip

field	from the database	with
city, country, region, lat, lon	city, country, first subdivision (state, province), coordinates	always
metrocode, continent, timezone	US metro (DMA) code — empty outside the US — continent name, IANA time zone	allfields=true

lang names places in en (the default), de, es, fr, ja, pt-BR, ru or zh-CN, and must be quoted. A place with no name in that language falls back to English; any other value fails the search and lists these. An address the database does not know — a private range, say — gets empty fields.

Until it is set up, iplocation does not fail. The location fields come back empty, and on the default engine the search carries a warning saying where to set it up. A database that becomes unreadable behaves the same way, and the settings page and the node’s log say why.

Before October 2026 builds bundled a third-party database instead. Saved searches written for it keep working: its lang="CN" now means zh-CN, and the default changed to English. It also produced an isp field; City databases carry no ISP, so that field is no longer added.

That is the same shape mvcombine and chart were in before they were fixed: a command can parse cleanly and still be dead one layer down. Verified 7 Sep 2026 by reading the two visitor families in LogicalPlanner — the QueryContext overloads are the ones that plan; a Void overload is the interval visitor and does not mean the operator is supported.

Machine learning commands

Three detectors run inside the engine — there is no separate service to install and no model to train, save or load. Each reads a time series produced by the pipeline before it, and adds columns rather than replacing rows. They are search2 only.

There is no apply. An earlier design fitted a model in one query and applied it in another through a Python tier; that tier was removed, and these three fit on the data in front of them. fit is unrelated and unchanged — it runs the built-in log-analysis algorithms (LogReduce, ReservoirSampling, TokenAnalyzer).

ml anomalies — points that do not fit the pattern

… | ml anomalies risk=0.00005 persistence=2 two_sided=true 'my_field'

option	meaning
risk	false-alarm rate per point. Write it as a decimal, not scientific notation — the lexer reads 1e-4 as 1e and then fails on the -. 0.001 … 0.000001; 0.00005 is about one false alarm per series per fortnight at one evaluation a minute
persistence	how many consecutive points must be out of band before it counts
period	points per seasonal cycle. Omitted, the engine infers one day from the query’s span
two_sided	true also flags drops, not only rises
seasonal_break	opt in to seasonal-shape detection (see below)
max_series	refuse rather than silently truncate above this many series (default 500)

Columns added: predicted_<field>, lower_predicted_<field>, upper_predicted_<field> (the detection bounds — outside is an anomaly), anomaly_score, anomaly_magnitude, anomaly_kind and anomaly_calibrating.

anomaly_score is signed — positive above the band, negative below — so a rule can say “unusually high” with one comparison. anomaly_magnitude is its absolute value. anomaly_kind is SPIKE, LEVEL_SHIFT or SEASONAL_BREAK on a flagged point and empty otherwise; a level shift is not a spike, and after a deploy moves a baseline that distinction is what separates one useful alert from two hundred useless ones. anomaly_calibrating is true while the tail fit has too little history to give a threshold it will stand behind.

Filtering by kind needs both halves. The option turns a kind on in the engine, the where clause selects it. Ask for one without the other and you get an empty result that looks like it worked:

… | ml anomalies risk=0.00005 seasonal_break=true 'my_field'
  | where isnull(anomaly_kind) or anomaly_kind = "SEASONAL_BREAK"

Note the double quotes. In SonQL 'x' is a field reference and "x" is a string literal, so anomaly_kind = 'SPIKE' compares the column against a column that does not exist and fails the whole query.

seasonal_break is experimental and off by default. It looks for the shape of a repeating cycle changing while its level stays put. It can miss real changes and some of what it flags will not be real. It also needs roughly 20 cycles of history to find anything, and the query must be coarse enough to hold them under the 1,500-point cap mstats applies — at a 1-minute span a daily cycle is period 1440, which needs 28,800 points and cannot fire. The Metric Explorer raises the span to 1h for you when you pick this mode; in a hand-written query, set it yourself.

ml forecast — where the series is heading

… | ml forecast horizon=24 threshold=90 direction="above" sustained=3 'my_field'

option	meaning
horizon	points to project (at least 1)
threshold	the value whose crossing is the answer
direction	above or below
sustained	points that must stay across before a crossing counts
period	as for anomalies

Columns added: predicted_<field> with lower_predicted_<field> / upper_predicted_<field> — here a conformal band, the range the value is expected to lie in, not detection bounds — plus forecast_status, forecast_model, forecast_coverage, and, when a threshold is given, crossing_time, crossing_earliest, crossing_latest and crossing_probability.

ml outliers — members that do not match their cohort

… | ml outliers tolerance=3 two_sided=true 'my_field' by 'host'

Compares each member against its cohort’s median and spread, in robust-sigma units. tolerance is that multiple; two_sided includes members below the cohort as well as above. Columns added: outlier_score, is_outlier, cohort_median, cohort_scale, cohort_degenerate and cohort_size. cohort_size is how many members were actually compared — members with no data in the window are left out rather than scored. cohort_degenerate is true when fewer than five members have data, or when they have no usable spread; either way it tells you the score beside it means nothing, and no member is flagged.

A cohort is not optional. ml outliers compares each member against its cohort, so it needs by <field> or split by <field> (either is enough). Without one the query is refused before it runs, with a message that says what to add: “ml outliers needs a cohort to compare each member against: add by <field>, or split by <field>. For example: ml outliers tolerance=3 ‘value’ by ‘host’”. Until 6 Sep 2026 the same query returned no rows and no error — a silent empty result that read exactly like “nothing was unusual” — which is why it is now an error rather than an answer.

Durations, not point counts

persistence, period and sustained are counts of points, and a point means whatever the query’s span makes it. The alert and Metric Explorer forms take them as durations and convert against the span the query will actually use, which is why the same rule keeps its meaning when you change the span. Writing SonQL by hand, you are converting yourself.

Time ranges

Time is a parameter of the search, not part of the query text. In the UI it is the range picker; over the API it is startTime and endTime in epoch milliseconds.

The CLI takes absolute times only, in three forms: --start "2026-08-05 09:00:00" (read in your machine’s local time zone), an ISO 8601 timestamp such as 2026-08-05T09:00:00Z or 2026-08-05T09:00:00-07:00 (the offset is honored), or epoch milliseconds. Relative forms like -24h are rejected with a usage error. Every comparable tool accepts relative time here, and it is recorded as a usability gap.

Fields you always have

Every event carries these regardless of parsing:

field	meaning
_raw	the original line, verbatim
_time	event timestamp
_indexTime	when it became searchable
host	reporting host
origin	source path or collector
repo	the index it lives in
sourcetype	its declared format

Anything beyond these has to be parsed out. Sondar parses in the collection agent, as a transformers stage in the collection config — not at write time on the server. So events sent straight to the ingest API arrive with the fields above and nothing else, and JSON keys do not become queryable on their own.

_raw is only as reliable as the ingest path that wrote it. Events that arrive through a collection agent carry the original line in _raw; events POSTed straight to the ingest API as JSON are parsed into fields and may retain no _raw at all, so both a bare-term full-text search and the rex field=_raw below can silently match nothing on such data.

To get at structure without a collection pipeline, extract at search time:

search2 index="app_logs"
| rex field=_raw "duration=(?<duration_ms>\d+)"
| stats avg(duration_ms) as avg_ms by service

Live Tail

Live Tail follows an index in near real time. It polls once a second over a rolling window held slightly behind the present, because an event takes a moment to become searchable after it arrives — the delay control is that offset. Lower it to see events sooner; raise it if the stream looks like it is missing events under load.

Re-using a query: Dataset, Report, Macro

Sondar has no object called a saved search, and the three things it does have are not interchangeable. This section previously called a macro a “saved search”, which conflated two of them.

you want to	use	how you call it back
re-run a whole query	Dataset	| from dataset:name (leading pipe)
re-run it with its chart (on a schedule: subscribe to a dashboard that holds it)	Report	| from report:name
re-use a fragment inside other queries	Macro	backticks, Splunk-style: `name` or `name(arg)`

A Macro is not a saved query. It is a named, optionally parameterized piece of SonQL that is substituted into the query text before parsing, so it can be a few commands, a filter, or a single expression — the UI calls these Search Macros and gives each a search statement and an argument list. Macros can be called from other queries and from alert conditions.

Dataset and Report are whole saved queries; see interactive-search.md for how they are created from the search bar.

---