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
(long) (double)
(double) (double) (timestamp) (timestamp) (duration) (timeframe) (timeframe) (long) (double) (double) (double) (duration) (timestamp (duration) (timeframe) (long) (double) (duration) (double) (double) (duration, rounded to full nanos) (duration) (duration, rounded to full nanos) (long) (double) (double) (double) (duration) (duration rounded to full nanos) (double) (long) (double) (double) (double) (duration) (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
trueANDnull=nullnullANDtrue=nullfalseANDnull=falsenullANDfalse=falsenullANDnull=null
-
OR
trueORnull=truenullORtrue=truefalseORnull=nullnullORfalse=nullnullORnull=null
-
XOR
trueXORnull=nullnullXORtrue=nullfalseXORnull=nullnullXORfalse=nullnullXORnull=null
-
NOT
- NOT
null=null
- NOT
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.
- For details see, string functions.
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
- ( ) - true/false based on result of operator
- ( ) - 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]
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]
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 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:
hsupports all divisors of24:1h,2h,3h,4h,6h,8h,12h,24h.@24his similar to@1dbut might differ in the case of daylight-saving times.mandssupport all divisors of60:1m,2m,3m,4m,5m,6m,10m,12m,15m,20m,30m,60m, and equivalently fors.ms,us, andnssupport all divisors of1000.
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