What the engine runs, and how.

Search expressions

Commands

In-process execution

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

29 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

58 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
• tostring
• "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.