DQL operators

Latest Dynatrace
Reference
22-min read

The following table shows a list of all the DQL operators.

Operator

Description

+

Addition

-

Subtraction or arithmetic negation

*

Multiplication

/

Division

%

Modulo

<

Less than

<=

Less than or equal to

>

Greater than

>=

Greater than or equal to

==

Equals

!=

Does not equal

not

Logical NOT (negation)

and

Logical AND

or

Logical OR

xor

Logical XOR (exclusive or)

in

Subquery comparison

@

Time alignment

~

The precedence for the operators is as follows (from strongest to weakest):

- (arithmetic negation)
*, /, %
@
+, - (subtraction)
~
==, !=, >, >=, <, <=
in
not
and
xor
or

Arithmetic operators

You can use arithmetic operators with numbers, represented by both the types long or double. In addition, some operators support the types timestamp, timeframe, duration or ip.

Operator

Description

Example

+

Addition

2 + 2.5

-

Subtraction

0.2 - 0.11

*

Multiplication

4 * 5, 60 * 1s

/

Division

10 / 2, 1h / 60

%

Modulo

4 % 2

-

Arithmetic negation

-1

ADDITION

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

(long)

(double)

(IP)

Double

(double)

(IP)

String

Boolean

Timestamp

(timestamp)

Duration

(timestamp)

(duration)

(timeframe)

Timeframe

(timeframe)

Binary

(IP)

UID

Array

Record

SUBTRACTION

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

(long)

(double)

Double

(double)

String

Boolean

Timestamp

(duration)

(timestamp)

Duration

(duration)

Timeframe

(timeframe)

Binary

(IP)

UID

Array

Record

MULTIPLICATION

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

(long)

(double)

(duration)

Double

(double)

(duration, rounded to full nanos)

String

Boolean

Timestamp

Duration

(duration)

(duration, rounded to full nanos)

Timeframe

Binary

UID

Array

Record

Integer division

When you divide a long value by another long value using the / operator, the result is also a long value, and any fractional part is discarded. To get a result with the fractional part (a double value), you need to convert or cast at least one of the operands to double (e.g., by using the toDouble function).

DIVISION

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

(long)

(double)

Double

(double)

String

Boolean

Timestamp

Duration

(duration rounded to full nanos)

(double)

Timeframe

Binary

UID

Array

Record

The data type resulting from the operation is indicated in parentheses in the table above.

MODULO

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

(long)

(double)

Double

(double)

String

Boolean

Timestamp

Duration

(duration)

Timeframe

Binary

UID

Array

Record

NEGATION

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

SELF

(long)

(double)

(duration)

Comparison operators

Operator

Description

Example

<

Less than

8 < 9, now()-1m < now()

<=

Less than or equal to

4 <= 5

>

Greater than

5 > 4, "a" > "A"

>=

Greater than or equal to

4 >=4

( ) - true or false based on the result of the operator
( ) - null

<, <=, >, >=

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Equality operators

Operator

Description

Example

==

Equals

2 == 2

!=

Does not equal

1 != 2

Equality comparisons (==, !=) use a tri-state boolean algebra (true, false, null). This means that if any side of the equality comparison is null, the overall result of the comparison is null. There are four DQL functions that cover scenarios where missing or null records need to be retrieved:

For example, the below query that uses basic filtering does not provide records with null or missing values:

fetch logs
| filter log.source != "logsourcename"  // does not provide the records where `log.source` is null or missing

However, using the isTrueOrNull function includes those records with null and missing values:

fetch logs
| filter isTrueOrNull(log.source != "logsourcename") // also provides the records where `log.source` is null or missing

( ) - false for non-comparable types in case of == operator, true for non-compatible types in case of != operator
( ) - true or false based on the result of the operator
null - if one of the operands is null
null == null - null

==, !=

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Long

Double

String

Boolean

Timestamp

Duration

Timeframe

Binary

UID

Array

Record

Logical operators

Operator

Description

Example (yields true)

not

Logical NOT (negation) - Negates a logical state

not 2==1

and

Logical AND (multiplication) - Yields true if both operands are true.

not 2==1 and 1<2

or

Logical OR (addition) - Yields true if one of the operands is true, regardless of the other operand.

1 < 2 or 1 > 2

xor

Logical XOR (exclusive OR) - Yields true if one of the operands is true, but false in case both are true.

1 < 2 xor 1 > 2

The behavior of logical operators follows the tri-state boolean logic.

AND
- true AND null = null
- null AND true = null
- false AND null = false
- null AND false = false
- null AND null = null
OR
- true OR null = true
- null OR true = true
- false OR null = null
- null OR false = null
- null OR null = null
XOR
- true XOR null = null
- null XOR true = null
- false XOR null = null
- null XOR false = null
- null XOR null = null
NOT
- NOT null = null

Iterative expressions

Iterative expressions can be used to evaluate every element of a given array or every i-th element of one or more arrays.

iAny

Checks an iterative boolean expression and returns true, if the expression was true at least once, false if it wasn't. For example:

fetch logs
| fieldsAdd a = array(1, 2, 3)
| filter iAny(a[] > 2)

iCollectArray

Collects the results of an iterative expression into an array. For example:

fetch logs
| fieldsAdd a = array(1, 2, 3), b = array(10, 11, 12)
| fieldsAdd iCollectArray(a[] + b[])

iIndex

Allows to access the index of an iterative expression element. For example, you can add the index of a value in the array and expand the array.

data record(a = array(2, 3, 7, 7, 1))
| fields a = record(value = a[], index = iIndex())
| expand a
| fields value = a[value], index = a[index]

iIndex only works in expressions where at least one iterative expression is present.

Operators for subqueries

in

The in comparison operator evaluates the occurrence of a value returned by the left side's expression within a list of values returned by the right side's DQL subquery.

Syntax

expression in [execution block]

Usage and constraints

Name

Type

Mandatory

Constraints

Description

left side

expression

yes

Either a field identifier or an expression.

The element to be found in the list returned by the right side's subquery.

right side

execution block

yes

It has to return a single field providing a list of values.

The DQL Subquery which returns the list of values to compare against.

Example

This example shows how to use the in keyword for filtering a host metric for the host's attribute:

timeseries avg(dt.host.cpu.usage), filter:dt.entity.host in [fetch dt.entity.host
 | fieldsAdd tags
 | expand tags
 | filter tags == "ServiceNow" | fields id]

Time alignment

The @ operator aligns a timestamp to the provided time unit. It rounds down the timestamp to the beginning of the time unit.

Syntax

[timestamp|duration|calendarDuration] @ unit

Left side

On the left side of the @ operator, you can use a timestamp expression, a duration expression, or a calendar duration.
If you use the @ operator without an expression on the left side, the operator will use the timestamp expression now() and will align the current time to the time unit. For example, @h is the beginning of the current hour, and equivalent to now()@h. Expressions of type duration and calendar durations are considered as an offset to now().
For example, -2M@... is equivalent to (now() - 2M)@....

Right side

The time unit can be any DQL supported duration unit including s (second), m (minute), h (hour), or a calendar duration unit like d (day), w (week), M (month), q (quarter), and y (year).

Duration units (h, m, s, ms, us, and ns) allow to add a factor, for example, @3h.
Leaving the factor out is equivalent to setting it to 1. Note the following constraints when adding such factor:

h supports all divisors of 24: 1h, 2h, 3h, 4h, 6h, 8h, 12h, 24h. @24h is similar to @1d but might differ in the case of daylight-saving times.
m and s support all divisors of 60: 1m, 2m, 3m, 4m, 5m, 6m, 10m, 12m, 15m, 20m, 30m, 60m, and equivalently for s.
ms, us, and ns support all divisors of 1000.

By default, the week unit w uses the first day of the week as defined by your configured locale. To explicitly specify the first day of the week, you can use the following time units:

w0 (Sunday)
w1 (Monday)
w2 (Tuesday)
w3 (Wednesday)
w4 (Thursday)
w5 (Friday)
w6 (Saturday)
w7 (Sunday)

For example, @w1 means midnight of Monday of the current week.

Examples

For the following examples, the current time is Wednesday, 04 September 2024, 14:47:05+0200.

Time modifier

Description

Resulting time

-2h@h

2 hours ago, aligned to the hour

Wednesday, 04 September 2024, 12:00:00+0200

-1d@d

Yesterday, aligned to the day

Tuesday, 03 September 2024, 00:00:00+0200

-7d@d

7 days ago, aligned to the day

Wednesday, 28 August 2024, 00:00:00+0200

@w0

Start of this week, from Sunday

Sunday, 01 September 2024, 00:00:00+0200

@w1

Start of this week, from Monday

Monday, 02 September 2024, 00:00:00+0200

@M

Start of this month

Sunday, 01 September 2024, 00:00:00+0200

-1M@M

Start of last month

Thursday, 01 August 2024, 00:00:00+0200

@q

Start of this quarter

Monday, 01 July 2024, 00:00:00+0200

@y

Start of this year

Monday, 01 January 2024, 00:00:00+0100

Search

You can use the ~ operator in expressions to match the value of fields against a given search string. The performed comparison is case-insensitive and supports pattern matching using wildcards. The ~ operator returns a boolean value: true in case of a match, and false otherwise.

Syntax

fieldName ~ "string literal"

Left side

You can use the field identifier of a root-level field on the left side of the ~operator. If you want to search in any other expression, create a new field first by using the fieldsAdd command. For example, the expression "hello world" ~ "hello" isn't allowed.

Right side

The string literal to search for. It can be one of the following:

A search string without a wildcard (*). For example:
```
content ~ "error"
```
A search string with a wildcard (*) as first and/or last character. For example:
```
user ~ "*dynatrace.com"
```

Returns

Search strings without wildcards

The ~ operator searches the value as a string token inside a string. Its behavior depends on the data type of the field on the left side:

If the field is of type string, the operator searches the value as a token inside the string. It's case-insensitive. For example, "Hello World" matches ~"world", but "HelloWorld" doesn't.
If the field is of type long, double, smartscape ID, IP address, or UID, the operator only matches if the string representation of its value is equal to the search string. For example, the IP address 192.0.0.1 matches ~"192.0.0.1", but not ~"192".
If the field is of type array, each element is checked. The operator matches if at least one of the elements in the array does.
If the field is of type record, the operator matches if any field name or value matches.
If the field is of type boolean, timestamp, duration, or binary, the result is always false.

Field type

Field value

Operation

Result

Note

String

"Hello WORLD!"

~"world"

true

String

"helloWorld"

~"World"

false

helloWorld is one token since there are no separators.

String

"192.168.0.7"

~"192"

true

As it’s a string, the field has four tokens.

192.168.0.7

~"192"

false

Only strings are tokenized.

192.168.0.7

~"192.168.0.7"

true

The value is auto-converted, so there's an exact match.

Long

12

~"12"

true

The value is auto-converted, so there's an exact match.

UID

uuid(1,2)

~"00000000-0000-0001-0000-000000000002"

true

The value is not tokenized, but can be auto-converted.

Smartscape ID

smartscapeId("HOST", 1)

~"HOST-0000000000000001"

true

The value is auto-converted.

Smartscape ID

smartscapeId("HOST", 1)

~"host-0000000000000001"

false

For a Smartscape ID, the check is case-sensitive.

Smartscape ID

smartscapeId("HOST", 1)

~"HOST"

false

The value isn't tokenized.

Record

record(firstName="John",lastName="Doe")

~"john"

true

Search also works in nested fields.

Record

record(firstName="John",lastName="Doe")

~"lastName"

true

Search also works in the names of nested fields.

Record

record(firstName="John",lastName="Doe")

~"name"

false

firstName and lastName are one token since they don't contain separators.

Record

record(first name="John",last name="Doe")

~"name"

true

Search also works in the names of nested fields.

Array

array(1,2,3,5,8,13)

~"3"

true

One element of the array is 3, which can be auto-converted to match ~"3".

Boolean

true

~"true"

false

Booleans aren't supported.

Duration

1h

~"1h"

false

Durations aren't supported.

Search strings with wildcards

The operator searches the pattern in the tokens of a string. Its behaviour depends on the data type of the field on the left side:

If the field is of type string, the result is true if at least one of the tokens matches the pattern.
If the field is of type array, the result is true if one of the elements of the array matches the pattern.
If the field is of type record, the result is true if the name or value of a nested field matches the pattern.
If the field is of any other type (long, double, smartscape ID, IP address, UID, boolean, timestamp, duration, or binary) patterns aren't supported and the result is always false.

Field type

Field value

Operation

Result

Note

String

"AuthenticationError"

~"*error"

true

String

"There was an AuthenticationError"

~"authentication*"

true

String

"There was an NoAuthenticationError"

~"authentication*"

false

String

"helloWorld"

~"*ow*"

true

String

"hello world"

~"*ow*"

false

String

"192.168.0.7"

~"192.168.*"

true

It matches as it's a string and not an IP address.

Record

record(firstName="John",lastName="Doe")

~"*name"

true

The string matches the name of the nested field in the record.

Record

record(firstName="John",lastName="Doe")

~"*do*"

true

The string matches the record.

Array

array("hello", "world", "myCustomName")

~"my*"

true

The string matches within the array.

192.168.0.7

~"192*"

false

Only strings allow patterns.

Long

192

~"1*"

false

Only strings allow patterns.

Smartscape ID

smartscapeId("HOST", 1)

~"HOST*"

false

Only strings allow patterns.

Boolean

true

~"t*"

false

Only strings allow patterns.

Duration

1h

~"*h"

false

Only strings allow patterns.

DQL operators

Arithmetic operators

Comparison operators

Equality operators

Logical operators

Iterative expressions

iAny

iCollectArray

iIndex

Operators for subqueries

in

Time alignment

Syntax

Left side

Right side

Examples

Search

Syntax

Left side

Right side

Returns

Search strings without wildcards

Search strings with wildcards

Related topics