We use cookies or similar technologies to personalize your online experience and tailor marketing to you. Many of our product features require cookies to function properly. Your use of this site and online product constitutes your consent to these personalization technologies. Read our Privacy Policy to find out more.

X

Derived Column Reference

Function reference

The following functions can be used in derived column expressions. Functions operate within the context of a single event, meaning that each function takes column values from an event, and produces a new “derived” column attached to that event.

List of Operators

Conditional operators

IF

IF( condition, then-val [,else-val]). If condition evaluates to true, evaluates to then-val, otherwise evaluates to else-val (or NULL if else-val is not specified). All non-zero numbers, as well as the string true, evaluate to true.

# Usage: IF(condition, then-val, else-val)
# Examples
IF(CONTAINS($team_name, "acme"), "important-customer", "everyone else")
IF(GTE($http_status, 400), true, false)
IF(GTE($duration_ms, 500), "slow")

COALESCE

Evaluates to the first non-empty argument.

# Usage: COALESCE(arg1, arg2, ...)
# Examples
COALESCE($full_name, $email)
COALESCE($container_id, $hostname, $availability_zone, "unknown")

Comparison operators

LT

If both arguments are numbers, returns true if the first provided value is less than the second. If both arguments are strings, returns true if the first provided value falls lexicographically before the second. Always false if either argument is empty, or if the types of the columns do not match.

# Usage: LT(left, right)
# Examples
LT($roundtrip_us, 500)
LT($mysql_read_ms, $mysql_write_ms)

LTE

If both arguments are numbers, returns true if the first provided value is less than or equal to the second. If both arguments are strings, returns true if the first provided value is the same as, or falls lexicographically before, the second. Always false if either argument is empty, or if the types of the columns do not match.

# Usage: LTE(left, right)
# Examples
LTE($roundtrip_ms, 0.5)
LTE($get_schema_ms, $persist_schema_ms)

GT

If both arguments are numbers, returns true if the first provided value is greater than the second. If both arguments are strings, returns true if the first provided value falls lexicographically after the second. Always false if either argument is empty, or if the types of the columns do not match.

# Usage: GT(left, right)
# Examples
GT($payload_size_kb, 300)
GT($num_invalid_columns, $num_valid_columns)

GTE

If both arguments are numbers, returns true if the first provided value is greater than or equal to the second. If both arguments are strings, returns true if the first provided value is the same as, or falls lexicographically after, the second. Always false if either argument is empty, or if the types of the columns do not match.

# Usage: GTE(left, right)
# Examples
GTE($payload_size_mb, 0.3)
GTE($memory_inuse, MUL($max_memory_process, 0.75))

EQUALS

True if the two provided arguments are equal. (Arguments of different types, such as the integer 200 and the string "200", are not considered equal.)

# Usage: EQUALS(arg1, arg2)
# Examples
EQUALS($remote_addr, "216.3.123.12")
EQUALS($gzipped, true)
EQUALS($oversize_num_columns, 0)

IN

True if the first provided argument is equal to any of the subsequent arguments. IN can be thought of as a more compact form of a series of OR equality checks.

# Usage: IN(arg1, compare1, ...)
# Examples
IN($method, "DELETE", "POST", "PUT")
IN($build_id, "9051", "9052")
IN($num_invalid_payloads, 0, 1, -1)

EXISTS

True when the supplied argument has a defined value, false where it does not.

# Usage: EXISTS(arg1)
# Examples
EXISTS($batch_size)
EXISTS($team_name)
EXISTS($json_serialization_ms)

Boolean operators

NOT

Evaluates the provided argument to a boolean value, then inverts that value.

# Usage: NOT(arg1)
# Examples
NOT(EXISTS($batch))
NOT(IN($build_id, "175", "176"))
NOT(IN($company, "acme", "globex", "soylent"))

AND

Takes a variable number of arguments and returns true if all arguments are truthy.

# Usage: AND(arg1, arg2, ...)
# Examples
AND(GTE($roundtrip_ms, 100), EQUALS($method, "POST"))
AND(NOT(IN($method, "GET", "DELETE")), EXISTS($batch))
AND(EQUALS($api_version, "v3"), OR(LT($request_ms, 30), GT($request_ms, 300)))

OR

Takes a variable number of arguments and returns true if any arguments are truthy.

# Usage: OR(arg1, arg2, ...)
# Examples
OR(EQUALS($company, "acme"), GTE($priority, 5))
OR(GTE($mysql_latency_ms, 20), AND(GTE($s3_latency_ms, 100), EQUALS($method, "GET")))

Math operators

MIN

Evaluates to the smallest argument of the same type as the first non-empty argument. “Smallest” means the smaller, if numeric, or lexicographically first, if a string.

# Usage: MIN(arg1, arg2, ...)
# Examples
MIN($memory_inuse_local, $memory_inuse_merge, $memory_inuse_fetch)

MAX

Evaluates to the largest argument of the same type as the first non-empty argument. “Largest” means the larger, if numeric, or lexicographically last, if string.

# Usage: MAX(arg1, arg2, ...)
# Examples
MAX($mysql_latency_ms, $redis_latency_ms)
MAX(1, DIV($total_volume, $count))

SUM

Evaluates to the sum of all numeric arguments. Strings are parsed into numbers if possible, unparseable strings or other values evaluate to zero.

# Usage: SUM(arg1, arg2, ...)
# Examples
SUM($serialize_ms, $scan_ms, $publish_ms)
SUM(1.0, 5, "2.3")

SUB

Evaluates to the first argument subtracted by the second, or arg1 - arg2. Strings are parsed into numbers if possible, unparseable strings or other values evaluate to zero.

# Usage: SUB(arg1, arg2)
# Examples
SUB($serialization_ms, 100)
SUB($total_ms, SUM($local_ms, $merge_ms, $serialize_ms))

MUL

Evaluates to the product of all numeric arguments. Strings are parsed into numbers if possible, unparseable strings or other values evaluate to zero.

# Usage: MUL(arg1, arg2, ...)
# Examples
MUL($count, $time_per_item)
MUL(100, DIV($json_decode_ms, $total_ms))

DIV

Divides the first argument by the second, or arg1 / arg2. Strings are parsed into numbers if possible, unparseable strings or other values evaluate to zero. A division by zero evaluates to null.

# Usage: DIV(arg1, arg2)
# Examples
DIV($io_bytes_read, 1024)
DIV($total_ms, $rows_examined)
DIV($json_parse_time_ms, $total_request_time_ms)

LOG10

Computes the base-10 logarithm of the argument. Strings are parsed into numbers if possible. Unparseable strings, and arguments less than or equal to 0, evaluate to null.

# Usage: LOG10(arg1)
# Examples
LOG10($duration_ms)

BUCKET

Computes discrete (categorical) bins, or buckets, to transform continuous fields into categorical ones. This can be useful to group data into groups. The syntax is BUCKET( $column, size, [min, [max]]). The function returns size-sized buckets from min to max. For example, BUCKET( $column, 10, 0, 30) will return groups named < 0, 0 - 10, 10 - 20, 20 - 30, and > 30.

If only the min is specified, then the function will run without an upper bound; if neither a min nor max is specified, then the function will run without either bound, starting from a bucket 0 - size.

In all versions, points on the boundary between two bins will fall into the lower bin. The size, min, and max may not be columns. If the column is not a float value, the function returns null.

# Usage: BUCKET( $column, size, [min, [max]])
# Examples
BUCKET($duration_ms, 500, 0, 3000) # size, min and max
BUCKET($current_budget, 10, -5) # min only
BUCKET($num_users, 10) # size only

Cast operators

INT

Casts the argument to an integer, truncating the value if necessary. The argument is first coerced to a float if possible. Non-numeric values return 0.

# Usage: INT(arg1)
# Examples
INT($price_dollars)
INT(DIV($seconds, 3600))

FLOAT

Casts the argument to an float. Non-numeric values return 0.0.

# Usage: FLOAT(arg1)
# Examples
FLOAT($price_dollars) # e.g. 300.5
FLOAT("3.1415926535") # 3.1415926535

BOOL

Casts the argument to a bool. Evaluates to true if the argument is truthy:

source type value BOOL($value)
int 0 false
int (anything else) true
float 0.0 false
float (anything else) true
string "true" true
string (anything else) false
bool true true
bool false false
nil false
# Usage: BOOL(arg1)
# Examples
BOOL($price_dollars) # e.g. true
BOOL("")             # false
BOOL(true)           # true

STRING

Casts the argument to a string. Empty arguments are converted to "".

# Usage: STRING(arg1)
# Examples
STRING($price_dollars) # e.g. "300.5"
STRING(true)           # "true"
STRING($empty_column)  # ""

String operators

CONCAT

Concatenates string representations of all arguments into a single string result. Non-string arguments are converted to strings, empty arguments are ignored.

# Usage: CONCAT(arg1, arg2, ...)
# Examples
CONCAT($api_version, $sdk)
IF($is_batch, CONCAT($url, "-batch"), $url)

STARTS_WITH

True if the first argument starts with the second argument. False if either argument is not a string.

# Usage: STARTS_WITH(string, prefix)
# Examples
STARTS_WITH($url, "https")
STARTS_WITH($user_agent, "ELB-")

CONTAINS

True if the first argument contains the second argument. False if either argument is not a string.

# Usage: CONTAINS(string, substr)`
# Examples
CONTAINS($email, "@honeycomb.io")
CONTAINS($header_accept_encoding, "gzip")
IF(CONTAINS($url, "/v1/"), "api_v1", "api_v2")

REG_MATCH

True if the first argument matches the second argument, which must be a defined regular expression. False if the first argument is not a string or is empty. The provided regex must be a string literal containing a valid regular expression.

Note: Golang regex syntax can be tested here. If your regular expression contains character classes such as \s, \d or \w, enclose the regular expression in `backticks` so that it’s treated as a raw string literal.

# Usage: REG_MATCH(string, regex)
# Examples
REG_MATCH($error_msg, `^[a-z]+\[[0-9]+\]$`)
REG_MATCH($referrer, `[\w-_]+\.(s3\.)?amazonaws.com`)

REG_VALUE

Evaluates to the first regex submatch found in the first argument. Evaluates to an empty value if the first argument contains no matches or is not a string. The provided regex must be a string literal containing a valid regular expression.

Note: Golang regex syntax can be tested here. If your regular expression contains character classes such as \s, \d or \w, enclose the regular expression in `backticks` so that it’s treated as a raw string literal.

# Usage: REG_VALUE(string, regex)
# Examples
REG_VALUE($user_agent, `Chrome/[\d.]+`)
REG_VALUE($source, `^(ui-\d+|log|app-\d+)`)

The first example above yields a string like Chrome/1.2.3 and the second could be any one of ui-123, log, or app-456.

REG_VALUE is most effective when combined with other functions. As an example, the honeytail agent sets its User-Agent header to a string like libhoney-go/1.3.0 honeytail/1.378 (nginx), but there are also User-Agents like "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36.... In order to extract only the name of the parser used and not get caught up with other things in parentheses (such as the Macintosh... bit), we use this as a derived column:

IF(CONTAINS($user_agent, "honeytail"), REG_VALUE($user_agent, `\([a-z]+\)`), null)`

This results in fields that contain (nginx), (mysql), and so on. Combining CONTAINS or REG_MATCH with REG_VALUE is an easy way to limit the total number of strings available to the match and more effectively grab only the values you’re expecting.

REG_COUNT

Returns the number of non-overlapping successive matches yielded by the provided regex. Returns 0 if the first argument contains no matches or is not a string. The provided regex must be a string literal containing a valid regular expression.

Note: Golang regex syntax can be tested here. If your regular expression contains character classes such as \s, \d or \w, enclose the regular expression in `backticks` so that it’s treated as a raw string literal.

# Usage: REG_COUNT(string, regex)
# Examples
REG_COUNT($sql, `JOIN`)
REG_COUNT($ip, `19.`)

Utility functions

UNIX_TIMESTAMP

Converts a date string in RFC3339 format (e.g., 2017-07-20T11:22:44.888Z) to a Unix timestamp (1500549764.888). This is useful for comparing two timestamps in an event; for example, to calculate a duration from a start and an end timestamp.

# Usage: UNIX_TIMESTAMP(string)
# Examples
UNIX_TIMESTAMP($timestamp)

EVENT_TIMESTAMP

Returns the Unix timestamp field of the current event (e.g. 1500549764). This is useful for comparing two timestamps in an event; for example, to calculate a duration from a start and an end timestamp. This function takes no arguments.

# Usage: UNIX_TIMESTAMP()
# Examples
EVENT_TIMESTAMP()