DQL functions
Functions can be used to perform computations on fields of DQL commands.
Aggregation functions
Aggregation functions compute results from a collection of values.
count
Counts the total number of records.
-
Syntax
count()
-
Example
The example query counts the number of log lines in the last two minutes.
fetch logs, from:now()-2m | summarize Count = count()
Query result
count 184612
countIf
Counts the number of records that match the condition.
-
Syntax
countIf(condition)
-
Parameters
Name Type Mandatory Default Constraints Description condition
boolean
yes
-
Example
The example query counts the number of log lines in the last two minutes if the log line contains log level
ERROR
.fetch logs, from:now()-2m | summarize Error_count = countif(loglevel == "ERROR")
Query result
Error_count 24
countDistinct
Calculates the cardinality of unique values of a field for a list of records. In comparison to SQL, | summarize countDistinct(x)
would relate to SELECT count(distinct(x))
.
-
Syntax
countDistinct (Expression, [precision])
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timestamp
yes
The precision that should be used for the HLL implementation in the interval [1, 16].
-
Example
The example query counts the number of distinct hosts where log data has been recorded in the last 10 minutes:
fetch logs, from:now()-10m | summarize distinctHosts = countDistinct(host.name)
Query result
distinctHosts 738
-
Aggregation precision
The
countDistinct()
aggregation function uses a variant of the HyperLogLog (HLL) algorithm, which makes a stochastic estimation of set cardinality. Theprecision
defines the number of possible values(2^precision)
and the standard expected error is approximately0.785/sqrt(2^precision)
.By default, the precision is set to 10, which results in
2^10
values and a maximum error of approximately2%
.fetch logs, from:now()-10m | summarize distinctHosts = countDistinct(host.name)
Query result
distinctHosts 738
sum
Calculates the sum of a field for a list of records.
-
Syntax
sum(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, duration
yes
-
Example
The example query displays the sum value for the
amount
field in the events data set.fetch events | summarize Total_amount = sum(amount)
Query result
Total_amount 1,064,497
avg
Calculates the average value of a field for a list of records.
-
Syntax
avg(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, duration
yes
-
Example
The example query displays the average value for the
amount
field in the events data set.fetch events | summarize Average_amount = avg(amount)
Query result
Average_amount 958
max
Calculates the maximum value of a field for a list of records.
-
Syntax
max(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, duration, timestamp, string, boolean
yes
expression
-
Example
The example query displays the maximum value of the
amount
field in the events data set.fetch events | summarize Maximum_amount = max(amount)
Query example
Maximum_amount 1,993
min
Calculates the minimum value of a field for a list of records.
-
Syntax
min(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, duration, timestamp, string, boolean
yes
expression
-
Example
The example query displays the minimum value of the
amount
field in the events data set.fetch events | summarize Minumum_amount = min(amount)
Query result
Minumum_amount 188
median
Calculates the median of an expression (short for percentile(expression, 50))
-
Syntax
median(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, duration, timestamp, boolean
yes
Returns NULL if input contains mixed data types.
No nested aggregation functions.
The expression from which to compute the median.
-
Example
fetch events | summarize median_amount = median(amount) | fieldsAdd median_amount = if(isNull(median_amount), "24", else:median_amount)
Query result
median_amount 24
percentile
Calculates a given percentile of an expression.
-
Syntax
percentile(expression, percentile)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
Returns NULL if input contains mixed data types.
No nested aggregation functions.
The expression from which to compute a percentile.
percentile
double, long
yes
Must be between 0 and 100.
No nested aggregation functions.
The percentile to be calculated from the expression.
-
Example
fetch events | summarize amount_90_percentile = percentile(amount, 90) | fieldsAdd amount_90_percentile = if(isNull(amount_90_percentile), "58", else:amount_90_percentile)
Query result
amount_90_percentile 58
takeFirst
Returns the first value of a field for a list of records.
-
Syntax
takeFirst(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
The example query displays the first timestamp value in the log data set.
fetch logs, from:now()-1m | summarize First_timestamp = takeFirst(timestamp)
Query result
First_timestamp 7/28/2022, 1:28:02 PM
takeLast
Returns the last value of a field for a list of records.
-
Syntax
takeLast(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
The example query displays the last timestamp value in the log data set.
fetch logs, from:now()-1m | summarize Last_timestamp = takeLast(timestamp)
Query result
Last_timestamp 2022-08-02T11:28:51.724000000Z
collectDistinct
Collects the values of the provided field into an array. The original order of elements is not guaranteed.
-
Syntax
collectDistinct(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
This example is based on a
bizevents
dataset. It shows how thecollectDistinct()
function can be used to generate structured output based on a raw dataset.In this case, the generated events and consecutive products touched by each client (IP address) are put into a structured output table. This option is well suited to use cases where a flat representation of the output table is not wanted.
We use the
in()
function to illustrate how the occurrence of a value inside an array can be evaluated.The result shows the two most active clients who touched the
Paris
travel options.fetch bizevents | fields timestamp, event.type, client.ip, product | summarize activity=count(), actions=collectDistinct(event.type), products=collectDistinct(product), by:client.ip | filterOut isNull(client.ip) | filter in("Paris", products) | sort activity desc | limit 2
Query result
client.ip activity actions products 152.125.149.246
100
[{"fields":[{"name":"element","type":"string"}],"values":{"element":"com.travel.frontend.sea...
[{"fields":[{"name":"element","type":"string"}],"values":{"element":"Paris"}},{"field......
124.75.6.61
84
[{"fields":[{"name":"element","type":"string"}],"values":{"element":"com.travel.frontend.bo...
[{"fields":[{"name":"element","type":"string"}],"values":{"element":"Paris"}},{"field...
collectArray
Collects the values of the provided field into an array (preservation of order not guaranteed).
-
Syntax
collectArray(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
fetch logs | limit 5 | summarize Array_Hosts = collectArray(dt.entity.host)
Query result
Array_Hosts \[{fields=\[{name=element, type=string}], values={element=HOST-1}}, {fields=\[{name=element, type=string}], values={element=HOST-12}}, {fields=\[{name=element, type=string}], values={element=HOST-123}}, {fields=\[{name=element, type=string}], values={element=HOST-11234}}, {fields=\[{name=element, type=string}], values={element=HOST-12345}}]
takeMin and takeMax
Retrieves the minimum and the maximum from a list of records. Renders results for lists with both homogenous and non-homogenous data. If the data type of the field is not homogenous, the data type order is applied as described in the table below:
-
Syntax
takeMin(expression)
takeMax(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
any
yes
No nested aggregation functions.
The expression used in the min/max calculation.
-
Example
DATA record(long=6, string="2", any=now()), record(long=2, string="1", any="10"), record(long=1, string="3", any=true) | SUMMARIZE takeMin(long), takeMin(string), takeMin(any), takeMax(long), takeMax(string), takeMax(any)
Query result
takeMin(long) takeMin(string) takeMin(any) takeMax(long) takeMax(string) takeMax(any) 1
1
true
6
3
3/1/2023, 5:36:12 PM (now)
stddev
Calculates the standard deviation of a field for a list of records.
-
Syntax
stddev(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
-
Example
This example calculates the average, minimum, maximum, and standard deviation for a list of numeric
amount
values.... | filter isNotNull(amount) | summarize avg(amount), min(amount), max(amount) , stdev = stddev(amount)
Query result
avg(amount) min(amount) max(amount) stddev 504.7122282608696
99
1906.3
369.45191419809464
variance
Calculates the variance of a field for a list of records.
-
Syntax
variance(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
-
Example
... | filter isNotNull(amount) | summarize avg(amount), min(amount), max(amount) , var = variance(amount)
Query result
avg(amount) min(amount) max(amount) var 504.7122282608696
99
1906.3
188038.16206188488
correlation
Calculates the Pearson correlation of two numeric fields for a list of records. If one of the fields has a constant value, the covariance of both fields used for correlation is zero. In this case, the correlation coefficient causes a division by zero, yielding null
for the correlation.
-
Syntax
correlation(expression1, expression2)
-
Parameters
Name Type Mandatory Default Constraints Description expression1
double, long
yes
expression2
double, long
yes
-
Example
...
| fields timestamp, a=toDouble(timestamp), b=toDouble(timestamp)
| summarize correlation=correlation(a,b)
Query result
correlation |
---|
|
takeAny
Returns any non-null value of a field for a list of records.
-
Syntax
takeAny(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
In this example, the
takeAny
function returns any non-null host name for each log level type.fetch logs | summarize takeAny(host.name), by:loglevel
Query result
loglevel
takeAny(host.name)
DEBUG
HOST NAME 1
ERROR
HOST NAME 1
INFO
HOST NAME 1
NONE
HOST NAME 1
WARN
HOST NAME 1
Conversion functions
Conversion functions convert the expression or value from one data type to another type.
toArray
Returns the value if it is an array
. Otherwise, converts a value to the single element array holding that value.
-
Syntax
toArray(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
In this example, we convert to an array
var_value
variable and try to convertnum_array
containing 15 numbers. As a result, the variable is converted to the single element array holding that variable's valuearray_2
and the array is preserved as an array in a new field (array_1
).... | fields var_value = 235711131719, num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields array_1 = toArray(num_array), array_2 = toArray(var_value)
Query result
array_1 array_2 [2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11]
[235711131719]
toBoolean
Converts a value to Boolean if the value is of a suitable type. If the argument is an array, the element at position 0 is converted.
Use asBoolean(value)
function to return if the value is boolean
or variant<boolean>
, otherwise NULL.
Converts string values true
or TRUE
to a Boolean true
.The comparison is case insensitive. Converts other values to Boolean false
.
Converts numeric value 0 to Boolean false
. Converts other numeric values to Boolean true
.
-
Syntax
toBoolean(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
boolean, double, long, string, array
yes
-
Example
... | fields bValue1 = toBoolean("true"), bValue2 = toBoolean("TrUe"), bValue3 = toBoolean("1"), bValue4 = toBoolean(3), bValue5 = toBoolean("test"), bValue6 = toBoolean(0)
Query result
bValue1 bValue2 bValue3 bValue4 bValue5 bValue6 true
true
false
true
false
false
toDouble
Converts a value to double
if the value is of a suitable type. If the argument is an array
, the element at position 0 is converted.
Use asDouble(value)
function to return if the value is double
or variant<double>
, otherwise NULL.
-
Syntax
toDouble(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
double, long, string, boolean, ip, timestamp, duration, array
yes
-
Example
... | fields dbl_1 = toDouble("1234.5"), dbl_2 = toDouble(4+3/2)
Query result
dbl_1 dbl_2 1234.5
5.0
toDuration
Converts a value to duration
if the value is of a suitable type. If the argument is an array
, the element at position 0 is converted.
-
Syntax
toDuration(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
duration, double, long, string, timeframe, array
yes
-
Example
fetch logs | fields dur = toDuration(62*1000000000*60*60*24) | fieldsAdd dur > 60d
Query result
dur dur > 60d 2 M 1.126 D
true
toLong
Converts a value to long
if the value is of a suitable type. If the argument is an array
, the element at position 0 is converted.
-
Syntax
toLong(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
long, double, string, boolean, ip, timestamp, duration, array
yes
-
Example
... | fields cnv_long1 = toLong("83457264009472472"), cnv_long2 = toLong(30), cnv_long3 = toLong(25.34)
cnv_long1 cnv_long2 cnv_long3 83457264009472472
30
25
toString
Returns the string representation of a value.
-
Syntax
toString(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
double, boolean, timestamp, timeframe, duration, ip, array, record
yes
Parameter that should be transformed into text form.
-
Example 1
... | fields str_Boolean = toString(toBoolean(1)), str_Array = toString(array(1,2,3)), str_Number = toString(1), str_Time = toString(toTimestamp(now()))
Query result
str_Number str_Array str_Boolean str_IPaddr str_Time str_Variant 1
[1, 2, 3]
true
192.168.0.1
2022-08-02T09:15:10.360391000 +0000
1
-
Example 2
data record() | fields result = toString(record(a=true, b=1, c=2.5, d=null, e=record(f=3), g="foo", h=1d, i=toTimestamp(1) ))
Query result
result {"a":true, "b":1, "c":2.5, "d":null, "e":{"f":3}, "g":"foo", "h":"1 days, 00:00:00.000000000", "i":"1970-01-01T01:00:00.000000001 +0100"}
-
Example 3
data record() | fields result = toString(array(1s, 2h, 3d))
Query result
result ["01.000000000", "02:00:00.000000000", "3 days, 00:00:00.000000000"]
-
Example 4
data record() | fields result = toString(array(decodeBase64ToBinary("YWJj")))
Query result
result ["616263"]
toTimeframe
Converts a value to timeframe
if the value is of a suitable type. If the argument is an array
, the element at position 0 is converted.
-
Syntax
toTimeframe(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
timeframe, string, array
yes
-
Example
data record(tf=toTimeframe(toString(timeframe(from:now()-2h, to:now()))))
Query result
tf start tf end 21/08/2023, 09:01:48
21/08/2023, 11:01:48
toTimestamp
Converts a value to timestamp
if the value is of a suitable type. If the argument is an ARRAY, the element at position 0 is converted.
Use asTimestamp(<value>)
function to return if the value is timestamp
or variant<timestamp>
, otherwise NULL.
-
Syntax
toTimestamp(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
timestamp, double, long , string, array
yes
-
Example
... | fields cnv_timestamp = toTimestamp("2022-03-19 09:24:54")
Query result
cnv_timestamp 2022-03-19T09:24:54.000000000Z
radianToDegree
Converts the numeric expression of an angle in radians to an approximately equivalent angle as expressed in degrees. Returns null
if numeric_expr
evaluates to NULL.
-
Syntax
radianToDegree(numeric_expr)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The angle to be converted from radians to degrees.
-
Example
... | limit 1 | fields degrees = radianToDegree(3.19), degrees_null = radianToDegree(5/0)
Query result
degrees degrees_null 182.7735366467326
null
degreeToRadian
Converts the numeric expression of an angle in degrees to an approximately equivalent angle as expressed in radians. Returns null
if numeric_expr
evaluates to NULL.
-
Syntax
degreeToRadian(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The angle to be converted from radians to degrees.
-
Example
... | limit 1 | fields radians = degreeToRadian(3*19), radians_null = degreeToRadian(5/0)
Query result
radians radians_null 0.9948376736367679
null
Converting typed variants
Functions prefixed with as<Type>
will try to cast the input data type to the target but not do any conversions. It will fail for strongly typed fields and produce null for incompatible variants. These functions are applicable after parsing.
...
| parse content, "LD'DQL 'KVP{LD:key'='(LONG:valueLong| BOOLEAN:valueBoolean| [!;]*:valueStr)';'?}:q"
| fields timestamp, asLong(q[workTime])
- asArray
- asBoolean
- asDouble
- asDuration
- asLong
- asString
- asSummaryStats
- asTimeframe
- asTimestamp
- asIp()
encode/decode
The encode/decode functions allow encoding binary data and strings into a string representation, and the opposite way. There are two types of encode/decode functions, BASE64 and BASE16.
-
The BASE64 functions include:
encodeBase64
that encodes a given binary/string to a BASE64 string.decodeBase64ToString
that decodes a given BASE64 string to a plain string.decodeBase64ToBinary
that decodes a given BASE64 string to a binary.
-
The BASE16 (or HEX-encoding) functions include:
encodeBase16
that encodes a given binary/string to a BASE16 string.decodeBase16ToString
that decodes a given BASE16 string to a plain string.decodeBase16ToBinary
that decodes a given BASE16 string to a binary.
-
Syntax
encodeBase64(expression)
decodeBase64ToString(expression)
decodeBase64ToBinary(expression)
encodeBase16(expression)
decodeBase16ToString(expression)
decodeBase16ToString(expression)
-
Parameters: encode
Name Type Mandatory Default Constraint Description expression
string, binary
yes
A string or binary expression to encode.
-
Parameters: decode
Name Type Mandatory Default Constraint Description expression
string
yes
An encoded string that needs to be decoded to a plain string or binary. Retrieves
NULL
if the encoding format does not match the outcome format. -
Example 1
Example BASE64
... | fields encodeBase64("dynatrace"), decodeBase64ToString("ZHluYXRyYWNl"), decodeBase64ToBinary("ZHluYXRyYWNl")
Query result
encodeBase64("dynatrace") decodeBase64ToString("ZHluYXRyYWNl") decodeBase64ToBinary("ZHluYXRyYWNl") ZHluYXRyYWNl
dynatrace
ZHluYXRyYWNl
-
Example 2
Example BASE16 (or HEX-encoding)
... | fields encodeBase16("dynatrace"), decodeBase16ToString("64796E617472616365"), decodeBase16ToBinary("64796E617472616365")
Query result
encodeBase16("dynatrace") decodeBase16ToString("64796E617472616365") decodeBase64ToBinary("64796E617472616365") 64796e617472616365
dynatrace
ZHluYXRyYWNl
toIp
You can use this function to convert an expression to an IP address.
-
Syntax
toIp(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string expression, ip address
yes
The expression to convert an expression to an IP address.
-
Example
data record() | fields r1=toIp("1.1.1.1"), r2=toIp(ip("127.0.0.1"))
Query result
r1 r2 1.1.1.1
127.0.0.1
numberToHexString
Converts a number to a hexadecimal string.
-
Syntax
numberToHexString(expression)
-
Parameters
Name Type Mandatory Default Consraints Descriptiont expression
numeric expression
yes
The numeric expression that will be converted to a hexadecimal string.
-
Example
data record() | fields r1 = numberToHexString(576460752303423487), r2 = numberToHexString(-256), r3 = numberToHexString(2147483648), r4 = numberToHexString(-2147483648)
Query result
r1 r2 r3 r4 7ffffffffffffff
ffffffffffffff00
80000000
ffffffff80000000
hexStringToNumber
Converts a hexadecimal string to a number.
-
Syntax
hexStringToNumber(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string expression
yes
The string expression that will be converted to a number.
-
Example
data record() | fields r1 = hexStringToNumber("0x7FFFFFFFFFFFFFF"), r2 = hexStringToNumber("0X7FFFFFFFFFFFFFF"), r3 = hexStringToNumber("0x7ffffffffffffff"), r4 = hexStringToNumber("7ffffffffffffff")
Query result
r1 r2 r3 r4 576460752303423487
576460752303423487
576460752303423487
576460752303423487
Array functions
Functions related to a collection of items of the same data type stored at adjacent memory locations.
array
Creates an array
from the list of given parameters.
-
Syntax
array(expression, …)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
An element inside the array.
-
Example
In this example, we create an array (
num_array
) containing 15 elements (integers).... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11)
Query result
num_array [2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11]
arraySize
Returns the size of an array.
-
Syntax
arraySize(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and calculate the size of the given array (number of elements in the array).
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_size = arraySize(num_array)
Query result
num_array_size 15
arrayPercentile
Calculates a given percentile of an array.
-
Syntax
arrayPercentile(expression, percentile)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array
yes
The array from which to compute a percentile.
percentile
double, long
yes
The percentile to compute, between 0 and 100.
-
Example
fetch logs | fields timestamp | fieldsAdd arrayPercentile(array(2,4,6,8),(100))
Query result
timestamp arrayPercentile(array(2, 4, 6, 8), 100) 24/08/2023, 14:59:07
8
arraySum
Returns the sum of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
-
Syntax
arraySum(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and calculate the sum of all values in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_values_sum = arraySum(num_array)
Query result
num_array_values_sum 157.0
arrayAvg
Returns the average of an array. Values that are not numeric are ignored. Returns 0 if there is no matching element.
-
Syntax
arrayAvg(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and calculate the average value for all values in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_average = arrayAvg(num_array)
Query result
num_array_average 10.466666666666667
arrayDistinct
Returns the array without duplicates. It sorts numbers in ascending order and strings in lexicographic order.
-
Syntax
arrayDistinct(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and select only the distinct values for all values in the given array.
fetch logs | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_distinct_values = arrayDistinct(num_array)
Query result
num_array_distinct_values [2, 3, 5, 7, 11, 13, 17, 19, 23, 29]
arrayConcat
Concatenates multiple arrays into a single array. This function skips all null values and non-array input parameter values. If no array parameter input is available, it returns null.
-
Syntax
arrayConcat(array, …)
-
Parameters
Name Type Mandatory Default Constraints Description array
array expression
Y
Array expression that should be combined with others.
-
Example
In this example, the order of the query result is dependent on the arrayConcat
field, you can control it by changing the order of the letters in the parentheses.
data record(a=array(1, 2), b=array(3, 4), c=array(5, 6))
| fields y = arrayConcat(a, b, c)
Query result
y |
---|
|
arrayRemoveNulls
Returns the array where NULL elements are removed.
-
Syntax
arrayRemoveNulls(array)
-
Parameters
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
array | array | yes |
-
Example
In this example, we create an array containing 4 numbers and remove NULL values.
data record(a = array(1, 2, 8, NULL, 5)) | fields arrayRemoveNulls(a)
Query result
arrayRemoveNulls(a) [1, 2, 8, 5]
arrayFirst
Returns the first element of an array.
-
Syntax
arrayFirst(arrayName)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and select only the first value in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_first_value = arrayFirst(num_array)
Query result
num_array_first_value 2
arrayLast
Returns the last element of an array.
-
Syntax
arrayLast(array)
-
Example
In this example, we create an array containing 15 numbers and select only the last value in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_last_value = arrayLast(num_array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
Query result
num_array_last_value 11
arrayMax
Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
-
Syntax
arrayMax(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and select the element with the highest value in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_max_value = arrayMax(num_array)
Query result
num_array_max_value 29
arrayMin
Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. Returns NULL if there is no matching element.
-
Syntax
arrayMin(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, we create an array containing 15 numbers and select the element with the lowest value in the given array.
... | fields num_array = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11) | fields num_array_min_value = arrayMin(num_array)
Query result
num_array_min_value 2
arrayReverse
Returns the array with elements in reversed order.
-
Syntax
arrayReverse(array)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
-
Example
In this example, the array returns elements in a reversed order.
data record(a=array(1,3,5,0)) | fields arrayReverse(a)
Query result
arrayReverse(a) 0, 5, 3, 1
arraySort
Returns the array with elements sorted in ascending order by default. If you need to control the order, use the direction
parameter.
-
Syntax
arraySort(array, direction)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
direction
string
no
ascending
possible values: ascending, descending
a sort order
-
Example
In this example, we sort the
integer_set
array in descending order, using thedirection
parameter.data record(integer_set=array(1,3,5,0,22,45)) | fields arraySort(integer_set, direction:"descending")
Query result
arraySort(integer_set, direction:"descending") |
---|
|
arrayIndexOf
Returns position of the first member in the array, which is equal to the given value.
-
Syntax
arrayIndexOf(array, value)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
The array expression in which the value is searched for.
value
expression
yes
The primitive value to search for in the expression.
-
Example
data record() | fields r1 = arrayIndexOf(array(2,1,4,2,3), 2), r2 = arrayIndexOf(array("a", "b", "c"), "b"), r3 = arrayIndexOf(array(1,2,3), "ab")
Query result
r1 r2 r3 0
1
-1
arrayLastIndexOf
Returns position of the last member in the array, which is equal to the given value.
-
Syntax
arrayLastIndexOf(array, value)
-
Parameters
Name Type Mandatory Default Constraints Description array
array
yes
The array expression in which the value is searched for.
value
expression
yes
The primitive value to search for in the expression.
-
Example
data record() | fields r1 = arrayLastIndexOf(array(2,1,4,2,3), 2), r2 = arrayLastIndexOf(array("a", "b", "c"), "b"), r3 = arrayLastIndexOf(array(1,2,3), "ab")
Query result
r1 r2 r3 3
1
-1
String functions
String functions allow you to create expressions that manipulate text strings in a variety of ways.
All string matching functions are case-sensitive per default. If otherwise required, the caseSensitive
parameter provides the ability to change the behavior.
...
| fieldsAdd str_found = contains(content, "FlushCommand", caseSensitive:false)
getCharacter
Returns the character at a given position from a string expression. Negative values for the position parameter are counted from the end of the string. If a position refers to a position outside the string, the function returns NULL.
-
Syntax
getCharacter(expression, position)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
position
long
yes
The position at which to get the character.
-
Example
In this example, we search for the character located on the
-1
position.... | fields getCharacter("012345", -1)
Query result
getCharacter("012345", -1) 5
matchesValue
Searches records for a specific value in a given attribute. Returns true
or false
.
-
Syntax
matchesValue(expression, value [, caseSensitive])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string, array
yes
The expression (string or array of strings) that should be checked.
value
string
yes
The value to search for using patterns.
caseSensitive"=
boolean
no
false
Whether the match should be done case-sensitive
-
Example
In this example, you add a filter record where
process.technology
attribute containsnginx
value.... | filter matchesValue(process.technology, "nginx")
Query result
Examples of event processing using DQL matchesValue function:
Part of the input event Processing query Match result Description attribute="Dynatrace"
matchesValue(attribute, "dynaTrace")
Case insensitive equality.
attribute="User 'käärmanü' failed to login from 192.168.0.1"
matchesValue(attribute, "192.168.0.1")
The whole attribute value is considered.
attribute="User 'käärmanü' failed to login from 192.168.0.1"
matchesValue(attribute, "*192.168.0.1")
The value ends with
192.168.0.1
.attribute="User 'käärmanü' failed to login from 192.168.0.1"
matchesValue(attribute, "user*")
The value starts with
user
(case-insensitively).attribute="User 'käärmanü' failed to login from 192.168.0.1"
matchesValue(attribute, "*failed to log*")
The value contains the string
failed to log
.attribute="Österreich"
matchesValue(attribute, "österreich")
Case insensitive only for ASCII characters.
attribute="Österreich"
matchesValue(attribute, "Österreich")
Exact match.
attribute=["Java", "DOCKER", "k8s"]
matchesValue(attribute, "docker")
The function handles multi-value attributes in "any-match" manner, in this case,
docker
is matched in the second value.attribute=["Java11", "java17"]
matchesValue(attribute, "java")
None of the values is equal to string java.
attribute=["Java11", "java17"]
matchesValue(attribute, "java*")
Both values start with a string
java
.
indexOf
Returns the index of the first occurrence of a substring in a string expression.
Starts to search forward from a given index. Negative values for the from
parameter are counted from the end of the string.
The default value for from
is 0
(the search from the start of the string).
The search is case-sensitive.
If the defined substring is not found, the function returns -1
.
-
Syntax
indexOf(expression, substring [, from])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression in which the substring is searched for.
substring
string
yes
The substring expression to search for in the expression.
from
long
yes
The index from which to start the forward search for the first occurrence of the substring within the expression. Negative values are counted from the end of the string.
-
Example
In this example, we search for the first occurrence of an
ab
substring in theababcd
expression. The count starts from1
.... | fields indexOf("ababcd", "ab", from: 1)
Query result
indexOf("ababcd", "ab", from:1) 2
lastIndexOf
Returns the index of the last occurrence of a substring in a string expression. Starts to search backward from a given index. Negative values for the from parameter are counted from the end of the string. The default value for from is -1 (search from the end of the string). The search is case-sensitive. If the substring is not found, the function returns -1
.
-
Syntax
lastIndexOf(expression, substring [, from])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression in which the substring is searched for.
substring
string
yes
The substring expression to search for in the expression.
from
long
no
-
Example
In this example, we search for the last occurrence of a
cd
substring in theabcdcd
expression. The count starts from-3
.... | fields lastIndexOf("abcdcd", "cd", from: -3)
Query result
lastIndexOf("abcdcd", "cd", from: -3) 2
matchesPhrase
Matches a phrase against the input string expression using token matchers.
-
Syntax
matchesPhrase(<fieldName>, <string>, [caseSensitive])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string, array
yes
The expression (string or array of strings) that should be checked.
phrase
string
yes
The phrase to search for.
caseSensitive
boolean
no
false
Whether the match should be done case-sensitive.
-
Example
In this example, we add a field that checks if field
a
contains the tokenhaproxy
. Unlike thecontains()
function, thematchesPhrase()
function yields true only if the entire token matches.fetch logs | fields a = "2022-09-30T04:42:36Z localhost haproxy[12528]: 10.176.33.178" | limit 1 | fields matchesPhrase(a, "haproxy"), matchesPhrase(a, "ha"), matchesPhrase(a,"10.176")
Query result
matchesPhrase(a, phrase:"haproxy") matchesPhrase(a, phrase:"ha") matchesPhrase(a, phrase:"10.176") true
false
true
contains
Searches the string expression for a substring. Returns true
if the substring was found, false
otherwise.
-
Syntax
contains(expression, substring [, caseSensitive])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The field or expression to check.
substring
string
yes
The substring that should be contained.
-
Example
In this example, we add a field that checks if field
content
contains theFlushCommand
string.... | fieldsAdd str_found = contains(content, "FlushCommand")
Query result
timestamp content event.type str_found 2022-08-03 11:27:19
2022-08-03 09:27:19.836 WARN 1 --- [-QueueProcessor] i.j.internal.reporters.RemoteReporter : FlushCommand execution failed! Repeated errors of this command will not be logged. io.jaegertracing.internal.exceptions.SenderException: Failed to flush spans.
LOG
true
2022-08-03 12:53:26
2022-08-03T10:52:31Z localhost haproxy[12529]: 192.168.19.100:38440 http-in~ individual_servers/apmng6 217/0/0/1/218 HTTP_STATUS 200 284 - - --NN 5749/5745/0/1/0 0/0
LOG
false
startsWith
Checks if a string expression starts with a prefix. Returns true
if does, false
otherwise.
-
Syntax
startsWith(expression, prefix [, caseSensitive])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be checked.
prefix
string
yes
The prefix string with which the expression should start.
caseSensitive
boolean
no
Whether the check should be done in a case-sensitive way.
-
Example
In this example, we test the
logs on Grail
string to see if it begins with thelog
string or theGrail
string. The results are displayed in thestr1_start
andstr2_start
fields.... | fields str1_start = startsWith ("logs on Grail" , "log"), str2_start = startsWith ("logs on Grail", "Grail")
Query result
str1_start str2_start true
false
endsWith
Checks if a string expression ends with a suffix. Returns true
if does, false
otherwise.
-
Syntax
endsWith(expression, suffix [, caseSensitive])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be checked.
suffix
string
yes
The suffix string with which the expression should end.
caseSensitive
boolean
no
Whether the check should be done in a case-sensitive way.
-
Example 1
In this example, we test if the
logs on Grail
string ends with theGrail
string, or if it ends with thelog
string. The results are displayed in thestr1_end
andstr2_end
fields.... | fields str1_end = endsWith ("logs on Grail" , "Grail"), str2_end = endsWith ("logs on Grail", "log")
Query result
str1_end str2_end true
false
-
Example 2
In this example, we filter all records for which the value of
log.source
ends with/pgi.log
.... | filter endsWith(log.source,"/pgi.log")
Query result
timestamp content log.source event.type dt.entity.host dt.entity.process_group_instance 2022-08-11 11:21:26
2022-08-11T09:21:26Z localhost haproxy[12529]: 192.168.33.178:46070 http-in~ local/local0 19/0/1/110/130 HTTP_STATUS 204 64 - - ---- 5753/5749/358/359/0 0/0 {|||us-ea-1-54-88-80-100-DA} {|}
/var/spool/cws/umsaywsjuo/HOST-PS-319/PGI-04050984636/pgi.log
LOG
HOST-PS-319
PGI-04050984636
2022-08-11 11:29:26
2022-08-11T09:29:26Z localhost haproxy[12529]: 192.168.33.178:46070 http-in~ local/local0 19/0/1/110/130 HTTP_STATUS 204 64 - - ---- 5753/5749/358/359/0 0/0 {|||us-ea-1-54-88-80-100-DA} {|}
/var/spool/cws/umsaywsjuo/HOST-PS-319/PGI-0405543/pgi.log
LOG
HOST-PS-319
PGI-0405543
like
Tests if a string expression matches a pattern. If the pattern does not contain percent signs, like()
acts as the ==
operator (equality check). A percent character in the pattern (%)
matches any sequence of zero or more characters. An underscore in the pattern (\_)
matches a single character.
-
Syntax
like(expression, pattern)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
pattern
string
yes
-
Example
... | limit 1 | fields a = "2022-09-30T04:42:36Z localhost haproxy[12528]: 10.176.33.178" | fields like(a,"%10.%"), like(a,"%178"), like(a,"haproxy")
Query result
like(a, "%10.%")_end | like(a, "%178")_end | like(a, "haproxy")_end |
---|---|---|
|
|
|
concat
Concatenates the expressions into a single string.
-
Syntax
concat(expression, …)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long, string
yes
A numeric or string expressions that should be concatenated with others.
-
Example
In this example, we combine the
DQL
,is
, andawesome!
strings in thestr_concatenated
field.... | fields str_concatenated = concat("DQL ", "is ", "awesome!")
Query result
str_concatenated DQL is awesome!
lower
Converts a string to lowercase.
-
Syntax
lower(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to convert to lowercase.
-
Example
In this example, we convert the string
ThiS is a tEsT
to lowercase and place the result in thestr_lower
field.... | fields str_lower = lower("ThiS is a tEsT")
Query result
str_lower this is a test
upper
Converts a string to uppercase.
-
Syntax
upper(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to convert to uppercase.
-
Example
In this example, we convert the string
ThiS is a tEsT
to uppercase and place the result in thestr_upper
field.... | fields str_upper = upper("ThiS is a tEsT")
Query result
str_upper THIS IS A TEST
stringLength
Returns the length of a string expression. Length is defined as the number of UTF-16 code units, which is often the same as the number of characters in the string. In some cases, the number of characters is smaller than the number of UTF-16 code units, for example when Combining Diacritical Marks are used, or if characters outside the Basic Multilingual Plane (BMP), such as Emoji, are present.
If your use case requires consistent length for the same characters, consider ingesting strings after Unicode normalization.
No specific normalization form is guaranteed for Dynatrace-provided strings.
-
Syntax
stringLength(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to get the number of UTF-16 code units for.
-
Example
In this example, we return the length of the
content
field in logs, sorted from shortest to longest log lines.fetch logs | fields content, stringLength(content) | sort stringLength(content) asc
Query result
content stringLength(content) Dec 21 11:38:22 ls-ub-lf40a86v systemd[1]: Finished Message of the Day.
71
Dec 21 11:29:12 ls-ub-lf40a86v systemd[1]: Finished Daily man-db regeneration.
78
substring
Gets a code unit range using a start index (inclusive) and an end index (exclusive).
Returns an empty string if from >=
to.
Indexes >=0
are relative to the start of the string and address consecutive characters from left to right, starting from the index position.
Indexes <=-1
are relative to the last character of the string and are used to address characters from the right side of an expression, for example, -2
is the penultimate character.
Positive indexes
beyond the bounds of the string are assigned to the string length.
Negative indexes
beyond the bounds of the string are equal to 0
. For example, in the 321
string, the index -4
is beyond the bounds of the string therefore it equals 0
. However, the index -2
is located within the bounds of that string and extracts 21
if used as a from
the index.
The returned substring never starts or ends with an incomplete UTF-16 surrogate pair. Instead of that, it starts or ends with a question mark. This safeguards against the creation of invalid Unicode strings.
-
Syntax
substring(expression [, from] [, to])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to get a substring of.
from
long
no
Index of first code unit to include in sub-string, inclusive, relative to start of
expression
if positive, relative to end if negative. Clamped at string bounds.to
long
no
Index of last code unit to include in sub-string, exclusive, relative to start of
expression
if positive, relative to end if negative. Clamped at string bounds. -
Example 1
In this example, we return a substring of
012
starting from a positive index1
located in the first code unit, and a substring of012
starting from a negative index,-2
, located in the penultimate code unit.... | fields substring("012", from: 1), substring("012", to: -2)
Query result
substring("012", from: 1) substring("012", to: -2) 12
0
-
Example 2
In this example, we return substrings from expressions containing special characters (non-BMP characters). For example, the Mahjong Tile Summer character (HTML entity
🀧
), is returned as a question mark.... | fields substring("<special_character>abc", from: 1), substring("<special_character>abc", to: -2), substring("<special_character>abc", from: 2, to: -2)
Query result
substring("<special_character>abc", from: 1) substring("<special_character>abc", to: -2) substring("<special_character>abc", from: 2, to: -2) ?abc
\<special\_character>a
a
splitString
Splits a string according to the parameters set.
Retrieves an array of substrings of the specified expression that are adjacent to occurrences of the given pattern.
Parameters are interpreted literally. For example, splitting www.dynatrace.org
by .
results in www
and dynatrace
and org
.
Using an empty string as a pattern splits the string into one-byte substrings. For example, a split of four characters becomes an array of four strings having one byte each (splitting the "1234"
expression results in array("1", "2", "3", "4")
).
The non-ASCII characters are represented by multiple bytes. Splitting a string containing such characters by ""
breaks these bytes apart into separate invalid strings.
If the pattern is not found in the expression, it returns an array that contains only the input expression.
If the expression starts with one or more occurrences of the pattern, an empty string will be added for each occurrence. For example, split("abc", "a")
results in "", "bc"
. Analogically, empty strings are added if the pattern is found at the end of the expression.
An empty string is also added for adjacent occurrences of the pattern that do not border the start or end of the string. For example, split("abbc", "b")
results in "a", "", "c"
.
If the pattern is empty, it splits the expression into one-byte substrings. For example, split("abc", "")
results in "a", "b", "c"
.
-
Syntax
splitString(expression, pattern)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to split up into an array.
pattern
string
yes
The pattern to split the string expression at, or the empty string to split into one-byte strings.
-
Example 1
This example illustrates the scenarios listed in the bullet points above.
... | fields a=splitString("abc", "a"), b=splitString("abbc", pattern: "b"), c=splitString("abc", "")
Query result
a b c ,bc
a, , c
a,b,c
-
Example 2
In this example, we split the log content using the string expression
" "
. The split is triggered when a space is found.fetch logs | fields content, splitString(content, " ")
Query result
content splitString(content, " ") [2022-12-21 14:15:59.102 UTC] [/rework/loggroupinstance.cpp] [debug] /home/labuser/.dynaTrace/easyTravel 2.0.0/easyTravel/tmp/chrome/184e7c31cbc/Default/chrome_debug.log isn't valid for reading, current status: FILE_STATUS_NOT_EXIST
[2022-12-21
14:15:59.102
UTC]
[/rework/loggroupinstance.cpp]
[debug]
/home/labuser/.dynaTrace/easyTravel
2.0.0/easyTravel/tmp/chrome/184e7c31cbc/Default/chrome_debug.log
isn't
valid
for
reading,
current
status:
FILE_STATUS_NOT_EXIST
replaceString
Replaces each substring of a string with a given string. This function replaces only exactly matched substrings from the original string to the replacement. Matching is case-sensitive and doesn't use any wildcards. All found patterns will be replaced if they do not intersect. For instance, replacing abcabca
in a string with abca
pattern produces only one replacement. Only the first occurrence at the beginning of the string will be replaced.
-
Syntax
replaceString(expression, substring, replacement)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The field or expression where substrings should be replaced.
substring
string
yes
The substring that should be replaced.
replacement
string
yes
The string that should replace the found substrings.
-
Example 1
In this example, we replace all occurrences of
this`` with
that`.data record() | fields replaceString("replace this and this", "this", "that")
Query result
replaceString("replace this and this", "this", "that") replace that and that
-
Example 2
In this example, we demonstrate that the wild card symbol can't be used.
data record() | fields replaceString("replacement", "repl*", "REPL")
Query result
replaceString("replacement", "repl*", "REPL") replacement
trim
Removes leading and trailing whitespaces. Any code point <= ASCII 32 in decimal is considered a whitespace, where ASCII 32 is a blank space.
-
Syntax
trim(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression to remove leading and trailing white-space from.
-
Example
In this example, we compare the length of the untrimmed and trimmed
content
field in logs. The untrimmed string is thecontent
field concatenated with a leading and a trailing space.fetch logs | fieldsAdd untrimmed = concat(" ", content, " ") | fields content, untrimmed, stringLength(untrimmed), trim(untrimmed), stringLength(trim(untrimmed))
Query result
content untrimmed stringLength(untrimmed) trim(untrimmed) stringLength(trim(untrimmed)) 2022-12-21 08:50:01.207 UTC \[001a71fa] info \[native] 139988444554816(ThreadPoolExecutor-0\_0) - \[asynchronous\_strategy] Not running containters
2022-12-21 08:50:01.207 UTC \[001a71fa] info \[native] 139988444554816(ThreadPoolExecutor-0\_0) - \[asynchronous\_strategy] Not running containters
147
2022-12-21 08:49:01.111 UTC \[001a7212] info \[native] 139988436162112(ThreadPoolExecutor-0\_1) - \[asynchronous\_strategy] Not running containters
145
Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...
Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...
75
Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...
73
punctuation
Extracts punctuation characters out of an input string.
-
Syntax
punctuation(expression, [, count] [, withSpace])
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression from which the punctuation characters are extracted.
count
positive integer
no
32
The maximum number of returned punctuation characters.
withSpace
boolean
no
false
Whether space characters should be included.
-
Example
In this example, we extract the punctuation characters from each input string.
fetch logs | fields text1 = punctuation("$(my variable);"), text2 = punctuation("$(my variable);", count: 2), text3 = punctuation("$(my variable);", withSpace: true), text4 = punctuation("$(my variable);", count: 3, withSpace: true) | limit 1
Query result
text1 text2 text3 text4 $();
$(
$(_);
$(_
levenshteinDistance
Computes the Levenshtein distance between two input strings.
-
Syntax
levenshteinDistance(expression, expression)
-
Parameters
Name Type Mandatory Default Constraints Description first expression
string
yes
The first string expression to compute the Levenshtein distance from.
second
string
yes
The second string expression to compute the Levenshtein distance from.
-
Example
In this example, we calculate the Levenshtein distance between the input strings
gray
-grey
andkitten
-sitting
.fetch logs | fields distance1 = levenshteinDistance("gray", "grey"), distance2 = levenshteinDistance("kitten", "sitting") | limit 1
Query result
distance1 distance2 1
3
Time functions
Time functions return the decimal number for a particular time value, calculate the number of time units (days, months, years) between two dates, and allow to determine timestamps and timeframes, among others.
formatTimestamp
Formats a given timestamp according to a format string using a given pattern. Timestamps according to the ISO 8601 standard can be parsed and converted to the timestamp datatype. The function is using the Java DateTime Formatter and supports the consecutive formatting patterns and symbols.
-
Syntax
formatTimestamp(timestamp [, interval] [, format])
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
interval
duration
no
format
string
no
-
Example
In this example, a string according to the ISO 8601 standard is converted to the timestamp datatype. Then, the
formatTimestamp
function is used to format the given timestamp into the desired output, based on the used symbol.... | limit 1 | fields t = toTimestamp("2022-10-12T21:52:37Z") | fieldsAdd formatted = formatTimestamp(t, format:"MM-dd-YYYY"), year = formatTimestamp(t, format:"Y"), month = formatTimestamp(t, format:"M"), week = formatTimestamp(t, format:"w"), dayofWeek = formatTimestamp(t, format:"E"), hour = formatTimestamp(t, format:"H")
Query result
t formatted year month week dayofWeek hour 2022-10-12T21:52:37.000000000Z
10-12-2022
2022
10
42
Wed
21
timeframe
Creates a timeframe structure from the given start and end timestamps.
-
Syntax
formatTimestamp(timestamp [, interval] [, format])
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
-
Example
... | limit 1 | fields tf=timeframe(from:now()-5m, to:now())
Optionally, you can use the following syntax, where duration is automatically interpreted as an offset of the
now()
function.... | limit 1 | fields tf=timeframe(from:-5m, to:now())
Query result
tf {"start":"2022-10-07T05:44:20.256891000Z","end":"2022-10-07T05:49:20.256891000Z"}
timestampFromUnixMillis
Creates a timestamp from the given milliseconds since Unix epoch.
-
Syntax
timestampFromUnixMillis(millis)
-
Parameters
Name Type Mandatory Default Constraints Description millis
long
yes
Milliseconds since unix start time.
-
Example
fetch bizevents | limit 1 | fields t_ms = timestampFromUnixMillis(1665064316000)
Query result
t_ms 06/10/2022, 15:51:56
timestampFromUnixNanos
Creates a timestamp from the given nanoseconds since Unix epoch.
-
Syntax
timestampFromUnixNanos(nanos)
-
Parameters
Name Type Mandatory Default Constraints Description nanos
long
yes
Nanoseconds since unix start time.
-
Example
fetch bizevents | limit 1 | fields t_ns = timestampFromUnixNanos(123456760000)
Query result
t_ns 01/01/1970, 01:02:03
timestampFromUnixSeconds
Creates a timestamp from the given seconds since Unix epoch.
-
Syntax
timestampFromUnixSeconds(seconds)
-
Parameters
Name Type Mandatory Default Constraints Description seconds
long
yes
Seconds since unix start time.
-
Example
fetch bizevents | limit 1 | fields t_s = timestampFromUnixSeconds(1665064316)
Query result
t_s 06/10/2022, 15:51:56
unixNanosFromTimestamp
Converts a timestamp into nanoseconds since Unix epoch.
-
Syntax
unixNanosFromTimestamp(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression which will be converted to nanoseconds since Unix epoch.
-
Example
fetch logs | fields unixNanosFromTimestamp(timestamp) | limit 1
Query result
unixNanosFromTimestamp(timestamp) 1687773662000000000
unixMillisFromTimestamp
Converts a timestamp into milliseconds since Unix epoch.
-
Syntax
unixMillisFromTimestamp(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression which will be converted to milliseconds since Unix epoch.
-
Example
fetch logs | fields unixMillisFromTimestamp(timestamp) | limit 1
Query result
unixMillisFromTimestamp(timestamp) 1,687,773,528,000
unixSecondsFromTimestamp
Converts a timestamp into seconds since Unix epoch.
-
Syntax
unixSecondsFromTimestamp(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression which will be converted to seconds since Unix epoch.
-
Example
fetch logs | fields unixSecondsFromTimestamp(timestamp) | limit 1
Query result
unixSecondsFromTimestamp(timestamp) 1,687,773,709
getDayOfMonth
Extracts the day of the month from a timestamp.
-
Syntax
getDayOfMonth(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the day of the month will be extracted.
-
Example
fetch logs | fields getDayOfMonth(timestamp) | limit 1
Query result
getDayOfMonth(timestamp) 23
getDayOfWeek
Extracts the day of the week from a timestamp.
-
Syntax
getDayOfWeek(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the day of the week will be extracted.
-
Example
fetch logs | fields getDayOfWeek(timestamp) | limit 1
Query result
getDayOfWeek(timestamp) 5
getDayOfYear
Extracts the day of the year from a timestamp.
-
Syntax
getDayOfYear(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the day of the year will be extracted.
-
Example
fetch logs | fields getDayOfYear(timestamp) | limit 1
Query result
getDayOfYear(timestamp) 174
getHour
Extracts the hour from a timestamp.
-
Syntax
getHour(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the hour will be extracted.
-
Example
fetch logs | fields getHour(timestamp) | limit 1
Query result
getHour(timestamp) 13
getMinute
Extracts the minute from a timestamp.
-
Syntax
getMinute(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp expression
yes
The timestamp expression from which the minute will be extracted.
-
Example
fetch logs | fields getMinute(timestamp) | limit 1
Query result
getMinute(timestamp) 24
getSecond
Extracts the second from a timestamp.
-
Syntax
getSecond(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the second will be extracted.
-
Example
fetch logs | fields getSecond(timestamp) | limit 1
Query result
getSecond(timestamp) 48
getYear
Extracts the year from a timestamp.
-
Syntax
getYear(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the year will be extracted.
-
Example
fetch logs | fields getYear(timestamp) | limit 1
Query result
getYear(timestamp) 2,023
getWeekOfYear
Extracts the week of the year from a timestamp.
-
Syntax
getWeekOfYear(expression)
-
Parameters
Name Type Mandatory Default Constraints Description timestamp
timestamp
yes
The timestamp expression from which the week of the year will be extracted.
-
Example
fetch logs | fields getWeekOfYear(timestamp) | limit 1
Query result
getWeekOfYear(timestamp) 25
Bitwise functions
bitwiseAnd
Calculates the bitwise and
between two long expressions.
-
Syntax
bitwiseAnd(long expression, long expression)
-
Parameters
Name Type Mandatory Default Constraints Description firstExpression
long expression
yes
The first long expression for the binary operation.
secondExpression
long expression
yes
The second long expression for the binary operation.
-
Example
data record() | fields bitwiseAnd(1, 1), bitwiseAnd(1, 0), bitwiseAnd(0, 0), bitwiseAnd(1, 2)
Query result
bitwiseAnd(1, 1) bitwiseAnd(1, 0) bitwiseAnd(0, 0) bitwiseAnd(1, 2) 1
0
0
0
bitwiseOr
Calculates the bitwise or
between two long expressions.
- Syntax
bitwiseOr(long expression, long expression)
-
Parameters
Name Type Mandatory Default Constraints Description firstExpression
long expression
yes
The first long expression for the binary operation.
secondExpression
long expression
yes
The second long expression for the binary operation.
-
Example
data record() | fields bitwiseOr(1, 0), bitwiseOr(0, 0), bitwiseOr(1, 1)
Query result
bitwiseOr(1, 0) bitwiseOr(0, 0) bitwiseOr(1, 1) 1
0
1
bitwiseXor
Calculates the bitwise xor
between two long expressions.
-
Syntax
bitwiseXor(long expression, long expression)
-
Parameters
Name Type Mandatory Default Constraints Description firstExpression
long expression
yes
The first long expression for the binary operation.
secondExpression
long expression
yes
The second long expression for the binary operation.
-
Example
data record() | fields bitwiseXor(0, 0), bitwiseXor(1, 0), bitwiseXor(1, 1), bitwiseXor(9223372036854775807, 1)
Query result
bitwiseXor(0, 0) bitwiseXor(1, 0) bitwiseXor(1, 1) bitwiseXor(1, 1) 0
1
0
9223372036854775806
bitwiseNot
Inverts the bits included in the long expression.
-
Syntax
bitwiseNot(long expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
long expression
yes
The long expression whose bits will be inverted.
-
Example
data record() | fields bitwiseNot(1), bitwiseNot(0), bitwiseNot(9223372036854775807), bitwiseNot(-9223372036854775808)
Query result
bitwiseNot(1) bitwiseNot(0) bitwiseNot(9223372036854775807) bitwiseNot(-9223372036854775808) -2
-1
-9223372036854775808
9223372036854775807
bitwiseCountOnes
Counts the bits assigned to one of the long expressions.
-
Syntax
bitwiseCountOnes(long expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
long expression
yes
The long expression whose bits will be inverted.
-
Example
data record() | fields bitwiseCountOnes(1), bitwiseCountOnes(-1), bitwiseCountOnes(9223372036854775807), bitwiseCountOnes(-9223372036854775807)
Query result
bitwiseCountOnes(1) bitwiseCountOnes(-1) bitwiseCountOnes(9223372036854775807) bitwiseCountOnes(-9223372036854775807) 1
64
63
2
bitwiseShiftLeft
Shifts the long expressions by the number of given bits to the left.
-
Syntax
bitwiseShiftLeft(long expression, long expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
long expression
yes
The long expression that will be bitwise shifted to the left.
numberOfBits
long expression
yes
The number of bits by which the expression will be shifted left.
-
Example
data record() | fields bitwiseShiftLeft(9223372036854775807, 1), bitwiseShiftLeft(-9223372036854775808, 1)
Query result
bitwiseShiftLeft(9223372036854775807, 1) bitwiseShiftLeft(-9223372036854775808, 1) -2
0
bitwiseShiftRight
Shifts the long expression by number of given bits to the right. It has an optional parameter ignoreSign
, that defines, if the sign bit should be ignored. If the parameter is false, it can be compared to >>
in Java, otherwise to >>>
.
-
Syntax
bitwiseShiftRight(long expression, long expression, ignoreSign: boolean)
-
Parameters
Name Type Mandatory Default Constraints Description expression
long expression
yes
The long expression that will be bitwise shifted right.
numberOfBits
long expression
yes
The number of bits by which the expression will be shifted right.
ignoreSign
boolean expression
no
false
The boolean expression that indicates if the sign bit should be ignored (treated like any bit) while shifting, If false, the sign bit is preserved and just the other bits are shifted.
-
Example
data record() | fields bitwiseShiftRight(-8, 2, ignoreSign: false), bitwiseShiftRight(-2, 1, ignoreSign: false), bitwiseShiftRight(9223372036854775807, 1, ignoreSign: false)
Query result
bitwiseShiftRight(-8, 2, ignoreSign: FALSE) bitwiseShiftRight(-2, 1, ignoreSign: FALSE) bitwiseShiftRight(9223372036854775807, 1, ignoreSign: FALSE) -2
-1
4611686018427387903
Mathematical functions
power
Raises a numeric expression to a given power.
-
Syntax
power(base, exponent)
-
Parameters
Name Type Mandatory Default Constraints Description base
double, long
yes
The numeric expression acting as the base of the power calculation.
exponent
double, long
yes
The numeric expression acting as the exponent of the power calculation.
-
Example
In this example, we raise
2
to3rd
power.| fields power(2,3)
Query result
power(2,3) 8
exp
Calculates the exponential function e^x
, where e
is the Euler's number and x
is a numeric expression.
-
Syntax
exp(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the exponential function.
-
Example
In this example, we display the exponential function of
1
.| fields exp(1)
Query result
exp(1) 2.718281828459045
sqrt
Computes the positive square root of a numeric expression.
-
Syntax
sqrt(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the square root.
-
Example
In this example, we display the square root of
123.45
in thesqrt_value_1
field and the square root of a result of a mathematical expression in thesqrt_value_2
field.... | fields sqrt_value_1 = sqrt(123.45), sqrt_value_2 = sqrt(123.45 + 67.89 * 321)
Query result
sqrt_value_1 sqrt_value_2 11.110805551354051
148.0410078322895
cbrt
Calculates the real cubic root of a numeric expression.
-
Syntax
cbrt (numeric_expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the real cubic root.
-
Example
In this example, we display the real cubic root of
-8
.| fields cbrt(-8)
Query result
cbrt(-8) -2
log
Calculates the natural logarithm (the base is e
, the Euler's number) of a numeric expression.
-
Syntax
log(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the natural logarithm (base e).
-
Example
In this example, we display the natural logarithm of
e()
.| fields log(e())
Query result
log(e()) 1
log1p
Calculates log(1+x), where log
is the natural logarithm and x
is a numeric expression.
-
Syntax
log1p(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to add one and calculate the natural logarithm (base e).
-
Example
In this example, we display the natural logarithm of
1
usinglog1p(0)
.| fields log1p(0)
Query result
log1p(0) 0
log10
Calculates the decadic (common) logarithm (the base is 10) of a numeric expression.
-
Syntax
log10(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the decadic logarithm (base 10).
-
Example
In this example, we display the decadic (common) logarithm of
100
.| fields log10(100)
Query result
log10 2
sin
Computes the trigonometric sine of angle <expression>
(in radians). Returns null
if <expression>
evaluates to NULL.
-
Syntax
sin(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the sin.
-
Example
... | fields sine = sin(19.71)
Query result
sine 0.758132221587245
sinh
Computes the hyperbolic sine of <expression>
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
sinh(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the sinh.
-
Example
... | fields hyperbolic_sine = sinh(19.71)
Query result
hyperbolic_sine 181515719.99114084
asin
Computes arc sine of expression
. The returned angle is in the range -pi/2
through pi/2
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
asin(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the asin.
-
Example
... | fields arc_sine = asin(.71)
Query result
arc_sine 0.7894982093461719
cos
Computes the trigonometric cosine of an angle expression
(in radians). Returns null
if expression
evaluates to NULL.
-
Syntax
cos(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the sin.
-
Example
... | fields cosine = cos(19.71)
Query result
cosine 0.6521008622837332
cosh
Computes the hyperbolic cosine of an angle <expression>
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
cosh(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the cosh.
-
Example
... | fields hyperbolic_cosine = cosh(19.71)
Query result
hyperbolic_cosine 181515719.99114084
acos
Computes arc cosine of expression
. The returned angle is in the range 0.0
through pi. Returns null
if expression
evaluates to NULL.
-
Syntax
acos(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the acos.
-
Example
... | fields arc_cosine = acos(.71)
Query result
arc_cosine 0.7812981174487247
tan
Computes the trigonometric tangent of angle expression
(in radians). Returns null
if expression
evaluates to NULL.
-
Syntax
tan(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the tan.
-
Example
... | fields tangent = tan(19.71)
Query result
tangent 1.1625996305727577
tanh
Computes the hyperbolic tangent of expression
. Returns null
if expression
evaluates to NULL.
-
Syntax
tanh(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the tanh.
-
Example
... | fields hyperbolic_tangent = tanh(.71)
Query result
hyperbolic_tangent 0.6106768328168443
atan
Computes the arc tangent of expression
. The returned angle is in the range -p/2
through pi/2
. Returns null
if expression
evaluates to NULL.
-
Syntax
atan(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression, angle in radians for which to calculate the atan.
-
Example
... | fields arc_tangent = atan(.71)
Query result
arc_tangent 0.6174058917515727
atan2
Computes the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta). Returns null
if either of the expressions evaluates to NULL.
-
Syntax
atan2(ordinate, abscissa)
-
Parameters
Name Type Mandatory Default Constraints Description ordinate
double, long
yes
The ordinate coordinate.
abscissa
double, long
yes
The abscissa coordinate.
-
Example
... | fields angle_theta = atan2(19.71,20.06)
Query result
angle_theta 0.7765977871210077
hypotenuse
Returns sqrt(x^2 + y^2)
. Returns null
if expression
evaluates to NULL.
-
Syntax
hypotenuse(x, y)
-
Parameters
Name Type Mandatory Default Constraints Description x
double, long
yes
Length of the first of the catheti.
-
Example
... | fields hypotenuse = hypotenuse(19.71,20.06)
Query result
hypotenuse 28.122725685822132
pi
Returns the constant value of PI (Archimedes’ number).
-
Syntax
pi()
-
Example
In this example, we display the circumference of a circle with a radius of
19.71
in thecircumference
field.... | fields circumference = 2 * pi( ) * 19.71
Query result
circumference 123.84158240450965
e
Returns Euler’s number.
-
Syntax
e()
-
Example
In this example, we display the value of Euler's number in the
eNumber
field.... | fields eNumber = e()
Query result
eNumber 2.718281828459045
round
Rounds any numeric value to the specified number of decimal places. If you don't specify the number of decimal places, it rounds to the nearest integer. The return type is of the same type as the input parameter.
-
Syntax
round(expression [, decimals])
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
Numeric expression to be rounded.
decimals
long
no
Number of places after the decimal point.
-
Example
... | fields pi(), round(10-7), round(pi()), round(pi(), decimals:2), round(pi(), decimals:3)
Query result
pi() round(10-7) round(pi()) round(pi(), decimals:2) round(pi(), decimals:3) 3.141592653589793
3
3.0
3.14
3.142
abs
Returns the absolute value of numeric_expression
. Returns NULL if numeric_expression
evaluates to NULL.
-
Syntax
abs(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the absolute value.
-
Example
... | fields abs_1 = abs(pi()), abs_2 = abs(-34), abs_3 = abs(5/0)
Query result
abs_1 abs_2 abs_3 3.141592653589793
34
null
ceil
Calculates the smallest (closest to negative infinity) double
value greater than or equal to the numeric_expression
; is equal to a mathematical integer. Returns null
if numeric_expression
evaluates to NULL. The return type is of the same type as the input parameter.
-
Syntax
ceil(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression to be rounded up.
-
Example
... | fields ceil_1 = ceil(pi()), ceil_2 = ceil(10-6), ceil_3 = ceil(5/0)
Query result
ceil_1 ceil_2 ceil_3 4.0
4
null
floor
Calculates the largest (closest to positive infinity) double
value less than or equal to the numeric_expression
; and is equal to a mathematical integer. Returns NULL if numeric_expression
evaluates to NULL. The return type is of the same type as the input parameter.
-
Syntax
floor(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression to be rounded down.
-
Example
... | fields floor_1 = floor(pi()), floor_2 = floor(10-7), floor_3 = floor(5/0)
Query result
floor_1 floor_2 floor_3 3.0
3
null
signum
Returns the signum
(sign) result of an argument. It returns one of four possible values: -1
(if numeric_expression
evaluates to a value less than 0
), 0
(if numeric_expression
evaluates to 0
), 1
(if numeric_expression
evaluates to a value greater than 0
), or null
(if numeric_expression
evaluates to NULL).
The return type is of the same type as the input parameter.
-
Syntax
signum(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
double, long
yes
The numeric expression for which to calculate the signum.
-
Example
... | fields signum_1 = signum(3-40), signum_2 = signum(3-3), signum_3 = signum(3*40), signum_4 = signum(3/0), signum_5 = signum(-3.0)
Query result
signum_1 signum_2 signum_3 signum_4 signum_5 -1
0
1
null
-1.0
Hash functions
Hash related functions.
hashMd5
Computes the MD5 hash for a given string expression.
Syntax
hashMd5(expression)
Parameters
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string | yes | The expression from which the MD5 hash needs to be computed. |
-
Example 1
fetch logs | fields hashMd5(host.name)
Query result
hashMd5(host.name) 123abc4567cc1234567890cf123cbc4f567e89dc
-
Example 2
fetch logs | fields hash=hashMd5("test")// hash = "098f6bcd4621d373cade4e832627b4f6"
Query result
hash=hashMd5("test") 098f6bcd4621d373cade4e832627b4f6
hashSha1
Computes the SHA-1 hash for a given string expression.
- Syntax
hashSha1(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The expression from which the SHA-1 hash needs to be computed.
-
Example 1
fetch logs | fields hashSha1(host.name)
Query result
hashSha1(host.name) ca1db2fd3456789c0d12e345e678a9fff1234567
-
Example 2
fetch logs | fields hash=hashSha1("test")// hash = "a94a8fe5ccb19ba61c4c0873d391e987982fbbd3"
Query result
hashSha1("test") a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
hashSha256
Returns a SHA-256 hash for the given expression.
-
Syntax
hashSha256(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be hashed.
-
Example
fetch logs | fields hash = hashSha256("example")
Query result
hash 5ac4f2e8224c55b4356c6709a117cdfd93faa9f3be07d9aea8c51064fedc2140
hashSha512
Returns a SHA-512 hash for the given expression.
-
Syntax
hashSha512(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be hashed.
-
Example
fetch logs | fields hash = hashSha512("example")
Query result
hash f0f12c26c50172cca9582c4c7fc44e9790020796326a08a77c73f7303a461224fda48a0071149ab415ac88789bf640771e4d301eaf1395d34e166114e78115a6
IP functions
Functions related to IP.
isIp
This set of functions can be used to check if an expression is an IP address. The expression can either be a string or an IP address.
- Syntax
isIp(expression)
isIpV4(expression)
isIpV6(expression)
- Parameters
isIp
This function will return true if the given expression is an IP address (no distinction between v4/v6), otherwise false.
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string, ip | yes | The expression to check if it contains/produces an IPv4/v6 address. |
isIpV4
This function will return true if the given expression is an IPv4 address, otherwise false.
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string, ip | yes | The expression to check if it contains/produces an IPv4 address. |
isIpV6
This function will return true if the given expression is an IPv6 address, otherwise false.
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string, ip | yes | The expression to check if it contains/produces an IPv6 address. |
- Example 1
In this example, we check if the given expression is a valid IP address.
data record()
| fields ipv4_correct=isIp("127.0.0.1"), ipv4_wrong=isIp("127.0.0."), ipv6_correct=isIp("2001:0db8:85a3:0000:0000:8a2e:0370:7334"), ipv6_wrong=isIp("2001:0db8:85a3:0000:0000:")
Query result
Ipv4_correct | Ipv4_wrong | Ipv6_correct | Ipv6_wrong |
---|---|---|---|
|
|
|
|
- Example 2
In this example, we check if the given expression is an IPv4 address.
data record()
| fields ipv4_correct_string=isIpV4("127.0.0.1"), ipv4_correct_ip=isIpV4(toIp("127.0.0.1")), ipv4_wrong_string=isIpV4("127.0.0.")
Query result
Ipv4_correct_string | Ipv4_correct_Ip | Ipv4_wrong_string |
---|---|---|
|
|
|
- Example 3
In this example, we check if the given expression is an IPv6 address.
data record()
| fields ipv6_correct_string=isIpV6("2001:0db8:85a3:0000:0000:8a2e:0370:7334"), ipv6_correct_ip=isIpV6(toIp("::1")), ipv6_wrong_string=isIpV6("2001:0db8:85a3:0000:0000:")
Query result
Ipv6_correct_string | Ipv6_correct_Ip | Ipv6_wrong_string |
---|---|---|
|
|
|
ipMask
This function can be used to mask an expression with given bits. The expression can be either a string or an IP address.
The second parameter, which specifies the mask bits, is applied to IP addresses with versions 4 and 6 if the optional parameter (ipv6MaskBits) is not set.
The optional parameter can be used to specify mask bits only for ipv6 addresses. If set, the mandatory mask bits will then be applied to ipv4 addresses.
- Syntax
ipMask(expression, maskBits [, ipv6MaskBits])
- Parameters
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string, ip | yes | The expression which should be masked. | ||
maskBits | number | yes | The maskBits that should be used to mask IPv4/v6 addresses. | ||
ipv6MaksBits | number | no | The ipv6MaskBits that should be used to mask IPv4 addresses. If it is set, the maskBits will only apply to IPv4 addresses |
- Example 1
In this example, the function returns the IP address where the mask bits were applied.
data record()
| fields ipv4_mask=ipMask("127.1.2.3", 8), ipv6_mask=ipMask("2001:0db8:85a3:0000:0000:8a2e:0370:7334", 16), ipv_mask_1=ipMask("127.1.2.3", 16, ipv6MaskBits: 32)
| fieldsAdd ipv_mask_2=ipMask("2001:0db8:85a3:0000:0000:8a2e:0370:7334", 16, ipv6MaskBits: 32)
Query result
Ipv4_mask | Ipv6_mask | Ipv_mask_1 | Ipv_mask_2 |
---|---|---|---|
|
|
|
|
-
Example 2
data record() | fields ipv4_mask=ipMask("127.1.2.3", 8) | fieldsAdd ipv6_mask=ipMask("2001:0db8:85a3:0000:0000:8a2e:0370:7334", 16, ipv6MaskBits: 32)
Query result
Ipv4_mask Ipv6_mask 127.0.0.0
2001:0db8::0000
ipIn
This function can be used to check if a list of IP addresses or an IP network (e.g. 127.0.0.1/8) contains particular IP addresses. An IP address expression can either be a type of string or an IP address.
-
Syntax
ipIn(needle_expressions, haystack_expressions...)
ipIsPrivate(expression)
ipIsLinkLocal(expression)
ipIsLoopback(expression)
ipIsPublic(expression)
-
Parameters
ipIn
The function returns a Boolean which indicates if at least one IP address of the first parameter can be found in the following ones - the same behavior as the in() function.
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
needle_expressions | string, ip, array of string expressions/IP addresses | yes | Represents the IP addresses that needs to be found in the haystack_expressions. | ||
haystack_expressions | string, ip, array of string expressions/ip addresses | yes | The variable argument list where the needle_expressions should be found. |
ipIsPrivate(), ipIsLinkLocal(), ipIsLoopback(), ipIsPublic()
This function will return true if the given expression is a private/link-local/loopback/public address, otherwise false.
Name | Type | Mandatory | Default | Constraints | Description |
---|---|---|---|---|---|
expression | string, ip | yes | The expression to check if it is a particular type of IP address. |
-
Example 1
data record() | fields r1_correct=ipIn("127.0.0.1", "127.0.0.1"), r2_correct=ipIn("127.0.0.1", "127.0.0.1/8"), r3_correct=ipIn(toIp("127.0.0.1"), toIp("127.0.0.1")) | fieldsAdd r4_wrong=ipIn("127.0.0.1/8", "127.0.0.1/16"), r5_wrong=ipIn("127.0.0.1/8", "127.0.0.1"), r6_correct=ipIn(toIp("127.0.0.1"), "127.0.0.1/8") | fieldsAdd r7_correct=ipIn(array("127.0.0.1", toIp("10.0.0.1")), "127.0.0.1"), r8_correct=ipIn(array("127.0.0.1", toIp("10.0.0.1")), array("127.0.0.1/8", toIp("10.0.0.2")))
Query result
r1_corect r2_corect r3_corect r4_corect r5_corect r6_corect r7_corect r8_corect true
true
true
false
false
true
true
true
-
Example 2
data record() | fields correct1=ipIn("127.0.0.1", "127.0.0.1/8"), wrong=ipIn("127.0.0.1/8", "127.0.0.1") | fieldsAdd correct2=ipIn(array("127.0.0.1", toIp("10.0.0.1")), "127.0.0.1")
Query result
corect1 wrong correct2 true
false
true
ip
You can use this function to create an IP address.
- Syntax
ip(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The expression to create a new IP address.
-
Example
In this example, we can use strings to create IP addresses.
data record()
| fields ip1="127.0.0.1", ip2=toVariant("10.0.0.1")
| fieldsAdd r1=ip("1.1.1.1"), r2=ip(ip1), r3=ip(ip2)
Query result
Ip1 | Ip2 | r1 | r2 | r3 |
---|---|---|---|---|
|
|
|
|
|
IP address mathematical operations
-
IP address + numeric
-
IP address + IP address
-
Example 1
data record() | fields r1=toIp("127.0.0.10")+toIp("127.0.0.10"), r2=toIp("127.0.0.10")+1
Query result
r1 r2 254.0.0.20
127.0.0.11
-
IP address - numeric
-
IP address - IP address
-
Example 2
data record() | fields r1=toIp("127.0.0.10")-toIp("10.0.0.1"), r2=toIp("127.0.0.10")-1
Query result
r1 r2 117.0.0.9
127.0.0.9
Other functions
encodeUrl
Encodes a URL string by replacing characters that aren't numbers or letters with percentage symbols and hexadecimal numbers.
-
Syntax
encodeUrl(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be encoded.
-
Example
fetch logs | fields encodedUrl = encodeUrl("https://mywebsite.com?param=first parameter") | limit 1
Query result
encodedUrl https%3A%2F%2Fmywebsite.com%3Fparam%3Dfirst+parameter
decodeUrl
Returns a URL-decoded string.
-
Syntax
decodeUrl(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be decoded.
-
Example
fetch logs | fields decodedUrl = decodeUrl("https://mywebsite.com?param=first parameter") | limit 1
Query result
decodedUrl https://mywebsite.com?param=first parameter
unescapeHtml
Unescapes HTML in a string by replacing ASCII characters with HTML syntax.
-
Syntax
unescapeHtml(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
string
yes
The string expression that will be unescaped.
-
Example
fetch logs | fields html = unescapeHtml("<h2 title="I'm a header">The title Attribute</h2>") | limit 1
Query result
html <h2 title="I'm a header">The title Attribute</h2>
bin
Rounds values down to a multiple of a given numeric bin
size.
Used frequently in combination with summarize , by: ....
. If it encounters a scattered set of values, they will be grouped into a smaller set of specific values.
-
Syntax
bin(expression, interval)
-
Parameters
Name Type Mandatory Default Constraints Description expression
timestamp, long, double, duration
yes
The expression that should be aligned.
interval
duration, double, long
yes
statically evaluated
The interval by which to align the expression.
at
timestamp, number,duration
no
0
non zero, matched to the expression
The offset to which each interval should be shifted.
-
Example 1
In this example, we filter business events for the last 10 minutes where the amount field is not empty. Next, we group these business events into buckets for each 100-dollar increment.
fetch bizevents, from:now()-10m | filterOut isNull(amount) | summarize count(), by:bin(toLong(amount), 100)
Query result
bin(toLong(amount), 100) count() bin(toLong(amount), 100):0
17
bin(toLong(amount), 100):100
17
bin(toLong(amount), 100):200
18
bin(toLong(amount), 100):300
47
bin(toLong(amount), 100):400
18
-
Example 2
In this example, we filter log data containing log level
ERROR
and group log records by timestamp at 1-minute intervals.fetch logs, from:now()-10m | filter loglevel == "ERROR" | summarize count(), by:{bin(timestamp, 1m), host.name}
Query result
host.name bin(timestamp, 1m) count() HOST-1
2022-08-26T10:43:00.000000000Z
15
HOST-2
2022-08-26T10:44:00.000000000Z
6
HOST-3
2022-08-26T10:44:00.000000000Z
20
HOST-3
2022-08-26T10:45:00.000000000Z
20
HOST-3
2022-08-26T10:46:00.000000000Z
20
-
Example 3
In this example, we align the DateTime to noon.
data record(ts=now()) | fields bin(ts, 1d, at: 12h)
Query result
bin(ts, 1d, at:12h) 01/08/2023, 14:00
-
Example 4
In this example, we align each number to the middle of the interval.
data record(a=-17), record(a=2), record(a=7) | fields a, bin(a, 10, at:5)
Query result
a bin(a, 10, at:5) -17
-25
2
-5
7
5
range
Aligns the given value/timestamp to value range based on the provided alignment parameter. The range
function is similar to the bin
function, but produces a range instead, then provides information about the start and the end of the bin the value is aligned to.
-
Syntax
range(expression, interval [, at])
-
Parameters
Name Type Mandatory Default Constraints Description expressions
expression
yes
The numeric, timestamp or duration expression that should be aligned into bins.
interval
expression
yes
numeric_expression, duration_expression
The size of bins produced and the values that are aligned to it.
at
expression
no
0, EPOCH
The starting value for the first bin that is produced.
-
Example 1
fetch logs | summarize count(), by: range(timestamp, 1d)
Query result
range(timestamp, 1d) count() start: 07/08/2023, 02:00
21,579,999
end: 08/08/2023, 02:00
-
Example 2
fetch logs | summarize count(), by: {range(timestamp, 1d), status}
Query result
range(timestamp, 1d) status count start: 07/08/2023, 02:00
end: 08/08/2023, 02:00
ERROR
5,071
start: 07/08/2023, 02:00
end: 08/08/2023, 02:00
INFO
64,373
start: 07/08/2023, 02:00
end: 08/08/2023, 02:00
NONE
21,479,985
start: 07/08/2023, 02:00
end: 08/08/2023, 02:00
WARN
3,726
-
Example 3
fetch bizevents | summarize count(), by: range(amount, 100)
Query result
range(amount, 100) count start: NaN
end: NaN
354
start: NaN
end: NaN
138
start: NaN
end: NaN
118
coalesce
Returns the first non-null
argument, if any, otherwise null
.
-
Syntax
coalesce(expression, …)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
Returned if previous arguments are null.
-
Example
In this example, the result is the value of the first non-null field.
fetch logs | fields result = coalesce(content, ordinal, timestamp, "string")
Query result
result 2023-03-19T14:03:43Z localhost haproxy[12529]: 10.10.10.100:38440 http-in~ individual_servers/apmng6 217/0/0/1/218 HTTP_STATUS 200 284 - - --NN 5749/5745/0/1/0 0/0 {|||domain.com:443} {|} "POST /communication?targetServerId=6 TLSv1.2
if
Evaluates the condition, and returns the value of either the then or else parameter, depending on whether the condition evaluated to true (then) or false or null (else - or null if the else parameter is missing).
-
Syntax
if(condition, then [, else])
-
Parameters
Name Type Mandatory Default Constraints Description condition
boolean
yes
The condition to check.
then
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
The expression if the condition is true.
else
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
no
The expression if the condition is false or null.
-
Example
In this example:
- We filter log data for the
HOST-123
host that includes log records containingPOST /cart
. - Next, we display the timestamp and the filtered content.
- Next, we add
isCheckout
field with records containing thecheckout
string. - Next, using the
if
function, addcheckout_ts
field and if theisCheckout
field contains a timestamp, we populate theisCheckout
field with that timestamp. If no timestamp is found theinvalid
value is used. - Finally, using the
if
function, we add thecart_ts
field and if theisCheckout
field contains nocheckout
string, the timestamp is populated intocart_ts
field. If no timestamp is found theinvalid
value is used.
... fetch logs, from:now()-30m | filter host.name == "HOST-123" | filter contains(content, "POST /cart") | fields timestamp, content | fieldsAdd isCheckout = contains(content,"checkout") | fieldsAdd checkout_ts = if(isCheckout, toLong(timestamp), else:"invalid"), cart_ts = if(NOT isCheckout, toLong(timestamp), else:"invalid")
Query result
timestamp content cart_ts checkout_ts isCheckout 2022-08-26 12:37:37
10.10.10.52 - [26/Aug/2022:10:37:37 +0000] POST /cart HTTP/1.1 302 0
1661510257000000000
invalid
false
2022-08-26 12:37:51
10.10.10.97 - [26/Aug/2022:10:37:51 +0000] POST /cart HTTP/1.1 302 0
1661510271000000000
invalid
false
2022-08-26 12:37:53
10.10.10.108 - [26/Aug/2022:10:37:53 +0000] POST /cart/checkout HTTP/1.1 200 7268
invalid
1661510273000000000
true
2022-08-26 12:38:07
10.10.10.97 - [26/Aug/2022:10:38:07 +0000] POST /cart/checkout HTTP/1.1 200 7228
invalid
1661510287000000000
true
2022-08-26 12:38:22
10.10.10.97 - [26/Aug/2022:10:38:22 +0000] POST /cart HTTP/1.1 302 0
1661510302000000000
invalid
false
- We filter log data for the
in
Tests if a value is a member of an array
.
The in
function supports multiple haystacks and allows arrays in all arguments.
-
Syntax
in(needle, haystack, …)
-
Parameters
Name Type Mandatory Default Constraints Description element
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
The element(s) to search for (the needle).
elements
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
The elements where to search for the needle element (the haystack).
-
Example
In this example, we filter log records containing an ERROR within the log message for three processes of interest:
fetch logs | filter matchesPhrase(content, "ERROR") and in(dt.process.name, "IIS", "MongoDB", "Postgres") | fields timestamp, content, dt.process.name | limit 1
Query result
timestamp content dt.process.name 2022-11-25 09:17:09
{"t":{"$date":"2022-11-25T08:17:09.848+00:00"},"s":"I", "c":"NETWORK", "id":22988, "ctx":"conn876571","msg":"Error receiving...
MongoDB
-
Example 2
To better illustrate the overall behavior of the
in()
function, we create three fields and then check whether the values ofa
andb
are contained in the arrayc
... | fields a = 1, b="error", c=array("info","error",1,2,3) | fields in(a,c), in(b,c)
Query result
in(a, c) in(b,c) true
true
-
Example 3
This is an example with multiple haystacks. In this example, we create three fields and then check if
a
is inb
orc
or both.... | fields a = 1, b=array(1, 2, 3), c=array("error", "info") | fields in(1, b, c), in("info", b, c)
Query result
in(1, {b, c}) in("info", {b, c}) true
true
-
Example 4
In this example, we create three arrays. The arrays are flattened and we check to see if at least one element of the needle is found in one of the haystacks.
... | fields a=array(2, "warning"), b=array(1, 2, 3), c=array("error", "info") | fields in(a, b, c)
Query result
in(a, {b, c}) true
now
Returns the current time as a fixed timestamp of the query start.
-
Syntax
now()
-
Example
In this example, we display the current time in the
Time_now
field.... | fields Time_now = now()
Query result
Time_now 2022-08-02T09:23:00.188312000Z
isNotNull
Tests if a value is not NULL.
-
Syntax
isNotNull(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
In this example, we filter (select) data where the
host.name
field contains a value.... | filter isNotNull(host.name)
Query result
timestamp content event.type host.name 2022-08-03 11:27:19
2022-08-03 09:27:19.836 [QueueProcessor] RemoteReporter...
LOG
HOST-AF-710319
isNull
Tests if a value is NULL
.
-
Syntax
isNull(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
In this example, we filter (select) data where the
host.name
field doesn't contain a value.... | filter isNull(host.name)
Query result
timestamp content event.type host.name 2022-08-03 12:53:26
2022-08-03T10:52:31Z localhost haproxy[12529]: 192.168.19.100:38440
LOG
isFalseOrNull
Evaluates if an expression is false
or NULL
.
-
Syntax
isFalseOrNull(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
boolean
yes
-
Example
fetch events | limit 1 | fields a=1, b=2 | fieldsAdd isFalseOrNull(a>b)
Query result
a b isFalseOrNull(a > b) 1
2
true
isTrueOrNull
Evaluates if an expression is true
or NULL
.
-
Syntax
isTrueOrNull(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
boolean
yes
-
Example
... | limit 1 | fields a=1, b=2 | fieldsAdd isTrueOrNull(a>b), isFalseOrNull(a>b)
Query result
a b isTrueOrNull(a>b)
isFalseOrNull(a>b)
1
2
false
true
timestamp
Creates a timestamp
using provided values in mandatory parameters.
-
Syntax
timestamp(year,month,day,hour,minute,second [, millis] [, micros] [,nanos])
-
Parameters
Name Type Mandatory Default Constraints Description year
long
yes
month
long
yes
day
long
yes
hour
long
yes
minute
long
yes
second
long
yes
millis
long
no
micros
long
no
nanos
long
no
-
Example
... | fields time = timestamp(year:2023,month:4,day:28,hour:3,minute:30,second:30)
Query result
time 2022-08-01T11:00:00.000000000Z
duration
Creates a duration
from the given amount and time unit.
-
Syntax
duration(value, unit)
-
Parameters
Name Type Mandatory Default Constraints Description value
long
yes
The numeric value for the duration.
unit
string
yes
The time unit of the duration.
-
Example
... | limit 1 | fields dur = 124 | fieldsAdd dur_ns = duration(dur, unit:"ns")
Query result
dur dur_ns 124
124
type
Returns the type of a value as a string
.
-
Syntax
type(expression)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
-
Example
In this example, we create six variables of various types and, using the
type
function, we identify the type of each variable and put the result as a string in a corresponding field.... | fields var_1 = "ThiS is a tEsT", var_2 = 12345, var_3 = array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11), var_4 = 123.34, var_5 = true, var_6 = `00.00:00.--"` | fields var_1_type = type(var_1), var_2_type = type(var_2), var_3_type = type(var_3), var_4_type = type(var_4), var_5_type = type(var_5), var_6_type = type(var_6)
Query result
var_1_type var_2_type var_3_type var_4_type var_5_type var_6_type string
long
array
double
boolean
variant
asBoolean
Returns boolean value if the value is boolean or variant<boolean>, otherwise, returns null.
-
Syntax
asBoolean(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
boolean
yes
-
Example
... | fields bValue_1 = asBoolean(TRUE), bValue_2 = asBoolean(`string`), bValue_3 = asBoolean(False), bValue_4 = asBoolean(TrUe)
Query result
bValue_1 bValue_2 bValue_3 bValue_4 true
false
true
asDouble
Returns double
value if the value is double
, otherwise, returns null
.
-
Syntax
asDouble(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
double
yes
-
Example
... | fields dbl_1 = asDouble(1234.5), dbl_2 = asDouble(4.0+3.0/2.3)
Query result
dbl_1 dbl_2 1234.5
5.304347826086957
asLong
Returns long
value if the value is long
, otherwise null
.
-
Syntax
asLong(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
long
yes
-
Example
... | fields vLong_1 = asLong(83457264009472472), vLong_2 = asLong(`30`)
Query result
vLong_1 vLong_2 83457264009472472
asTimestamp
Returns timestamp
value if the value is timestamp
, otherwise, returns null
.
-
Syntax
asTimestamp(value)
-
Parameters
Name Type Mandatory Default Constraints Description value
timestamp
yes
-
Example
... | fields new_timestamp = asTimestamp("2022-03-19 09:24:54")
Query result
new_timestamp 2022-03-19T09:24:54.000000000Z
record
Creates a record
from the keys and values of the parameter.
-
Syntax
record(expression, …)
-
Parameters
Name Type Mandatory Default Constraints Description expression
array, boolean, double, duration, ip, long, record, string, timeframe, timestamp
yes
An expression to add to the record.
-
Example
fetch logs | limit 1 | fields timestamp, person = record(name="John", age=34)
Query result
timestamp person 2022-08-31 07:57:37
{fields=[{name=name, type=string}, {name=age, type=number}], values={name=John, age=34}}
random
Creates a random double value. Generated values aren't deterministic. The value range of the generated double value is between 0.0 (inclusive) and 1.0 (exclusive).
-
Syntax
random()
-
Example
data record() | fields r1=random(), r2=random(), r3=random(), r4=random()
Query result
r1 r2 r3 r4 0.362
0.009
0.186
0.563