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.

transpose

Converts aggregate query results from a long format into a wide, tabular format by pivoting row values into column headers.

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-06 20:33:37.

| formatDate(1730925217838) as K
none

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

| formatDate(1730925217838, "YYYY-MM-dd") as K
none

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

| formatDate(1730925217838, "yyyy-MM-dd hh:mm:ss.SSS a", "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 <field> in (<value_1>[, <value_2>, <value_3>, ...])`
none
Examples
| source="pinot-server" | parse "[*]" as class | where class in ("ServerQueryLogger", "AppInfoParser")
none
fuseql in where

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.

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 <string expression> matches "<regex>"
none
Examples
| source="pinot-server" | parse "[*]" as class | where class matches "Server.*"
none
fuseql matches where

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

transpose

Converts aggregate query results from a long format into a wide, tabular format by pivoting row values into column headers.

Similar to a pivot table in Excel, the transpose operator transforms query results from a long list format into a wide table format in the Aggregates tab. You specify which fields define the rows and which define the columns.

The transpose operator dynamically creates columns for aggregate search results, allowing you to design queries without knowing the output schema in advance. This is particularly useful for formatting data for dashboard panels and charts.

Syntax
| transpose row <row_field1>[, <row_field2>, ...] column <column_field1>[, <column_field2>, ...]
none
Examples

Without transpose, the following query produces a long-format table that is difficult to read:

source="pinot-server" | timeslice 5m | count by _timeslice, status_code
none

With transpose, you can pivot the results to display status codes as columns and timeslices as rows:

source="pinot-server" | timeslice 5m | count by _timeslice, status_code | transpose row _timeslice column status_code
none

This transforms the data into an easy-to-read table where each status code becomes its own column, with counts displayed across time intervals.

The transpose operator supports multiple aggregate functions. When using multiple aggregates (e.g., count and avg), each combination of column values will generate separate columns for each aggregate:

source="pinot-server" | timeslice 5m | count as request_count, avg(response_time) as avg_response by _timeslice, status_code | transpose row _timeslice column status_code
none

This creates columns like request_count|200, avg_response|200, request_count|404, avg_response|404, etc.

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