DQL operators

Numerical operators

The types long, double and timestamp real represent numerical types. The following operators can be used between pairs of these types

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

<

Lower

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

<=

Lower than or equal

4<=5

>

Greater

5 > 4

>=

Greater than or equal

4 >=4

==

Equals

2 == 2

!=

Not equals

1 != 2

ADDITION
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
(long)
(double)
Double
(double)
(double)
String
Boolean
Timestamp
(timestamp)
Duration
(timestamp)
(duration)
(timeframe)
Timeframe
(timeframe)
Binary
Array
Record
SUBTRACT
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
(long)
(double)
Double
(double)
(double)
String
Boolean
Timestamp
(duration)
(timestamp
Duration
(duration)
Timeframe
(timeframe)
Binary
Array
Record
MULTIPLY
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
(long)
(double)
(duration)
Double
(double)
(double)
(duration, rounded to full nanos)
String
Boolean
Timestamp
Duration
(duration)
(duration, rounded to full nanos)
Timeframe
Binary
Array
Record
DIVIDE
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
(long)
(double)
Double
(double)
(double)
String
Boolean
Timestamp
Duration
(duration)
(duration rounded to full nanos)
(double)
Timeframe
Binary
Array
Record
MODULO
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
(long)
(double)
Double
(double)
(double)
String
Boolean
Timestamp
Duration
(duration)
Timeframe
Binary
Array
Record
NEGATE
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
SELF
(long)
(double)
(duration)

Logical or equality operators

Operator
Description
Example (yields true)

==

Equals - Yields true if both operands are not null and equal to each other. Otherwise, false.

2==2, "a" == "a"

!=

Not equals - Yields null, if one of the operands is null, or if the operands are not equal to each other.

2!=1, "b" != "a"

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

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

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 two DQL functions that cover scenarios where missing or NULL records need to be retrieved:

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

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

However, using the isTrueOrNull function renders those null and missing values:

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

String operators

By default, all string values in matching expressions are case-sensitive. The caseSensitive parameter provides the ability to change case sensitivity.

Operator
Description
Example (yields true)

==

Equals

"ab" == lower("aB")

!=

Not equals

"ab" != "aB"

<

Lower

"b" < "c"

<=

Lower than or equal

"ab" <= "aB"

>

Greater

"a" > "A"

>=

Greater than or equal

"ab" >= "Ab"

  • ( ) - false for non-comparable types in case of == operator, true for non-compatible types in case of != operator
  • ( ) - true/false comparable types based on operator
  • NULL - if one of the operands is NULL
  • NULL == NULL - null
==, !=
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
  • ( ) - true/false based on result of operator
  • ( ) - null
<, <=, >, >=
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record
Long
Double
String
Boolean
Timestamp
Duration
Timeframe
Binary
Array
Record

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]

Comparison operators

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]