Functions
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.
- Aggregation functions summarize many rows into one value. Use them in stats, chart, timechart, eventstats and streamstats.
- Scalar functions compute a value per row. Use them in eval and where.
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.
| Function | Aliases | Arguments | Returns |
|---|---|---|---|
count | 0–1 | Number of rows. count(f) counts rows where f is not null. Bare count needs no parentheses. | |
dc | distinct_count | 1 | Exact number of distinct values |
estdc | 1 | Approximate number of distinct values. Faster than dc on high-cardinality fields. | |
sum, avg, min, max | 1 | Sum, mean, minimum, maximum | |
values | 1 | Distinct values | |
list | 1 | All values | |
first, last | 1 | First or last value seen in the group | |
earliest, latest | 1 | Earliest or latest value of a time field | |
range | 1 | max - min | |
stdev, var | 1 | Standard deviation, variance | |
median | 1 | 50th percentile | |
mode | 1 | Most frequent value | |
perc95 | 1 | 95th percentile | |
p95, p99 | 1 | 95th or 99th percentile | |
percentile | 2 | percentile(f, N): the Nth percentile, N an integer 0–100 | |
rate | 1 | Per-second rate of a counter | |
histogram_quantile | 2 | histogram_quantile(f, N): quantile N (0–100) of an OTLP histogram metric | |
sparkline | 0–1 | Trend 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 -sentScalar functions
Each table lists the function, its aliases, the number of arguments, and whether it works after a nano-side stage.
String
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
lower(s) | tolower | 1 | Lowercase | yes |
upper(s) | toupper | 1 | Uppercase | yes |
len(s) | length | 1 | Length in characters | yes |
substr(s, start[, len]) | substring | 2–3 | Substring. start is 0-based. | yes |
replace(s, from, to) | 3 | Replace every literal occurrence of from | yes | |
trim(s), ltrim(s), rtrim(s) | 1 | Strip whitespace from both ends, the left, or the right | yes | |
reverse(s) | 1 | Reverse a string | yes | |
split(s, sep) | 2 | Split 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
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
base64_encode(s) | base64encode | 1 | Base64-encode | yes |
base64_decode(s) | base64decode | 1 | Base64-decode. An invalid or empty decoded value returns null. | yes |
hex_encode(s) | hexencode, tohex | 1 | Hex-encode | yes |
hex_decode(s) | hexdecode, fromhex | 1 | Hex-decode | yes |
url_encode(s) | urlencode | 1 | Percent-encode | yes |
url_decode(s) | urldecode | 1 | Percent-decode | yes |
md5(s), sha1(s), sha256(s) | 1 | Hex digest of the string | yes |
IP addresses
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
cidr_match(ip, cidr[, cidr…]) | cidrmatch | 2+ | 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_rfc1918 | 1 | True for 10/8, 172.16/12, 192.168/16 | yes |
is_public_ip(ip) | ispublicip | 1 | True unless private or loopback | yes |
is_loopback(ip) | isloopback | 1 | True for 127/8 | yes |
ip_version(ip) | 1 | 4, 6, or 0 if not an IP | yes | |
ipv4_to_int(ip) | ip_to_int | 1 | IPv4 as an integer | yes |
int_to_ipv4(n) | int_to_ip | 1 | Integer as dotted IPv4 | yes |
URLs and domains
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
extract_domain(url) | extractdomain | 1 | Hostname of a URL | yes |
extract_tld(host) | extracttld | 1 | Top-level domain (com) | yes |
extract_path(url) | extractpath | 1 | Path of a URL | yes |
extract_protocol(url) | extractprotocol | 1 | Scheme of a URL | yes |
extract_query_string(url) | extractquerystring | 1 | Query string of a URL | yes |
extract_root_domain(host) | extractrootdomain, registered_domain | 1 | Registered domain: www.mail.google.com gives google.com (handles .co.uk) | no |
extract_subdomain(host) | extractsubdomain, first_subdomain | 1 | First significant subdomain: www.mail.google.com gives google | no |
domain_depth(host) | subdomain_count | 1 | Number of labels, ignoring a leading www | yes |
Threat hunting
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
defang(s) | 1 | Make a URL or IP safe to share (hxxp, [.]) | yes | |
refang(s) | 1 | Undo defang | yes | |
entropy(s) | 1 | Shannon entropy of the characters. High values suggest random or encoded strings (DGA domains, encoded commands). | yes | |
levenshtein(a, b) | levenshtein_distance, edit_distance | 2 | Edit distance. Use to spot typosquats. | yes |
levenshtein_normalized(a, b) | edit_distance_normalized | 2 | Edit distance divided by the longer length (0–1) | yes |
ngram_distance(a, b) | ngramdistance | 2 | N-gram distance, 0–1, lower is more similar | no |
trigram_distance(a, b) | trigramdistance | 2 | Same as ngram_distance | no |
ngram_search(haystack, needle) | ngramsearch | 2 | N-gram match score, 0–1, higher means the needle is present | no |
Time
time is the event time. Intervals are written INTERVAL <n> <UNIT>, for example INTERVAL 15 MINUTE.
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
now(), today(), yesterday() | 0 | Current time; today's and yesterday's date | yes | |
strftime(t, fmt) | to_char | 2 | Format with strftime codes ("%Y-%m-%d %H:%M") | yes |
epoch(t) | to_epoch | 1 | Unix seconds | yes |
from_epoch(n) | fromepoch | 1 | Unix seconds to a timestamp | yes |
date_diff(a, b) / date_diff(unit, a, b) | datediff | 2–3 | a - b in seconds; or b - a in unit ("minute", "hour", "day", …) | yes |
age(t) / age(a, b) | 1–2 | Seconds since t; or a - b in seconds | yes | |
date_trunc(unit, t) | datetrunc | 2 | Truncate to second, minute, hour, day, week, month, quarter or year | yes |
date_part(part, t) | datepart | 2 | One component: "hour", "minute", "second", "day", "month", "year", "week", "dayofweek", "dayofyear". part must be a string literal. | yes |
year, month, day (dayofmonth), hour, minute, second | 1 | One component | yes | |
dayofweek (dow), dayofyear (doy), week (weekofyear), quarter | 1 | Calendar position | yes | |
start_of_hour, start_of_day, start_of_week, start_of_month, start_of_quarter, start_of_year | also without underscores | 1 | Truncate to the start of the period | yes |
earliest(a, b, …), latest(a, b, …) | 1+ | Smallest or largest of several timestamps | yes | |
date_add(t, interval) | dateadd | 2 | t + interval | no |
date_sub(t, interval) | datesub | 2 | t - interval | no |
relative_time(t, interval) | relativetime | 2 | t + interval | no |
time_bucket(interval, t) | timebucket | 2 | Start of the interval containing t | no |
timezone(zone, t) | at_timezone | 2 | Convert to a time zone ("America/New_York") | no |
to_timestamp(s[, fmt]) | totimestamp | 1–2 | Parse a timestamp | no |
to_date(s[, fmt]) | todate | 1–2 | Parse 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) | maketimestamp | 6 | Build a timestamp | no |
make_date(y, mo, d) | makedate | 3 | Build a date | no |
make_time(h, mi, s) | maketime | 3 | Build a time | no |
Conversion
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
tonumber(x) | to_number | 1 | To a number; unparseable values give 0 | yes |
toint(x) | to_int | 1 | To an integer; unparseable values give 0 | yes |
tolong(x) | 1 | To an integer; unparseable values give null | yes | |
tostring(x[, fmt]) | to_string | 1–2 | To a string. A fmt containing % formats a timestamp like strftime. | yes |
exact(x) | 1 | Returns x unchanged (compatibility) | yes | |
typeof(x) | type | 1 | Type name of the value | no |
Conditional and null handling
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
if(cond, then, else) | iif | 3 | Choose a value | yes |
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, …) | nvl | 1+ | First non-null argument | yes |
nullif(a, b) | 2 | Null if a = b, else a | yes | |
isnull(x) | is_null | 1 | True if null | yes |
isnotnull(x) | is_not_null | 1 | True if not null | yes |
in(x, v1, v2, …) | 2+ | True if x equals any value. On an array field, true if any element matches. | yes | |
not(cond) | 1 | Negation. A missing value counts as not matching, so not() of it is true. | yes | |
null(), true(), false() | 0 | Literal null, 1, 0 | yes |
Type tests and search expressions
These functions run in ClickHouse. Place them before an enrichment stage.
| Function | Arguments | Description | After nano-side stage |
|---|---|---|---|
isnum(x) | 1 | True when the value can be converted to a number; false for null | no |
isint(x) | 1 | True when the value can be converted to an integer; false for null | no |
isstr(x) | 1 | True for a string value; false for null | no |
isbool(x) | 1 | True for a Boolean value; false for null | no |
searchmatch("terms") | 1 | Match 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
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
abs(x) | 1 | Absolute value | yes | |
ceil(x) | ceiling | 1 | Round up | yes |
floor(x) | 1 | Round down | yes | |
round(x[, digits]) | 1–2 | Round | yes | |
sqrt(x) | 1 | Square root | yes | |
pow(x, y) | power | 2 | x to the power y | yes |
log(x) | ln | 1 | Natural log | yes |
log10(x) | 1 | Base-10 log | yes | |
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).
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
like(s, pattern) | 2 | SQL LIKE (% any run, _ one character), case-insensitive | yes | |
match(s, re) | regex_match | 2 | True if the regex matches anywhere in s | yes |
regex_extract(s, re[, group]) | extract_regex | 2–3 | Capture group 1, or group N | yes |
extract(s, re) | 2 | Capture group 1 | yes | |
regex_replace(s, re, repl) | 3 | Replace the first match | yes | |
regex_replace_all(s, re, repl) | 3 | Replace every match | yes |
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.
| Function | Aliases | Arguments | Description | After nano-side stage |
|---|---|---|---|---|
mvcount(arr) | 1 | Number of elements | yes | |
array_length(arr) | arraylength | 1 | Number of elements | yes |
mvindex(arr, i) | 2 | Element at index i | yes | |
mvfind(arr, re) | 2 | 1-based position of the first element matching re; 0 if none | yes | |
mvjoin(arr, sep) | 2 | Join elements into a string | yes | |
array_join(arr[, sep]) | arrayjoin | 1+ | Join elements; default separator , | yes |
array_contains(arr, v) | arraycontains, has | 2 | True if any element equals v (case-insensitive) | yes |
mvdedup(arr) | 1 | Remove duplicates | yes | |
mvsort(arr) | 1 | Sort elements | yes | |
mvappend(a, b, …) | 1+ | Build an array | yes | |
mvzip(a, b[, sep]) | 2+ | Pair elements as a[i] + sep + b[i]; default separator , | yes | |
mvrange(start, end[, step]) | 2+ | Numeric range | yes |
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, scoreFind 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, distOutbound 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 20Logons 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, hrWhat's next
Syntax
nPL search syntax: keywords, field comparisons, wildcards, regex, IN lists, CIDR ranges, ioc terms, precedence, comments, time modifiers and query limits.
Search best practices
Write nPL searches that return quickly and correctly: time range first, field filters over keywords, narrow by log source and class, nano-side stages last.