SPL compatibility matrix

Phase zero. "Pushed" means the command compiles into the ClickHouse query; "in-process" means it runs in the engine on the query result. Everything not listed parses as a generic command and fails with "not supported yet".

Search expressions#

Commands#

In-process execution#

Once a command has to run in the engine (the timechart and chart pivot, fillnull without a field list on events, spath without a path once the events' fields are gone), everything after it runs in the engine too, on the rows the query returned. These commands have an in-process form, with the semantics of their SQL form: search (terms against _raw, field comparisons), where, eval (every function below), fields, table, rename, head (with an expression too), sort, dedup, bin, rex (extraction and mode=sed), stats, eventstats, streamstats (with its resets and time_window), top, rare, timechart, chart, fillnull, spath and mvexpand. A transforming command there turns the output into a results table. The rest (transaction, lookup, ...) are not built at all yet.

The in-process steps see only the rows the query returned: after fillnull or spath on events that is at most the event cap (see SEARCH-LIMITS.md), so an in-process stats over more events than that counts only the newest ones, and the result says limited. After a pivot the rows are the chart's, which is the usual case.

Known deviations in commands#

• timechart, bin and the timeline bucket time in UTC even when the search has another time zone, so span=1d buckets start at UTC midnight; Splunk uses the user's zone.
• timechart's and chart's OTHER series is the cell-by-cell sum of the series it replaces, whatever the aggregation, which is how Splunk filters series after aggregating; for avg, max or dc that is a sum of averages, maxima or distinct counts.
• chart's NULL column (usenull=true) is ranked for limit like any other column, and so is timechart's NULL series.
• chart span= on a number field labels each bucket with its lower bound (0, 10000), as bin does; Splunk labels it as a range (0-10000).
• chart's where clause takes in topN, notin topN, in bottomN, notin bottomN, or a comparison to a number; Splunk's limit inside it is not accepted.
• streamstats time_window follows the time order the results stream in (newest first for events, oldest first after sort _time) and uses the _time field, so it cannot follow a command that drops _time; reset_on_change compares the BY values as the previous result had them, so a result without the BY field (which gets no values) still ends a run.
• spath's array index is {N} counting from 0, and {-1} is rejected; Splunk documents {N} without saying where it counts from.
• A rex with max_match other than 1 that matches nothing gives an empty multivalue field rather than no field, because ClickHouse arrays cannot be null; mvcount of it is 0, not null.
• fillnull without a field list on events fills the fields of the events returned (at most the event cap), so a field that only appears in events past the cap is not created in the ones shown.
• spath without a path, while the events' search-time fields are still there, keeps a JSON array under its own key (tags, multivalue) rather than Splunk's tags{}, and does not flatten objects inside arrays (Splunk's items{}.id); the in-process form, after table or fields, uses Splunk's names.
• spath without a path does not overwrite a field the search already has as a column (host and the other default fields, or one made by eval or rex): the column wins. Splunk overwrites it. A fields - or wildcard rename of search-time fields before spath also applies to the fields spath extracts.
• spath with a path gives numbers as ClickHouse prints them (1.50 as 1.5, 1e3 as 1000); in-process spath keeps the JSON text.
• In-process min and max compare numerically when every value has a number and as strings otherwise, where SQL goes by the column's type; values sorts as strings in both. In-process stdev and var of a single value are null, where SQL gives nan.
• In-process stats, chart and top keep a search-time field's stored type in the BY values (a status ingested as a number comes out as a number), where SQL groups by the field's text.
• In-process search matches a free-text term against _raw the way the query would (a bare word as a token, a phrase as a substring, * as a wildcard, without regard to case), and field=value with the same case and wildcard rules; earliest= and latest= are rejected there as they are in a later search in SQL.
• A wildcard rename that moves a search-time field onto the name of a column the query already has (rename data.* AS * with a JSON key data.host) keeps the column's value; Splunk overwrites it.

Stats functions#

count, count(field), count(eval(cond)), dc/distinct_count, estdc, sum, sumsq, avg/mean, min, max, median, perc<N>/p<N>/exactperc<N>, stdev, stdevp, var, varp, range, mode, first, last, earliest, latest, values, list, rate.

Known deviations:

• min/max on search-time fields compare numerically; Splunk falls back to lexicographic order when values are not numbers.
• first/last use _time order, which is what Splunk does for a time-ordered search but not after a sort; the same goes for eventstats and streamstats.
• list keeps the first 100 values, Splunk's default list_maxsize; values has no cap.
• rate(x) is (latest x − earliest x) / (latest − earliest time) over the events that have x, null when those times are equal.

Eval functions#

if, case, coalesce, nullif, null, isnull, isnotnull, isnum, isint, isstr, true, false, exact, len, lower, upper, trim, ltrim, rtrim, substr, replace, split, mvcount, mvindex, mvjoin, mvfilter, round, abs, ceil/ceiling, floor, sqrt, exp, ln, log, pow, max, min, random, tonumber (with a base from 2 to 36), tostring (with "hex", "commas", "duration"), now, relative_time, strftime, strptime, match, like, cidrmatch, md5, sha1, sha256, sha512, urldecode, json_extract, spath.

Known deviations:

• String concatenation with . treats null as an empty string.
• _time inside eval is epoch seconds, as in Splunk; strftime and strptime take Splunk's directives: %M, %h and %r, which ClickHouse reads differently, are translated, and the rest of the common set (%Y %y %m %d %e %H %I %S %p %j %a %b %T) means the same in both; Splunk's subsecond %N is not supported.
• round(x, n) needs a literal n.
• relative_time(x, modifier), the base of tonumber and the format of tostring need literals; relative_time, strftime and strptime work in the search's time zone.
• tostring(x, "commas") rounds to two decimals and drops trailing zeros (1,234.5); tostring(x, "duration") ignores fractions of a second and writes D+HH:MM:SS past a day.
• mvfilter reads one field; with nothing left it gives an empty multivalue rather than null, as rex does.
• Regular expressions in match, replace and rex are RE2; PCRE-only syntax such as lookbehind will fail.