• Home
  • Platform
  • Grail
  • Dynatrace Query Language
  • DQL Functions

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.

Aggregation functions in/out table for homogeneous data types.

The below table explains the results of combining homogeneous data types in the aggregation function, for example the avg() function for two numeric expressions.

In/outdoublelongdouble & longdurationtimestampbooleanstringtimeframerecordarray

Numeric

Calculables

Clear ordering

Ambiguous ordering

count()

long

long

long

long

long

long

long

long

long

long (number of arrays)

countif()

long

long

long

long

long

long

long

long

long

long (number of arrays)

sum()

double

double

double

duration

null

null

null

null

null

null

avg()

double

double

double

duration

null

null

null

null

null

null

correlation()

double

double

double

null

null

null

null

null

null

null

stddev

double

double

double

null

null

null

null

null

null

null

variance()

double

double

double

null

null

null

null

null

null

null

last()

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

first()

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

same as input

General rules:

  • If you mix two data types, the result is null, unless you mix data for which combinations are allowed, such as long and double.
  • You will also get the null result for operations not covered by a given function, for example the sum() of two boolean expressions.

Function-specific rules:

  • The sum function allows numeric expressions and duration expressions. If you mix types, the result is null.
    • The sum of two numeric expressions results in a double data type (for example, double + double, double + long, long + long).
    • The sum of two duration expressions results in a duration data type.
    • The sum of a numeric expression and a duration expression is null.
  • The avg function allows numeric expressions and duration expressions. If you mix types, the result is null.
    • The average of two numeric expressions results in a double data type (for example, double + double, double + long, long + long).
    • The average of two duration expressions results in a duration data type.
    • The average of a numeric expression and a duration expression is null.
  • The min and max functions allow numeric expressions, duration expressions, timestamp expressions, string expressions, and boolean expressions.
    • The minimum/maximum of numerical expressions results in a double data type, apart from min/max of long expressions that results in a long data type.
    • The minimum/maximum of any mixed types (other than double + long) is null.
    • For strings, lexicographic ordering is used.
    • For the boolean expressions, false < true.
  • The first and the last functions allow expressions of all types.
    • The first function selects the first non-null value (and the data type retrieved is the one of that value) within the existing order.
    • The last function selects the last non-null value (and the data type retrieved is the one of that value) within the existing order.

count

Counts the total number of records.

  • Syntax

    count()

  • Example

    The example query counts the number of log lines in the last two minutes.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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:

    dql
    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. 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%.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long, duration

    yes

  • Example

    The example query displays the sum value for the amount field in the events data set.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long, duration

    yes

  • Example

    The example query displays the average value for the amount field in the events data set.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    array, boolean, double, duration, ip, long, record, string, timeframe, timestamp

    yes

  • Example

    This example is based on a bizevents dataset. It shows how the collectDistinct() 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.

    dql
    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.ipactivityactionsproducts

    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    array, boolean, double, duration, ip, long, record, string, timeframe, timestamp

    yes

  • Example

    dql
    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:

Data type order in non-homogenous lists
ORDER_IDDATA_TYPE

-1

RECORD

-2

ARRAY

1

BOOLEAN

3

LONG

5

DOUBLE

6

BINARY

7

STRING

8

TIMESTAMP

10

DURATION

11

METRIC_VALUE (summaryStats, counter)

12

TIMEFRAME

30

IP_ADDRESS

34

GEO_POINT

  • Syntax

    takeMin(expression)
    takeMax(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    any

    yes

    No nested aggregation functions.

    The expression used in the min/max calculation.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

  • Example

    This example calculates the average, minimum, maximum, and standard deviation for a list of numeric amount values.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression1

    double, long

    yes

    expression2

    double, long

    yes

  • Example

dql
... | fields timestamp, a=toDouble(timestamp), b=toDouble(timestamp) | summarize correlation=correlation(a,b)

Query result

correlation

1.0

takeAny

Returns any non-null value of a field for a list of records.

  • Syntax

    takeAny(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    fetch logs | summarize takeAny(host.name), by:loglevel

    Query result

    logleveltakeAny(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

    NameTypeMandatoryDefaultConstraintsDescription

    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 convert num_array containing 15 numbers. As a result, the variable is converted to the single element array holding that variable's value array_2 and the array is preserved as an array in a new field (array_1).

    dql
    ... | 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_1array_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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    boolean, double, long, string, array

    yes

  • Example

    dql
    ... | fields bValue1 = toBoolean("true"), bValue2 = toBoolean("TrUe"), bValue3 = toBoolean("1"), bValue4 = toBoolean(3), bValue5 = toBoolean("test"), bValue6 = toBoolean(0)

    Query result

    bValue1bValue2bValue3bValue4bValue5bValue6

    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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    double, long, string, boolean, ip, timestamp, duration, array

    yes

  • Example

    dql
    ... | fields dbl_1 = toDouble("1234.5"), dbl_2 = toDouble(4+3/2)

    Query result

    dbl_1dbl_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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    duration, double, long, string, timeframe, array

    yes

  • Example

    dql
    fetch logs | fields dur = toDuration(62*1000000000*60*60*24) | fieldsAdd dur > 60d

    Query result

    durdur > 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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    long, double, string, boolean, ip, timestamp, duration, array

    yes

  • Example

    dql
    ... | fields cnv_long1 = toLong("83457264009472472"), cnv_long2 = toLong(30), cnv_long3 = toLong(25.34)
    cnv_long1cnv_long2cnv_long3

    83457264009472472

    30

    25

toString

Returns the string representation of a value.

  • Syntax

    toString(value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    value

    double, boolean, timestamp, timeframe, duration, ip, array, record

    yes

    Parameter that should be transformed into text form.

  • Example 1

    dql
    ... | 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_Numberstr_Arraystr_Booleanstr_IPaddrstr_Timestr_Variant

    1

    [1, 2, 3]

    true

    192.168.0.1

    2022-08-02T09:15:10.360391000 +0000

    1

  • Example 2

    dql
    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

    dql
    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    timeframe, string, array

    yes

  • Example

    dql
    data record(tf=toTimeframe(toString(timeframe(from:now()-2h, to:now()))))

    Query result

    tf starttf 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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    timestamp, double, long , string, array

    yes

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The angle to be converted from radians to degrees.

  • Example

    dql
    ... | limit 1 | fields degrees = radianToDegree(3.19), degrees_null = radianToDegree(5/0)

    Query result

    degreesdegrees_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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The angle to be converted from radians to degrees.

  • Example

    dql
    ... | limit 1 | fields radians = degreeToRadian(3*19), radians_null = degreeToRadian(5/0)

    Query result

    radiansradians_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.

dql
... | 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()
Casting input data type to the target data type matrix.

The as*T will try to cast the input to T but won't perform any conversions, it will cause an exception for strongly typed fields (marked as ) and null for incompatible variants (empty cell).

asT(arg) => if(typeOf(arg) == ’T’, arg)

as*TasNumberasIntegerasLongasDoubleasStringasBooleanasTimestampasDurationasTimeframeasGeopointasIpAddressasSummaryStatsasBinaryasArrayasRecord
IntegerInteger
LongLong
DoubleDouble
String
Boolean
Timestamp
Duration
Timeframe
Geopoint
IpAddress
Binary
Array
Record
V<Integer>V<Integer>
V<Long>V<Long>
V<Double>V<Double>
V<String>
V<Boolean>
V<Timestamp>
V<Duration>
V<Timeframe>
V<Geopoint>
V<IpAddress>
V<SummaryStats>
V<Binary>
V<Array>
V<Record>V<Record>

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

    NameTypeMandatoryDefaultConstraintDescription

    expression

    string, binary

    yes

    A string or binary expression to encode.

  • Parameters: decode

    NameTypeMandatoryDefaultConstraintDescription

    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

    dql
    ... | 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)

    dql
    ... | fields encodeBase16("dynatrace"), decodeBase16ToString("64796E617472616365"), decodeBase16ToBinary("64796E617472616365")

    Query result

    encodeBase16("dynatrace")decodeBase16ToString("64796E617472616365")decodeBase64ToBinary("64796E617472616365")

    64796e617472616365

    dynatrace

    ZHluYXRyYWNl

asTimeframe

Returns timeframe value if the value is timeframe, otherwise returns null.

  • Syntax

    asTimeframe(value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    value

    timeframe

    yes

  • Example

    dql
    fetch logs | fields asTimeframe(timeframe(from: - 24h, to: - 2h))

    Query example

    asTimeframe(timeframe(from: - 24h, to: - 2h)) startasTimeframe(timeframe(from: - 24h, to: - 2h)) end

    26/09/2023, 08:56:06

    27/09/2023, 06:56:06

toIp

You can use this function to convert an expression to an IP address.

  • Syntax

    toIp(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string expression, ip address

    yes

    The expression to convert an expression to an IP address.

  • Example

    dql
    data record() | fields r1=toIp("1.1.1.1"), r2=toIp(ip("127.0.0.1"))

    Query result

    r1r2

    1.1.1.1

    127.0.0.1

numberToHexString

Converts a number to a hexadecimal string.

  • Syntax

    numberToHexString(expression)

  • Parameters

    NameTypeMandatoryDefaultConsraintsDescriptiont

    expression

    numeric expression

    yes

    The numeric expression that will be converted to a hexadecimal string.

  • Example

    dql
    data record() | fields r1 = numberToHexString(576460752303423487), r2 = numberToHexString(-256), r3 = numberToHexString(2147483648), r4 = numberToHexString(-2147483648)

    Query result

    r1r2r3r4

    7ffffffffffffff

    ffffffffffffff00

    80000000

    ffffffff80000000

hexStringToNumber

Converts a hexadecimal string to a number.

  • Syntax

    hexStringToNumber(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string expression

    yes

    The string expression that will be converted to a number.

  • Example

    dql
    data record() | fields r1 = hexStringToNumber("0x7FFFFFFFFFFFFFF"), r2 = hexStringToNumber("0X7FFFFFFFFFFFFFF"), r3 = hexStringToNumber("0x7ffffffffffffff"), r4 = hexStringToNumber("7ffffffffffffff")

    Query result

    r1r2r3r4

    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

    NameTypeMandatoryDefaultConstraintsDescription

    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).

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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).

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    array

    yes

    The array from which to compute a percentile.

    percentile

    double, long

    yes

    The percentile to compute, between 0 and 100.

  • Example

    dql
    fetch logs | fields timestamp | fieldsAdd arrayPercentile(array(2,4,6,8),(100))

    Query result

    timestamparrayPercentile(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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

dql
data record(a=array(1, 2), b=array(3, 4), c=array(5, 6)) | fields y = arrayConcat(a, b, c)

Query result

y

1, 2, 3, 4, 5, 6

arrayRemoveNulls

Returns the array where NULL elements are removed.

  • Syntax

    arrayRemoveNulls(array)

  • Parameters

NameTypeMandatoryDefaultConstraintsDescription

array

array

yes

  • Example

    In this example, we create an array containing 4 numbers and remove NULL values.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    array

    array

    yes

  • Example

    In this example, we create an array containing 15 numbers and select only the first value in the given array.

    dql
    ... | 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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    array

    array

    yes

  • Example

    In this example, the array returns elements in a reversed order.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the direction parameter.

    dql
    data record(integer_set=array(1,3,5,0,22,45)) | fields arraySort(integer_set, direction:"descending")

    Query result

arraySort(integer_set, direction:"descending")

45, 22, 5, 3, 1, 0

arrayIndexOf

Returns position of the first member in the array, which is equal to the given value.

  • Syntax

    arrayIndexOf(array, value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    r1r2r3

    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    r1r2r3

    3

    1

    -1

String functions

String functions allow you to create expressions that manipulate text strings in a variety of ways.

Case sensitivity

All string matching functions are case-sensitive per default. If otherwise required, the caseSensitive parameter provides the ability to change the behavior.

dql
... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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 contains nginx value.

    dql
    ... | filter matchesValue(process.technology, "nginx")

    Query result

    Examples of event processing using DQL matchesValue function:

    Part of the input eventProcessing queryMatch resultDescription

    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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the ababcd expression. The count starts from 1.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the abcdcd expression. The count starts from -3.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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 token haproxy. Unlike the contains() function, the matchesPhrase() function yields true only if the entire token matches.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the FlushCommand string.

    dql
    ... | fieldsAdd str_found = contains(content, "FlushCommand")

    Query result

    timestampcontentevent.typestr_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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the log string or the Grail string. The results are displayed in the str1_start and str2_start fields.

    dql
    ... | fields str1_start = startsWith ("logs on Grail" , "log"), str2_start = startsWith ("logs on Grail", "Grail")

    Query result

    str1_startstr2_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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the Grail string, or if it ends with the log string. The results are displayed in the str1_end and str2_end fields.

    dql
    ... | fields str1_end = endsWith ("logs on Grail" , "Grail"), str2_end = endsWith ("logs on Grail", "log")

    Query result

    str1_endstr2_end

    true

    false

  • Example 2

    In this example, we filter all records for which the value of log.source ends with /pgi.log.

    dql
    ... | filter endsWith(log.source,"/pgi.log")

    Query result

    timestampcontentlog.sourceevent.typedt.entity.hostdt.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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    pattern

    string

    yes

  • Example

    dql
    ... | 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.%")_endlike(a, "%178")_endlike(a, "haproxy")_end

true

true

false

concat

Concatenates the expressions into a single string.

  • Syntax

    concat(expression, …)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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 , and awesome! strings in the str_concatenated field.

    dql
    ... | fields str_concatenated = concat("DQL ", "is ", "awesome!")

    Query result

    str_concatenated

    DQL is awesome!

lower

Converts a string to lowercase.

  • Syntax

    lower(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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 the str_lower field.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the str_upper field.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    fetch logs | fields content, stringLength(content) | sort stringLength(content) asc

    Query result

    contentstringLength(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

    NameTypeMandatoryDefaultConstraintsDescription

    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 index 1 located in the first code unit, and a substring of 012 starting from a negative index, -2, located in the penultimate code unit.

    dql
    ... | 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 &#127015;), is returned as a question mark.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | fields a=splitString("abc", "a"), b=splitString("abbc", pattern: "b"), c=splitString("abc", "")

    Query result

    abc

    ,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.

    dql
    fetch logs | fields content, splitString(content, " ")

    Query result

    contentsplitString(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

    NameTypeMandatoryDefaultConstraintsDescription

    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`.

    dql
    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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 the content field concatenated with a leading and a trailing space.

    dql
    fetch logs | fieldsAdd untrimmed = concat(" ", content, " ") | fields content, untrimmed, stringLength(untrimmed), trim(untrimmed), stringLength(trim(untrimmed))

    Query result

    contentuntrimmedstringLength(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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    text1text2text3text4

    $();

    $(

    $(_);

    $(_

levenshteinDistance

Computes the Levenshtein distance between two input strings.

  • Syntax

    levenshteinDistance(expression, expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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 and kitten - sitting.

    dql
    fetch logs | fields distance1 = levenshteinDistance("gray", "grey"), distance2 = levenshteinDistance("kitten", "sitting") | limit 1

    Query result

    distance1distance2

    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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

    tformattedyearmonthweekdayofWeekhour

    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

  • Example

    dql
    ... | 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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    millis

    long

    yes

    Milliseconds since unix start time.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    nanos

    long

    yes

    Nanoseconds since unix start time.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    seconds

    long

    yes

    Seconds since unix start time.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression which will be converted to nanoseconds since Unix epoch.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression which will be converted to milliseconds since Unix epoch.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression which will be converted to seconds since Unix epoch.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the day of the month will be extracted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the day of the week will be extracted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the day of the year will be extracted.

  • Example

    dql
    fetch logs | fields getDayOfYear(timestamp) | limit 1

    Query result

    getDayOfYear(timestamp)

    174

getHour

Extracts the hour from a timestamp.

  • Syntax

    getHour(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the hour will be extracted.

  • Example

    dql
    fetch logs | fields getHour(timestamp) | limit 1

    Query result

    getHour(timestamp)

    13

getMinute

Extracts the minute from a timestamp.

  • Syntax

    getMinute(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp expression

    yes

    The timestamp expression from which the minute will be extracted.

  • Example

    dql
    fetch logs | fields getMinute(timestamp) | limit 1

    Query result

    getMinute(timestamp)

    24

getSecond

Extracts the second from a timestamp.

  • Syntax

    getSecond(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the second will be extracted.

  • Example

    dql
    fetch logs | fields getSecond(timestamp) | limit 1

    Query result

    getSecond(timestamp)

    48

getYear

Extracts the year from a timestamp.

  • Syntax

    getYear(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the year will be extracted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    timestamp

    timestamp

    yes

    The timestamp expression from which the week of the year will be extracted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    long expression

    yes

    The long expression whose bits will be inverted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    long expression

    yes

    The long expression whose bits will be inverted.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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 to 3rd power.

    dql
    | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    | fields exp(1)

    Query result

    exp(1)

    2.718281828459045

sqrt

Computes the positive square root of a numeric expression.

  • Syntax

    sqrt(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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 the sqrt_value_1 field and the square root of a result of a mathematical expression in the sqrt_value_2 field.

    dql
    ... | fields sqrt_value_1 = sqrt(123.45), sqrt_value_2 = sqrt(123.45 + 67.89 * 321)

    Query result

    sqrt_value_1sqrt_value_2

    11.110805551354051

    148.0410078322895

cbrt

Calculates the real cubic root of a numeric expression.

  • Syntax

    cbrt (numeric_expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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().

    dql
    | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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 using log1p(0).

    dql
    | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the sin.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the sinh.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the asin.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the sin.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the cosh.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the acos.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the tan.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the tanh.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression, angle in radians for which to calculate the atan.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    ordinate

    double, long

    yes

    The ordinate coordinate.

    abscissa

    double, long

    yes

    The abscissa coordinate.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    x

    double, long

    yes

    Length of the first of the catheti.

  • Example

    dql
    ... | 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 the circumference field.

    dql
    ... | 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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    Numeric expression to be rounded.

    decimals

    long

    no

    Number of places after the decimal point.

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression for which to calculate the absolute value.

  • Example

    plaintext
    ... | fields abs_1 = abs(pi()), abs_2 = abs(-34), abs_3 = abs(5/0)

    Query result

    abs_1abs_2abs_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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression to be rounded up.

  • Example

    plaintext
    ... | fields ceil_1 = ceil(pi()), ceil_2 = ceil(10-6), ceil_3 = ceil(5/0)

    Query result

    ceil_1ceil_2ceil_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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression to be rounded down.

  • Example

    plaintext
    ... | fields floor_1 = floor(pi()), floor_2 = floor(10-7), floor_3 = floor(5/0)

    Query result

    floor_1floor_2floor_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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    double, long

    yes

    The numeric expression for which to calculate the signum.

  • Example

    plaintext
    ... | 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_1signum_2signum_3signum_4signum_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

NameTypeMandatoryDefaultConstraintsDescription

expression

string

yes

The expression from which the MD5 hash needs to be computed.

  • Example 1

    dql
    fetch logs | fields hashMd5(host.name)

    Query result

    hashMd5(host.name)

    123abc4567cc1234567890cf123cbc4f567e89dc

  • Example 2

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The expression from which the SHA-1 hash needs to be computed.

  • Example 1

    dql
    fetch logs | fields hashSha1(host.name)

    Query result

    hashSha1(host.name)

    ca1db2fd3456789c0d12e345e678a9fff1234567

  • Example 2

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The string expression that will be hashed.

  • Example

    dql
    fetch logs | fields hash = hashSha256("example")

    Query result

    hash

    5ac4f2e8224c55b4356c6709a117cdfd93faa9f3be07d9aea8c51064fedc2140

hashSha512

Returns a SHA-512 hash for the given expression.

  • Syntax

    hashSha512(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The string expression that will be hashed.

  • Example

    dql
    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.

NameTypeMandatoryDefaultConstraintsDescription

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.

NameTypeMandatoryDefaultConstraintsDescription

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.

NameTypeMandatoryDefaultConstraintsDescription

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.

dql
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_correctIpv4_wrongIpv6_correctIpv6_wrong

true

false

true

false

  • Example 2

In this example, we check if the given expression is an IPv4 address.

dql
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_stringIpv4_correct_IpIpv4_wrong_string

true

true

false

  • Example 3

In this example, we check if the given expression is an IPv6 address.

dql
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_stringIpv6_correct_IpIpv6_wrong_string

true

true

false

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
NameTypeMandatoryDefaultConstraintsDescription

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.

dql
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_maskIpv6_maskIpv_mask_1Ipv_mask_2

127.0.0.0

127.0.0.0

127.0.0.0

127.0.0.0

  • Example 2

    dql
    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_maskIpv6_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.

NameTypeMandatoryDefaultConstraintsDescription

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.

NameTypeMandatoryDefaultConstraintsDescription

expression

string, ip

yes

The expression to check if it is a particular type of IP address.

  • Example 1

    dql
    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_corectr2_corectr3_corectr4_corectr5_corectr6_corectr7_corectr8_corect

    true

    true

    true

    false

    false

    true

    true

    true

  • Example 2

    dql
    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

    corect1wrongcorrect2

    true

    false

    true

ip

You can use this function to create an IP address.

  • Syntax

ip(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The expression to create a new IP address.

  • Example

In this example, we can use strings to create IP addresses.

dql
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

Ip1Ip2r1r2r3

127.0.01

10.0.0.1

1.1.1.1

127.0.0.1

10.0.0.1

IP address mathematical operations

  • IP address + numeric

  • IP address + IP address

  • Example 1

    dql
    data record() | fields r1=toIp("127.0.0.10")+toIp("127.0.0.10"), r2=toIp("127.0.0.10")+1

    Query result

    r1r2

    254.0.0.20

    127.0.0.11

  • IP address - numeric

  • IP address - IP address

  • Example 2

    dql
    data record() | fields r1=toIp("127.0.0.10")-toIp("10.0.0.1"), r2=toIp("127.0.0.10")-1

    Query result

    r1r2

    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The string expression that will be encoded.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The string expression that will be decoded.

  • Example

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    string

    yes

    The string expression that will be unescaped.

  • Example

    dql
    fetch logs | fields html = unescapeHtml("&lt;h2 title=&quot;I'm a header&quot;&gt;The title Attribute&lt;/h2&gt;") | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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.

    dql
    fetch logs, from:now()-10m | filter loglevel == "ERROR" | summarize count(), by:{bin(timestamp, 1m), host.name}

    Query result

    host.namebin(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.

    dql
    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.

    dql
    data record(a=-17), record(a=2), record(a=7) | fields a, bin(a, 10, at:5)

    Query result

    abin(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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    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

    dql
    fetch logs | summarize count(), by: {range(timestamp, 1d), status}

    Query result

    range(timestamp, 1d)statuscount

    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

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    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

    NameTypeMandatoryDefaultConstraintsDescription

    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:

    1. We filter log data for the HOST-123 host that includes log records containing POST /cart.
    2. Next, we display the timestamp and the filtered content.
    3. Next, we add isCheckout field with records containing the checkout string.
    4. Next, using the if function, add checkout_ts field and if the isCheckout field contains a timestamp, we populate the isCheckout field with that timestamp. If no timestamp is found the invalid value is used.
    5. Finally, using the if function, we add the cart_ts field and if the isCheckout field contains no checkout string, the timestamp is populated into cart_ts field. If no timestamp is found the invalid value is used.
    dql
    ... 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

    timestampcontentcart_tscheckout_tsisCheckout

    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

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

    NameTypeMandatoryDefaultConstraintsDescription

    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:

    dql
    fetch logs | filter matchesPhrase(content, "ERROR") and in(dt.process.name, "IIS", "MongoDB", "Postgres") | fields timestamp, content, dt.process.name | limit 1

    Query result

    timestampcontentdt.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 of a and b are contained in the array c

    dql
    ... | 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 in b or c or both.

    dql
    ... | 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.

    dql
    ... | 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.

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | filter isNotNull(host.name)

    Query result

    timestampcontentevent.typehost.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

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | filter isNull(host.name)

    Query result

    timestampcontentevent.typehost.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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    boolean

    yes

  • Example

    dql
    fetch events | limit 1 | fields a=1, b=2 | fieldsAdd isFalseOrNull(a>b)

    Query result

    abisFalseOrNull(a > b)

    1

    2

    true

isTrueOrNull

Evaluates if an expression is true or NULL.

  • Syntax

    isTrueOrNull(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    boolean

    yes

  • Example

    dql
    ... | limit 1 | fields a=1, b=2 | fieldsAdd isTrueOrNull(a>b), isFalseOrNull(a>b)

    Query result

    abisTrueOrNull(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

    NameTypeMandatoryDefaultConstraintsDescription

    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

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    long

    yes

    The numeric value for the duration.

    unit

    string

    yes

    The time unit of the duration.

  • Example

    dql
    ... | limit 1 | fields dur = 124 | fieldsAdd dur_ns = duration(dur, unit:"ns")

    Query result

    durdur_ns

    124

    124

type

Returns the type of a value as a string.

  • Syntax

    type(expression)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    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.

    dql
    ... | 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_typevar_2_typevar_3_typevar_4_typevar_5_typevar_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

    NameTypeMandatoryDefaultConstraintsDescription

    value

    boolean

    yes

  • Example

    dql
    ... | fields bValue_1 = asBoolean(TRUE), bValue_2 = asBoolean(`string`), bValue_3 = asBoolean(False), bValue_4 = asBoolean(TrUe)

    Query result

    bValue_1bValue_2bValue_3bValue_4

    true

    false

    true

asDouble

Returns double value if the value is double, otherwise, returns null.

  • Syntax

    asDouble(value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    value

    double

    yes

  • Example

    dql
    ... | fields dbl_1 = asDouble(1234.5), dbl_2 = asDouble(4.0+3.0/2.3)

    Query result

    dbl_1dbl_2

    1234.5

    5.304347826086957

asLong

Returns long value if the value is long, otherwise null.

  • Syntax

    asLong(value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    value

    long

    yes

  • Example

    dql
    ... | fields vLong_1 = asLong(83457264009472472), vLong_2 = asLong(`30`)

    Query result

    vLong_1vLong_2

    83457264009472472

asTimestamp

Returns timestamp value if the value is timestamp, otherwise, returns null.

  • Syntax

    asTimestamp(value)

  • Parameters

    NameTypeMandatoryDefaultConstraintsDescription

    value

    timestamp

    yes

  • Example

    dql
    ... | 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

    NameTypeMandatoryDefaultConstraintsDescription

    expression

    array, boolean, double, duration, ip, long, record, string, timeframe, timestamp

    yes

    An expression to add to the record.

  • Example

    dql
    fetch logs | limit 1 | fields timestamp, person = record(name="John", age=34)

    Query result

    timestampperson

    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

    dql
    data record() | fields r1=random(), r2=random(), r3=random(), r4=random()

    Query result

    r1r2r3r4

    0.362

    0.009

    0.186

    0.563

Related topics
  • Dynatrace Query Language

    How to use Dynatrace Query Language.

  • How to use DQL queries

    Find out how DQL works and what are DQL key concepts.

  • DQL compared to SQL and more

    See how DQL compares to other query languages.

  • DQL language reference

    Dynatrace Query Language syntax reference.

  • DQL commands

    A list of DQL commands.

  • DQL operators

    A list of DQL Operators.

  • DQL data types

    A list of DQL data types.

  • DQL Best practices

    Best practices for using Dynatrace Query Language.