Miscellaneous operators

Use these functions in the Advanced Search interface to perform relatively complex data transformations. The Logs Query Builder does not natively support these functions.

backshift: Shifts a column in a table down by the specified number.
base64Decode: Converts a base64 string into to an ASCII/UTF-8 string.
base64Encode: Converts an ASCII/UTF-8 string to a base64 string.
compareCIDRPrefix: Checks if the network prefixes of two IPv4 addresses match.
concat: Concatenates multiple strings and numbers into a new string.
decToHex: Converts long value to hexadecimal value.
dedup: Removes duplicate results in a table by a set of columns.
format: Returns a formatted string given a format specifier and arguments.
formatDate: Returns a date string given a date, format, and timezone (default UTC).
getCIDRPrefix: Retrieves the network prefix from an IPv4 address.
hash: Hashes data into a string value using the specified hash algorithm.
hexToAscii: Converts hexadecimal string to ASCII value.
hexToDec: Converts hexadecimal string to a long value.
in: Checks if a field is in a set of string values or number values.
ipv4ToNumber: Converts an Internet Protocol version 4 (IPv4) IP address from the octet dot-decimal format to a decimal format.
isBlank: Checks if a string value is null, empty, or contains only empty characters.
isEmpty: Checks if a string value is an empty string that contains no characters or whitespace.
isNumeric: Checks if a string value can be parsed as a number.
isPrivateIP: Checks if an IPv4 address is private.
isPublicIP: Checks if an IPv4 address is public.
isValidIP: Checks if an IPv4 or IPv6 address is valid.
len: Returns the length of a string.
luhn: Validates credit card numbers in a string value using Luhn’s algorithm.
maskFromCIDR: Returns the subnet mask given a prefix length for IPv4 addresses.
matches: Matches strings using the RE2-compliant regex format.
now: Returns the current epoch time in milliseconds.
queryEndTime: Returns the end time of the search in milliseconds.
queryStartTime: Returns the start time of the search in milliseconds.
queryTimeRange: Returns the time range for the query being executed, in milliseconds.
replace: Replaces all occurrences of a specified string with another string.
substring: Extracts a part of a given string and start/end offsets.
toBytes: Parses a string representation of bytes (KB, MB, GB, and so on) into the number of bytes it represents.
toDuration: Parses a string representation of time (ns, µs, ms, s, m, h) to nanoseconds.
toFloat: Parses a string representation of a number or a number to a float.
toInt: Parses a string representation of a number, or a number to an int.
toLowerCase: Converts all letters of a string to lowercase.
toUpperCase: Converts all letters of a string to uppercase.
trim: Removes starting and trailing empty spaces in a string.
urlDecode: Returns an unescaped URL string.
urlEncode: Encodes a URL into the ASCII character set.

backshift

Shifts a column in a table down by the specified number.

For the views table.

The default shift is 1.

The default alias, when used on the command line, is _backshift.

Syntax

| backshift <field>
| backshift <field> as <alias>
| backshift <field>, <number>
| backshift <field>, <number> as <alias>

none

base64Decode

Converts a base64 string into to an ASCII/UTF-8 string.

Syntax

| base64Decode(<base64String>) as <alias>

none

Example

The following example returns K with a value of Hello World.

| base64Decode("SGVsbG8gV29ybGQ=") as K

none

base64Encode

Converts an ASCII/UTF-8 string to a base64 string.

Syntax

| base64Encode(<string>) as <alias>

none

Example

The following example returns K with a value of SGVsbG8gV29ybGQ=.

| base64Encode("Hello World") as K

none

compareCIDRPrefix

Checks if the network prefixes of two IPv4 addresses match.

Syntax

| compareCIDRPrefix(<ipv4String>, <ipv4String>) as <alias>

none

Examples

The following example returns K with a value of true.

| compareCIDRPrefix("10.10.1.35", "10.10.1.35", 24) as K

none

The following example returns K with a value of false.

| compareCIDRPrefix("10.10.1.35", "11.11.2.35", 24) as K

none

concat

Concatenates multiple strings and numbers into a new string.

Syntax

| concat(<field1>, <field2>, ...) as <alias>

none

Example

The following example returns K with a value of Hello World 1.

| concat("Hello", " ", "World", " ", 1) as K

none

decToHex

Converts long value to hexadecimal value.

Syntax

| decToHex(<longField>) as <alias>

none

Example

The following example returns K with a value of 461.

| decToHex("70") as K

none

dedup

Removes duplicate results in a table by a set of columns.

For the views table.

The default number is 1.

Syntax

| dedup by <field1>, <field2>, ...
| dedup by <number> <field1>, <field2>, ...

none

format

Returns a formatted string given a format specifier and arguments.

Syntax

| format(<formatSpecifierString>, <field1>, ...) as <alias>

none

Example

The following example returns K with a value of The count is: 1.

| format("%s : %s", "The count is", 1) as K

none

formatDate

Returns a date string given a date, format, and timezone (default UTC).

Syntax

| formatDate(<dateMilliseconds>) as <alias>

| formatDate(<dateMilliseconds>, <formatString>) as <alias>

| formatDate(<dateMilliseconds>, <formatString>, <timezoneString>) as <alias>

none

Examples

The following example returns K with a value of 2024-11-06T20:33:37Z.

| formatDate(1730925217838) as K

none

The following example returns K with a value of 11-06-2024.

| formatDate(1730925217838, "01-02-2006") as K

none

The following example returns K with a value of 2024-11-06 12:33:37.

| formatDate(1730925217838, "2006-01-02 15:04:05", "America/Los_Angeles") as K

none

getCIDRPrefix

Retrieves the network prefix from an IPv4 address.

Syntax

| getCIDRPrefix(<ipv4String>) as <alias>

none

Example

The following example returns K with a value of 24.

| getCIDRPrefix("10.10.1.35") as K

none

hash

Hashes data into a string value using the specified hash algorithm.

Supported hash algorithms are MD5, SHA1, SHA2, and MurmurHash3.

Syntax

| hash(<field>) as <alias>

| hash(<field>, <hashAlgorithm>) as <alias>

where <hashAlgorithm> is either md5(default), sha1, sha2_256, or murmur3_128

none

Examples

The following example returns K with a value of 5eb63bbbe01eeed093cb22bb8f5acdc3.

| hash("hello world") as K

none

The following example returns K with a value of 2aae6c35c94fcfb415dbe95f408b9ce91ee846ed.

| hash("hello world", "sha1") as K

none

hexToAscii

Converts hexadecimal string to ASCII value.

Syntax

| hexToAscii(<hexString>) as <alias>

none

Example

The following example returns K with a value of Hello Gopher!.

| hexToAscii("48656c6c6f20476f7068657221") as K

none

hexToDec

Converts hexadecimal string to a long value.

Syntax

| hexToDec(<hexString>) as <alias>

none

Example

The following example returns K with a value of 4919.

| hexToAscii("0000000000001337") as K

none

in

Checks if a field is in a set of string values or number values.

Syntax

where
if

| where <field> in (<value_1>[, <value_2>, <value_3>, ...])`

none

| if(<field> in (<value_1>[, <value_2>, <value_3>, ...]), <value_if_true>, <value_if_false>) as <field>

none

Examples

where
if

| source="pinot-server" | parse "[*]" as class | where class in ("ServerQueryLogger", "AppInfoParser")

none

| source="pinot-server" | parse "[*]" as class | if (class in ("ServerQueryLogger", "AppInfoParser"), "true class", "false class") as myClass

none

ipv4ToNumber

Converts an Internet Protocol version 4 (IPv4) IP address from the octet dot-decimal format to a decimal format.

Syntax

| ipv4ToNumber(<ipv4String>) as <alias>

none

Example

The following example returns K with a value of 178455296.

| ipv4ToNumber("10.163.3.0") as K

none

isBlank

Checks if a string value is null, empty, or contains only empty characters.

Syntax

| isBlank(<string>) as <alias>

none

Example

The following example returns K with a value of true.

| isBlank("  ") as K

none

isEmpty

Checks if a string value is an empty string that contains no characters or whitespace.

Syntax

| isEmpty(<string>) as <alias>

none

Example

The following example returns K with a value of false.

| isEmpty(" a ") as K

none

isNumeric

Checks if a string value can be parsed as a number.

Syntax

| isNumeric(<string>) as <alias>

none

Example

The following example returns K with a value of true.

| isNumeric("1.234") as K

none

isPrivateIP

Checks if an IPv4 address is private.

Syntax

| isPrivateIP(<ipv4String>) as <alias>

none

Example

The following example returns K with a value of true.

| isPrivateIP("192.168.0.1") as K

none

isPublicIP

Checks if an IPv4 address is public.

Syntax

| isPublicIP(<ipv4String>) as <alias>

none

Example

The following example returns K with a value of false.

| isPrivateIP("192.168.0.1") as K

none

isValidIP

Checks if an IPv4 or IPv6 address is valid.

Syntax

| isValidIP(<ipString>) as <alias>

none

Examples

The following example returns K with a value of true.

| isValidIP("192.168.0.1") as K

none

The following example returns K with a value of false.

| isValidIP("192.168.400.1") as K

none

len

Returns the length of a string.

Syntax

| len(<string>) as <alias>

none

Example

The following example returns K with a value of 4.

| len("1234") as K

none

luhn

Validates credit card numbers in a string value using Luhn’s algorithm.

The operator removes all non-numeric values and checks if the resulting string is a valid credit card number.

Syntax

| luhn(<string>) as <alias>

none

Examples

The following example returns K with a value of true.

| luhn("6666-7777-6666-8888") as K

none

The following example returns K with a value of false.

| luhn("00000000000000031") as K

none

maskFromCIDR

Returns the subnet mask given a prefix length for IPv4 addresses.

Syntax

| maskFromCIDR(<prefixLength>) as <alias> where <prefixLength> is 0-32

none

Examples

The following example returns K with a value of 255.255.255.255.

| maskFromCIDR(32) as K

none

The following example returns K with a value of 255.255.255.254.

| maskFromCIDR(31) as K

none

matches

Matches strings using the RE2-compliant regex format.

Syntax

where
if

| where <string expression> matches "<regex>"

none

| if(<field> matches <regex_pattern>, <value_if_true>, <value_if_false>) as <field>

none

Examples

where
if

| source="pinot-server" | parse "[*]" as class | where class matches "Server.*"

none

| source="pinot-server" | parse "[*]" as class | if (class matches "Server.*", "match found", "match not found") as myClass

none

now

Returns the current epoch time in milliseconds.

Syntax

| now() as <alias>

none

Examples

| now() as K
| formatDate(now(), "YYYY-MM-dd") as today

none

queryEndTime

Returns the end time of the search in milliseconds.

Syntax

| queryEndTime() as <alias>

none

Examples

| queryEndTime() as K

none

queryStartTime

Returns the start time of the search in milliseconds.

Syntax

| queryStartTime() as <alias>

none

Examples

| queryStartTime() as K

none

queryTimeRange

Returns the time range for the query being executed, in milliseconds.

Syntax

| queryTimeRange() as <alias>

none

Examples

| queryTimeRange() as K

none

replace

Replaces all occurrences of a specified string with another string.

The specified string can be a literal or regex.

Syntax

| replace(<sourceString>, <searchString>, <replaceString>) as <alias>

| replace(<sourceString>, <regexString>, <replaceString>) as <alias>

none

Examples

The following example returns K with a value of hello gopher.

| replace("hello world", "world", "gopher") as K

none

The following example returns K with a value of "http://kloudfuse.com/products//logs".

| replace("http://kloudfuse.com/products/12345678/logs", "[0-9]{5,}", "") as K

none

substring

Extracts a part of a given string and start/end offsets.

If not including an offset, the substring is until the end of the source string.

Syntax

| substring(<sourceString>, <startOffsert>) as <alias>

| substring(<sourceString>, <startOffsert>, <endOffset>) as <alias>

none

Example

The following example returns K with a value of hello.

| substring("hello world", 0, 5) as K

none

toBytes

Parses a string representation of bytes (KB, MB, GB, and so on) into the number of bytes it represents.

Syntax

| toBytes(<storageSize>) as <alias>

none

Examples

The following example returns K with a value of 1500.

| toBytes("1.5KB") as K

none

toDuration

Parses a string representation of time (ns, µs, ms, s, m, h) to nanoseconds.

Syntax

| toDuration(<timeString>) as <alias>

none

Example

The following example returns K with a value of 2000000.

| toDuration("2ms") as K

none

toFloat

Parses a string representation of a number or a number to a float.

Syntax

| toFloat(<number>) as <alias>

| toFloat(<numberString>) as <alias>

none

Examples

The following example returns K with a value of 1.25.

| toFloat("1.25") as K

none

toInt

Parses a string representation of a number, or a number to an int.

Syntax

| toInt(<number>) as <alias>

| toInt(<numberString>) as <alias>

none

Example

The following example returns K with a value of 1.

| toInt("1.5") as K

none

toLowerCase

Converts all letters of a string to lowercase.

Syntax

| toLowerCase(<string>) as <alias>

none

Example

The following example returns K with a value of hello world.

| toLowerCase("HELLO WORLD") as K

none

toUpperCase

Converts all letters of a string to uppercase.

Syntax

| toUpperCase(<string>) as <alias>

none

Example

The following example returns K with a value of HELLO WORLD.

| toLowerCase("hello world") as K

none

trim

Removes starting and trailing empty spaces in a string.

Syntax

| trim(<string>) as <alias>

none

Example

The following example returns K with a value of hello world.

| trim("  hello world   ") as K

none

urlDecode

Returns an unescaped URL string.

Syntax

| urlDecode(<urlString>) as <alias>

none

Example

The following example returns K with a value of http://example-server123.org/api/v1/data.php?auth=AbCdEfGhIjKlMnOpQrStUvWxYz123456&;.

| urlDecode("http%3A%2F%2Fexample-server123.org%2Fapi%2Fv1%2Fdata.php%3Fauth%3DAbCdEfGhIjKlMnOpQrStUvWxYz123456%26") as K

none

urlEncode

Encodes a URL into the ASCII character set.

Syntax

| urlDecode(<urlString>) as <alias>

none

Example

The following example returns K with a value of http%3A%2F%2Fexample-server123.org%2Fapi%2Fv1%2Fdata.php%3Fauth%3DAbCdEfGhIjKlMnOpQrStUvWxYz123456%26.

| urlDecode("http://example-server123.org/api/v1/data.php?auth=AbCdEfGhIjKlMnOpQrStUvWxYz123456&") as K.

none