DQL functions

Functions can be used to perform computations on fields of DQL commands.

Aggregation functions

Aggregation functions compute results from a collection of values.

count

Counts the total number of records.

• Syntax

  count()

• Example

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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:

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long, duration	yes

• Example

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long, duration	yes

• Example

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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.ip	activity	actions	products
  152.125.149.246	100	[{"fields":[{"name":"element","type":"string"}],"values":{"element":"com.travel.frontend.sea...	[{"fields":[{"name":"element","type":"string"}],"values":{"element":"Paris"}},{"field......
  124.75.6.61	84	[{"fields":[{"name":"element","type":"string"}],"values":{"element":"com.travel.frontend.bo...	[{"fields":[{"name":"element","type":"string"}],"values":{"element":"Paris"}},{"field...

collectArray

Collects the values of the provided field into an array (preservation of order not guaranteed).

• Syntax

  collectArray(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  expression	array, boolean, double, duration, ip, long, record, string, timeframe, timestamp	yes

• Example

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

• Syntax

  takeMin(expression)
takeMax(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  expression	any	yes		No nested aggregation functions.	The expression used in the min/max calculation.

• Example

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long	yes

• Example

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long	yes

• Example

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

  Name	Type	Mandatory	Default	Constraints	Description
  expression1	double, long	yes
  expression2	double, long	yes

• Example

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

Query result

takeAny

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

• Syntax

  takeAny(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  loglevel	takeAny(host.name)
  DEBUG	HOST NAME 1
  ERROR	HOST NAME 1
  INFO	HOST NAME 1
  NONE	HOST NAME 1
  WARN	HOST NAME 1

Conversion functions

Conversion functions convert the expression or value from one data type to another type.

toArray

Returns the value if it is an array. Otherwise, converts a value to the single element array holding that value.

• Syntax

  toArray(value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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).

  dqlcopydownload
  ...
  | 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_1	array_2
  [2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 2, 3, 5, 7, 11]	[235711131719]

toBoolean

Converts a value to Boolean if the value is of a suitable type. If the argument is an array, the element at position 0 is converted.

Use asBoolean(value) function to return if the value is boolean or variant<boolean>, otherwise NULL.

Converts string values true or TRUE to a Boolean true.The comparison is case insensitive. Converts other values to Boolean false.
Converts numeric value 0 to Boolean false. Converts other numeric values to Boolean true.

• Syntax

  toBoolean(value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  value	boolean, double, long, string, array	yes

• Example

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

  Query result

  bValue1	bValue2	bValue3	bValue4	bValue5	bValue6
  true	true	false	true	false	false

toDouble

Converts a value to double if the value is of a suitable type. If the argument is an array, the element at position 0 is converted.

Use asDouble(value) function to return if the value is double or variant<double>, otherwise NULL.

• Syntax

  toDouble(value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  value	double, long, string, boolean, ip, timestamp, duration, array	yes

• Example

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

  Query result

  dbl_1	dbl_2
  1234.5	5.0

toDuration

Converts a value to duration if the value is of a suitable type. If the argument is an array, the element at position 0 is converted.

• Syntax

  toDuration(value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  value	duration, double, long, string, timeframe, array	yes

• Example

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

  Query result

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

  Name	Type	Mandatory	Default	Constraints	Description
  value	long, double, string, boolean, ip, timestamp, duration, array	yes

• Example

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

  cnv_long1	cnv_long2	cnv_long3
  83457264009472472	30	25

toString

Returns the string representation of a value.

• Syntax

  toString(value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  value	double, boolean, timestamp, timeframe, duration, ip, array, record	yes			Parameter that should be transformed into text form.

• Example 1

  dqlcopydownload
  ...
  | 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_Number	str_Array	str_Boolean	str_IPaddr	str_Time	str_Variant
  1	[1, 2, 3]	true	192.168.0.1	2022-08-02T09:15:10.360391000 +0000	1

• Example 2

  dqlcopydownload
  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

  dqlcopydownload
  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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  value	timeframe, string, array	yes

• Example

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

  Query result

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

  Name	Type	Mandatory	Default	Constraints	Description
  value	timestamp, double, long , string, array	yes

• Example

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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long	yes			The angle to be converted from radians to degrees.

• Example

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

  Query result

  degrees	degrees_null
  182.7735366467326	null

degreeToRadian

Converts the numeric expression of an angle in degrees to an approximately equivalent angle as expressed in radians. Returns null if numeric_expr evaluates to NULL.

• Syntax

  degreeToRadian(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  expression	double, long	yes			The angle to be converted from radians to degrees.

• Example

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

  Query result

  radians	radians_null
  0.9948376736367679	null

Converting typed variants

Functions prefixed with as<Type> will try to cast the input data type to the target but not do any conversions. It will fail for strongly typed fields and produce null for incompatible variants. These functions are applicable after parsing.

dqlcopydownload
...
| 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*T	asNumber	asInteger	asLong	asDouble	asString	asBoolean	asTimestamp	asDuration	asTimeframe	asGeopoint	asIpAddress	asSummaryStats	asBinary	asArray	asRecord
Integer	Integer
Long	Long
Double	Double
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

  Name	Type	Mandatory	Default	Constraint	Description
  expression	string, binary	yes			A string or binary expression to encode.

• Parameters: decode

  Name	Type	Mandatory	Default	Constraint	Description
  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

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  value	timeframe	yes

• Example

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

  Query example

  asTimeframe(timeframe(from: - 24h, to: - 2h)) start	asTimeframe(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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	string expression, ip address	yes			The expression to convert an expression to an IP address.

• Example

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

  Query result

  r1	r2
  1.1.1.1	127.0.0.1

numberToHexString

Converts a number to a hexadecimal string.

• Syntax

  numberToHexString(expression)

• Parameters

  Name	Type	Mandatory	Default	Consraints	Descriptiont
  expression	numeric expression	yes			The numeric expression that will be converted to a hexadecimal string.

• Example

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

  Query result

  r1	r2	r3	r4
  7ffffffffffffff	ffffffffffffff00	80000000	ffffffff80000000

hexStringToNumber

Converts a hexadecimal string to a number.

• Syntax

  hexStringToNumber(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  expression	string expression	yes			The string expression that will be converted to a number.

• Example

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

  Query result

  r1	r2	r3	r4
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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).

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

  Name	Type	Mandatory	Default	Constraints	Description
  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).

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

  Name	Type	Mandatory	Default	Constraints	Description
  expression	array	yes			The array from which to compute a percentile.
  percentile	double, long	yes			The percentile to compute, between 0 and 100.

• Example

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

  Query result

  timestamp	arrayPercentile(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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

Query result

arrayRemoveNulls

Returns the array where NULL elements are removed.

• Syntax

  arrayRemoveNulls(array)

• Parameters

• Example

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  array	array	yes

• Example

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

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  array	array	yes

• Example

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

arrayIndexOf

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

• Syntax

  arrayIndexOf(array, value)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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

  dqlcopydownload
  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

  r1	r2	r3
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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

  dqlcopydownload
  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

  r1	r2	r3
  3	1	-1

String functions

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  Examples of event processing using DQL matchesValue function:

  Part of the input event	Processing query	Match result	Description
  attribute="Dynatrace"	matchesValue(attribute, "dynaTrace")		Case insensitive equality.
  attribute="User 'käärmanü' failed to login from 192.168.0.1"	matchesValue(attribute, "192.168.0.1")		The whole attribute value is considered.
  attribute="User 'käärmanü' failed to login from 192.168.0.1"	matchesValue(attribute, "*192.168.0.1")		The value ends with 192.168.0.1.
  attribute="User 'käärmanü' failed to login from 192.168.0.1"	matchesValue(attribute, "user*")		The value starts with user (case-insensitively).
  attribute="User 'käärmanü' failed to login from 192.168.0.1"	matchesValue(attribute, "*failed to log*")		The value contains the string failed to log.
  attribute="Österreich"	matchesValue(attribute, "österreich")		Case insensitive only for ASCII characters.
  attribute="Österreich"	matchesValue(attribute, "Österreich")		Exact match.
  attribute=["Java", "DOCKER", "k8s"]	matchesValue(attribute, "docker")		The function handles multi-value attributes in "any-match" manner, in this case, docker is matched in the second value.
  attribute=["Java11", "java17"]	matchesValue(attribute, "java")		None of the values is equal to string java.
  attribute=["Java11", "java17"]	matchesValue(attribute, "java*")		Both values start with a string java.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  timestamp	content	event.type	str_found
  2022-08-03 11:27:19	2022-08-03 09:27:19.836 WARN 1 --- [-QueueProcessor] i.j.internal.reporters.RemoteReporter : FlushCommand execution failed! Repeated errors of this command will not be logged. io.jaegertracing.internal.exceptions.SenderException: Failed to flush spans.	LOG	true
  2022-08-03 12:53:26	2022-08-03T10:52:31Z localhost haproxy[12529]: 192.168.19.100:38440 http-in~ individual_servers/apmng6 217/0/0/1/218 HTTP_STATUS 200 284 - - --NN 5749/5745/0/1/0 0/0	LOG	false

startsWith

Checks if a string expression starts with a prefix. Returns true if does, false otherwise.

• Syntax

  startsWith(expression, prefix [, caseSensitive])

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  str1_start	str2_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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  str1_end	str2_end
  true	false

• Example 2

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

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

  Query result

  timestamp	content	log.source	event.type	dt.entity.host	dt.entity.process_group_instance
  2022-08-11 11:21:26	2022-08-11T09:21:26Z localhost haproxy[12529]: 192.168.33.178:46070 http-in~ local/local0 19/0/1/110/130 HTTP_STATUS 204 64 - - ---- 5753/5749/358/359/0 0/0 {|||us-ea-1-54-88-80-100-DA} {|}	/var/spool/cws/umsaywsjuo/HOST-PS-319/PGI-04050984636/pgi.log	LOG	HOST-PS-319	PGI-04050984636
  2022-08-11 11:29:26	2022-08-11T09:29:26Z localhost haproxy[12529]: 192.168.33.178:46070 http-in~ local/local0 19/0/1/110/130 HTTP_STATUS 204 64 - - ---- 5753/5749/358/359/0 0/0 {|||us-ea-1-54-88-80-100-DA} {|}	/var/spool/cws/umsaywsjuo/HOST-PS-319/PGI-0405543/pgi.log	LOG	HOST-PS-319	PGI-0405543

like

Tests if a string expression matches a pattern. If the pattern does not contain percent signs, like() acts as the == operator (equality check). A percent character in the pattern (%) matches any sequence of zero or more characters. An underscore in the pattern (\_) matches a single character.

• Syntax

  like(expression, pattern)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  expression	string	yes
  pattern	string	yes

• Example

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

concat

Concatenates the expressions into a single string.

• Syntax

  concat(expression, …)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  str_concatenated
  DQL is awesome!

lower

Converts a string to lowercase.

• Syntax

  lower(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

• Syntax

  stringLength(expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  content	stringLength(content)
  Dec 21 11:38:22 ls-ub-lf40a86v systemd[1]: Finished Message of the Day.	71
  Dec 21 11:29:12 ls-ub-lf40a86v systemd[1]: Finished Daily man-db regeneration.	78

substring

Gets a code unit range using a start index (inclusive) and an end index (exclusive).

Returns an empty string if from >= to.

Indexes >=0 are relative to the start of the string and address consecutive characters from left to right, starting from the index position.

Indexes <=-1 are relative to the last character of the string and are used to address characters from the right side of an expression, for example, -2 is the penultimate character.

Positive indexes beyond the bounds of the string are assigned to the string length.

Negative indexes beyond the bounds of the string are equal to 0. For example, in the 321 string, the index -4 is beyond the bounds of the string therefore it equals 0. However, the index -2 is located within the bounds of that string and extracts 21 if used as a from the index.

The returned substring never starts or ends with an incomplete UTF-16 surrogate pair. Instead of that, it starts or ends with a question mark. This safeguards against the creation of invalid Unicode strings.

• Syntax

  substring(expression [, from] [, to])

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  ...
  | 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 🀧), is returned as a question mark.

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  a	b	c
  ,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.

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

  Query result

  content	splitString(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

  Name	Type	Mandatory	Default	Constraints	Description
  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`.

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

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  content	untrimmed	stringLength(untrimmed)	trim(untrimmed)	stringLength(trim(untrimmed))
  2022-12-21 08:50:01.207 UTC \[001a71fa] info \[native] 139988444554816(ThreadPoolExecutor-0\_0) - \[asynchronous\_strategy] Not running containters	2022-12-21 08:50:01.207 UTC \[001a71fa] info \[native] 139988444554816(ThreadPoolExecutor-0\_0) - \[asynchronous\_strategy] Not running containters	147	2022-12-21 08:49:01.111 UTC \[001a7212] info \[native] 139988436162112(ThreadPoolExecutor-0\_1) - \[asynchronous\_strategy] Not running containters	145
  Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...	Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...	75	Dec 21 11:38:21 ls-ub-lf40a86v systemd\[1]: Starting Message of the Day...	73

punctuation

Extracts punctuation characters out of an input string.

• Syntax

  punctuation(expression, [, count] [, withSpace])

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

  dqlcopydownload
  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

  text1	text2	text3	text4
  $();	$(	$(_);	$(_

levenshteinDistance

Computes the Levenshtein distance between two input strings.

• Syntax

  levenshteinDistance(expression, expression)

• Parameters

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  Query result

  distance1	distance2
  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

  Name	Type	Mandatory	Default	Constraints	Description
  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.

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

  t	formatted	year	month	week	dayofWeek	hour
  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

  Name	Type	Mandatory	Default	Constraints	Description
  timestamp	timestamp	yes

• Example

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

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

  Name	Type	Mandatory	Default	Constraints	Description
  millis	long	yes			Milliseconds since unix start time.

• Example

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  nanos	long	yes			Nanoseconds since unix start time.

• Example

  dqlcopydownload
  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

  Name	Type	Mandatory	Default	Constraints	Description
  seconds	long	yes			Seconds since unix start time.

• Example

  dqlcopydownload
  fetch bizevents
  | limit 1
  | fields t_s =  timestampFromUnixSeconds(1665064316)

  Query result

  t_s
  06/10/2022, 15:51:56