nano SIEM
Search Commands

Functions

Supported in:Self-hosted

Every eval and where function in nPL, plus the aggregation functions for stats, chart, timechart, eventstats and streamstats, with aliases and arguments.

Use this reference to choose functions for your nPL expressions and aggregations. Scalar functions compute values per row; aggregation functions summarize multiple rows.

Function names are case-insensitive. An unknown function fails the query with Unknown eval function '<name>'. Use a supported function. Arguments that name fields use exact OCSF 1.9 paths (see OCSF field reference).

Aggregation functions

General form: func(field) [as alias]. The argument can also be an expression (sum(traffic.bytes_in + traffic.bytes_out)) or a condition with eval() (count(eval(http_response.code>=500))). Use as to give each output column an explicit name. See stats for default names and name collisions.

FunctionAliasesArgumentsReturns
count0–1Number of rows. count(f) counts rows where f is not null. Bare count needs no parentheses.
dcdistinct_count1Exact number of distinct values
estdc1Approximate number of distinct values. Faster than dc on high-cardinality fields.
sum, avg, min, max1Sum, mean, minimum, maximum
values1Distinct values
list1All values
first, last1First or last value seen in the group
earliest, latest1Earliest or latest value of a time field
range1max - min
stdev, var1Standard deviation, variance
median150th percentile
mode1Most frequent value
perc95195th percentile
p95, p99195th or 99th percentile
percentile2percentile(f, N): the Nth percentile, N an integer 0–100
rate1Per-second rate of a counter
histogram_quantile2histogram_quantile(f, N): quantile N (0–100) of an OTLP histogram metric
sparkline0–1Trend of counts over time, for inline charts

Examples:

class_uid=4002 | stats count, dc(src_endpoint.ip) as clients, p95(duration) as p95_ms by http_request.url.hostname
class_uid=4002 | stats count(eval(http_response.code>=500)) as errors, count as total by metadata.log_source | eval error_pct=round(errors * 100 / total, 1)
class_uid=3002 status_id=2 | stats count, values(src_endpoint.ip) as sources, earliest(time) as first_try, latest(time) as last_try by user.name
class_uid=4001 | stats sum(traffic.bytes_out) as sent, estdc(dst_endpoint.ip) as peers by src_endpoint.ip | sort -sent

Scalar functions

Each table lists the function, its aliases, the number of arguments, and whether it works after a nano-side stage.

String

FunctionAliasesArgumentsDescriptionAfter nano-side stage
lower(s)tolower1Lowercaseyes
upper(s)toupper1Uppercaseyes
len(s)length1Length in charactersyes
substr(s, start[, len])substring2–3Substring. start is 0-based.yes
replace(s, from, to)3Replace every literal occurrence of fromyes
trim(s), ltrim(s), rtrim(s)1Strip whitespace from both ends, the left, or the rightyes
reverse(s)1Reverse a stringyes
split(s, sep)2Split into an array on a literal separator. Null input gives an empty array.yes
concat(a, b, …)1+Join values into one string. Same as the . operator.yes

Encoding and hashing

FunctionAliasesArgumentsDescriptionAfter nano-side stage
base64_encode(s)base64encode1Base64-encodeyes
base64_decode(s)base64decode1Base64-decode. An invalid or empty decoded value returns null.yes
hex_encode(s)hexencode, tohex1Hex-encodeyes
hex_decode(s)hexdecode, fromhex1Hex-decodeyes
url_encode(s)urlencode1Percent-encodeyes
url_decode(s)urldecode1Percent-decodeyes
md5(s), sha1(s), sha256(s)1Hex digest of the stringyes

IP addresses

FunctionAliasesArgumentsDescriptionAfter nano-side stage
cidr_match(ip, cidr[, cidr…])cidrmatch2+True if ip is in any of the CIDRs. With two arguments the order can be either way (cidrmatch(cidr, ip) also works). Non-IP values are false.yes
is_private_ip(ip)isprivateip, is_rfc19181True for 10/8, 172.16/12, 192.168/16yes
is_public_ip(ip)ispublicip1True unless private or loopbackyes
is_loopback(ip)isloopback1True for 127/8yes
ip_version(ip)14, 6, or 0 if not an IPyes
ipv4_to_int(ip)ip_to_int1IPv4 as an integeryes
int_to_ipv4(n)int_to_ip1Integer as dotted IPv4yes

URLs and domains

FunctionAliasesArgumentsDescriptionAfter nano-side stage
extract_domain(url)extractdomain1Hostname of a URLyes
extract_tld(host)extracttld1Top-level domain (com)yes
extract_path(url)extractpath1Path of a URLyes
extract_protocol(url)extractprotocol1Scheme of a URLyes
extract_query_string(url)extractquerystring1Query string of a URLyes
extract_root_domain(host)extractrootdomain, registered_domain1Registered domain: www.mail.google.com gives google.com (handles .co.uk)no
extract_subdomain(host)extractsubdomain, first_subdomain1First significant subdomain: www.mail.google.com gives googleno
domain_depth(host)subdomain_count1Number of labels, ignoring a leading wwwyes

Threat hunting

FunctionAliasesArgumentsDescriptionAfter nano-side stage
defang(s)1Make a URL or IP safe to share (hxxp, [.])yes
refang(s)1Undo defangyes
entropy(s)1Shannon entropy of the characters. High values suggest random or encoded strings (DGA domains, encoded commands).yes
levenshtein(a, b)levenshtein_distance, edit_distance2Edit distance. Use to spot typosquats.yes
levenshtein_normalized(a, b)edit_distance_normalized2Edit distance divided by the longer length (0–1)yes
ngram_distance(a, b)ngramdistance2N-gram distance, 0–1, lower is more similarno
trigram_distance(a, b)trigramdistance2Same as ngram_distanceno
ngram_search(haystack, needle)ngramsearch2N-gram match score, 0–1, higher means the needle is presentno

Time

time is the event time. Intervals are written INTERVAL <n> <UNIT>, for example INTERVAL 15 MINUTE.

FunctionAliasesArgumentsDescriptionAfter nano-side stage
now(), today(), yesterday()0Current time; today's and yesterday's dateyes
strftime(t, fmt)to_char2Format with strftime codes ("%Y-%m-%d %H:%M")yes
epoch(t)to_epoch1Unix secondsyes
from_epoch(n)fromepoch1Unix seconds to a timestampyes
date_diff(a, b) / date_diff(unit, a, b)datediff2–3a - b in seconds; or b - a in unit ("minute", "hour", "day", …)yes
age(t) / age(a, b)1–2Seconds since t; or a - b in secondsyes
date_trunc(unit, t)datetrunc2Truncate to second, minute, hour, day, week, month, quarter or yearyes
date_part(part, t)datepart2One component: "hour", "minute", "second", "day", "month", "year", "week", "dayofweek", "dayofyear". part must be a string literal.yes
year, month, day (dayofmonth), hour, minute, second1One componentyes
dayofweek (dow), dayofyear (doy), week (weekofyear), quarter1Calendar positionyes
start_of_hour, start_of_day, start_of_week, start_of_month, start_of_quarter, start_of_yearalso without underscores1Truncate to the start of the periodyes
earliest(a, b, …), latest(a, b, …)1+Smallest or largest of several timestampsyes
date_add(t, interval)dateadd2t + intervalno
date_sub(t, interval)datesub2t - intervalno
relative_time(t, interval)relativetime2t + intervalno
time_bucket(interval, t)timebucket2Start of the interval containing tno
timezone(zone, t)at_timezone2Convert to a time zone ("America/New_York")no
to_timestamp(s[, fmt])totimestamp1–2Parse a timestampno
to_date(s[, fmt])todate1–2Parse a date. fmt uses strftime codes and must be a literal; rows that don't match give null.no
isocalendar(t)1[ISO year, ISO week, day of week]no
make_timestamp(y, mo, d, h, mi, s)maketimestamp6Build a timestampno
make_date(y, mo, d)makedate3Build a dateno
make_time(h, mi, s)maketime3Build a timeno

Conversion

FunctionAliasesArgumentsDescriptionAfter nano-side stage
tonumber(x)to_number1To a number; unparseable values give 0yes
toint(x)to_int1To an integer; unparseable values give 0yes
tolong(x)1To an integer; unparseable values give nullyes
tostring(x[, fmt])to_string1–2To a string. A fmt containing % formats a timestamp like strftime.yes
exact(x)1Returns x unchanged (compatibility)yes
typeof(x)type1Type name of the valueno

Conditional and null handling

FunctionAliasesArgumentsDescriptionAfter nano-side stage
if(cond, then, else)iif3Choose a valueyes
case(c1, v1, c2, v2, …[, default])1+First value whose condition is true. With an even number of arguments the default is null.yes
coalesce(a, b, …)nvl1+First non-null argumentyes
nullif(a, b)2Null if a = b, else ayes
isnull(x)is_null1True if nullyes
isnotnull(x)is_not_null1True if not nullyes
in(x, v1, v2, …)2+True if x equals any value. On an array field, true if any element matches.yes
not(cond)1Negation. A missing value counts as not matching, so not() of it is true.yes
null(), true(), false()0Literal null, 1, 0yes

Type tests and search expressions

These functions run in ClickHouse. Place them before an enrichment stage.

FunctionArgumentsDescriptionAfter nano-side stage
isnum(x)1True when the value can be converted to a number; false for nullno
isint(x)1True when the value can be converted to an integer; false for nullno
isstr(x)1True for a string value; false for nullno
isbool(x)1True for a Boolean value; false for nullno
searchmatch("terms")1Match a quoted search expression with keywords, field comparisons, IN lists, and Boolean operators. Pipes, subsearches, feed lookups, and nested function filters are rejected.no

Math

FunctionAliasesArgumentsDescriptionAfter nano-side stage
abs(x)1Absolute valueyes
ceil(x)ceiling1Round upyes
floor(x)1Round downyes
round(x[, digits])1–2Roundyes
sqrt(x)1Square rootyes
pow(x, y)power2x to the power yyes
log(x)ln1Natural logyes
log10(x)1Base-10 logyes
min(a, b, …), max(a, b, …)2+Smallest or largest argument (scalar, not the aggregation)yes

Regex and patterns

Unlike the =~ operator and field=/re/, these functions are case-sensitive unless the pattern starts with (?i).

FunctionAliasesArgumentsDescriptionAfter nano-side stage
like(s, pattern)2SQL LIKE (% any run, _ one character), case-insensitiveyes
match(s, re)regex_match2True if the regex matches anywhere in syes
regex_extract(s, re[, group])extract_regex2–3Capture group 1, or group Nyes
extract(s, re)2Capture group 1yes
regex_replace(s, re, repl)3Replace the first matchyes
regex_replace_all(s, re, repl)3Replace every matchyes

Arrays and multivalue fields

OCSF repeated fields, such as observables.value, answers.rdata, file.hashes.value and http_request.url.categories, are arrays. Indexes are 0-based.

FunctionAliasesArgumentsDescriptionAfter nano-side stage
mvcount(arr)1Number of elementsyes
array_length(arr)arraylength1Number of elementsyes
mvindex(arr, i)2Element at index iyes
mvfind(arr, re)21-based position of the first element matching re; 0 if noneyes
mvjoin(arr, sep)2Join elements into a stringyes
array_join(arr[, sep])arrayjoin1+Join elements; default separator ,yes
array_contains(arr, v)arraycontains, has2True if any element equals v (case-insensitive)yes
mvdedup(arr)1Remove duplicatesyes
mvsort(arr)1Sort elementsyes
mvappend(a, b, …)1+Build an arrayyes
mvzip(a, b[, sep])2+Pair elements as a[i] + sep + b[i]; default separator ,yes
mvrange(start, end[, step])2+Numeric rangeyes

mvfilter() is parsed but always rejected ("mvfilter() is not supported"). Use mvfind, array_contains, or mvexpand followed by where.

Examples

Flag encoded PowerShell and score how random the command line looks:

class_uid=1007 process.name="powershell.exe" | eval encoded=match(process.cmd_line, "(?i)-e(nc|ncodedcommand)?\s"), score=round(entropy(process.cmd_line), 2) | where encoded=1 OR score > 5 | table time, device.hostname, actor.user.name, process.cmd_line, score

Find lookalike domains for your own domain:

class_uid=4002 | eval root=extract_root_domain(http_request.url.hostname), dist=levenshtein(root, "microsoft.com") | where dist > 0 AND dist <= 2 | stats count by root, dist

Outbound traffic to public addresses, labeled by direction:

class_uid=4001 | where is_private_ip(src_endpoint.ip) AND is_public_ip(dst_endpoint.ip) | eval flow=src_endpoint.ip . " -> " . dst_endpoint.ip . ":" . dst_endpoint.port | stats count by flow | sort -count | head 20

Logons outside business hours:

class_uid=3002 status_id=1 | eval hr=hour(time), dow=dayofweek(time) | where hr < 7 OR hr > 19 OR dow >= 6 | stats count by user.name, hr

What's next

  • eval: compute fields
  • where: filter with expressions
  • stats: aggregate
  • Syntax: operators and precedence

On this page