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()
Count 184612
countIf
Counts the number of records that match the condition.
-
Syntax
countIf(Expression)
-
Example
The example query counts the number of log lines in the last two minutes if the log line contains log levelERROR
fetch logs, from:now()-2m | summarize Error_count = countif(loglevel == "ERROR")
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])
-
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)
distinctHosts 738
-
Aggregation precision
The countDistinct()
aggregation function uses a variant of the HyperLogLog (HLL) algorithm, which makes a stochastic estimation of set cardinality. The precision
defines the number of possible values (2^precision)
and the standard expected error is approximately 0.785/sqrt(2^precision)
.
By default, the precision is set to 10, which results in 2^10
values and a maximum error of approximately 2%
.
fetch logs, from:now()-10m
| summarize distinctHosts = countDistinct(host.name)
distinctHosts |
---|
|
sum
Calculates the sum of a field for a list of records.
-
Example
The example query displays the sum value for theamount
field in the events data set.fetch events | summarize Total_amount = sum(amount)
Total_amount 1,064,497
avg
Calculates the average value of a field for a list of records.
-
Syntax
avg(numeric_expr)
-
Example
The example query displays the average value for theamount
field in the events data set.fetch events | summarize Average_amount = avg(amount)
Average_amount 958
max
Calculates the maximum value of a field for a list of records.
-
Syntax
max(numeric_expr)
-
Example
The example query displays the maximum value of theamount
field in the events data set.fetch events | summarize Maximum_amount = max(amount)
Maximum_amount 1,993
min
Calculates the minimum value of a field for a list of records.
-
Syntax
min(numeric_expr)
-
Example
The example query displays the minimum value of theamount
field in the events data set.fetch events | summarize Minumum_amount = min(amount)
Minumum_amount 188
takeFirst
Returns the first value of a field for a list of records.
-
Syntax
takeFirst(fieldName)
-
Example
The example query displays the first timestamp value in the log data set.fetch logs, from:now()-1m | summarize First_timestamp = takeFirst(timestamp)
First_timestamp 7/28/2022, 1:28:02 PM
takeLast
Returns the last value of a field for a list of records.
-
Syntax
takeLast(fieldName)
-
Example
The example query displays the last timestamp value in the log data set.fetch logs, from:now()-1m | summarize Last_timestamp = takeLast(timestamp)
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(fieldName)
-
Example
This example is based on abizevents
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
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...
stddev
Calculates the standard deviation of a field for a list of records.
-
Syntax
stdev(<field>)
-
Example
This example calculates the average, minimum, maximum, and standard deviation for a list of numericamount
values.... | filter isNotNull(amount) | summarize avg(amount), min(amount), max(amount) , stdev = stddev(amount)
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(<field>)
... | filter isNotNull(amount) | summarize avg(amount), min(amount), max(amount) , var = variance(amount)
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(<expression>,<expression>)
...
| fields timestamp, a=toDouble(timestamp), b=toDouble(timestamp)
| summarize correlation=correlation(a,b)
correlation |
---|
|
takeAny
Returns the first non-null value of a field for a list of records.
-
Syntax
takeAny(expression)
-
Parameters
expression: non-static expression
-
Example In this example, the
takeAny
function returns the first non-null host name for each log level type.
fetch logs
| summarize takeAny(host.name), by:loglevel
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)
-
Example
In this example, we convert to an arrayvar_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)
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>)
-
Example
... | fields bValue1 = toBoolean("true"), bValue2 = toBoolean("TrUe"), bValue3 = toBoolean("1"), bValue4 = toBoolean(3), bValue5 = toBoolean("test"), bValue6 = toBoolean(0)
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>)
-
Example
... | fields dbl_1 = toDouble("1234.5"), dbl_2 = toDouble(4+3/2)
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.
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>)
-
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>)
-
Example
... | fields str_Boolean = toString(toBoolean(1)), str_Array = toString(array(1,2,3)), str_Number = toString(1), str_Time = toString(toTimestamp(now())), str_Variant = toString(toVariant(1)), str_IPaddr = toString(toIpAddress("192.168.0.1"))
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
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.
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>)
-
Example
... | fields cnv_timestamp = toTimestamp("2022-03-19 09:24:54")
cnv_timestamp 2022-03-19T09:24:54.000000000Z
toVariant
Converts a value to VARIANT with boxed element inside.
-
Syntax
toVariant(<value>)
-
Example
... | fields cnv_var1 = toVariant( "122 / 2" ), cnv_var2 = toVariant( 122 / 2 * 5 ), cnv_var3 = toVariant( 123 )
cnv_var1 cnv_var2 cnv_var3 122 / 2
305
123
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>)
-
Example
... | limit 1 | fields degrees = radianToDegree(3.19), degrees_null = radianToDegree(5/0)
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(<numeric_expr>)
-
Example
... | limit 1 | fields radians = degreeToRadian(3*19), radians_null = degreeToRadian(5/0)
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
- asVariant
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(<value>,<value>,<value>)
-
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)
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(<arrayName>)
-
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)
num_array_size 15
arraySum
Returns the sum of an array. Values that are not numeric are ignored. 0 if there is no matching element.
-
Syntax
arraySum(<arrayName>)
-
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)
num_array_values_sum 157.0
arrayAvg
Returns the average of an array. Values that are not numeric are ignored. 0 if there is no matching element.
-
Syntax
arrayAvg(<arrayName>)
-
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)
num_array_average 10.466666666666667
arrayDistinct
Returns the array without duplicates.
-
Syntax
arrayDistinct(<arrayName>)
-
Example
In this example, we create an array containing 15 numbers and select only the distinct values 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_distinct_values = arrayDistinct(num_array)
num_array_distinct_values [2, 3, 5, 7, 11, 13, 17, 19, 23, 29]
arrayFirst
Returns the first element of an array.
-
Syntax
arrayFirst(<arrayName>)
-
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)
num_array_first_value 2
arrayLast
Returns the last element of an array.
-
Syntax
arrayLast(<arrayName>)
-
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)
num_array_last_value 11
arrayMax
Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. NULL if there is no matching element.
-
Syntax
arrayMax(<arrayName>)
-
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)
num_array_max_value 29
arrayMin
Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. NULL if there is no matching element.
-
Syntax
arrayMin(<arrayName>)
-
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)
num_array_min_value 2
collectArray
Collects the values of the provided field into an array (preservation of order not guaranteed).
-
Syntax
collectArray(<fieldName>)
-
Example
fetch logs | limit 5 | summarize Array_Hosts = collectArray(dt.entity.host)
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}}]
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)
-
Example
In this example, we search for the character located on the-1
position.... | fields getCharacter("012345", -1)
getCharacter("012345", -1) 5
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
is0
(the search from the start of the string). - The search is case-sensitive.
- If the defined substring is not found, the function returns
-1
.
- Negative values for the
-
Syntax
indexOf(expression, substring [, from])
-
Example
In this example, we search for the first occurrence of anab
substring in theababcd
expression. The count starts from1
.... | fields indexOf("ababcd", "ab", from: 1)
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])
-
Example
In this example, we search for the last occurrence of acd
substring in theabcdcd
expression. The count starts from-3
.... | fields lastIndexOf("abcdcd", "cd", from: -3)
lastIndexOf("abcdcd", "cd", from: -3) 2
matchesPhrase
Matches a phrase against the input string expression using token matchers.
-
Syntax
matchesPhrase(<fieldName>, <string>, [caseSensitive])
-
Example
In this example, we add a field that checks if fielda
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")
matchesPhrase(a, phrase:"haproxy") | matchesPhrase(a, phrase:"ha") | matchesPhrase(a, phrase:"10.176") |
---|---|---|
|
|
|
contains
Searches the string expression for a substring. Returns TRUE if the substring was found, FALSE otherwise.
-
Syntax
contains(<fieldName>, <string>, [caseSensitive])
-
Example
In this example, we add a field that checks if fieldcontent
contains theFlushCommand
string.... | fieldsAdd str_found = contains(content, "FlushCommand")
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(<testString>, <string>, [caseSensitive])
-
Example
In this example, we test thelogs 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")
str1_start str2_start true
false
endsWith
Checks if a string expression ends with a suffix. Returns TRUE if does, FALSE otherwise.
-
Syntax
endsWith(<testString>, <string>, [caseSensitive])
-
Example 1
In this example, we test if thelogs 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")
str1_end str2_end true
false
-
Example 2
In this example, we filter all records for which the value oflog.source
ends with/pgi.log
.... | filter endsWith(log.source,"/pgi.log")
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(<string_1>,<pattern>)
...
| 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")
like(a, "%10.%")_end | like(a, "%178")_end | like(a, "haproxy")_end |
---|---|---|
|
|
|
concat
Concatenates the expressions into a single string.
-
Syntax
concat(<string_1>, <string_2>, <string_3>, ...)
-
Example
In this example, we combine theDQL
,is
, andawesome!
strings in thestr_concatenated
field.... | fields str_concatenated = concat("DQL ", "is ", "awesome!")
str_concatenated DQL is awesome!
lower
Converts a string to lowercase.
-
Syntax
lower(<string>)
-
Example
In this example, we convert the stringThiS is a tEsT
to lowercase and place the result in thestr_lower
field.... | fields str_lower = lower("ThiS is a tEsT")
str_lower this is a test
upper
Converts a string to uppercase.
-
Syntax
upper(<string>)
-
Example
In this example, we convert the stringThiS is a tEsT
to uppercase and place the result in thestr_upper
field.... | fields str_upper = upper("ThiS is a tEsT")
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.
Note: No specific normalization form is guaranteed for Dynatrace-provided strings.
-
Syntax
stringLength(<string>)
-
Example
In this example, we return the length of thecontent
field in logs, sorted from shortest to longest log lines.fetch logs | fields content, stringLength(content) | sort stringLength(content) asc
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: <idx>] [, to: <idx> ])
-
Example 1
In this example, we return a substring of012
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)
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)
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
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(<string>)
-
Example
In this example, we compare the length of the untrimmed and trimmedcontent
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))
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
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-8061 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.
- Example 1
In this example, a string according to the ISO-8061 standard is converted to the timestamp datatype. Then, theformatTimestamp
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")
t | formatted | year | month | week | dayofWeek | hour |
---|---|---|---|---|---|---|
|
|
|
|
|
|
|
timeframe
Creates a timeframe structure from the given start and end timestamps.
-
Syntax
timeframe(from:<expression>, to:<expression>)
... | 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())
{"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(<expression>)
timestampFromUnixNanos
Creates a timestamp from the given nanoseconds since Unix epoch.
- Syntax
timestampFromUnixNanos(<expression>)
timestampFromUnixSeconds
Creates a timestamp from the given seconds since Unix epoch.
-
Syntax
timestampFromUnixSeconds(<expression>)
... | limit 1 | fields t_s = timestampFromUnixSeconds(1665064316), t_ms = timestampFromUnixMillis(1665064316000), t_ns = timestampFromUnixNanos(1665064316000000000)
t_s
t_ms
t_ns
2022-10-06T13:51:56.000000000Z
2022-10-06T13:51:56.000000000Z
2022-10-06T13:51:56.000000000Z
Mathematical functions
power
Raises a numeric expression to a given power.
-
Syntax
power(numeric_expression, numeric_expression)
-
Example
In this example, we raise2
to3rd
power.
| fields
power(2,3)
power(2,3) |
---|
|
exp
Calculates the exponential function e^x
, where e
is the Euler's number and x
is a numeric expression.
-
Syntax
exp(numeric_expression)
-
Example
In this example, we display the exponential function of1
.
| fields
exp(1)
exp(1) |
---|
|
sqrt
Computes the positive square root of a numeric expression.
-
Syntax
sqrt(<value>)
-
Example
In this example, we display the square root of123.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)
sqrt_value_1 sqrt_value_2 11.110805551354051
148.0410078322895
cbrt
Calculates the real cubic root of a numeric expression.
-
Syntax
cbrt (numeric_expression)
-
Example In this example, we display the real cubic root of
-8
.
| fields
cbrt(-8)
cbrt(-8) |
---|
|
log
Calculates the natural logarithm (the base is e
, the Euler's number) of a numeric expression.
-
Syntax
log(numeric_expression)
-
Example In this example, we display the natural logarithm of
e()
.
| fields
log(e())
log(e()) |
---|
|
log1p
Calculates log(1+x), where log
is the natural logarithm and x
is a numeric expression.
-
Syntax
log1p(numeric_expression)
-
Example
In this example, we display the natural logarithm of1
usinglog1p(0)
.
| fields
log1p(0)
log1p(0) |
---|
|
log10
Calculates the decadic (common) logarithm (the base is 10) of a numeric expression.
-
Syntax
log10(numeric_expression)
-
Example In this example, we display the decadic (common) logarithm of
100
.
| fields
log10(100)
log10 |
---|
|
sin
Computes the trigonometric sine of angle <expression>
(in radians). Returns null
if <expression>
evaluates to NULL.
-
Syntax
sin(<expression>)
-
Example
... | fields sine = sin(19.71)
sine 0.758132221587245
sinh
Computes the hyperbolic sine of <expression>
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
sinh(<expression>)
-
Example
... | fields hyperbolic_sine = sinh(19.71)
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>)
-
Example
... | fields arc_sine = asin(.71)
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>)
-
Example
... | fields cosine = cos(19.71)
cosine 0.6521008622837332
cosh
Computes the hyperbolic cosine of an angle <expression>
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
cosh(<expression>)
-
Example
... | fields hyperbolic_cosine = cosh(19.71)
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>)
-
Example
... | fields arc_cosine = acos(.71)
arc_cosine 0.7812981174487247
tan
Computes the trigonometric tangent of angle <expression>
(in radians). Returns null
if <expression>
evaluates to NULL.
-
Syntax
tan(<expression>)
-
Example
... | fields tangent = tan(19.71)
tangent 1.1625996305727577
tanh
Computes the hyperbolic tangent of <expression>
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
tanh(<expression>)
-
Example
... | fields hyperbolic_tangent = tanh(.71)
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>)
-
Example
... | fields arc_tangent = atan(.71)
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(<expression>,<expression>)
-
Example
... | fields angle_theta = atan2(19.71,20.06)
angle_theta 0.7765977871210077
hypotenuse
Returns sqrt(x^2 + y^2)
. Returns null
if <expression>
evaluates to NULL.
-
Syntax
hypotenuse(<expression>,<expression>)
-
Example
... | fields hypotenuse = hypotenuse(19.71,20.06)
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 of19.71
in thecircumference
field.... | fields circumference = 2 * pi( ) * 19.71
circumference 123.84158240450965
e
Returns Euler’s number.
-
Syntax
e()
-
Example
In this example, we display the value of Euler's number in theeNumber
field.... | fields eNumber = e()
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.
-
Syntax
round(numeric_expression [, decimals: integer])
-
Example
... | fields pi(), round(pi()), round(pi(), decimals:2), round(pi(), decimals:3)
pi() round(pi()) round(pi(), decimals:2) round(pi(), decimals:3) 3.141592653589793
3
3.14
3.142
abs
Returns the absolute value of numeric_expression
. Returns NULL if numeric_expression
evaluates to NULL.
-
Syntax
abs(numeric_expression)
-
Example
... | fields abs_1 = abs(pi()), abs_2 = abs(-34), abs_3 = abs(5/0)
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.
-
Syntax
ceil(numeric_expression)
-
Example
... | fields ceil_1 = ceil(pi()), ceil_2 = ceil(5/0)
ceil_1 ceil_2 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.
-
Syntax
floor(numeric_expression)
-
Example
... | fields floor_1 = floor(pi()), floor_2 = floor(5/0)
floor_1 floor_2 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).
-
Syntax
signum(numeric_expression)
-
Example
... | fields signum_1 = signum(3-40), signum_2 = signum(3-3), signum_3 = signum(3*40), signum_4 = signum(3/0)
signum_1 signum_2 signum_3 signum_4 -1
0
1
null
Other functions
bin
Aligns the value of the numeric or timestamp into buckets of the given interval starting at 0 (numeric) or Unix epoch (timestamp).
-
Syntax
bin(<numeric_value>, <interval>)
-
Example 1
In this example, we filter log data containing log levelERROR
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}
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 2
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)
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
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>, <true_statement> , else:<false_statement>)
-
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")
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(<value>,<array>)
-
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
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
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)
in(a, c) in(b,c) true
true
-
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)
in(1, {b, c}) in("info", {b, c}) true
true
-
Example with arrays in all arguments
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)
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 theTime_now
field.... | fields Time_now = now()
Time_now 2022-08-02T09:23:00.188312000Z
isNotNull
Tests if a value is not NULL
-
Syntax
isNotNull(<fieldName>)
-
Example
In this example, we filter (select) data where thehost.name
field contains a value.... | filter isNotNull(host.name)
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(<fieldName>)
-
Example
In this example, we filter (select) data where thehost.name
field doesn't contain a value.... | filter isNull(host.name)
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>)
isTrueOrNull
Evaluates if an expression is TRUE
or NULL
.
-
Syntax
isTrueOrNull(<expression>)
... | limit 1 | fields a=1, b=2 | fieldsAdd isTrueOrNull(a>b), isFalseOrNull(a>b)
a b isTrueOrNull(a>b)
isFalseOrNull(a>b)
1
2
false
true
timestamp
Creates a timestamp
from the provided values.
-
Syntax
timestamp(<value>)
-
Example
In this example, we set the timestamp to2022-08-01T12:00:00+01:00
in thetime
field.... | fields time = timestamp("2022-08-01T12:00:00+01:00")
time 2022-08-01T11:00:00.000000000Z
duration
Creates a duration
from the given amount and time unit.
-
Syntax
duration(<value>,[unit:])
... | limit 1 | fields dur = 124 | fieldsAdd dur_ns = duration(dur, unit:"ns")
dur dur_ns 124
124
type
Returns the type of a value as STRING.
-
Syntax
type(<value>)
-
Example
In this example, we create six variables of various types and, using thetype
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)
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 NULL.
-
Syntax
asBoolean(<value>)
-
Example
... | fields bValue_1 = asBoolean(TRUE), bValue_2 = asBoolean(`string`), bValue_3 = asBoolean(False), bValue_4 = asBoolean(TrUe)
bValue_1 bValue_2 bValue_3 bValue_4 true
false
true
asDouble
Returns DOUBLE value if the value is DOUBLE or VARIANT<DOUBLE>, otherwise NULL.
-
Syntax
asDouble(<value>)
-
Example
... | fields dbl_1 = asDouble(1234.5), dbl_2 = asDouble(4.0+3.0/2.3)
dbl_1 dbl_2 1234.5
5.304347826086957
asLong
Returns LONG value if the value is LONG or VARIANT<LONG>, otherwise NULL.
-
Syntax
asLong(<value>)
-
Example
... | fields vLong_1 = asLong(83457264009472472), vLong_2 = asLong(`30`)
vLong_1 vLong_2 83457264009472472
asTimestamp
Returns TIMESTAMP value if the value is TIMESTAMP or VARIANT<TIMESTAMP>, otherwise NULL.
-
Syntax
asTimestamp(<value>)
-
Example
... | fields new_timestamp = asTimestamp("2022-03-19 09:24:54")
new_timestamp 2022-03-19T09:24:54.000000000Z
record
Creates a complex RECORD from the keys and values of the parameters.
-
Syntax
record(<key1>=<value1>, <key2>=<value2>)
-
Example
fetch logs | limit 1 | fields timestamp, person = record(name="John", age=34)
timestamp person 2022-08-31 07:57:37
{fields=[{name=name, type=string}, {name=age, type=number}], values={name=John, age=34}}
matchesValue
Searches records for a specific value in a given attribute. Returns true
or false
.
-
Syntax
matchesValue(<fieldName>, <value>)
-
Example
In this example, you add a filter record whereprocess.technology
attribute containsnginx
value.... | filter matchesValue(process.technology, "nginx")
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
.