Functions
SPL has two kinds of functions:
- Evaluation functions compute a value for each row. Use them in
eval,where,fields … as, and anywhere an expression goes. - Aggregation functions combine many rows into one value. Use them in
stats,timechart,chart,eventstatsandstreamstats.
Function names are case-insensitive. Arguments that are text go in double or single quotes.
Attributes are stored as text, but functions that need numbers read them as numbers: round(duration / 1000, 1) and avg(duration) work without converting first.
Evaluation Functions
Comparison and Conditional
| Function | Returns |
|---|---|
if(cond, a, b) | a when cond is true, else b |
case(cond1, v1, cond2, v2, …) | The value of the first true condition; NULL when none is. Use true(), v as the last pair for a default |
coalesce(a, b, …) | The first value that is not empty. With numbers only, a number; mixed with text or attributes, as text: coalesce(duration, 0) |
nullif(a, b) | NULL when a equals b, else a |
null() | NULL |
true() / false() | True / false |
in(x, v1, v2, …) | Whether x is one of the values |
like(s, pattern) | Whether s matches a SQL LIKE pattern (% any text, _ one character) |
match(s, regex) | Whether s matches the regular expression |
cidrmatch(cidr, ip) | Whether the IP address is in the subnet, e.g. cidrmatch("10.0.0.0/8", client_ip) |
searchmatch("search") | Whether the row matches a search, e.g. searchmatch("level=error timeout") |
validate(cond1, msg1, cond2, msg2, …) | The message of the first false condition; NULL when all hold |
Informational
| Function | Returns |
|---|---|
isnull(x) | Whether x is NULL or empty |
isnotnull(x) / ispresent(x) | Whether x has a value |
isblank(x) | Whether x is empty |
isnum(x) | Whether x is a number |
isint(x) | Whether x is a whole number |
isstr(x) | Whether x has a value that is not a number |
isbool(x) | Whether x is true, false, 1 or 0 |
typeof(x) | Number, String, Boolean or Invalid |
Conversion
| Function | Returns |
|---|---|
tonumber(s[, base]) | s as a number; base 2, 8, 10 (default) or 16 (tonumber("0x1A", 16) is 26). NULL when it is not one |
tostring(x) | x as text |
tostring(x, "commas") | 1,234,567.89 (two decimal places) |
tostring(x, "hex") | 0xFF |
tostring(x, "duration") | Seconds as [D+]HH:MM:SS, e.g. 1+01:01:01 |
printf(format, …) | Formatted text, e.g. printf("%s took %d ms", url, duration) |
ipmask(mask, ip) | The IP address masked, e.g. ipmask("255.255.255.0", "10.1.2.3") is 10.1.2.0 |
toFloat64(x), toInt64(x), … | ClickHouse conversions; NULL instead of an error when x is not a number |
Text
| Function | Returns |
|---|---|
len(s) | The number of characters in s |
lower(s) / upper(s) | s in lower / upper case |
trim(s[, chars]) / ltrim / rtrim | s without leading and trailing spaces, or the given characters |
substr(s, start[, length]) | Part of s, in characters. start counts from 1; a negative start counts from the end (substr("splunk", -3) is unk) |
replace(s, regex, replacement) | s with every match replaced; \1 puts back a group |
split(s, delimiter) | A multivalue list of the parts |
urldecode(s) | s URL-decoded |
md5(s), sha1(s), sha256(s), sha512(s) | The hash as hex |
s1 . s2 / s1 + "text" | s1 and s2 joined. + joins when one side is a quoted string, and adds otherwise |
Math
| Function | Returns |
|---|---|
abs(x) | Absolute value |
ceil(x) / ceiling(x), floor(x) | Rounded up / down |
round(x[, digits]) | Rounded, to digits decimal places; halves round away from zero (round(2.5) is 3) |
sigfig(x) | Rounded to six significant digits |
sqrt(x), pow(x, y), exp(x) | Square root, x to the power y, ex |
ln(x) / log(x[, base]), log10(x) | Natural logarithm; logarithm in base (10 by default, as in Splunk); base-10 logarithm. Empty for x of 0 or less |
min(a, b, …) / max(a, b, …) | The smallest / largest number (with two or more arguments; with one it is the aggregation) |
avg(a, b, …) / sum(a, b, …) | The average / total of the numbers (with two or more arguments) |
sin, cos, tan, asin, acos, atan, atan2(y, x), sinh, cosh, tanh, asinh, acosh, atanh, hypot(x, y) | Trigonometry, in radians |
pi() | π |
random() | A random whole number from 0 to 231−1 |
exact(x) | x |
bit_and(x, y), bit_or(x, y), bit_xor(x, y) | Bitwise AND, OR, exclusive OR of whole numbers |
bit_not(x[, bits]) | Bitwise NOT; with bits, only the lowest bits bits |
bit_shift_left(x, n), bit_shift_right(x, n) | x shifted n bits |
Date and Time
| Function | Returns |
|---|---|
now() / time() | The current time |
relative_time(t, "offset") | t moved by a relative time, e.g. relative_time(now(), "-1d@d") is midnight yesterday |
strftime(t, "format") | t as text, e.g. strftime(_time, "%Y-%m-%d %H:%M"). %s gives epoch seconds |
strptime(s, "format") | The time s describes; NULL when it does not match the format |
Formats use Splunk's specifiers:
| Specifier | Means | Specifier | Means |
|---|---|---|---|
%Y / %y | Year, 4 / 2 digits | %H / %I | Hour, 24 / 12-hour |
%m | Month number | %M | Minute |
%d / %e | Day of month | %S | Second |
%b / %B | Month name, short / full | %3N, %6N, %N | Subseconds |
%a / %A | Weekday name, short / full | %p | AM / PM |
%j | Day of year | %z | Time zone offset |
strftime also accepts the older SPL order, strftime("format", time).
Times compare with times: where _time > relative_time(now(), "-1h").
Multivalue
A multivalue field holds a list of values. split, rex max_match=, makemv, values() and list() make one. A function that takes a multivalue field treats a single value as a list of one. Numeric stats functions (sum, avg, min, max, pN, …) over a multivalue field count each of its values, as in Splunk; in eval arithmetic a multivalue field is empty.
| Function | Returns |
|---|---|
mvcount(mv) | The number of values (0 when the field is empty) |
mvindex(mv, start[, end]) | The value at start, or the values from start to end. Positions count from 0; negative counts from the end (-1 is the last) |
mvjoin(mv, delimiter) | The values joined into text |
mvappend(a, b, …) | The values of every argument, in order |
mvdedup(mv) | The values without repeats |
mvsort(mv) | The values sorted |
mvfind(mv, regex) | The position of the first value that matches; NULL when none does |
mvfilter(condition) | The values of the field named in the condition for which it holds, e.g. mvfilter(match(email, "\.net$")) |
mvmap(mv, expression) | The expression applied to each value, e.g. mvmap(sizes, sizes * 1024) |
mvrange(start, end[, step]) | The numbers from start up to, not including, end |
mvzip(a, b[, delimiter]) | The values of a and b paired: a1,b1, a2,b2, … |
mv_to_json_array(mv) | The values as a JSON array |
JSON
| Function | Returns |
|---|---|
spath(json, "path") / json_extract(json, "path") | The value at the path, e.g. spath(parsed_log, "req.items{0}"). A path is keys separated by dots; {N} picks an array element (from 0) and {} returns every element |
json_extract_exact(json, "key") | The value of a key whose name contains dots |
json_keys(json) | The top-level keys, as a JSON array |
json_valid(json) | Whether the text is valid JSON |
json_object(key, value, …) | A JSON object, e.g. json_object("id", 7, "ok", true()) is {"id":7,"ok":true} |
json_array(value, …) | A JSON array, e.g. json_array(2, 3) is [2,3] |
json_array_to_mv(json) | A JSON array as a multivalue field |
json_has_key_exact(json, "key") | Whether the object has the key (a name that may contain dots) |
Editing JSON. These return the JSON with the changes made. Paths are written like spath paths (a.b{0}.c) and must be quoted text; each function takes several paths (with their values) at once, of up to 32 steps each.
| Function | Returns |
|---|---|
json_set(json, "path", value, …) | The JSON with the value at each path, replaced or added. Missing objects along the path are created |
json_set_exact(json, "key", value, …) | The same, with each key taken as one name even if it contains dots |
json_delete(json, "path", …) | The JSON without the keys or array elements at the paths |
json_append(json, "path", value, …) | The JSON with the value added to the end of the array at each path |
json_extend(json, "path", array, …) | The JSON with the elements of a JSON array added to the array at each path |
Values are written by their type, in these functions and in json_object() and json_array(): numbers (including numbers computed by eval) as JSON numbers, true(), false(), conditions such as status = 500 and null() as booleans and null, multivalue fields as arrays, values made by json_object(), json_array() or another of these functions as JSON, and anything else as a JSON string. Attributes are text, so they become strings: use tonumber(duration) for a number. A result that is not a finite number, such as 1/0, becomes null.
json_set adds missing keys along the path, but an array element on the path must already exist; if one does not, the JSON is returned unchanged. A path that does not exist leaves the JSON unchanged for json_delete, json_append and json_extend. For example, json_set(parsed_log, "user.region", "eu") adds "region":"eu" inside user.
Functions that would show details of the server, such as version() or hostname(), are refused.
Aggregation Functions
| Function | Returns |
|---|---|
count / c / count(*) | The number of rows |
count(x) | The number of rows that have a value for x |
count(eval(cond)) | The number of rows where the condition holds |
dc(x) / distinct_count(x) | The number of distinct values of x |
estdc(x) | An estimate of the number of distinct values, faster on very many values |
sum(x) | The total |
avg(x) / mean(x) | The average |
min(x) / max(x) | The smallest / largest value. Numeric when every value is a number, else alphabetical |
median(x) | The median |
mode(x) | The most common value |
range(x) | max(x) - min(x) |
stdev(x) / stdevp(x) | Sample / population standard deviation |
var(x) / varp(x) | Sample / population variance |
sumsq(x) | The sum of the squares |
perc<N>(x) / p<N>(x) | The Nth percentile, e.g. p95(duration), perc99.9(duration) |
exactperc<N>(x) | The exact Nth percentile (slower) |
upperperc<N>(x) | The Nth percentile, rounded up to an actual value |
first(x) / last(x) | The value of the first / last row in the rows' order. Log events are newest first, so by default first is the most recent; after sort or stats it follows that order |
latest(x) / earliest(x) | The value of the most recent / oldest row, by time |
latest_time(x) / earliest_time(x) | The time of the most recent / oldest row with a value for x |
rate(x) | How fast a counter grew: the change between the oldest and most recent value, per second |
values(x) | The distinct values, sorted, as a multivalue field |
list(x) | Every value, as a multivalue field |
sparkline(<aggregation>[, span]) | A trend per row (stats only); see Sparklines |
per_second(x), per_minute(x), per_hour(x), per_day(x) | The total per time unit within each bucket (timechart only) |
Any aggregation can take an expression through eval: sum(eval(duration / 1000)), dc(eval(lower(user))).
Aggregations can be combined with arithmetic: stats sum(bytes) / count as avg_bytes, p99(duration) - p50(duration) as spread.
Without as, a column is named after the function and field: count, avg_duration, p95_duration, count_status.