dt-dql-essentials

어떤 스킬인가요?

Core DQL syntax, pitfalls, query patterns, and query optimization. Load to write, build, fix, or OPTIMIZE a DQL query — prevents syntax errors and makes queries faster, more efficient, and cheaper (less data scanned = lower query consumption/cost per run). Covers fetch commands, data models, field namespaces, time alignment, entity/smartscape patterns, metric discovery, and performance/cost optimization (filter early, bucket filters, short time ranges, field selection, sampling, cardinality). Trigger: \"write/build/fix a DQL query\", \"DQL syntax\", \"query logs/spans/metrics\", \"create a timeseries\", \"optimize my DQL\", \"make my query faster/cheaper\", \"reduce DQL cost/consumption/scanned data\", \"keep DQL cost under control\". Do NOT use to explain an existing query or answer product questions. For MONITORING a tenant's ACTUAL query consumption/billing (how much queries cost, who scanned most, cost trends) use dt-platform-costs — this tunes the query text, not billing data.

설치 방법

프로젝트 폴더에서 아래 명령어를 실행하고, 설치할 에이전트를 선택하세요.

설치 명령을 직접 실행해야 적용됩니다. 지원 에이전트와 필요한 권한·라이선스는 제작자의 안내를 확인하세요.

스킬 원문 파일

# DQL Commands

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`append`](#append) · [`data`](#data) · [`dedup`](#dedup) · [`describe`](#describe) · [`expand`](#expand) · [`fetch`](#fetch) · [`fields`](#fields) · [`fieldsAdd`](#fieldsadd) · [`fieldsFlatten`](#fieldsflatten) · [`fieldsKeep`](#fieldskeep) · [`fieldsRemove`](#fieldsremove) · [`fieldsRename`](#fieldsrename) · [`fieldsSnapshot`](#fieldssnapshot) · [`fieldsSummary`](#fieldssummary) · [`filter`](#filter) · [`filterOut`](#filterout) · [`join`](#join) · [`joinNested`](#joinnested) · [`limit`](#limit) · [`load`](#load) · [`lookup`](#lookup) · [`makeTimeseries`](#maketimeseries) · [`metrics`](#metrics) · [`parse`](#parse) · [`search`](#search) · [`smartscapeEdges`](#smartscapeedges) · [`smartscapeNodes`](#smartscapenodes) · [`sort`](#sort) · [`summarize`](#summarize) · [`timeseries`](#timeseries) · [`traverse`](#traverse)

## `append`
Merges the current list of records with another list of records.
`append source`
  `source` (—) — The sub-query to append.

## `data`
Creates a static dataset to work with.
`data [json ,] record, …`
  `json:?` (String) — A JSON string that holds an object or an array of objects that will represent the data set.
  `record*` (Record) — One of the static records that should be part of the data set.
  `expression*` (any) — An expression to add to the record.  [assign:optional]

## `dedup`
Removes duplicates from a list of records.
`dedup expression, … [, sort: expression [asc|desc], …]`
  `expression*` (any) — An expression defining the how duplicate entries should be sorted (the first record by this order will be kept).
  `direction:*?` (—) — An expression defining the how duplicate entries should be sorted (the first record by this order will be kept).  [default:"ascending"]
  `filterPushThrough:?` (Boolean) — Whether the filter should be push through the dedup command.  [default:FALSE]
  `limit:?` (Long) — The maximum number of records returned by the dedup command.  [default:9223372036854775807]
  `expression*` (any) — An expression for which all different unique values should be kept.

## `describe`
Describes the schema of a given data object.
`describe dataObject`
  `dataObject` (—) — The data object to describe.

## `expand`
Expands an array into separate records.
`expand expression [, limit]`
  `expression` (Array) — A field or an array expression that should be expanded.  [assign:optional]
  `limit:?` (Long) — The maximum number of items to expand.  [default:2147483647, min:1]

## `fetch`
Loads data from the resource.
`fetch dataObject [, bucket: name, …] [, from] [, to] [, timeframe] [, samplingRatio] [, scanLimitGBytes]`
  `dataObject` (—) — The data object to fetch data for.
  `name*` (—) — A bucket (name or pattern) to retrieve data from.
  `from:?` (Duration|Long|String|Timestamp) — The start of the timeframe (if no explicit timeframe is specified). A duration is interpreted as an offset from `now()`.  [min:0]
  `to:?` (Duration|Long|String|Timestamp) — The end of the timeframe (if no explicit timeframe is specified). A duration is interpreted as an offset from `now()`.  [min:0]
  `timeframe:?` (String|Timeframe) — The desired timeframe (if not specified, global timeframe is used).
  `samplingRatio:?` (Double|Long) — The desired sampling ratio.  [min:1]
  `scanLimitGBytes:?` (Long) — The maximum number of gigabytes that shall be scanned during loading data.

## `fields`
Keeps only the specified fields.
`fields expression, …`
  `expression*` (any) — An expression that will be retained in the result list.  [assign:optional]

## `fieldsAdd`
Evaluates an expression and appends or replaces a field.
`fieldsAdd expression, …`
  `expression*` (any) — An expression, its result will be added to the record list.  [assign:optional]

## `fieldsFlatten`
Adds fields from a record to the current record list.
`fieldsFlatten expression [, prefix] [, fields: { [field, …] }] [, depth]`
  `expression` (Record) — An expression returning the record from which to add the fields.
  `prefix:?` (—) — Prefix that is applied to all fields that are going to be added.
  `field*` (any) — Field to add from the record.  [assign:optional]
  `depth:?` (Long) — Flatten nested records until the specified depth is reached.  [default:1, min:1]

## `fieldsKeep`
Keeps the fields in the result.
`fieldsKeep field, …`
  `field*` (any) — A field or fields based on a pattern to keep in the record list.

## `fieldsRemove`
Removes fields from the result.
`fieldsRemove field, …`
  `field*` (any) — A field or fields based on a pattern to remove from the record list.

## `fieldsRename`
Renames a field.
`fieldsRename field, …`
  `field*` (any) — A field to rename (needs to be fully qualified).  [assign:mandatory]

## `fieldsSnapshot`
Loads a fields snapshot for a data source.
`fieldsSnapshot dataObject [, by: { [field, …] }] [, bucket: name, …]`
  `dataObject` (—) — The data object for which to load the fields snapshot.
  `field*` (any) — A field name from the result schema to group by.
  `name*` (—) — A bucket for which to retrieve fields.

## `fieldsSummary`
Calculates the facets for the listed fields.
`fieldsSummary [topValues] [, extrapolateSamples ,] field, …`
  `topValues:?` (Long) — The number of top values for each field.  [default:20]
  `extrapolateSamples:?` (Boolean) — Whether the result should be extrapolated using the sampling rate.  [default:FALSE]
  `field*` (any) — A field identifier.

## `filter`
Reduces the number of records in a list by excluding all records not matching a specific condition.
`filter condition`
  `condition` (Boolean) — The condition all records have to fulfill.

## `filterOut`
Removes records that match a specific condition.
`filterOut condition`
  `condition` (Boolean) — The condition all records have to fulfill.

## `join`
Joins all records from the source and the sub-query as long as they fulfill the join condition.
`join joinTable [, kind] [, executionOrder ,] on: condition, … [, prefix] [, fields: { [field, …] }]`
  `joinTable` (—) — Sub-query for records with fields to add or overwrite in the input.
  `kind:?` (—) — Defines how records get joined.  [default:inner]
  `executionOrder:?` (—) — Defines which side of the join will be executed first.  [default:auto]
  `broadcast:?` (—) — Defines broadcasting strategy.  [default:enabled]
  `condition*` (—) — Records must match this condition in order to be joined.
  `prefix:?` (—) — Specifies a prefix string for all new fields.  [default:"right."]
  `field*` (any) — A field from the sub-query to add to the source.  [assign:optional]

## `joinNested`
Joins all records from the source and the sub-query as long as they fulfill the join condition. The matching results from the sub-query are added as an array of nested records.
`joinNested joinTable, alias, on: condition, … [, executionOrder] [, fields: { [field, …] }]`
  `joinTable` (—) — Sub-query for records with fields to add or overwrite in the input.  [assign:mandatory]
  `condition*` (—) — Records must match this condition in order to be joined.
  `executionOrder:?` (—) — Defines which side of the join will be executed first.  [default:auto]
  `broadcast:?` (—) — Defines broadcasting strategy.  [default:enabled]
  `field*` (any) — A field from the sub-query to add to the source.  [assign:optional]

## `limit`
Limits the number of returned records.
`limit size`
  `size` (Long) — The maximum number of records.  [min:0]

## `load`
Load command to read tabular file stored by Save command.
`load tabularFile [, offset]`
  `tabularFile` (—) — The name of the tabular file that was saved.
  `offset:?` (Long) — Number of skipped records.  [min:0]

## `lookup`
Loads an external record and adds the fields to the current record.
`lookup lookupTable [, sourceField ,] lookupField [, prefix] [, fields: { [field, …] }] [, executionOrder]`
  `lookupTable` (—) — Sub-query for records with fields to add or overwrite in the input.
  `sourceField:?` (any) — The field to use from the source for equality comparison.
  `lookupField:` (any) — The field to use from the sub-query for equality comparison.
  `prefix:?` (—) — Specifies a prefix string for all new fields.  [default:"lookup."]
  `field*` (any) — A field from the sub-query to add to the source.  [assign:optional]
  `executionOrder:?` (—) — Defines which side of the join will be executed first.  [default:auto]
  `broadcast:?` (—) — Defines broadcasting strategy.  [default:enabled]

## `makeTimeseries`
Converts the input into the time series format.
`makeTimeseries [by: { [expression, …] }] [, interval] [, bins] [, from] [, to] [, timeframe] [, time] [, spread] [, nonempty ,] aggregation, …`
  `expression*` (any) — An expression to split the series by.  [assign:optional]
  `interval:?` (Duration) — An expression that provides the duration of a bins in the series.
  `bins:?` (Long) — An positive non-zero long integer number that defines the number of bins that shall be created within the series timeframe.  [default:120, min:0]
  `from:?` (Duration|Timestamp) — The global timeframe start for the series for which values should be considered.
  `to:?` (Duration|Timestamp) — The global timeframe end for the series for which values should be considered.
  `timeframe:?` (Timeframe) — The global timeframe end for the series for which values should be considered.
  `time:?` (Timestamp) — A timestamp expression that provides the timestamp for the bucket calculation of the values in the series.
  `spread:?` (Timeframe) — A timeframe expression that provides the timeframe for the bucket calculation of the values in the series.
  `nonempty:?` (Boolean) — Produces empty series when there is no data.  [default:FALSE]
  `aggregation*` (—) — The series that shall be calculated.  [assign:optional]
  `default:*?` (Double|Long) — The default value in the series bin, if no value is present.
  `rate:*?` (Duration) — The rate the resulting series values shall be scaled to.

## `metrics`
Loads metric data.
`metrics [[bucket: name, …] [, from] [, to] [, timeframe]]`
  `name*` (—) — A bucket (name or pattern) to retrieve data from.
  `from:?` (Duration|String|Timestamp) — The global timeframe start for retrieving metrics.
  `to:?` (Duration|String|Timestamp) — The global timeframe end for retrieving metrics.
  `timeframe:?` (String|Timeframe) — The global timeframe for retrieving metrics.

## `parse`
Parses a record field and puts the result(s) into one or more fields as specified in the pattern.
`parse expression, pattern [, preserveFieldsOnFailure] [, parsingPrerequisite]`
  `expression` (String) — A field or string expression to parse.
  `pattern` (—) — The parse pattern.
  `preserveFieldsOnFailure:?` (Boolean) — Determines if fields values should be preserved if parsing fails.  [default:FALSE]
  `parsingPrerequisite:?` (Boolean) — Determines if record should be parsed.  [default:TRUE]
  `baseTime:?` (Timestamp) — A timestamp expression providing the base time for date/time parsing.

## `search`
Reduces the number of records in a list by excluding all records where the search condition doesn't apply.
`search condition`
  `condition` (—) — The condition all records have to fulfill.
  `caseSensitive:?` (Boolean) — Whether search patterns should be considered as case-sensitive (default: false).  [default:FALSE]
  `scope:?` (—) — Where search patterns should be searched.  [default:"all"]
  `field*` (any) — A field on which to apply search patterns.
  `field*` (any) — A field to be excluded from the search for search patterns.

## `smartscapeEdges`
Returns the edges of a smartscape graph.
`smartscapeEdges [from] [, to] [, timeframe ,] type, …`
  `from:?` (Duration|String|Timestamp) — The global timeframe start for retrieving the smartscape edges.
  `to:?` (Duration|String|Timestamp) — The global timeframe end for retrieving the smartscape edges.
  `timeframe:?` (String|Timeframe) — The global timeframe for retrieving the smartscape edges.
  `type*` (—) — The type or type pattern of the smartscape edges.

## `smartscapeNodes`
Returns the nodes of a smartscape graph.
`smartscapeNodes [from] [, to] [, timeframe ,] type, …`
  `from:?` (Duration|String|Timestamp) — The global timeframe start for retrieving the smartscape edges.
  `to:?` (Duration|String|Timestamp) — The global timeframe end for retrieving the smartscape edges.
  `timeframe:?` (String|Timeframe) — The global timeframe for retrieving the smartscape edges.
  `type*` (—) — The type or type pattern of the smartscape nodes.

## `sort`
Sorts the records.
`sort expression [asc|desc], …`
  `expression*` (any) — An expression defining the sort order.
  `direction:*?` (—) — The direction of the sorting.  [default:"ascending"]

## `summarize`
Groups together records that have the same values for a given field and aggregates them.
`summarize aggregation, … [, by: { [expression, …] }]`
  `expression*` (any) — An expression to group by.  [assign:optional]
  `aggregation*` (any) — An aggregation function (min, max, avg, ...).  [assign:optional]

## `timeseries`
Reads metrics in the time series format from the data source.
`timeseries [bucket: name, …] [, from] [, to] [, timeframe] [, by: { [expression, …] }] [, filter] [, interval] [, bins] [, shift] [, nonempty] [, union ,] metric, …`
  `name*` (—) — A bucket (name or pattern) to retrieve data from.
  `from:?` (Duration|String|Timestamp) — The global timeframe start for the series for which values should be considered.
  `to:?` (Duration|String|Timestamp) — The global timeframe end for the series for which values should be considered.
  `timeframe:?` (String|Timeframe) — The global timeframe for the series for which values should be considered.
  `expression*` (any) — An expression to split the series by.  [assign:optional]
  `filter:?` (Boolean) — An additional filter condition that shall be applied on the source records before time-/space-aggregation.
  `interval:?` (Duration) — A suggested interval for the series.
  `bins:?` (Long) — A suggested number of bins in the series.  [default:120, min:0]
  `shift:?` (Duration) — Shifts the effective timeframe by the provided duration.
  `nonempty:?` (Boolean) — Produces empty series when there is no data.  [default:FALSE]
  `union:?` (Boolean) — Whether the results will be combined as union if multiple metric keys are specified.  [default:FALSE]
  `metric*` (—) — The metric that shall be calculated.  [assign:optional]
  `rollup:*?` (—) — The rollup type that shall be used for the metric.
  `default:*?` (Double|Long) — The default value to fill gaps.
  `rate:*?` (Duration) — The rate the resulting series values shall be scaled to.

## `traverse`
Switches from the current list of records to a different one.
`traverse edgeType, …, targetType, … [, direction] [, fieldsKeep: { [field, …] }] [, nodeId]`
  `edgeType*` (—) — The type of the edge to traverse.
  `targetType*` (—) — The type of the target nodes.
  `direction:?` (—) — The traversal direction.  [default:"forward"]
  `field*` (any) — A field or field pattern to keep in the traversal history.
  `nodeId:?` (SmartscapeId) — The field that contains the id of the node in the incoming records.  [default:id]

# DQL Data Types

| key | name | description |
|-----|------|-------------|
| `array` | Array | A data structure that contains a sequence of values, each identified by index. |
| `binary` | Binary | A sequence of bytes. |
| `boolean` | Boolean | Boolean has only two possible values: true and false. |
| `double` | Double | Double-precision 64-bit IEEE 754 floating point. |
| `duration` | Duration | A duration between two timestamps, consisting of an amount and a time unit |
| `long` | Long | The signed long has a minimum value of -2^63 and a maximum value of 2^63-1 |
| `record` | Record | A set of key-value pair data whose value can be any DQL data type. |
| `string` | String | Sequence of characters with a specified character set. |
| `timeframe` | Timeframe | A specific time frame with a starttime and an endtime as timestamps with nanosecond precision. |
| `timestamp` | Timestamp | A reference to a point in time with the precision of a nanosecond. |
| `uid` | UID | A data type that is used for spans to represent 64-bit identifiers and 128-bit identifiers. |

# DQL Functions — Aggregation

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`avg`](#avg) · [`collectArray`](#collectarray) · [`collectDistinct`](#collectdistinct) · [`correlation`](#correlation) · [`count`](#count) · [`countDistinct`](#countdistinct) · [`countDistinctApprox`](#countdistinctapprox) · [`countDistinctExact`](#countdistinctexact) · [`countIf`](#countif) · [`max`](#max) · [`median`](#median) · [`min`](#min) · [`percentRank`](#percentrank) · [`percentile`](#percentile) · [`percentileFromSamples`](#percentilefromsamples) · [`percentiles`](#percentiles) · [`stddev`](#stddev) · [`sum`](#sum) · [`takeAny`](#takeany) · [`takeFirst`](#takefirst) · [`takeLast`](#takelast) · [`takeMax`](#takemax) · [`takeMin`](#takemin) · [`variance`](#variance)

## `avg`
Calculates the average value of a field for a list of records.
`avg(expression)`
  `expression` (Double|Duration|Long) — The expression from which to compute the average.
  → Double|Duration

## `collectArray`
Collects the values of the provided field into an array (preservation of order not guaranteed).
`collectArray(expression [, maxLength] [, expand])`
  `expression` (any) — The expression from which to collect the values.
  `maxLength:?` (Long) — The maximum length of the resulting array.  [min:0]
  `expand:?` (Boolean) — The boolean expression that indicates whether the output should be a flat array.  [default:FALSE]
  → Array

## `collectDistinct`
Collects the values of the provided field into an array (preservation of order not guaranteed).
`collectDistinct(expression [, maxLength] [, expand])`
  `expression` (any) — The expression from which to collect the distinct values.
  `maxLength:?` (Long) — The maximum length of the resulting array.  [min:0]
  `expand:?` (Boolean) — The boolean expression that indicates whether the output should be a flat array.  [default:FALSE]
  → Array

## `correlation`
Calculates the correlation of two fields for a list of records.
`correlation(expression1, expression2)`
  `expression1` (Double|Long) — The first expression to correlate.
  `expression2` (Double|Long) — The second expression to correlate.
  → Double

## `count`
Counts the total number of records.
`count()`
  → Long

## `countDistinct`
Calculates the cardinality of unique values of a field for a list of records based on a stochastic estimation.
`countDistinct(expression [, precision])`
  `expression` (any) — The expression from which to count distinct elements.
  `precision:?` (Long) — The precision in the interval [3, 16].  [default:14, min:3]
  → Long

## `countDistinctApprox`
Calculates the cardinality of unique values of a field for a list of records based on a stochastic estimation.
`countDistinctApprox(expression [, precision])`
  `expression` (any) — The expression from which to count distinct elements.
  `precision:?` (Long) — The precision in the interval [3, 16].  [default:14, min:3]
  → Long

## `countDistinctExact`
Calculates the cardinality of unique values of a field for a list of records.
`countDistinctExact(expression)`
  `expression` (any) — The expression from which to count distinct elements.
  → Long

## `countIf`
Counts the number of records that match the condition.
`countIf(condition)`
  `condition` (Boolean) — The expression from which to count matched elements.
  → Long

## `max`
Calculates the maximum value of a field for a list of records.
`max(expression)`
  `expression` (Boolean|Double|Duration|Long|String|Timestamp) — The expression from which to get the maximum element.
  → Boolean|Double|Duration|Long|String|Timestamp

## `median`
Calculates the median value of a field for a list of records.
`median(expression [, weight])`
  `expression` (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute the median.
  `weight:?` (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio).  [default:1, min:0]
  → Boolean|Double|Duration|Timestamp

## `min`
Calculates the minimum value of a field for a list of records.
`min(expression)`
  `expression` (Boolean|Double|Duration|Long|String|Timestamp) — The expression from which to get the minimum element.
  → Boolean|Double|Duration|Long|String|Timestamp

## `percentRank`
Calculates the percentile rank for a given value.
`percentRank(expression, value)`
  `expression` (Boolean|Double|Duration|Long|Timestamp) — The expression for which to compute a percentile rank.
  `value` (Boolean|Double|Duration|Long|Timestamp) — The value for which to retrieve the percentile.
  → Double

## `percentile`
Calculates the percentile value of a field for a list of records:percentile(x, 50) == median(x).
`percentile(expression, percentile [, weight])`
  `expression` (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute a percentile.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  `weight:?` (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio).  [default:1, min:0]
  → Boolean|Double|Duration|Timestamp

## `percentileFromSamples`
Calculates the percentile value of array fields.
`percentileFromSamples(expression, percentile [, originalCount])`
  `expression` (Array) — The array expression from which to compute a percentile.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  `originalCount:?` (Double|Long) — The original element count of the given array expression.  [min:0]
  → Boolean|Double|Duration|Timestamp

## `percentiles`
Calculates multiple percentile values of a field for a list of records (similar to percentile, but returns an array of values instead of a single one).
`percentiles(expression [, weight ,] percentile, …)`
  `expression` (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute a percentile.
  `weight:?` (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio).  [default:1, min:0]
  `percentile*` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  → Array

## `stddev`
Calculates the standard deviation of a field for a list of records.
`stddev(expression)`
  `expression` (Double|Long) — The expression from which to compute standard deviation.
  → Double

## `sum`
Calculates the sum of a field for a list of records.
`sum(expression)`
  `expression` (Double|Duration|Long) — The expression from which to compute the sum.
  → Double|Duration

## `takeAny`
Returns a value of a field for a list of records. Any record can be given despite records are ordered or not.
`takeAny(expression)`
  `expression` (any) — The expression from which to take any element.
  → any

## `takeFirst`
Returns the first value of a field for a list of records in the current order.
`takeFirst(expression)`
  `expression` (any) — The expression from which to take the first element.
  → any

## `takeLast`
Returns the last value of a field for a list of records in the current order.
`takeLast(expression)`
  `expression` (any) — The expression from which to take the last element.
  → any

## `takeMax`
Returns the maximum value of a field for a list of records. The records will be ordered based on the field data type and the field value and the maximum will be taken.
`takeMax(expression)`
  `expression` (any) — The expression from which to take the maximum element.
  → any

## `takeMin`
Returns the minimum value of a field for a list of records. The records will be ordered based on the field data type and the field value and the minimum will be taken.
`takeMin(expression)`
  `expression` (any) — The expression from which to take the minimum element.
  → any

## `variance`
Calculates the variance of a field for a list of records.
`variance(expression)`
  `expression` (Double|Long) — The expression from which to compute variance.
  → Double

# DQL Functions — Array

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`arrayAvg`](#arrayavg) · [`arrayConcat`](#arrayconcat) · [`arrayCumulativeSum`](#arraycumulativesum) · [`arrayDelta`](#arraydelta) · [`arrayDiff`](#arraydiff) · [`arrayDistinct`](#arraydistinct) · [`arrayFirst`](#arrayfirst) · [`arrayFlatten`](#arrayflatten) · [`arrayIndexOf`](#arrayindexof) · [`arrayLast`](#arraylast) · [`arrayLastIndexOf`](#arraylastindexof) · [`arrayMax`](#arraymax) · [`arrayMedian`](#arraymedian) · [`arrayMin`](#arraymin) · [`arrayMovingAvg`](#arraymovingavg) · [`arrayMovingMax`](#arraymovingmax) · [`arrayMovingMin`](#arraymovingmin) · [`arrayMovingSum`](#arraymovingsum) · [`arrayPercentile`](#arraypercentile) · [`arrayRemoveNulls`](#arrayremovenulls) · [`arrayReverse`](#arrayreverse) · [`arraySize`](#arraysize) · [`arraySlice`](#arrayslice) · [`arraySort`](#arraysort) · [`arraySum`](#arraysum) · [`arrayToString`](#arraytostring) · [`vectorCosineDistance`](#vectorcosinedistance) · [`vectorInnerProductDistance`](#vectorinnerproductdistance) · [`vectorL1Distance`](#vectorl1distance) · [`vectorL2Distance`](#vectorl2distance)

_array function_

## `arrayAvg`
Returns the average of an array. Values that are not numeric are ignored. 0 if there is no matching element.
`arrayAvg(array)`
  `array` (Array) — an array expression
  → Double

## `arrayConcat`
Concatenates multiple arrays into a single array.
`arrayConcat(array, …)`
  `array*` (Array) — Array expression that should be combined with others.
  → Array

## `arrayCumulativeSum`
Returns the sums of elements from the input array and all elements with a lower index.
`arrayCumulativeSum(array)`
  `array` (Array) — an array expression
  → Array

## `arrayDelta`
Returns array of delta of array elements
`arrayDelta(array)`
  `array` (Array) — an array expression
  → Array

## `arrayDiff`
Returns array of same length where result[i] == input[i] - input[i-1].
`arrayDiff(array)`
  `array` (Array) — an array expression
  → Array

## `arrayDistinct`
Returns the array without duplicates.
`arrayDistinct(array)`
  `array` (Array) — an array expression
  → Array

## `arrayFirst`
Returns the first non-null element of an array (use myArray[0] to get the first nullable element).
`arrayFirst(array)`
  `array` (Array) — an array expression
  → any

## `arrayFlatten`
Returns flattened array
`arrayFlatten(array)`
  `array` (Array) — an array expression
  → Array

## `arrayIndexOf`
Returns the index of the first array element with the given value.
`arrayIndexOf(array, value)`
  `array` (Array) — The array expression in which the value is searched for.
  `value` (any) — The primitive value to search for in the expression.
  → Long

## `arrayLast`
Returns the last non-null element of an array (use myArray[-1] to get the last nullable element).
`arrayLast(array)`
  `array` (Array) — an array expression
  → any

## `arrayLastIndexOf`
Returns the index of the last array element with the given value.
`arrayLastIndexOf(array, value)`
  `array` (Array) — The array expression in which the value is searched for.
  `value` (any) — The primitive value to search for in the expression.
  → Long

## `arrayMax`
Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. `null` if there is no matching element.
`arrayMax(array)`
  `array` (Array) — an array expression
  → any

## `arrayMedian`
Returns the median of the members of an array.
`arrayMedian(expression)`
  `expression` (Array) — The array from which to compute the median.
  → Boolean|Double|Duration|Timestamp

## `arrayMin`
Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. `null` if there is no matching element.
`arrayMin(array)`
  `array` (Array) — an array expression
  → any

## `arrayMovingAvg`
Returns the averages of elements from the input array calculated according to the moving window size.
`arrayMovingAvg(array, windowSize)`
  `array` (Array) — The array of numeric values.
  `windowSize` (Long) — The size of moving window.  [min:0]
  → Array

## `arrayMovingMax`
Returns the maximums of elements from the input array calculated according to the moving window size.
`arrayMovingMax(array, windowSize)`
  `array` (Array) — The array of numeric values.
  `windowSize` (Long) — The size of moving window.  [min:0]
  → Array

## `arrayMovingMin`
Returns the minimums of elements from the input array calculated according to the moving window size.
`arrayMovingMin(array, windowSize)`
  `array` (Array) — The array of numeric values.
  `windowSize` (Long) — The size of moving window.  [min:0]
  → Array

## `arrayMovingSum`
Returns the sums of elements from the input array calculated according to the moving window size.
`arrayMovingSum(array, windowSize)`
  `array` (Array) — The array of numeric values.
  `windowSize` (Long) — The size of moving window.  [min:0]
  → Array

## `arrayPercentile`
Returns a percentile of the members of an array.
`arrayPercentile(expression, percentile)`
  `expression` (Array) — The array from which to compute a percentile.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  → Boolean|Double|Duration|Timestamp

## `arrayRemoveNulls`
Returns the array where NULL elements are removed.
`arrayRemoveNulls(array)`
  `array` (Array) — an array expression
  → Array

## `arrayReverse`
Returns the array with elements in reversed order.
`arrayReverse(array)`
  `array` (Array) — an array expression
  → Array

## `arraySize`
Returns the size of an array.
`arraySize(array)`
  `array` (Array) — an array expression
  → Long

## `arraySlice`
Returns a slice of an array.
`arraySlice(array [, from] [, to])`
  `array` (Array) — an array expression
  `from:?` (Long) — Index of first element to include in the resulting array, inclusive, relative to start of `array` if positive, relative to end if negative. Clamped at array bounds.  [default:0]
  `to:?` (Long) — Index of last element to include in the resulting array, exclusive, relative to start of `array` if positive, relative to end if negative. Clamped at array bounds.  [default:9223372036854775807]
  → Array

## `arraySort`
Returns the array with members sorted in ascending order.
`arraySort(array [, direction])`
  `array` (Array) — an array expression
  `direction:?` (—) — direction  [default:"ascending"]
  → Array

## `arraySum`
Returns the sum of an array. Values that are not numeric are ignored. 0 if there is no matching element.
`arraySum(array)`
  `array` (Array) — an array expression
  → Double

## `arrayToString`
Converts an array to a string.
`arrayToString(array [, delimiter])`
  `array` (Array) — Array expression that should be converted to a string.
  `delimiter:?` (String) — A constant string expression that is added between the concatenated array elements.  [default:""]
  → String

## `vectorCosineDistance`
Calculates the cosine distance between two arrays.
`vectorCosineDistance(firstExpression, secondExpression)`
  `firstExpression` (Array) — An array of numeric values.
  `secondExpression` (Array) — An array of numeric values.
  → Double

## `vectorInnerProductDistance`
Calculates the inner product distance between two arrays.
`vectorInnerProductDistance(firstExpression, secondExpression)`
  `firstExpression` (Array) — An array of numeric values.
  `secondExpression` (Array) — An array of numeric values.
  → Double

## `vectorL1Distance`
Calculates the L1 distance between two arrays.
`vectorL1Distance(firstExpression, secondExpression)`
  `firstExpression` (Array) — An array of numeric values.
  `secondExpression` (Array) — An array of numeric values.
  → Double

## `vectorL2Distance`
Calculates the L2 distance between two arrays.
`vectorL2Distance(firstExpression, secondExpression)`
  `firstExpression` (Array) — An array of numeric values.
  `secondExpression` (Array) — An array of numeric values.
  → Double

# DQL Functions — Bitwise

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_bitwise function_

## `bitwiseAnd`
Calculates the bitwise `and` between two long expressions.
`bitwiseAnd(firstExpression, secondExpression)`
  `firstExpression` (Long) — The first long expression for the binary bitwise operation.
  `secondExpression` (Long) — The second long expression for the binary bitwise operation.
  → Long

## `bitwiseCountOnes`
Counts the bits set to one of a long expression.
`bitwiseCountOnes(expression)`
  `expression` (Long) — The long expression whose bits set to one will be counted.
  → Long

## `bitwiseNot`
Inverts the bits of a long expression.
`bitwiseNot(expression)`
  `expression` (Long) — The long expression whose bits will be inverted.
  → Long

## `bitwiseOr`
Calculates the bitwise `or` between two long expressions.
`bitwiseOr(firstExpression, secondExpression)`
  `firstExpression` (Long) — The first long expression for the binary bitwise operation.
  `secondExpression` (Long) — The second long expression for the binary bitwise operation.
  → Long

## `bitwiseShiftLeft`
Bitwise left shift long expression by a number of given bits.
`bitwiseShiftLeft(expression, numberOfBits)`
  `expression` (Long) — The long expression that will be bitwise shifted left.
  `numberOfBits` (Long) — The number of bits by which the expression will be shifted left.
  → Long

## `bitwiseShiftRight`
Bitwise right shift long expression by a number of given bits.
`bitwiseShiftRight(expression, numberOfBits [, ignoreSign])`
  `expression` (Long) — The long expression that will be bitwise shifted right.
  `numberOfBits` (Long) — The number of bits by which the expression will be shifted right.
  `ignoreSign:?` (Boolean) — 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.  [default:FALSE]
  → Long

## `bitwiseXor`
Calculates the bitwise `xor` between two long expressions.
`bitwiseXor(firstExpression, secondExpression)`
  `firstExpression` (Long) — The first long expression for the binary bitwise operation.
  `secondExpression` (Long) — The second long expression for the binary bitwise operation.
  → Long

# DQL Functions — Boolean

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_boolean checks_

## `exists`
Tests if a field exists.
`exists(field)`
  `field` (any) — The name of the field that will be checked if it exists.
  → Boolean

## `in`
Tests if a needle value is contained in any of the haystack parameters.
`in(needle, haystack, …)`
  `needle` (any) — The element(s) to search for (the needle).
  `haystack*` (any) — The elements where to search for the needle element (the haystack).
  → Boolean

## `isFalseOrNull`
Tests if a value is `false` or `null`
`isFalseOrNull(expression)`
  `expression` (Boolean) — The expression to check if it is false or null.
  → Boolean

## `isNotNull`
Tests if a value is not `null`
`isNotNull(expression)`
  `expression` (any) — The expression to check if it is not null.
  → Boolean

## `isNull`
Tests if a value is `null`.
`isNull(expression)`
  `expression` (any) — The expression to check if it is null.
  → Boolean

## `isTrueOrNull`
Tests if a value is `true` or `null`.
`isTrueOrNull(expression)`
  `expression` (Boolean) — The expression to check if it is true or null.
  → Boolean

## `isUid128`
Tests if a uid value is of subtype uid128.
`isUid128(expression)`
  `expression` (UID) — The uid expression that will be checked if it is of subtype uid128.
  → Boolean

## `isUid64`
Tests if a uid value is of subtype uid64.
`isUid64(expression)`
  `expression` (UID) — The uid expression that will be checked if it is of subtype uid64.
  → Boolean

## `isUuid`
Tests if a uid value is of subtype uuid.
`isUuid(expression)`
  `expression` (UID) — The uid expression that will be checked if it is of subtype uuid.
  → Boolean

# DQL Functions — Cast

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_cast function_

## `asArray`
Returns `array` value if the value is `array`, otherwise `null`.
`asArray(value)`
  `value` (Array) — The expression to cast as an array.
  → Array

## `asBinary`
Returns `binary` value (byte array) if the value is `binary`, otherwise `null`.
`asBinary(value)`
  `value` (Binary) — The expression to cast as a byte array.
  → Binary

## `asBoolean`
Returns `boolean` value if the value is `boolean`, otherwise `null`.
`asBoolean(value)`
  `value` (Boolean) — The expression to cast as a boolean.
  → Boolean

## `asDouble`
Returns `double` value if the value is `double`, otherwise `null`.
`asDouble(value)`
  `value` (Double) — The expression to cast as a double.
  → Double

## `asDuration`
Returns `duration` value if the value is `duration`, otherwise `null`.
`asDuration(value)`
  `value` (Duration) — The expression to cast as a duration.
  → Duration

## `asIp`
Returns `ip_address` value if the value is `ip_address`, otherwise `null`.
`asIp(value)`
  `value` (IpAddress) — The expression to cast as an ip address.
  → IpAddress

## `asLong`
Returns `long` value if the value is `long`, otherwise `null`.
`asLong(value)`
  `value` (Long) — The expression to cast as a long.
  → Long

## `asNumber`
Returns same value if the value is `integer`, `long`, `double`, otherwise `null`.
`asNumber(value)`
  `value` (Double|Long) — The expression to cast as a number.
  → Double|Long

## `asRecord`
Returns `record` value if the value is `record`, otherwise `null`.
`asRecord(value)`
  `value` (Record) — The expression to cast as a record.
  → Record

## `asSmartscapeId`
Returns `smartscapeId` value if the value is `smartscapeId`, otherwise `null`.
`asSmartscapeId(value)`
  `value` (SmartscapeId) — The expression to cast as a smartscape id.
  → SmartscapeId

## `asString`
Returns `string` value if the value is `string`, otherwise `null`.
`asString(value)`
  `value` (String) — The expression to cast as a string.
  → String

## `asTimeframe`
Returns `timeframe` value if the value is `timeframe`, otherwise `null`.
`asTimeframe(value)`
  `value` (Timeframe) — The expression to cast as a timeframe.
  → Timeframe

## `asTimestamp`
Returns `timestamp` value if the value is `timestamp`, otherwise `null`.
`asTimestamp(value)`
  `value` (Timestamp) — The expression to cast as a timestamp.
  → Timestamp

## `asUid`
Returns `uid` value if the value is `uid`, otherwise `null`.
`asUid(value)`
  `value` (UID) — The expression to cast as a uid.
  → UID

# DQL Functions — Constant

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_mathematical constant_

## `e`
Returns Euler’s number.
`e()`
  → Double

## `pi`
Returns the constant value of PI (Archimedes’ number).
`pi()`
  → Double

# DQL Functions — Conversion

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_conversion function_

## `toArray`
Returns the value if it is an `array`. Otherwise, converts a value to the single element array holding that value.
`toArray(value)`
  `value` (any) — The expression to convert to an array if possible.
  → Array

## `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.
`toBoolean(value)`
  `value` (Array|Boolean|Double|Long|String) — The expression to convert to a boolean if possible.
  → Boolean

## `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.
`toDouble(value)`
  `value` (Array|Boolean|Double|Duration|IpAddress|Long|String|Timestamp|UID) — The expression to convert to a double if possible.
  → Double

## `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.
`toDuration(value)`
  `value` (Array|Double|Duration|Long|String|Timeframe) — The expression to convert to a duration if possible.
  → Duration

## `toIp`
Converts a value to `ip_address` if the value is of a suitable type. If the argument is an `array`, the element at position 0 is converted.
`toIp(value)`
  `value` (Array|Double|IpAddress|Long|String) — The expression to convert to an ip address if possible.
  → IpAddress

## `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.
`toLong(value)`
  `value` (Array|Boolean|Double|Duration|IpAddress|Long|String|Timestamp|UID) — The expression to convert to a long if possible.
  → Long

## `toSmartscapeId`
Converts a value to `smartscapeId` if the value is of a suitable type. If the argument is an `array`, the element at position 0 is converted.
`toSmartscapeId(value)`
  `value` (Array|SmartscapeId|String) — The expression to convert to a smartscape id if possible.
  → SmartscapeId

## `toString`
Returns the string representation of a value.
`toString(value)`
  `value` (any) — The expression to convert to a string if possible.
  → String

## `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.
`toTimeframe(value)`
  `value` (Array|String|Timeframe) — The expression to convert to a timeframe if possible.
  → Timeframe

## `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.
`toTimestamp(value)`
  `value` (Array|Double|Long|String|Timestamp) — The expression to convert to a timestamp if possible.
  → Timestamp

## `toUid`
Converts a value to `uid` if the value is of a suitable type. If the argument is an `array`, the element at position 0 is converted.
`toUid(value)`
  `value` (Array|Double|Long|String|UID) — The expression to convert to a uid if possible.
  → UID

## `toVariant` (deprecated)
Converts a value to `variant` with boxed element inside.
`toVariant(value)`
  `value` (any) — The expression to convert to a variant if possible.
  → any

# DQL Functions — Create

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_create function for primitive data types_

## `array`
Creates an `array` from the list of given parameters.
`array(expression, …)`
  `expression*` (any) — An element inside the array.
  → Array

## `duration`
Creates a `duration` from the given amount and time unit.
`duration(value, unit)`
  `value` (Double|Long) — The numeric value for the duration.
  `unit` (String) — The time unit of the duration.
  → Duration

## `ip`
Creates an `ip` from the given string expression.
`ip(expression)`
  `expression` (String) — The string expression for an ip address
  → IpAddress

## `record`
Creates a `record` from the keys and values of the parameters.
`record(expression, …)`
  `expression*` (any) — An expression to add to the record.  [assign:optional]
  → Record

## `smartscapeId`
Creates a `smartscapeId` from the given string and long expression.
`smartscapeId(type, numericId)`
  `type` (String) — The type of smartscapeId as string.
  `numericId` (Long) — The numeric id of smartscapeId as long.
  → SmartscapeId

## `timeframe`
Creates a `timeframe` from the given start and end timestamp or duration.
`timeframe(from [, to])`
  `from` (Duration|String|Timestamp) — The start of the timeframe. Can be a timestamp or a duration. A duration is interpreted as an offset from `now()`.
  `to:?` (Duration|String|Timestamp) — The end of the timeframe. Can be a timestamp or a duration. A duration is interpreted as an offset from `now()`.  [default:now()]
  → Timeframe

## `timestamp`
Creates a `timestamp` from the provided values.
`timestamp(year, month, day, hour, minute, second [, millis] [, micros] [, nanos] [, timezone])`
  `year` (Long) — The year of the timestamp as a number.
  `month` (Long) — The month of the timestamp as a number.
  `day` (Long) — The day of the timestamp as a number.
  `hour` (Long) — The hour of the timestamp as a number.
  `minute` (Long) — The minute of the timestamp as a number.
  `second` (Long) — The second of the timestamp as a number.
  `millis:?` (Long) — The millisecond of the timestamp as a number.  [default:0]
  `micros:?` (Long) — The microsecond of the timestamp as a number.  [default:0]
  `nanos:?` (Long) — The nanosecond of the timestamp as a number.  [default:0]
  `timezone:?` (—) — The timezone used to format the timestamp.
  → Timestamp

## `timestampFromUnixMillis`
Creates a `timestamp` from the given milliseconds since Unix epoch.
`timestampFromUnixMillis(millis)`
  `millis` (Long) — Milliseconds since unix start time.
  → Timestamp

## `timestampFromUnixNanos`
Creates a `timestamp` from the given nanoseconds since Unix epoch.
`timestampFromUnixNanos(nanos)`
  `nanos` (Long) — Nanoseconds since unix start time.
  → Timestamp

## `timestampFromUnixSeconds`
Creates a `timestamp` from the given seconds since Unix epoch.
`timestampFromUnixSeconds(seconds)`
  `seconds` (Long) — Seconds since unix start time.
  → Timestamp

## `uid128`
Creates a `uid` from the given two long expressions.
`uid128(firstExpression, secondExpression)`
  `firstExpression` (Long) — The 1st long expression for a uid.
  `secondExpression` (Long) — The 2nd long expression for a uid.
  → UID

## `uid64`
Creates a `uid` from the given long expression.
`uid64(expression)`
  `expression` (Long) — The long expression for a uid.
  → UID

## `uuid`
Creates a `uuid` from the given two long expressions.
`uuid(mostSignificantBits, leastSignificantBits)`
  `mostSignificantBits` (Long) — The 1st long expression for the most significant bits of a uuid.
  `leastSignificantBits` (Long) — The 2nd long expression for the least significant bits of a uuid.
  → UID

# DQL Functions — Cryptographic

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_cryptographic string function_

## `hashCrc32`
Returns a CRC32 hash for the given expression.
`hashCrc32(expression)`
  `expression` (Binary|String) — The string expression that will be hashed.
  → String

## `hashMd5`
Returns a MD5 hash for the given expression.
`hashMd5(expression)`
  `expression` (Binary|String) — The string expression that will be hashed.
  → String

## `hashSha1`
Returns a SHA-1 hash for the given expression.
`hashSha1(expression)`
  `expression` (Binary|String) — The string expression that will be hashed.
  → String

## `hashSha256`
Returns a SHA-256 hash for the given expression.
`hashSha256(expression)`
  `expression` (Binary|String) — The string expression that will be hashed.
  → String

## `hashSha512`
Returns a SHA-512 hash for the given expression.
`hashSha512(expression)`
  `expression` (Binary|String) — The string expression that will be hashed.
  → String

## `hashXxHash32`
Returns a xxHash32 hash for the given expression.
`hashXxHash32(expression)`
  `expression` (Binary|String) — The expression that is considered for the hash function.
  → String

## `hashXxHash64`
Returns a xxHash64 hash for the given expression.
`hashXxHash64(expression)`
  `expression` (Binary|String) — The expression that is considered for the hash function.
  → String

# DQL Functions — Entities

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_entities function_

## `classicEntitySelector`
Returns entities matching the specified entity selector.
`classicEntitySelector(entitySelector)`
  `entitySelector` (String) — The entity selector string.
  → Array

## `entityAttr`
Returns the attribute value for an entity.
`entityAttr(expression, name [, type])`
  `expression` (any) — The expression to determine the entity ID.
  `name` (—) — The entity attribute name that to be queried.
  `type:?` (—) — The entity type that to be queried.
  → any

## `entityName`
Returns the name of an entity.
`entityName(expression [, type])`
  `expression` (any) — The expression to determine the entity ID.
  `type:?` (—) — The entity type that to be queried.
  → String

# DQL Functions — Time series aggregation for expressions

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`avg`](#avg) · [`count`](#count) · [`countDistinct`](#countdistinct) · [`countDistinctApprox`](#countdistinctapprox) · [`countDistinctExact`](#countdistinctexact) · [`countIf`](#countif) · [`end`](#end) · [`max`](#max) · [`median`](#median) · [`min`](#min) · [`percentRank`](#percentrank) · [`percentile`](#percentile) · [`percentileFromSamples`](#percentilefromsamples) · [`start`](#start) · [`sum`](#sum)

_makeTimeseries_

## `avg`
Calculates the average of the expression values in each bucket.
`avg(expression [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `count`
Counts the number of records in each bucket.
`count([[default] [, rate] [, scalar]])`
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `countDistinct`
This function is an alias for countDistinctApprox().
`countDistinct(expression [, precision] [, default] [, rate] [, scalar])`
  `expression` (any) — The expression the aggregation function shall be applied to.
  `precision:?` (Long) — The precision in the interval [3, 16].  [default:14, min:3]
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `countDistinctApprox`
Counts the approximate number of distinct records in each bucket.
`countDistinctApprox(expression [, precision] [, default] [, rate] [, scalar])`
  `expression` (any) — The expression the aggregation function shall be applied to.
  `precision:?` (Long) — The precision in the interval [3, 16].  [default:14, min:3]
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `countDistinctExact`
Counts the precise number of distinct records in each bucket.
`countDistinctExact(expression [, default] [, rate] [, scalar])`
  `expression` (any) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `countIf`
Counts the number of records matching the provided condition in each bucket.
`countIf(expression [, default] [, rate] [, scalar])`
  `expression` (Boolean) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `end`
Produces an array of timestamps representing the end of the bin.
`end()`
  → Array

## `max`
Calculates the maximum of the expression values in each bucket.
`max(expression [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `median`
Calculates the median of the expression value in each bucket.
`median(expression [, weight] [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `weight:?` (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio).  [default:1, min:0]
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `min`
Calculates the minimum of the expression values in each bucket.
`min(expression [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `percentRank`
Calculates the percentile rank for a given value.
`percentRank(expression, value [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `value` (Double|Long) — The percentile to compute, between 0 and 100.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `percentile`
Calculates the requested percentile of the expression value in each bucket.
`percentile(expression, percentile [, weight] [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  `weight:?` (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio).  [default:1, min:0]
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `percentileFromSamples`
Calculates the requested percentile of the array expression in each bucket.
`percentileFromSamples(expression, percentile [, originalCount] [, default] [, rate] [, scalar])`
  `expression` (Array) — The expression the aggregation function shall be applied to.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  `originalCount:?` (Double|Long) — The original element count of the given array expression.  [min:0]
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

## `start`
Produces an array of timestamps representing the start of the bin.
`start()`
  → Array

## `sum`
Calculates the sum of the expression values in each bucket.
`sum(expression [, default] [, rate] [, scalar])`
  `expression` (Double|Duration|Long) — The expression the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  → Array|Double

# DQL Functions — Flow

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_boolean flow control_

## `coalesce`
Returns the first non-`null` argument, if any, otherwise `null`.
`coalesce(expression, …)`
  `expression*` (any) — Returned if previous arguments are null.
  → any

## `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).
`if(condition, then [, else])`
  `condition` (Boolean) — The condition to check.
  `then` (any) — The expression if the condition is true.
  `else:?` (any) — The expression if the condition is false or null.  [default:NULL]
  → any

# DQL Functions — General

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_function_

## `jsonField`
Parses a JSON string and extracts one field.
`jsonField(expression, fieldName [, seek])`
  `expression` (String) — The json string that should be parsed.
  `fieldName` (String) — The string literal with the name of the field to be extracted.
  `seek:?` (Boolean) — Flag indicating if the function should search for JSON object in the expression.  [default:FALSE]
  → Array|Boolean|Double|Long|Record|String

## `jsonPath`
Parses a JSON string and extracts one field described by a path.
`jsonPath(expression, jsonPath [, seek])`
  `expression` (String) — The json string that should be parsed.
  `jsonPath` (—) — The string literal with the JSON-path to be extracted.
  `seek:?` (Boolean) — Flag indicating if the function should search for JSON object in the expression.  [default:FALSE]
  → Array|Boolean|Double|Long|Record|String

## `lookup`
Returns a record containing all lookup fields.
`lookup(lookupTable [, sourceField ,] lookupField [, executionOrder])`
  `lookupTable` (—) — Sub-query for records with fields to add or overwrite in the input.
  `sourceField:?` (any) — Specifies a field of the source ("left").
  `lookupField:` (any) — Specifies a field of the lookup ("right").
  `executionOrder:?` (—) — Defines which side of the join will be executed first.  [default:auto]
  `broadcast:?` (—) — Defines broadcasting strategy.  [default:enabled]
  → Record

## `parse`
Extracts a single value from a string as specified in the pattern or a record if there are multiple named matchers.
`parse(expression, pattern)`
  `expression` (String) — A field or string expression to parse.
  `pattern` (—) — The parse pattern.
  `baseTime:?` (Timestamp) — A timestamp expression providing the base time for date/time parsing.
  → any

## `parseAll`
Extracts several values from a string as specified in the pattern.
`parseAll(expression, pattern)`
  `expression` (String) — A field or string expression to parse.
  `pattern` (—) — The parse pattern.
  `baseTime:?` (Timestamp) — A timestamp expression providing the base time for date/time parsing.
  → Array

## `type`
Returns the type of a value as `string`.
`type(expression [, withSubtype])`
  `expression` (any) — The expression to get the type of.
  `withSubtype:?` (Boolean) — Whether the type string should include subtype information if available.  [default:FALSE]
  → String

# DQL Functions — Get

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_get function_

## `arrayElement`
Extracts a single element from an array.
`arrayElement(expression, index)`
  `expression` (Array) — The array from which to extract an element.
  `index` (Long) — The index of the element to extract.
  → any

## `getEnd`
Extracts the end timestamp from a timeframe.
`getEnd(timeframe)`
  `timeframe` (Timeframe) — The timeframe expression from which to get the end of the interval.
  → Timestamp

## `getHighBits`
Extracts the most significant bits of a given UID or IP.
`getHighBits(expression)`
  `expression` (IpAddress|UID) — The expression from which to extract the most significant bits.
  → Long

## `getLowBits`
Extracts the least significant bits of a given UID or IP.
`getLowBits(expression)`
  `expression` (IpAddress|UID) — The expression from which to extract the least significant bits.
  → Long

## `getStart`
Extracts the start timestamp from a timeframe.
`getStart(timeframe)`
  `timeframe` (Timeframe) — The timeframe expression from which to get the start of the interval.
  → Timestamp

# DQL Functions — Iterative

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_iterative function_

## `iAny`
Checks an iterative boolean expression and returns `true` if it was `true` at least once, `false` if not.
`iAny(expression)`
  `expression` (Boolean) — The iterative boolean expression.
  → Boolean

## `iCollectArray`
Collects the results of an iterative expression into an array.
`iCollectArray(expression)`
  `expression` (any) — The iterative expression that should be collected into an array.
  → Array

## `iIndex`
Returns the current index of an iterative expression.
`iIndex()`
  → Long

# DQL Functions — Mathematical

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`abs`](#abs) · [`acos`](#acos) · [`asin`](#asin) · [`atan`](#atan) · [`atan2`](#atan2) · [`bin`](#bin) · [`cbrt`](#cbrt) · [`ceil`](#ceil) · [`cos`](#cos) · [`cosh`](#cosh) · [`degreeToRadian`](#degreetoradian) · [`exp`](#exp) · [`floor`](#floor) · [`hexStringToNumber`](#hexstringtonumber) · [`hypotenuse`](#hypotenuse) · [`log`](#log) · [`log10`](#log10) · [`log1p`](#log1p) · [`numberToHexString`](#numbertohexstring) · [`power`](#power) · [`radianToDegree`](#radiantodegree) · [`random`](#random) · [`range`](#range) · [`round`](#round) · [`signum`](#signum) · [`sin`](#sin) · [`sinh`](#sinh) · [`sqrt`](#sqrt) · [`tan`](#tan) · [`tanh`](#tanh)

_mathematical function_

## `abs`
Returns the absolute value of a numeric expression.
`abs(expression)`
  `expression` (Double|Duration|Long) — The numeric expression for which to calculate the absolute value.
  → Double|Duration|Long

## `acos`
Calculate the acos of the given expression as an angle in radians.
`acos(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the acos.
  → Double

## `asin`
Calculate the asin of the given expression as an angle in radians.
`asin(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the asin.
  → Double

## `atan`
Calculate the atan of the given expression as an angle in radians.
`atan(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the atan.
  → Double

## `atan2`
Calculate the atan2 of the given coordinates.
`atan2(ordinate, abscissa)`
  `ordinate` (Double|Long) — The ordinate coordinate.
  `abscissa` (Double|Long) — The abscissa coordinate.
  → Double

## `bin`
Aligns the value of the numeric or timestamp into buckets of the given interval starting at 0 (numeric) or Unix epoch (timestamp).
`bin(expression, interval [, at])`
  `expression` (Double|Duration|Long|Timestamp) — The expression that should be aligned.
  `interval` (Double|Duration|Long) — The interval by which to align the expression.
  `at:?` (Double|Duration|Long|Timestamp) — The offset to which each interval shall be shifted.  [default:NULL]
  → Double|Duration|Long|Timestamp

## `cbrt`
Computes the real cubic root of a numeric expression
`cbrt(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the real cubic root.
  → Double

## `ceil`
Returns the smallest integer greater than or equal to the given number.
`ceil(expression)`
  `expression` (Double|Long) — The numeric expression to be rounded up.
  → Double|Long

## `cos`
Calculate the cos of the given expression as an angle in radians.
`cos(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the cos.
  → Double

## `cosh`
Calculate the cosh of the given expression as an angle in radians.
`cosh(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the cosh.
  → Double

## `degreeToRadian`
Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
`degreeToRadian(expression)`
  `expression` (Double|Long) — The angle to be converted from degrees to radians.
  → Double

## `exp`
Computes the exponential function of a numeric expression.
`exp(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the exponential function.
  → Double

## `floor`
Returns the largest integer smaller than or equal to the given number.
`floor(expression)`
  `expression` (Double|Long) — The numeric expression to be rounded down.
  → Double|Long

## `hexStringToNumber`
Converts a hexadecimal string into a number.
`hexStringToNumber(expression)`
  `expression` (String) — The string expression that will be converted to a number.
  → Double|Long

## `hypotenuse`
Calculate the hypotenuse of the right triangle of given sides.
`hypotenuse(x, y)`
  `x` (Double|Long) — Length of the first of the catheti.
  `y` (Double|Long) — Length of the second of the catheti.
  → Double

## `log`
Computes the natural logarithm (base e) of a numeric expression
`log(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the natural logarithm (base e).
  → Double

## `log10`
Computes the decadic logarithm (base 10) of a numeric expression.
`log10(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the decadic logarithm (base 10).
  → Double

## `log1p`
Computes log(1 + x) of a numeric expression x, where log is the natural logarithm (base e).
`log1p(expression)`
  `expression` (Double|Long) — The numeric expression for which to add one and calculate the natural logarithm (base e).
  → Double

## `numberToHexString`
Converts a number into a hexadecimal string.
`numberToHexString(expression [, minLength])`
  `expression` (Long) — The numeric expression that will be converted to a hexadecimal string.
  `minLength:?` (Long) — The minimum length of the returned hexadecimal string.  [min:0]
  → String

## `power`
Raises a base numeric expression to a given exponent.
`power(base, exponent)`
  `base` (Double|Long) — The numeric expression acting as the base of the power calculation.
  `exponent` (Double|Long) — The numeric expression acting as the exponent of the power calculation.
  → Double

## `radianToDegree`
Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
`radianToDegree(expression)`
  `expression` (Double|Long) — The angle to be converted from radians to degrees.
  → Double

## `random`
Creates a random double value.
`random()`
  → Double

## `range`
Aligns the value of the numeric or timestamp into buckets of the given interval starting at 0 (numeric) or Unix epoch (timestamp) keeping start and end of each interval.
`range(expression, interval [, at])`
  `expression` (Double|Duration|Long|Timestamp) — The expression that should be aligned.
  `interval` (Double|Duration|Long) — The interval by which to align the expression.
  `at:?` (Double|Duration|Long|Timestamp) — The offset to which each interval shall be shifted.  [default:NULL]
  → Record

## `round`
Round the numeric expression to the next long or to the double closest to the provided number of places after the decimal point.
`round(expression [, decimals])`
  `expression` (Double|Long) — Numeric expression to be rounded.
  `decimals:?` (Long) — Number of places after the decimal point.  [default:0, min:0]
  → Double|Long

## `signum`
Returns the signum of a numeric expression, that is, 1 if the expression is positive, -1 if it is negative, or 0 if it is zero.
`signum(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the signum.
  → Double|Long

## `sin`
Calculate the sin of the given expression as an angle in radians.
`sin(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the sin.
  → Double

## `sinh`
Calculate the sinh of the given expression as an angle in radians.
`sinh(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the sinh.
  → Double

## `sqrt`
Computes the positive square root of a numeric expression.
`sqrt(expression)`
  `expression` (Double|Long) — The numeric expression for which to calculate the square root.
  → Double

## `tan`
Calculate the tan of the given expression as an angle in radians.
`tan(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the tan.
  → Double

## `tanh`
Calculate the tanh of the given expression as an angle in radians.
`tanh(expression)`
  `expression` (Double|Long) — The numeric expression, angle in radians for which to calculate the tanh.
  → Double

# DQL Functions — Network

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_network function_

## `ipIn`
Checks if an ip address matches with given ip addresses. Returns `true` if it does, `false` otherwise.
`ipIn(needle, haystack, …)`
  `needle` (Array|IpAddress|String) — The expression that will be compared with the given ip addresses
  `haystack*` (Array|IpAddress|String) — The ip addresses with which the expression should be compared
  → Boolean

## `ipIsLinkLocal`
Checks if a string or ip address expression is a link local ip address. Returns `true` if it is, `false` otherwise.
`ipIsLinkLocal(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `ipIsLoopback`
Checks if a string or ip address expression is a loopback ip address. Returns `true` if it is, `false` otherwise.
`ipIsLoopback(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `ipIsPrivate`
Checks if a string or ip address expression is a private ip address. Returns `true` if it is, `false` otherwise.
`ipIsPrivate(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `ipIsPublic`
Checks if a string or ip address expression is a public ip address. Returns `true` if it is, `false` otherwise.
`ipIsPublic(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `ipMask`
Returns an ip address where a given mask is applied
`ipMask(expression, maskBits [, ipv6MaskBits])`
  `expression` (IpAddress|String) — The string or ip address expression that will be masked.
  `maskBits` (Long) — The mask bits that should be applied to an ip address.  [min:0]
  `ipv6MaskBits:?` (Long) — The mask bits that should be applied to an ipv6 address.  [min:0]
  → IpAddress

## `isIp`
Checks if a string or ip address expression is an ip address. Returns `true` if it is, `false` otherwise.
`isIp(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `isIpV4`
Checks if a string or ip address expression is an ipv4 address. Returns `true` if it is, `false` otherwise.
`isIpV4(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

## `isIpV6`
Checks if a string or ip address expression is an ipv6 address. Returns `true` if it is, `false` otherwise.
`isIpV6(expression)`
  `expression` (IpAddress|String) — The string or ip address expression that will be checked.
  → Boolean

# DQL Functions — Smartscape

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

_smartscape function_

## `getNodeField`
Returns the field value for a smartscape node.
`getNodeField(expression, name)`
  `expression` (SmartscapeId|String) — The expression to determine the smartscape node ID.
  `name` (String) — The smartscape field name to be queried.
  → any

## `getNodeName`
Returns the name of a smartscape node.
`getNodeName(expression)`
  `expression` (SmartscapeId|String) — The expression to determine the smartscape node ID.
  → String

# DQL Functions — String

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`concat`](#concat) · [`contains`](#contains) · [`decodeBase16ToBinary`](#decodebase16tobinary) · [`decodeBase16ToString`](#decodebase16tostring) · [`decodeBase64ToBinary`](#decodebase64tobinary) · [`decodeBase64ToString`](#decodebase64tostring) · [`decodeUrl`](#decodeurl) · [`encodeBase16`](#encodebase16) · [`encodeBase64`](#encodebase64) · [`encodeUrl`](#encodeurl) · [`endsWith`](#endswith) · [`escape`](#escape) · [`getCharacter`](#getcharacter) · [`indexOf`](#indexof) · [`lastIndexOf`](#lastindexof) · [`levenshteinDistance`](#levenshteindistance) · [`like`](#like) · [`lower`](#lower) · [`matchesPattern`](#matchespattern) · [`matchesPhrase`](#matchesphrase) · [`matchesRegex`](#matchesregex) · [`matchesValue`](#matchesvalue) · [`punctuation`](#punctuation) · [`replacePattern`](#replacepattern) · [`replaceString`](#replacestring) · [`splitByPattern`](#splitbypattern) · [`splitString`](#splitstring) · [`startsWith`](#startswith) · [`stringLength`](#stringlength) · [`substring`](#substring) · [`trim`](#trim) · [`unescape`](#unescape) · [`unescapeHtml`](#unescapehtml) · [`upper`](#upper)

_string function_

## `concat`
Concatenates the expressions into a single string.
`concat([delimiter ,] expression, …)`
  `delimiter:?` (String) — A constant string expression that is added between the concatenated expressions.  [default:""]
  `expression*` (Double|Long|String) — A numeric or string expressions that should be concatenated with others.
  → String

## `contains`
Searches the string expression for a substring. Returns `true` if the substring was found, `false` otherwise.
`contains(expression, substring [, caseSensitive])`
  `expression` (String) — The field or expression to check (the haystack).
  `substring` (String) — The substring that should be contained (the needle).
  `caseSensitive:?` (Boolean) — caseSensitive  [default:TRUE]
  → Boolean

## `decodeBase16ToBinary`
Decodes the given BASE16-string to a binary.
`decodeBase16ToBinary(expression)`
  `expression` (Binary|String) — The encoded string or binary that shall be decoded.
  → Binary

## `decodeBase16ToString`
Decodes the given BASE16-string to a string.
`decodeBase16ToString(expression)`
  `expression` (Binary|String) — The encoded string or binary that shall be decoded.
  → String

## `decodeBase64ToBinary`
Decodes the given BASE64-string to a binary.
`decodeBase64ToBinary(expression)`
  `expression` (Binary|String) — The encoded string or binary that shall be decoded.
  → Binary

## `decodeBase64ToString`
Decodes the given BASE64-string to a string.
`decodeBase64ToString(expression)`
  `expression` (Binary|String) — The encoded string or binary that shall be decoded.
  → String

## `decodeUrl`
Returns a decoded url string.
`decodeUrl(expression)`
  `expression` (String) — The string expression that will be decoded.
  → String

## `encodeBase16`
Encodes the given binary or string as BASE16-string.
`encodeBase16(expression)`
  `expression` (Binary|String) — The string or binary expression that shall be encoded.
  → String

## `encodeBase64`
Encodes the given binary or string as BASE64-string.
`encodeBase64(expression)`
  `expression` (Binary|String) — The string or binary expression that shall be encoded.
  → String

## `encodeUrl`
Returns an encoded url string.
`encodeUrl(expression)`
  `expression` (String) — The string expression that will be encoded.
  → String

## `endsWith`
Checks if a string expression ends with a suffix. Returns `true` if does, `false` otherwise.
`endsWith(expression, suffix [, caseSensitive])`
  `expression` (String) — The string expression that will be checked.
  `suffix` (String) — The suffix string with which the expression should end.
  `caseSensitive:?` (Boolean) — Whether the check should be done in a case-sensitive way.  [default:TRUE]
  → Boolean

## `escape`
Returns an escaped string.
`escape(expression)`
  `expression` (String) — The string expression that will be escaped.
  → String

## `getCharacter`
Returns the character at a given position from a string expression. Negative positions are counted from the end of the string.
`getCharacter(expression, position)`
  `expression` (String) — The string expression from which to get the character.
  `position` (Long) — The position at which to get the character (negative positions are counted from the end of the string).
  → String

## `indexOf`
Finds the index of the first occurrence of a substring in a string expression, starting a forward search from a given index. Returns -1, if the substring is not found.
`indexOf(expression, substring [, from])`
  `expression` (String) — The string expression in which the substring is searched for.
  `substring` (String) — The substring expression to search for in the expression.
  `from:?` (Long) — 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.  [default:0]
  → Long

## `lastIndexOf`
Finds the index of the last occurrence of a substring in a string expression, starting a backward search from a given index. Returns -1, if the substring is not found.
`lastIndexOf(expression, substring [, from])`
  `expression` (String) — The string expression in which the substring is searched for.
  `substring` (String) — The substring expression to search for in the expression.
  `from:?` (Long) — The index from which to start the backward search for the last occurrence of the substring within the expression. Negative values are counted from the end of the string.  [default:9223372036854775807]
  → Long

## `levenshteinDistance`
Computes Levenshtein distance between two given strings.
`levenshteinDistance(firstExpression, secondExpression)`
  `firstExpression` (String) — The first string expression to compute the Levenshtein distance from.
  `secondExpression` (String) — The second string expression to compute the Levenshtein distance from.
  → Long

## `like`
Tests if a string expression matches a pattern. If the pattern doesn't contain percent signs then like() acts as == 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.
`like(expression, pattern)`
  `expression` (String) — The string expression that will be checked.
  `pattern` (String) — The matching pattern.
  → Boolean

## `lower`
Converts a string to lowercase.
`lower(expression)`
  `expression` (String) — The string expression to convert to lowercase.
  → String

## `matchesPattern`
Tests if a string expression matches the DPL pattern.
`matchesPattern(expression, pattern)`
  `expression` (String) — A field or string expression to test.
  `pattern` (—) — The matching pattern.
  → Boolean

## `matchesPhrase`
Matches a phrase against the input string expression using token matchers.
`matchesPhrase(expression, phrase [, caseSensitive])`
  `expression` (Array|String) — The expression (string or array of strings) that should be checked.
  `phrase` (String) — The phrase to search for.
  `caseSensitive:?` (Boolean) — Whether the match should be done case-sensitive (default: false).  [default:FALSE]
  `wildcard:?` (String) — A single character that will be used as wildcard (default: "*").  [default:"*"]
  → Boolean

## `matchesRegex` (deprecated)
Tests if a string expression matches a regular expression.
`matchesRegex(expression, pattern)`
  `expression` (String) — The string to check.
  `pattern` (String) — The applied regular expression pattern (has to match the whole string).
  → Boolean

## `matchesValue`
Matches a value against the input expression using token matchers.
`matchesValue([caseSensitive ,] expression, value, …)`
  `caseSensitive:?` (Boolean) — Whether the match should be done case-sensitive (default: false).  [default:FALSE]
  `wildcard:?` (String) — A single character that will be used as wildcard (default: "*").  [default:"*"]
  `expression` (Array|SmartscapeId|String) — The expression (string or array of strings) that should be checked.
  `value*` (Array|String) — The value to search for using patterns (supports an array of patterns or a list of patterns).
  → Boolean

## `punctuation`
Returns punctuation characters contained in given string.
`punctuation(expression [, count] [, withSpace])`
  `expression` (String) — The string expression of which to extract the punctuation characters.
  `count:?` (Long) — The maximum number of returned punctuation characters.  [default:32, min:0]
  `withSpace:?` (Boolean) — Whether space characters should be included.  [default:FALSE]
  → String

## `replacePattern`
Replaces each substring of a string that matches the DPL pattern with the given string.
`replacePattern(expression, pattern, replacement)`
  `expression` (String) — A field or string expression to replace.
  `pattern` (—) — The replacing pattern.
  `replacement` (String) — The string that should replace the found substrings.
  → String

## `replaceString`
Replaces each substring of a string with a given string.
`replaceString(expression, substring, replacement)`
  `expression` (String) — The field or expression where substrings should be replaced.
  `substring` (String) — The substring that should be replaced.
  `replacement` (String) — The string that should replace the found substrings.
  → String

## `splitByPattern`
Splits a string into an array at each occurrence of the DPL pattern.
`splitByPattern(expression, pattern)`
  `expression` (String) — A field or string expression to split.
  `pattern` (—) — The splitting pattern.
  → Array

## `splitString`
Splits a string at each occurrence of a pattern. If not found, returns an array with a single element that contains the full string. Splits into single-byte strings if the pattern is empty.
`splitString(expression, pattern)`
  `expression` (String) — The string expression to split up into an array.
  `pattern` (String) — The pattern to split the string expression at, or the empty string to split into one-byte strings.
  → Array

## `startsWith`
Checks if a string expression starts with a prefix. Returns `true` if does, `false` otherwise.
`startsWith(expression, prefix [, caseSensitive])`
  `expression` (String) — The string expression that will be checked.
  `prefix` (String) — The prefix string with which the expression should start.
  `caseSensitive:?` (Boolean) — Whether the check should be done in a case-sensitive way.  [default:TRUE]
  → Boolean

## `stringLength`
Returns number of UTF-16 code units in given string.
`stringLength(expression)`
  `expression` (String) — The string expression to get the number of UTF-16 code units for.
  → Long

## `substring`
Gets part of a string using a start index (inclusive) and an optional end index (exclusive).Negative indexes are relative to the last code unit. Indexes that are out-of-bounds are clamped at the string length for positive indexes, and at zero for negative indexes.Returns empty string in case of out-of-bounds indexes.Indexes are in UTF-16 code units and may not correspond to a single character.
`substring(expression [, from] [, to])`
  `expression` (String) — The string expression to get a substring of.
  `from:?` (Long) — 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.  [default:0]
  `to:?` (Long) — 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.  [default:9223372036854775807]
  → String

## `trim`
Returns given string without leading and trailing white-space.
`trim(expression)`
  `expression` (String) — The string expression to remove leading and trailing white-space from.
  → String

## `unescape`
Returns an unescaped string.
`unescape(expression)`
  `expression` (String) — The string expression that will be unescaped.
  → String

## `unescapeHtml`
Returns an unescaped html string.
`unescapeHtml(expression)`
  `expression` (String) — The string expression that will be unescaped.
  → String

## `upper`
Converts a string to uppercase.
`upper(expression)`
  `expression` (String) — The string expression to convert to uppercase.
  → String

# DQL Functions — Time

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`formatTimestamp`](#formattimestamp) · [`getDayOfMonth`](#getdayofmonth) · [`getDayOfWeek`](#getdayofweek) · [`getDayOfYear`](#getdayofyear) · [`getHour`](#gethour) · [`getMinute`](#getminute) · [`getMonth`](#getmonth) · [`getSecond`](#getsecond) · [`getWeekOfYear`](#getweekofyear) · [`getYear`](#getyear) · [`now`](#now) · [`unixMillisFromTimestamp`](#unixmillisfromtimestamp) · [`unixNanosFromTimestamp`](#unixnanosfromtimestamp) · [`unixSecondsFromTimestamp`](#unixsecondsfromtimestamp)

_time function_

## `formatTimestamp`
Formats the timestamp according to a format string (using the defined interval).
`formatTimestamp(timestamp [, interval] [, format] [, timezone] [, locale])`
  `timestamp` (Timestamp) — The timestamp expression that should be formatted.
  `interval:?` (Duration) — The duration expression used to align the timestamp.
  `format:?` (String) — The formatting pattern.  [default:"yyyy-MM-dd'T'HH:mm:ss.SSSSSSSSS"]
  `timezone:?` (—) — The timezone used to format the timestamp.
  `locale:?` (—) — The locale used to format the timestamp.
  → String

## `getDayOfMonth`
Extracts the day of month from a timestamp.
`getDayOfMonth(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the day of month will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getDayOfWeek`
Extracts the day of week from a timestamp.
`getDayOfWeek(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the day of week will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getDayOfYear`
Extracts the day of year from a timestamp.
`getDayOfYear(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the day of year will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getHour`
Extracts the hour from a timestamp.
`getHour(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the hour will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getMinute`
Extracts the minute from a timestamp.
`getMinute(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the minute will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getMonth`
Extracts the month from a timestamp.
`getMonth(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the month will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getSecond`
Extracts the second from a timestamp.
`getSecond(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the second will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getWeekOfYear`
Extracts the week of year from a timestamp.
`getWeekOfYear(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the week of year will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `getYear`
Extracts the year from a timestamp.
`getYear(timestamp [, timezone])`
  `timestamp` (Timestamp) — The timestamp expression from which the year will be extracted.
  `timezone:?` (—) — The timezone that should be used.
  → Long

## `now`
Returns the current time as fixed timestamp of the query start.
`now()`
  → Timestamp

## `unixMillisFromTimestamp`
Converts a timestamp into milliseconds
`unixMillisFromTimestamp(timestamp)`
  `timestamp` (Timestamp) — The timestamp expression which will be converted to milliseconds since epoch.
  → Long

## `unixNanosFromTimestamp`
Converts a timestamp into nanoseconds
`unixNanosFromTimestamp(timestamp)`
  `timestamp` (Timestamp) — The timestamp expression which will be converted to nanoseconds since epoch.
  → Long

## `unixSecondsFromTimestamp`
Converts a timestamp into seconds
`unixSecondsFromTimestamp(timestamp)`
  `timestamp` (Timestamp) — The timestamp expression which will be converted to seconds since epoch.
  → Long

# DQL Functions — Time series aggregation for metrics

Param notation: `name` = required positional · `name:` = required named · suffix `*` = variadic · suffix `?` = optional · types listed as `|`-separated names or `any` (all scalar+collection types)

## Table of Contents

[`avg`](#avg) · [`count`](#count) · [`countDistinct`](#countdistinct) · [`end`](#end) · [`max`](#max) · [`median`](#median) · [`min`](#min) · [`percentRank`](#percentrank) · [`percentile`](#percentile) · [`start`](#start) · [`sum`](#sum)

_timeseries_

## `avg`
Calculates the average of the metric values in each bucket.
`avg(metricKey [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `count`
Calculates the number of time bins with values.
`count(metricKey [, default] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `countDistinct`
Calculates the number of distinct values in each bucket.
`countDistinct(metricKey [, default] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `end`
Produces an array of timestamps representing the end of the bin.
`end()`
  → Array

## `max`
Calculates the maximum of the metric values in each bucket.
`max(metricKey [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `median`
Calculates the median of the metric values in each bucket.
`median(metricKey [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `min`
Calculates the minimum of the metric values in each bucket.
`min(metricKey [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `percentRank`
Calculates the percentile rank for a given value.
`percentRank(metricKey, value [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `value` (Double|Long) — The percentile to compute, between 0 and 100.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `percentile`
Calculates the requested percentile of the metric values in each bucket.
`percentile(metricKey, percentile [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `percentile` (Double|Long) — The percentile to compute, between 0 and 100.  [min:0]
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

## `start`
Produces an array of timestamps representing the start of the bin.
`start()`
  → Array

## `sum`
Calculates the sum of the metric values in each bucket.
`sum(metricKey [, rollup] [, default] [, rate] [, scalar] [, filter])`
  `metricKey` (—) — The metric key the aggregation function shall be applied to.
  `rollup:?` (—) — The rollup type that shall be used for the metric.
  `default:?` (Double|Long) — The default value to fill gaps.  [default:NULL]
  `rate:?` (Duration) — The rate the series values shall be scaled to.
  `scalar:?` (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated.  [default:FALSE]
  `filter:?` (Boolean) — Filter condition that shall be applied for this aggregation.
  → Array|Double

# DQL Parameter Value Types

| key | name | description |
|-----|------|-------------|
| `bucket` | name or pattern for bucket filters | plain string used to specify the name or pattern for buckets to filter on |
| `dataObject` | data object | is validated against the data objects in the record type repository |
| `dplPattern` | pattern for parsing | validated DPL pattern string |
| `entityAttribute` | entity attribute | an entity attribute |
| `entitySelector` | entity selector | an entity selector |
| `entityType` | entity type | an entity type |
| `enum` | predefined string value | a static string, but only a predefined list of values is allowed |
| `executionBlock` | execution block | an execution block that may or may not contain commands (e.g. for a fork where [] means identity) |
| `expressionTimeseriesAggregation` | expression-based timeseries aggregation | a timeseries aggregation in the form of functionName(field) used to calculate timeseries on expressions |
| `expressionWithConstantValue` | constant expression | an expression with a constant value, e.g. `1+1` is constant, but no primitive value as the `+` is executed |
| `expressionWithFieldAccess` | expression | any expression; it might also access fields from records |
| `fieldPattern` | pattern for filtering field names | plain string used to specify multiple fields using a pattern with wildcards |
| `filePattern` | pattern for file listing | pattern for selecting files |
| `identifierForAnyField` | field identifier | has to refer to an existing field, but it might also be a nested record list |
| `identifierForEdgeType` | edge type | edge types are for smartscape - they do NOT refer to an existing field and can't be nested |
| `identifierForFieldOnRootLevel` | field identifier on root level | has to refer to an existing field on root level |
| `identifierForNodeType` | node type | node types are for smartscape - they do NOT refer to an existing field and can't be nested |
| `joinCondition` | join condition | can either be a field identifier or an equality comparison of left and right fields |
| `jsonPath` | JSONPath | validated JSONPath |
| `metricKey` | metric key | it has to be a metric key and will provide special suggestions |
| `metricTimeseriesAggregation` | metric-based timeseries aggregation | a timeseries aggregation in the form of functionName(metric) to calculate timeseries on metrics |
| `namelessDplPattern` | pattern for parsing | validated DPL pattern string, that might not contain field names |
| `nonEmptyExecutionBlock` | non-empty execution block | an execution block that has to contain at least one command (e.g. for a join) |
| `prefix` | prefix for flattening fields | plain string used to specify the prefix of all fields that are pushed to the root record |
| `primitiveValue` | primitive value | a primitive value; usually represented by a literal |
| `simpleIdentifier` | new field name | new field names are for aliases and names - they do NOT refer to an existing field and can't be nested |
| `tabularFileExisting` | tabular file name | a string that represents the name of a tabular file to load |
| `tabularFileNew` | new tabular file name | a string that represents the name of a tabular file to save |
| `url` | URL | fully qualified HTTP(S) URL |

# Iterative expressions and arrays - a way to work with arrays and timeseries in DQL

## Table of Contents
- [General rules](#general-rules)
- [Arrays and timeseries](#arrays-and-timeseries)
- [Filtering involving arrays](#filtering-involving-arrays)
    - [Testing membership in array](#testing-membership-in-array)
    - [Testing using iterative expressions](#testing-using-iterative-expressions)
- [Examples](#examples)
    - [New timeseries/array based on condition](#new-timeseriesarray-based-on-condition)
    - [Removing last array element, replacing last array element with null](#removing-last-array-element-replacing-last-array-element-with-null)
    - [Further time aggregation of timeseries](#further-time-aggregation-of-timeseries)
    - [Summarization of timeseries](#summarization-of-timeseries)
    - [How to get each datapoint of timeseries separately with its own timestamp](#how-to-get-each-datapoint-of-timeseries-produced-by-timeseries-or-maketimeseries-commands-separately-with-its-own-timestamp)
    - [How to get last timestamp when data was present in timeseries](#how-to-get-last-timestamp-when-data-was-present-in-timeseries)
    - [Timestamp of last metric ingestion](#timestamp-of-last-metric-ingestion)
    - [How to remove values from array and make it shorter](#how-to-remove-values-from-array-and-make-it-shorter-by-number-of-removed-elements)
    - [How to apply complex conditions on arrays](#how-to-apply-complex-conditions-on-arrays)

## General rules

* Iterative expression is a notation when specific operation is performed on every element of arrays used in the query
* Operations over multiple arrays run in lockstep: at each index `i`, the elements at position `i` from every array are combined in the same step. This is pairwise, **not** a cross-product. `iAny(f1[] == "a" AND f2[] == "b")` is `TRUE` only when `"a"` and `"b"` sit at the *same* index, not when each value appears somewhere in its array. To test whether each array independently contains a value, use separate `in()` calls:

  ```dql-snippet
  // WRONG — only matches if "a" and "b" are at the same index
  | filter iAny(f1[] == "a" AND f2[] == "b")

  // RIGHT — each check is independent
  | filter in(f1, "a") AND in(f2, "b")
  ```
* All arrays need to be of the same length / size. If not iterative expressions fail.
* Result of iterative expression is another arrays.
* DQL has a set of dedicated array functions. Their names start with `array....()` — see [dql/dql-functions-array.md](dql/dql-functions-array.md) for the full reference.
* Iterative expression can be wrapped in `iCollectArray()` function. After this array functions can be used immediately.
* `iAny(logical_iterative_expression)` performs test of logical iterative expression. `TRUE` is returned when expression was `TRUE` for at least one element
* `not iAny(not logical_iterative_expression)` allows to check if `TRUE` was returned for all elements

## Arrays and timeseries
* Timeseries is array produced by `timeseries` command accompanied by timeframe and interval fields. Elements of array represent values for subsequent time buckets from earliest to latest (most recent)
* `timeframe` field (type of field: timeframe) defines time span covered by timeseries from the beginning (accessible via `timeframe[start]`) of first time bucket (represented by first element of array) to the end of last bucket (accessible via `timeframe[end]`)
* If timeseries produced is named e.g. `d`, following is true: `arraySize(d)*interval == timeframe[end]-timeframe[start]`
* Timeseries produced by `timeseries` command can carry unit and this information is tied to name of field. If transformations using iterative expression are reusing fields names, unit information will be kept. Metric unit can be found in query result metadata.
* **Time bucket mapping**: The timestamp of a value at array index `i` (0-based) is: `timeframe[start] + interval * i`. E.g. index 0 → `timeframe[start]`, index 1 → `timeframe[start] + interval`, index 2 → `timeframe[start] + interval * 2`, etc.

## Filtering involving arrays

### Testing membership in array

* `in()` function allows to have 1st (`needle`) and 2nd and following parameter (`haystacks`) as array. It returns true if any element of `needles` (or just `needle` if it is not an array) is element of any `haystacks` (or equal to any `haystacks` if eny of them is not an array)
* Syntax when `needle` is field, `haystacks` is constant
    - `in(field, {"a", "b", "c"})`
    - `in(field, array("a", "b", "c"))`
    - `in(field, {array("a", "b", "c"), array("d", "e")})` - 2 haystacks
    - `in(field, "a", "b", "c")` - simplified syntax where parameters except 1st one are treated as haystack
* Recommended syntax for dashboard multi-select variables: `in(field, array($variable))`. Omitting `array()` will cause error when nothing was selected which can happen when variable options are empty
    - In case variable is not string: `in(field, array($variable:noquote))` (by defuale each variable value is surrounded by double-quotes)
    - In case variable is string which may contain unescaped characters needing escaping  `in(field, array($variable:triplequote))`
* `in(needle_array, haystack_array)` checks it there are any common elements (intersections) between arrays. E.g.

```dql
data record(a1=array("1","2","3"), a2=array("3","4")),
record(a1=array("1","2","3"), a2=array("5"))
| fieldsAdd in(a1, a2)
```

checks if there is any common elements between array a1 and a2

### Testing using iterative expressions
* Checks if given string (a1) begins (`startsWith()` function) with any of strings in a2

```dql
data record(a1="a", a2=array("b","c"))
| fieldsAdd iAny(startsWith(a1, a2[]))
```

* Tests if all elements of array a1 are present in array a2

```dql
data record(a1=array("1","2"), a2=array("1", "2", "3","4")),
record(a1=array("1","2","3"), a2=array("1", "2", "4" , "5", "6")),
record(a1=array("a"), a2=array("1", "2", "4" , "5", "6"))
| fieldsAdd not iAny(not in(a1[], a2))
```

## Examples

### new timeseries/array based on condition
* Question: based on 2 timeseries: bad and total I want 3rd one: failed. Element of failed has to have value of bad when value of total is grater then 100. DQL query so far:

```dql
timeseries { bad = sum(dt.service.request.failure_count) ,
total = sum( dt.service.request.count ) }, union:true
```

* Answer: Last command can do it using iterative expression

```dql
timeseries { bad = sum(dt.service.request.failure_count) ,
total = sum( dt.service.request.count ) }, union:true
| fieldsAdd bad = if (total[]>350000, bad[], else:0)
```

### Removing last array element, replacing last array element with null
* Question: One of our customers tried to remove the last datapoint from the graphs. Incomplete data for last datapoints the aggregated calculations shows wrong, so they want to exclude the last incomplete data points.
  Are there options than using custom timeframe?
* Answer: you can set the last element of array to null this way:

```dql
  data record(a=array(1,2,3,2))
  | fieldsAdd a=if(iIndex()<arraySize(a)-1, a[], else:null)
```

### Further time aggregation of timeseries
* Question: Is it possible to rollup a timeseries on a larger time-interval (1h in my case)?
  Use case:

```dql
timeseries {
good = sum(dt.service.request.count, filter: not failed),
total = sum(dt.service.request.count)
}, by: { k8s.namespace.name }, interval: 1m
| fieldsAdd sli = good[] / total[] * 100
```

* Answer:

```dql
timeseries {
  good = sum(dt.service.request.count, filter: not failed),
  total = sum(dt.service.request.count),
  timestamp=start()
}, by: { k8s.namespace.name }, interval: 1m

| fieldsAdd d = record( sli = good[] / total[] * 100, timestamp=timestamp[] )
| expand d
| makeTimeseries sli = avg(d[sli]), time: d[timestamp], interval:1h
```

### Summarization of timeseries
* Question: I need some help summarizing a timeseries in another timeseries. So far I have this:

```dql
timeseries {
    memoryUsage = max(dt.kubernetes.container.memory_working_set),
    memoryRequest = avg(dt.kubernetes.container.requests_memory)
  },
  filter: (
    (k8s.cluster.name == "jenkins-worker"
    OR k8s.cluster.name == "jenkins-worker-ha"
    OR k8s.cluster.name == "jenkins-worker-windows")
    AND k8s.namespace.name != "kube-system"
    AND k8s.namespace.name != "dynatrace"
    AND k8s.container.name != "jnlp"
  ),
  by: { dt.smartscape.k8s_pod, k8s.cluster.name, k8s.namespace.name, k8s.pod.name, k8s.container.name }, interval: 60000ms
| fieldsAdd overUsage = memoryUsage[] / memoryRequest[]
```

and I want to summarize by dt.smartscape.k8s_pod, k8s.cluster.name, k8s.namespace.name, k8s.pod.name
* Answer: Summarize can produce timeseries:

```dql
timeseries {
memoryUsage = max(dt.kubernetes.container.memory_working_set),
memoryRequest = avg(dt.kubernetes.container.requests_memory)
},
filter: (
(k8s.cluster.name == "jenkins-worker"
OR k8s.cluster.name == "jenkins-worker-ha"
OR k8s.cluster.name == "jenkins-worker-windows")
AND k8s.namespace.name != "kube-system"
AND k8s.namespace.name != "dynatrace"
AND k8s.container.name != "jnlp"
),
by: { dt.smartscape.k8s_pod, k8s.cluster.name, k8s.namespace.name, k8s.pod.name, k8s.container.name }, interval: 60000ms
| fieldsAdd overUsage = memoryUsage[] / memoryRequest[]
| summarize overUsage = max(overUsage[]), by: {dt.smartscape.k8s_pod, k8s.cluster.name, k8s.namespace.name, k8s.pod.name, timeframe, interval}
```

Always keep timeframe and interval in the summarize by clause when you want to get timeseries as a result of summarize. Without it we cannot chart data on timeline.

### How to get each datapoint of timeseries produced by `timeseries` or `makeTimeseries` commands separately with its own timestamp?
Example metric: dt.service.request.count
Example aggregation used: sum()

```dql
timeseries {cnt=sum(dt.service.request.count), timestamp=start(), timestamp_end=end()}, from:-1y, interval:24h
| fieldsAdd d=record(cnt=cnt[], timestamp=timestamp[], timestamp_end=timestamp_end[])
| expand d
| fields cnt=d[cnt], timestamp=d[timestamp], timestamp_end=d[timestamp_end]
```

### How to get last timestamp when data was present in timeseries?

```dql
timeseries { d=sum(dt.service.request.count),  timestamp=start() }, filter: dt.smartscape.service == toSmartscapeId("SERVICE-0A596770A52979EB")
| fieldsAdd timestamp = arrayLast(iCollectArray(if (isNotNull(d[]), timestamp[] )))
```

### Timestamp of last metric ingestion
* Question: How to get last timestamp when data was ingested into timeseries?
* Answer: It is possible, but only with 1m accuracy. Timestamps of individual contributions

```dql
timeseries { d=sum(dt.service.request.count),  timestamp=start() }, filter: dt.smartscape.service == toSmartscapeId("SERVICE-0A596770A52979EB"), interval:1m
| fieldsAdd timestamp = arrayLast(iCollectArray(if (isNotNull(d[]), timestamp[] )))
```

If this query is run for longer timeframes, the interval will not stay at 1m. It may be adjusted because only 1500 time bins/buckets are allowed.
Use the identified longer bin to run a second query with a 1m interval for that specific timeframe.


### How to remove values from array and make it shorter by number of removed elements
* Question: Let's assume I want to keep only strings beginning with capital letters in an array
* Answer

```dql
data record(a=array("John", "cat", "London", "cloud"))
| fieldsAdd a = arrayRemoveNulls( iCollectArray( if(substring(a[], from:0, to:1)==upper(substring(a[], from:0, to:1)), a[] ) ) )
```

but if the task included also keeping elements which are null this query requires additional step:

```dql
data record(a=array("John", "cat", "London", "cloud", null))
| fieldsAdd a = record(n = isNull(a[]), v=a[] )
| fieldsAdd a = arrayRemoveNulls( iCollectArray( if( a[][n] or substring(a[][v], from:0, to:1)==upper(substring(a[][v], from:0, to:1)), a[] ) ) )
| fieldsAdd a = a[][v]
```

### How to apply complex conditions on arrays
* Question: How to find hosts where average CPU usage measured in 1m intervals is higher than 70 at least 3 times
* Answer

```dql
timeseries cpu=avg(dt.host.cpu.usage), by: {dt.smartscape.host}, interval:1m
| filter arraySum(iCollectArray(if(cpu[]>70,1)))>3
```

* Question: How to find hosts where average CPU usage measured in 1m intervals is higher than 70 at least 50% of the times when measurement was provided
* Answer

```dql
timeseries cpu=avg(dt.host.cpu.usage), by: {dt.smartscape.host}, interval:1m
|filter arraySum(iCollectArray(if(cpu[]>70,1.0))) / arraySum(iCollectArray(if(isNotNull(cpu[]),1.0))) > 0.5
```

### Finding timestamp of highest value
* Question: In addition to the aggregated average and 95th percentile values, I would like to know the timeslot when the value was maximal?
* Answer: Using the array of maximal values and the array of timestamps, the exact time slot can be determined:
```dql
timeseries {
  avg_rt = avg(dt.service.request.response_time, scalar:true),
  p95_rt = percentile(dt.service.request.response_time, 95, scalar:true),
  max_rt = max(dt.service.request.response_time, default:0),
  timestamp = start()}, by:{endpoint.name}
| fieldsAdd d = record(max_rt=max_rt[], timestamp=timestamp[])
| fieldsAdd d = arraySort(d, direction:"descending")
| fields endpoint.name, avg_rt, p95_rt, max_rt=arrayFirst(d)[max_rt], when_max_rt = arrayFirst(d)[timestamp]
```

# Operators

## `in` operator

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.
The `in` operator allows building the comparison set dynamically (vs. statically using the `in()` function).

Syntax: `expression in [execution block]`
- Right side execution block must return one field
- Result of right side block cannot be larger than 128MB

## Examples

Getting events having `analysis.id` equal to `scan.id` (from a specific event in the bizevent table):

```dql
fetch events, from: -24h
| filter analysis.id in [
fetch bizevents, from:-24h
| filter event.type=="COMPLIANCE_SCAN_COMPLETED"
// systemIds are passed from FE when building the query
| filter in(object.id, array("KUBERNETES_CLUSTER-641F38AF23F564F6", "KUBERNETES_CLUSTER-96A48749295CC703", "KUBERNETES_CLUSTER-ECEB343907CBAFCC"))
| dedup object.id, sort: {timestamp, desc}
| fields scan.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`

### Critical rules

**CRITICAL:** No space between `@` and the unit — `now()@h` not `now() @h`.

**Order:** Apply offset first, then align — `now()-2h@h`, not `now()@h-2h`.

### `m` vs. `M`

- `m` = **minutes** — e.g. `now()-30m` (30 minutes ago)
- `M` = **months** — e.g. `now()-1M` (1 month ago)

This is a frequent source of errors. Double-check the case.

### 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, it uses `now()` and aligns the current time to the time unit. For example, `@h` is the beginning of the current hour, 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 adding a factor, for example, `@3h`.
Leaving the factor out is equivalent to setting it to 1. Note the following constraints when adding such factor:

| Unit | Meaning — rounding to beginning of                                                    | Allowed factors                    | Comments                                |
|------|---------------------------------------------------------------------------------------|------------------------------------|-----------------------------------------|
| `ns` | nanosecond                                                                            | all divisors of 1000 are supported |                                         |
| `us` | microsecond                                                                           | all divisors of 1000 are supported |                                         |
| `ms` | millisecond                                                                           | all divisors of 1000 are supported |                                         |
| `s`  | second                                                                                | all divisors of 60 are supported   |                                         |
| `m`  | minute                                                                                | all divisors of 60 are supported   |                                         |
| `h`  | hour                                                                                  | all divisors of 24 are supported   |                                         |
| `d`  | day                                                                                   | any                                | daylight saving time taken into account |
| `w`  | week (Monday)                                                                         |                                    | daylight saving time taken into account |
| `wW` | week starting on chosen day (W=0 or 7 — Sunday, W=1 — Monday, W=2 — Tuesday, etc)    |                                    | daylight saving time taken into account |
| `M`  | month                                                                                 |                                    | daylight saving time taken into account |
| `q`  | quarter                                                                               |                                    |                                         |
| `y`  | year                                                                                  |                                    |                                         |

### Common patterns

| Expression   | Meaning                                                     |
| ------------ | ----------------------------------------------------------- |
| `now()@h`    | Current time, aligned to the hour boundary                  |
| `now()@d`    | Midnight today                                              |
| `now()@w1`   | Monday this week                                            |
| `@w1`        | Monday this week (shorthand for `now()@w1`)                 |
| `@w2`        | Tuesday this week                                           |
| `@w5`        | Friday this week                                            |
| `now()-2h@h` | 2 hours ago, aligned to the hour (offset first, then align) |
| `-1w@w1`     | 1 week ago, aligned to the start of the week (Monday)       |

# DQL Query Optimization Guide

Comprehensive guide to writing efficient DQL queries with best practices, patterns, and performance tips.

> **This is the right guide when the goal is to make a DQL query faster, more efficient, or
> cheaper to run.** In Dynatrace, query cost and consumption are driven primarily by the
> **amount of data a query scans**. Every technique here — filtering early, bucket filters,
> short time ranges, field selection, sampling, and limiting cardinality — reduces scanned
> data, which simultaneously makes the query **faster** and **lowers its consumption/cost per
> execution**. So "how do I make my queries faster?", "how do I make my queries cheaper?",
> and "how do I keep DQL query consumption/cost under control?" are the **same question** and
> are all answered here. Share these practices with users to keep query cost from getting out
> of hand.
>
> **Not covered here — use `dt-platform-costs` instead:** monitoring or analyzing a tenant's
> *actual* recorded consumption/billing — e.g. "how much are my queries costing", "who
> scanned the most", "which dashboard/workflow costs most", cost trends and spike
> investigation. That skill *measures* consumption from billing data; this guide *reduces* it
> by improving the query text.

## Table of Contents

- [Core Optimization Principles](#core-optimization-principles)
- [Optimal Command Order](#optimal-command-order)
- [Filter Optimization](#filter-optimization)
- [Aggregation Optimization](#aggregation-optimization)
- [Field Selection Optimization](#field-selection-optimization)
- [Time Optimization](#time-optimization)
- [Join Optimization](#join-optimization)
- [Common Anti-Patterns](#common-anti-patterns)
- [Performance Benchmarks](#performance-benchmarks)
- [Optimization Checklist](#optimization-checklist)
- [Query Profiling Tips](#query-profiling-tips)
- [Advanced Optimization Techniques](#advanced-optimization-techniques)

______________________________________________________________________

## Core Optimization Principles

### 1. Bucket Filters

Always apply bucket filters using the ``bucket`` parameter of the ``fetch``, ``timeseries`` or ``metrics`` command.

```dql
fetch logs, bucket:{"mybucket"}
```

Refine a given query using ``dtctl query --include-contributions --metadata=contributions -o json "THE_DQL_QUERY"``. The metadata section of the result indicates how much each bucket contributed via the ``matchedRecordsRatio`` field. Apply a bucket filter based on the contributions ratio per bucket.


---


### 2. Filter Early

Apply filters immediately after fetch to reduce data volume:

✅ **Good:**

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
| summarize count(), by: {process}
```

❌ **Bad:**

```dql
fetch logs, from:now()-1h
| summarize count(), by: {process, loglevel}
| filter loglevel == "ERROR"
```

**Why it matters:**

- Reduces data processed by subsequent commands
- Lower memory usage
- Faster query execution
- Less data transferred

### 3. Specify Time Ranges

Always use the shortest necessary timeframe:

✅ **Good:**

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
```

❌ **Bad:**

```dql
fetch logs, from:now()-30d  // 30 days when you need 1 hour
| filter loglevel == "ERROR"
```

**Impact:**

- Dramatically reduces initial data volume
- Faster fetch operations
- Lower resource usage
- More responsive queries

**Best practices:**

- Use `from:` parameter in all queries
- Match timeframe to actual need
- Consider retention policies

### 4. Select Only Needed Fields

Limit fields to reduce data transfer:

✅ **Good:**

```dql
fetch logs
| filter loglevel == "ERROR"
| fields timestamp, content, loglevel
| limit 100
```

❌ **Bad:**

```dql
fetch logs
| filter loglevel == "ERROR"
| limit 100
// Returns all fields (potentially 50+ columns)
```

**Benefits:**

- Smaller result sets
- Faster serialization
- Better UI performance
- Clearer intent

### 5. Limit Grouping Cardinality

Avoid grouping by high-cardinality fields:

✅ **Good:**

```dql
fetch logs, from:now()-1h
| summarize count(), by: {dt.process_group.detected_name}
```

❌ **Bad:**

```dql
fetch logs, from:now()-1h
| summarize count(), by: {user.id}  // Can have millions of unique values
```

**High-cardinality fields to avoid:**

- User IDs
- Session IDs
- Request IDs
- Trace IDs
- Unique identifiers

**Low-cardinality fields (good for grouping):**

- Severity levels (ERROR, WARN, INFO, etc.)
- Services (typically 10-100s)
- Hosts (typically 10-1000s)
- Status codes (limited set)
- Namespaces

### 6. Combine Aggregations

Single query more efficient than multiple:

✅ **Good:**

```dql
fetch logs
| summarize
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    warnings = countIf(loglevel == "WARN")
```

❌ **Bad:**
```dql
fetch logs | summarize total = count()
```

```dql
fetch logs | filter loglevel == "ERROR" | summarize errors = count()
```

```dql
fetch logs | filter loglevel == "WARN" | summarize warnings = count()
```

**Benefits:**

- Single data scan
- Shared filtering and processing
- Consistent time windows
- Lower overall latency

### 7. Use Appropriate Time Bins

Match bin size to data volume and timeframe:

✅ **Good:**

```dql
// 24 hours: use 5-minute bins (288 data points)
fetch logs, from:now()-24h
| summarize count(), by: {bin(timestamp, 5m)}
```

❌ **Bad:**

```dql
// 30 days with 1-minute bins (43,200 data points - too many)
fetch logs, from:now()-30d
| summarize count(), by: {bin(timestamp, 1m)}
```

**Recommended bins:**

| Timeframe | Recommended Bin | Data Points |
| --------- | --------------- | ----------- |
| 1 hour    | 1m              | 60          |
| 6 hours   | 5m              | 72          |
| 24 hours  | 5m or 15m       | 288 or 96   |
| 7 days    | 1h              | 168         |
| 30 days   | 6h or 1d        | 120 or 30   |

______________________________________________________________________

## Optimal Command Order

Follow this order for best performance:

```
1. fetch (with time range)
2. filter (reduce data volume)
3. fieldsAdd (add calculated fields)
4. summarize (aggregate data)
5. filter (filter aggregated results)
6. sort (order results)
7. limit (restrict output)
```

**Example:**

```dql
fetch logs, from:now()-1h                              // 1. Fetch with time
| filter loglevel == "ERROR"                           // 2. Filter early
| fieldsAdd process = getNodeName(dt.smartscape.process)  // 3. Add fields
| summarize error_count = count(), by: {process}       // 4. Aggregate
| filter error_count > 10                              // 5. Filter aggregated
| sort error_count desc                                // 6. Sort
| limit 10                                             // 7. Limit
```

______________________________________________________________________

## Filter Optimization

### Use Specific Filters

More specific filters are more efficient:

✅ **Better:**

```dql
fetch logs
| filter loglevel == "ERROR" and http.status_code == 500
```

❌ **Less efficient:**

```dql
fetch logs
| filter contains(content, "error") and contains(content, "500")
```

### Equality vs Text Search

Simple equality checks faster than text search:

✅ **Faster:**

```dql
fetch logs
| filter loglevel == "ERROR"
| filter http.status_code == 500
```

❌ **Slower:**

```dql
fetch logs
| filter contains(loglevel, "ERROR")
| filter contains(http.status_code, "500")
```

### Use in() for Multiple Values

More efficient than multiple OR conditions:

✅ **Better:**

```dql
fetch logs
| filter in(loglevel, {"ERROR", "FATAL", "WARN"})
```

❌ **Less efficient:**

```dql
fetch logs
| filter loglevel == "ERROR" or loglevel == "FATAL" or loglevel == "WARN"
```

### Filter Before fieldsAdd

Calculate fields only on filtered data:

✅ **Good:**

```dql
fetch logs
| filter loglevel == "ERROR"
| fieldsAdd process = getNodeName(dt.smartscape.process)
```

❌ **Bad:**

```dql
fetch logs
| fieldsAdd process = getNodeName(dt.smartscape.process)
| filter loglevel == "ERROR"
```

______________________________________________________________________

## Aggregation Optimization

### Use countIf Instead of Multiple Filters

More efficient for conditional counts instead of issuing individual queries:

✅ **Good:**

```dql
fetch logs
| summarize
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    warnings = countIf(loglevel == "WARN")
```

❌ **Bad:**

```dql
fetch logs | summarize total = count()
```

```dql
fetch logs | filter loglevel == "ERROR" | summarize errors = count()
```

```dql
fetch logs | filter loglevel == "WARN" | summarize warnings = count()
```

### Limit Grouping Dimensions

Fewer dimensions = better performance:

✅ **Good:**

```dql
fetch logs
| summarize count(), by: {loglevel}
```

❌ **Bad:**

```dql
fetch logs
| summarize count(), by: {
    loglevel,
    host,
    process,
    log.source,
    user.id  // Also high cardinality
}
```

### Avoid Unnecessary Grouping

If you don't need groups, don't group:

✅ **Good:**

```dql
fetch logs
| filter loglevel == "ERROR"
| summarize error_count = count()
```

❌ **Bad:**

```dql
fetch logs
| filter loglevel == "ERROR"
| summarize error_count = count(), by: {loglevel}
// Grouping by severity when already filtered to "ERROR"
```

______________________________________________________________________

## Field Selection Optimization

### Select Fields Early

Remove unnecessary fields early in pipeline:

✅ **Good:**

```dql
fetch logs
| fields timestamp, content, loglevel, http.status_code
| filter loglevel == "ERROR"
| summarize count(), by: {loglevel, http.status_code}
```

❌ **Bad:**

```dql-snippet
fetch logs
| filter loglevel == "ERROR"
| fields timestamp, content, loglevel, http.status_code
| summarize count(), by: {loglevel, http.status_code}
```

### Rename in fields vs fieldsAdd

Use `fields` for rename when possible:

✅ **Better:**

```dql
fetch logs
| fields timestamp, level = loglevel, message = content
```

❌ **Less efficient:**

```dql
fetch logs
| fieldsAdd level = loglevel, message = content
| fields timestamp, level, message
```

______________________________________________________________________

## Time Optimization

### Use Time Alignment

Align to time boundaries for caching:

✅ **Good:**

```dql
fetch logs, from:now()-1h@h, to:now()@h
| summarize count(), by: {bin(timestamp, 5m)}
```

**Benefits:**

- Better cache hit rates
- Reproducible time windows
- Cleaner time boundaries

### Choose Appropriate Timeframes

Match timeframe to use case:

| Use Case         | Timeframe |
| ---------------- | --------- |
| Recent errors    | 15m - 1h  |
| Hourly trends    | 24h       |
| Daily patterns   | 7d        |
| Weekly analysis  | 30d       |
| Long-term trends | 90d       |

### Avoid Overlapping Queries

Consolidate timeframes:

✅ **Good:**

```dql
fetch logs, from:now()-1h
| summarize
    recent = countIf(timestamp > now()-15m),
    total = count()
```

❌ **Bad:**

```dql
fetch logs, from:now()-15m | summarize recent = count()
```

```dql
fetch logs, from:now()-1h | summarize total = count()
```

______________________________________________________________________

## Join optimization

### Use the optimal join execution order

More efficient for queries with a low cardinality left side:

✅ **Good:**

```dql
// left join side timeseries query yields only 100 results while the right join side Smartscape query yields many records and a high cardinality. Thus it is optimal to instruct the join command to execute the left side first.
timeseries cpu=avg(dt.host.cpu.usage), by:{dt.smartscape.k8s_node}
| sort arrayAvg(cpu) desc
| limit 100 
| join [   
    smartscapeNodes K8S_NODE 
    | fields id, tags
], on:left[dt.smartscape.k8s_node]==right[id], executionOrder:leftFirst
| fields timeframe, interval, cpu, node=dt.smartscape.k8s_node, instance_type=right.tags[`beta.kubernetes.io/instance-type`]
```

❌ **Bad:**

```dql-snippet
// left join side log query yields a large result set. Thus it is not optimal to instruct the join command to execute the left side first.
fetch logs, bucket:{prod_logs}
| join [   
    smartscapeNodes K8S_NODE 
    | fields id, tags
], on:left[dt.smartscape.k8s_node]==right[id], executionOrder:leftFirst
| fields timeframe, interval, cpu, node=dt.smartscape.k8s_node, instance_type=right.tags[`beta.kubernetes.io/instance-type`]
| summarize countIf(loglevel=="ERROR"), by:instance_type
```

______________________________________________________________________

## Common Anti-Patterns

### Anti-Pattern 1: Late Filtering

❌ **Bad:**

```dql
fetch logs, from:now()-24h
| fieldsAdd process = getNodeName(dt.smartscape.process)
| summarize count(), by: {loglevel, process}
| filter loglevel == "ERROR"
```

✅ **Good:**

```dql
fetch logs, from:now()-24h
| filter loglevel == "ERROR"
| fieldsAdd process = getNodeName(dt.smartscape.process)
| summarize count(), by: {process}
```

### Anti-Pattern 2: Excessive Timeframe

❌ **Bad:**

```dql
fetch logs, from:now()-90d  // Need last hour
| filter loglevel == "ERROR"
| limit 100
```

✅ **Good:**

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
| limit 100
```

### Anti-Pattern 3: High-Cardinality Grouping

❌ **Bad:**

```dql
fetch logs, from:now()-1h
| summarize count(), by: {trace_id}  // Millions of unique values
```

✅ **Good:**

```dql
fetch logs, from:now()-1h
| summarize count(), by: {service = getNodeName(dt.smartscape.service)}
```

### Anti-Pattern 4: Multiple Separate Queries

❌ **Bad:**

```dql
// Query 1
fetch logs | filter loglevel == "ERROR" | summarize errors = count()
```

```dql
// Query 2
fetch logs | filter loglevel == "WARN" | summarize warnings = count()
```

```dql
// Query 3
fetch logs | summarize total = count()
```

✅ **Good:**

```dql
fetch logs
| summarize
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    warnings = countIf(loglevel == "WARN")
```

### Anti-Pattern 5: Unnecessary Field Calculations

❌ **Bad:**

```dql
fetch logs
| fieldsAdd
    process = getNodeName(dt.smartscape.process),
    host = getNodeName(dt.smartscape.host),
    service = getNodeName(dt.smartscape.service)
| filter loglevel == "ERROR"
| fields loglevel, process
// Calculated host and service but never used
```

✅ **Good:**

```dql
fetch logs
| filter loglevel == "ERROR"
| fieldsAdd process = getNodeName(dt.smartscape.process)
| fields loglevel, process
```

### Anti-Pattern 6: Excessive Time Bins

❌ **Bad:**

```dql
fetch logs, from:now()-30d
| summarize count(), by: {bin(timestamp, 1m)}
// 43,200 data points
```

✅ **Good:**

```dql
fetch logs, from:now()-30d
| summarize count(), by: {bin(timestamp, 1h)}
// 720 data points
```

### Anti-Pattern 7: Unnecessary entity joins 

❌ **Bad:**

```dql
fetch logs 
| filter getNodeName(dt.smartscape.k8s_cluster)=="prod_useast"
// using an unnecessary join while the kubernetes cluster name is already present on logs
```

✅ **Good:**

```dql
fetch logs 
| filter k8s.cluster.name=="prod_useast"
```

______________________________________________________________________

## Performance Benchmarks

### Impact of Early Filtering

| Pattern                | Relative Performance |
| ---------------------- | -------------------- |
| Filter after fetch     | Baseline (1x)        |
| Filter after fieldsAdd | 2-3x slower          |
| Filter after summarize | 5-10x slower         |

### Impact of Timeframe

| Timeframe | Data Volume | Query Time |
| --------- | ----------- | ---------- |
| 15m       | 1x          | 1x         |
| 1h        | 4x          | 3-4x       |
| 24h       | 96x         | 20-30x     |
| 7d        | 672x        | 100-150x   |

### Impact of Cardinality

| Cardinality        | Groups  | Performance Impact |
| ------------------ | ------- | ------------------ |
| Low (< 10)         | 5-10    | Negligible         |
| Medium (10-100)    | 20-50   | Acceptable         |
| High (100-1000)    | 200-500 | Noticeable         |
| Very High (> 1000) | 1000+   | Severe             |

______________________________________________________________________

## Optimization Checklist

Before running a query, check:

- [ ] Time range specified with `from:`?
- [ ] Time range as short as possible?
- [ ] Filters applied immediately after fetch?
- [ ] Grouping cardinality reasonable (< 1000 groups)?
- [ ] Only needed fields selected?
- [ ] Multiple aggregations combined in single query?
- [ ] Time bins appropriate for timeframe?
- [ ] Commands in optimal order?
- [ ] No high-cardinality dimensions in grouping?
- [ ] Results limited if displaying sample data?

______________________________________________________________________

## Query Profiling Tips

### Add Intermediate Counts

See data volume at each stage:

```dql-snippet
fetch logs, from:now()-1h
| summarize stage1_count = count()  // Check initial volume
```

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
| summarize stage2_count = count()  // Check after filter
```

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
| fieldsAdd process = getNodeName(dt.smartscape.process)
| summarize final_count = count(), by: {process}
```

### Test with Smaller Timeframes

Start small, then expand:

```dql
// Start with 5 minutes to test
fetch logs, from:now()-5m
| filter loglevel == "ERROR"
| summarize count(), by: {process}
```

```dql
// Then expand to full timeframe
fetch logs, from:now()-24h
| filter loglevel == "ERROR"
| summarize count(), by: {process}
```

### Use limit During Development

Limit results while testing:

```dql
fetch logs, from:now()-1h
| filter loglevel == "ERROR"
| limit 10  // Add during development, remove for production
```

______________________________________________________________________

## Advanced Optimization Techniques

### Pre-Aggregate with summarize

When doing multiple analyses on same data:

```dql
fetch logs, from:now()-1h
| summarize
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    avg_size = avg(response_size),
    by: {bin(timestamp, 5m), service = getNodeName(dt.smartscape.service)}
// Now multiple analyses can use this aggregated data
```

### Use fieldsAdd for Reusable Calculations

Calculate once, use multiple times:

```dql
fetch logs
| fieldsAdd duration_ms = duration / 1000000
| filter duration_ms > 1000
| summarize
    slow_requests = count(),
    avg_duration = avg(duration_ms),
    p95_duration = percentile(duration_ms, 95)
```

### Combine Filters within a single command

Multiple conditions in one filter:

✅ **Good:**

```dql
fetch logs
| filter loglevel == "ERROR" and http.status_code >= 500 and response_time > 1000
```

❌ **Less readable**

```dql
fetch logs
| filter loglevel == "ERROR"
| filter http.status_code >= 500
| filter response_time > 1000
```

# Dynatrace Semantic Dictionary

Standardized field names used across logs, events, spans, metrics, and entities in Grail. Fields are organized by `namespace.sub_namespace.field_name` (e.g., `http.request.method`, `k8s.namespace.name`).

## IMPORTANT: Fetching Complete Field Lists

### The `dt.semantic_dictionary.fields` Table

The Semantic Dictionary is itself queryable as a Grail table: `dt.semantic_dictionary.fields`. Each row describes one field definition. The table exposes these columns:

| Column | Type | Description |
|--------|------|-------------|
| `name` | string | The fully qualified field name (e.g., `service.name`, `k8s.pod.uid`) |
| `type` | string | The field's data type (`string`, `long`, `double`, `boolean`, `timestamp`, `duration`, `uid`, `ipAddress`, `binary`, `timeframe`, `smartscapeId`, `string[]`, `array`, `record`, `record[]` , etc.) |
| `stability` | string | The stability level: `stable`, `experimental`, or `deprecated` |
| `description` | string | Human-readable description of what the field represents |
| `tags` | string[] | Semantic tags assigned to the field (e.g., `entity-id`, `permission`, `primary-field`, `smartscape-id`, `sensitive-spans`, `sensitive-user-events`) |
| `unit` | string | The unit of measurement for the field (e.g., `kBy`, `zl`); null when no unit applies |
| `supported_values` | string[] | Enumerated set of allowed values for fields with a fixed value set (e.g., `span.kind` supports `internal`, `server`, `client`, `producer`, `consumer`) |
| `examples` | string[] | Example values illustrating typical field content (e.g., `"Rome"` for `actor.geo.city.name`) |

### The `dt.semantic_dictionary.models` Table

Data models describe predefined schemas for Grail data objects — which fields belong together and how they map to Grail tables. Each row represents one model definition. The table exposes these columns:

| Column | Type | Description |
|--------|------|-------------|
| `name` | string | The model name (e.g., `audit_event`, `bizevents`, `dt.smartscape.host`) |
| `description` | string | Human-readable description of what the model represents |
| `data_object` | string | The Grail table this model maps to (e.g., `spans`, `logs`, `events`, `smartscape.nodes`, `dt.system.events`) |
| `fields` | string[] | Ordered list of field names that belong to this model |
| `relationships` | string[] | Entity relationships (e.g., `uses[dt.smartscape.aws_s3_bucket]`, `runs_on[dt.entity.host]`) |
| `smartscape_node_name` | string | The field used as display name for Smartscape nodes (e.g., `aws.resource.name`, `k8s.cluster.name`); null for non-entity models |

### Namespace Lookup Queries

**For every namespace referenced in this skill, the fields listed here are only the most commonly used ones.** The Semantic Dictionary contains many more fields per namespace. To get the **complete and up-to-date list** of all fields in any namespace, always run:

```dql
fetch dt.semantic_dictionary.fields
| filter startsWith(name, "<namespace_prefix>.")
| dedup name
```

Replace `<namespace_prefix>` with the target namespace (e.g., `aws`, `azure`, `k8s`, `http`, `db`, `dt.rum`, etc.).

Many namespaces have **sub-namespaces** (e.g., `k8s.pod.*`, `http.request.*`). Drill into them with the same pattern: `filter startsWith(name, "k8s.pod.")`. **Always use these queries** to discover fields beyond what is listed below.

### Model Lookup Queries

To find all models for a specific Grail data object:

```dql
fetch dt.semantic_dictionary.models
| filter data_object == "spans"
```

To find a model by name and see its fields:

```dql
fetch dt.semantic_dictionary.models
| filter name == "audit_event"
```

To list all Grail data objects that have models defined:

```dql
fetch dt.semantic_dictionary.models
| summarize modelCount = count(), by: {data_object}
| sort modelCount desc
```

## Stability Levels

| Level | Meaning |
|---|---|
| `stable` | Safe for production; will not change without notice |
| `experimental` | May change or be removed; use with caution |
| `deprecated` | Avoid; migrate to alternative fields |

## Global Field Namespaces

### Top-Level Fields

Core fields available across all data types:

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `timestamp` | timestamp | Point in time when the data point occurred | 1649822520123123165 |
| `start_time` | timestamp | Start time of a data point (UNIX Epoch in nanoseconds) | 1649822520123123165 |
| `end_time` | timestamp | End time of a data point (greater than or equal to start_time) | 1649822520123123165 |
| `duration` | duration | Difference between start_time and end_time in nanoseconds | 42 |
| `interval` | string | Timeframe represented by individual timeseries measurements | 1 min |

### Service Fields (service.*)

> **Gotcha**: `service.name` is a **resource attribute** on spans — do not confuse with `k8s.service.name` (Kubernetes Service) or `dt.smartscape.service` (Dynatrace Smartscape ID).

### Cloud Provider Fields

#### AWS (aws.*)

> See the `dt-obs-aws` skill for AWS field reference.

#### Azure (azure.*)

Key Azure fields:
- `azure.subscription` - Azure subscription ID (stable, primary-field, permission)
- `azure.location` - Geographical location (stable, primary-field)
- `azure.resource.group` - Resource group name (stable, primary-field, permission)
- `azure.resource.id` - Unique immutable identifier for the Azure resource (experimental)
- `azure.tenant.id` - Azure tenant identifier (experimental)
- `azure.vm.name` - Virtual machine name (experimental)
- `azure.tags.__tag_key__` - Azure tag values (experimental)

#### GCP (gcp.*)

Key GCP fields:
- `gcp.project.id` - GCP project identifier (stable, primary-field)
- `gcp.region` - GCP region (stable, primary-field)
- `gcp.zone` - Subset of a region (stable)
- `gcp.instance.id` - Unique numeric identifier (experimental)
- `gcp.resource.name` - Globally unique resource name (stable)
- `gcp.user_labels.__label__` - User labels (experimental)

### Kubernetes Fields (k8s.*)

> See the `dt-obs-kubernetes` skill and its `references/labels-annotations.md` for K8s field reference.

### Database Fields (db.*)

> See `dt-app-tracing/references/database-spans.md` for database field reference.

### URL Fields (url.*)

- `url.full`, `url.scheme`, `url.path`, `url.query` (sensitive), `url.fragment` — all stable
- `url.domain`, `url.path.pattern`, `url.port` — experimental

### Trace Fields (trace.*)

- `trace.id` - Unique trace identifier (16-byte, hex-encoded) (stable)
- `trace.state` - W3C trace context format state (experimental)
- `trace.is_sampled` - Sampling indicator (experimental)

### Log Fields (log.*)

Global `log.*` fields (not documented in other skills):
- `log.source` - Human-readable log stream identifier (stable, permission)
- `log.iostream` - I/O stream: `stdout`, `stderr` (stable)
- `log.file.name` - Basename of the log file (experimental)
- `log.file.path` - Full path to the log file (experimental)
- `log.logger` - Logger name inside the application (experimental)
- `log.raw_level` - Original severity level before standardization (experimental)

### Other Global Namespaces

The following namespaces follow standard conventions. Query `dt.semantic_dictionary.fields` for full field lists:

- `user.*` — `user.id` (stable), `user.email` (stable), `user.name`, `user.organization`
- `geo.*` — `geo.country.name`, `geo.city.name`, `geo.region.name` (stable); `geo.location.latitude/longitude` (sensitive)
- `network.*` — `network.transport` (`tcp`/`udp`), `network.type` (`ipv4`/`ipv6`), `network.peer.ip/port`
- `client.*` / `server.*` — `client.address`, `client.ip` (sensitive), `client.port`; `server.address`, `server.port`
- `container.*` — `container.id`, `container.name`, `container.image.name`, `container.image.version`
- `process.*` — `process.executable.name`, `process.executable.path`, `process.pid`
- `messaging.*` — see `dt-app-tracing/references/messaging-spans.md`
- `browser.*` — see `dt-app-frontend`
- `audit.*` — `audit.action`, `audit.identity`, `audit.result`, `audit.status` (all stable)

## Dynatrace-Specific Fields

### Smartscape IDs (dt.smartscape.*)

Entity ID format: `PREFIX-0123456789ABCDEF`

Common entity types:
- `dt.smartscape.host` - Host entity
- `dt.smartscape.service` - Service entity
- `dt.smartscape.process` - Process
- `dt.smartscape.frontend` - Web or mobile frontend
- `dt.smartscape.k8s_cluster` - K8s cluster
- `dt.smartscape.k8s_deployment` - K8s deployment

Legacy note: `dt.entity.*` field names are deprecated aliases in older content. Prefer `dt.smartscape.*` in all new queries and examples.

### Legacy Mapping (dt.entity.* → dt.smartscape.*)

| Deprecated field | Preferred field |
|---|---|
| `dt.entity.host` | `dt.smartscape.host` |
| `dt.entity.service` | `dt.smartscape.service` |
| `dt.entity.process_group_instance` | `dt.smartscape.process` |
| `dt.entity.kubernetes_cluster` | `dt.smartscape.k8s_cluster` |
| `dt.entity.cloud_application_instance` | `dt.smartscape.k8s_pod` |

> See the `dt-migration` skill for complete entity type mapping.

### Dynatrace System Fields (dt.system.*)

Automatically set by Grail, cannot be ingested.

Key system fields:
- `dt.system.bucket` - Grail bucket name (stable)
- `dt.system.table` - Table name (stable)
- `dt.system.environment` - Dynatrace environment (stable)
- `dt.system.segment_id` - Segment identifier (stable)
- `dt.system.monitoring_source` - License type (stable)

### Dynatrace Metadata Fields (dt.*)

Key metadata fields:
- `dt.host_group.id` - Host group name (stable, primary-field)
- `dt.security_context` - Security context for permissions (stable, permission)
- `dt.source_entity` - Source entity IDs (stable, entity-id)
- `dt.source_entity.type` - Source entity type (stable)
- `dt.cost.costcenter` - Cost center assignment (stable)
- `dt.cost.product` - Product/application assignment (stable)

### RUM Fields (dt.rum.*)

> See the `dt-app-frontend` skill for RUM field reference.

## Primary Grail Tags

Customer-selected tags automatically attached to raw telemetry. Format: `primary_tags.__key__` (e.g., `primary_tags.ownership`, `primary_tags.cost_center`, `primary_tags.environment`).

## Grail Special Field Tags

- **`permission`** — affects data access: `event.kind`, `event.type`, `event.provider`, `dt.security_context`
- **`primary-field`** — key organizational attributes: `aws.account.id`, `azure.subscription`, `k8s.cluster.name`, `k8s.namespace.name`
- **`sensitive-spans`** — `client.ip`, `db.connection_string`, `db.query.parameters`, `url.query`
- **`sensitive-user-events`** — `client.ip`, `geo.location.latitude`, `geo.location.longitude`

## Resources

- [Semantic Dictionary Overview](https://docs.dynatrace.com/docs/semantic-dictionary)
- [Global Field Reference](https://docs.dynatrace.com/docs/semantic-dictionary/fields)
- [Data Models](https://docs.dynatrace.com/docs/semantic-dictionary/model)
- [Grail Special Fields](https://docs.dynatrace.com/docs/semantic-dictionary/tags)
- [Versions & Changelog](https://docs.dynatrace.com/docs/semantic-dictionary/versions)

# Smartscape Topology Navigation

Navigate entity relationships using `traverse`, `smartscapeNodes` and `smartscapeEdges`.

## Table of Contents

- [Method Selection](#method-selection)
- [Node Types](#node-types)
- [Relationship Types](#relationship-types)
- [Traverse Syntax](#traverse-syntax)
- [Task: Multi-Hop Traversal](#task-multi-hop-traversal)
- [Task: Quick Relationship Lookup](#task-quick-relationship-lookup)
- [Task: Discover Edge Types](#task-discover-edge-types)
- [Task: Debug Empty Traversal Results](#task-debug-empty-traversal-results)
- [Common Patterns](#common-patterns)
- [Guidelines](#guidelines)

## Method Selection

| Task                            | Method            | Query Pattern                                                            |
| ------------------------------- | ----------------- | ------------------------------------------------------------------------ |
| Entity lookup / traversal start | `smartscapeNodes` | `smartscapeNodes "<TYPE>"` → `filter` / `traverse`                       |
| Discover node types             | `smartscapeNodes` | `smartscapeNodes "*"` → `dedup type`                                     |
| Discover edge types per node    | `smartscapeEdges` | `smartscapeEdges "*"` → `filter source_type` → `dedup type, target_type` |
| Multi-hop walk                  | `traverse`        | Chain multiple `traverse` commands                                       |
| Discover / verify edge types    | `smartscapeEdges` | Query before traverse                                                    |
| Empty results debug             | `smartscapeEdges` | Verify edge types exist                                                  |

**CRITICAL:** Wrong edge types return empty results (no error). Always validate for unfamiliar entities.

______________________________________________________________________

## Node Types

Node types are uppercase strings (e.g. `"HOST"`, `"SERVICE"`, `"K8S_POD"`). Entity IDs follow the pattern `<TYPE>-<HEX>` (e.g. `HOST-ABC123`).

- Use wildcard `*` to select all types of `smartscapeNodes`.
- Use partial wildcard matching such as
  - `AWS_*` to select all types starting with "AWS_" (AWS resources)
  - `*_CLUSTER` to select all types ending with "_CLUSTER"
  - `*_ELASTIC*` to select all types containing the substring "_ELASTIC".
  - Wildcard `*` is non-exclusive, i.e. also matches if there's no symbol preceding/following.

**Discover all node types in the environment:**

```dql
smartscapeNodes "*"
| dedup type
| fields type
```

**CRITICAL:** Wrong node types return empty results (no error). Always validate for unfamiliar entities.

### Tags and Labels

All cloud tags and Kubernetes labels/annotations are available on nodes via the `tags` field. Use backticks for label keys containing special characters (dots, slashes). For comprehensive Kubernetes label/annotation query patterns, see the [`dt-obs-kubernetes` skill's `labels-annotations` reference](../../../skills/dt-obs-kubernetes/references/labels-annotations.md).

## Relationship/Edge Types

The first argument to `smartscapeEdges` is the edge type name (e.g., `"calls"`, `"runs_on"`) or `"*"` (also supports partial wildcard matching) for all edge types — unlike `smartscapeNodes` where the argument is the node type. An edge type describes a type of relationship or dependency between two `smartscapeNodes`. The table below provides common edge types. However, note that there may be more edge types available in the environment. Always explore available edge types if uncertain.

| Edge Type        | Description                                                       | Opposite       | Examples                                                                              |
| ---------------- | ----------------------------------------------------------------- | -------------- | ------------------------------------------------------------------------------------- |
| `balanced_by`    | Load balancer relationship                                        | `balances`     |                                                                                       |
| `balances`       | Target balances source                                            | `balanced_by`  |                                                                                       |
| `belongs_to`     | Many-to-many without existential dependency (UML aggregation)     | `contains`     | SERVICE → K8S_CLUSTER, K8S_POD → K8S_NAMESPACE                                        |
| `calls`          | Horizontal communication between entities, no structural relation | —              | SERVICE → SERVICE, SERVICE → DATABASE                                                 |
| `contains`       | Parent contains children                                          | `belongs_to`   |                                                                                       |
| `instance_of`    | Instance-of-template relationship                                 | `instantiates` |                                                                                       |
| `is_attached_to` | Exclusively attached (1-to-many)                                  | —              | AWS_EBS_VOLUME → AWS_EC2_INSTANCE, AZURE_NETWORK_INTERFACE → AZURE_VIRTUAL_SUBNETWORK |
| `is_part_of`     | Composition (UML); child cannot exist without parent              | —              | AWS_EC2_INSTANCE → AWS_AUTOSCALING_GROUP, K8S_POD → K8S_DEPLOYMENT                    |
| `monitors`       | Monitoring OneAgent observes a monitored entity                   | —              | ONEAGENT → HOST                                                                       |
| `routes_to`      | Network routing relationship                                      | —              | ROUTE_TABLE → NAT_GATEWAY, VPC_PEERING → VPC, K8S_INGRESS → K8S_SERVICE               |
| `runs_on`        | Vertical "runs on" association, no composition implied            | —              | SERVICE → K8S_POD, CONTAINER → HOST                                                   |
| `uses`           | Loose usage dependency (opposite direction of `is_attached_to`)   | —              | K8S_POD → K8S_CONFIGMAP, ASG → LAUNCH_TEMPLATE                                        |

### Static vs Dynamic Edges

Edges are either **static** (infrastructure/config-based) or **dynamic** (observed at runtime). Use the hidden field `dt.system.edge_kind` via `fieldsAdd` on `smartscapeEdges` to verify whether an edge is `"static"` or `"dynamic"` in your environment.

**Get an overview of all Smartscape edges for all node types:**

```dql
smartscapeEdges "*"
| summarize count(), by:{ source_type, type, target_type, dt.system.edge_kind }
```

**List all edge types for a given node type:**

```dql
smartscapeEdges "*"
| filter source_type == "HOST" or target_type == "HOST"
| dedup type, source_type, target_type
| fields source_type, type, target_type
```

______________________________________________________________________

## Traverse Syntax

Parameters should always be wrapped in curly braces `{}`.

**Full syntax:**

```dql-template
smartscapeNodes <SOURCE_TYPE>
| traverse edgeTypes: {<EDGE_TYPE>}, targetTypes: {<TARGET_TYPE>}, direction: <forward|backward>
```

**Short syntax:**

The `edgeTypes:` and `targetTypes:` keywords can be omitted. `direction` defaults to `forward`.

```dql-snippet
smartscapeNodes "<SOURCE_TYPE>"
| traverse {"<EDGE_TYPE>"}, {"<TARGET_TYPE>"}
```

**CRITICAL:** Always prefer the full syntax for clarity.


| Parameter     | Values                                           | Usage                                             |
| ------------- | ------------------------------------------------ | ------------------------------------------------- |
| `edgeTypes`   | `{EDGE_TYPE}` or `{"*"}` or partial wildcard     | Edge type to follow                               |
| `targetTypes` | `{NODE_TYPE}` or `{"*"}` or partial wildcard     | Target node type                                  |
| `direction`   | `forward` or `backward`                          | forward = source→target, backward = source←target |
| `fieldsKeep`  | `{field1, field2}`                               | Preserve fields across hops                       |


**Multiple Node Types and/or Edge Types:**

Multiple node types and edge types can be used in a single query. Results contain all possible combinations of `<SOURCE_TYPE_*>`, `<EDGE_TYPE_*>`, `<TARGET_TYPE_*>`.

```dql-template
smartscapeNodes <SOURCE_TYPE_1>, <SOURCE_TYPE_2>
| traverse edgeTypes: {<EDGE_TYPE_1>, <EDGE_TYPE_2}, targetTypes: {<TARGET_TYPE_3>, <TARGET_TYPE_4>}, direction: <forward|backward>
```

**Accessing Traversal History/Path:**

Use the `dt.traverse.history` array of records to access the history of a traversal. For example:

- `dt.traverse.history[1]` returns the edge (`id`, `edge_type`, `direction`) of the first hop in a potential sequence of hops,
- ``dt.traverse.history[-1][`id`]`` returns the id of the target smartscape node of the last edge,
- ``dt.traverse.history[-2][`edge_type`]`` returns the edge type of the last but one edge.

### Direction Patterns

The following table provides examples of directional patterns:

| Pattern                    | Direction         | Use Case                                               |
| -------------------------- | ----------------- | ------------------------------------------------------ |
| Instance → Security Groups | `forward`         | What does this use?                                    |
| Security Group ← Instances | `backward`        | What uses this?                                        |
| Instance → Subnet → VPC    | `forward` (chain) | Follow dependencies                                    |
| VPC ← Resources            | `backward`        | Find all in VPC                                        |
| Service → Service          | `forward`         | Find horizontal Service-to-Service "calls" connections |

______________________________________________________________________

## Task: Multi-Hop Traversal

Same-direction 2-hop traversal:

```dql
smartscapeNodes "AWS_EC2_INSTANCE"
| filter aws.resource.id == "i-ABC123"
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_SUBNET}
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_VPC}
| fields vpc_id = id, vpc_name = name
```

In order to find entities that share a common dependency with the source node, combine `forward` and `backward` in a single chain (mixed-direction 2-hop query e.g. forward → backward).

**Preserve source fields:**

Use `fieldsKeep` to preserve fields from the origin of the traverse.

```dql
smartscapeNodes "AWS_EC2_INSTANCE"
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_SUBNET}, fieldsKeep: {lifetime}
| fields id, ec2_lifetime=lifetime
```

**Access history (previous hops):**

```dql
smartscapeNodes "AWS_ELASTICLOADBALANCINGV2_LOADBALANCER"
| traverse edgeTypes: {balanced_by}, targetTypes: {AWS_ELASTICLOADBALANCINGV2_TARGETGROUP}, direction: backward
| traverse edgeTypes: {balances}, targetTypes: {AWS_EC2_INSTANCE}
| fields dt.traverse.history[-1], dt.traverse.history[-2]
```

______________________________________________________________________

## Task: Forward Static Relationship Lookup without Traversal

**CRITICAL:**
- The `references` field only contains **forward, static** edges (e.g. infrastructure config-based relationships like PROCESS `runs_on` HOST).
- It does **not** contain **dynamic** edges (e.g. SERVICE `calls` SERVICE) or **backward** edges (where the node is the target). For discovering dynamic or backward edges, use `traverse` or `smartscapeEdges` instead.

To verify whether an edge is static or dynamic before relying on `references`, query `dt.system.edge_kind`:

```dql
smartscapeEdges "<EDGE_TYPE>"
| summarize count(), by: {dt.system.edge_kind}
```

**Method:** `references` field

**Key syntax:**
- Nested fields in `references` follow the pattern ``references[`<edge_type>.<target_type>`]`` where `<edge_type>` is the relationship (e.g. `runs_on`) and `<target_type>` is the lowercase target node type (e.g. `host`).
  - Example: ``references[`runs_on.host`]``.
  - Reference keys use the format `<edge_type>.<target_type>` (lowercase). Use backticks for keys with dots: ``references[`runs_on.host`]``, ``references[`uses.aws_ec2_securitygroup`]``.
- `references` always returns arrays. Use `[0]` when the relationship is known to be 1:1.

**Discover structure:**

```dql
smartscapeNodes "K8S_POD" | limit 1 | fieldsAdd references
```

**Extract specific relationships:**

```dql
smartscapeNodes "HOST"
| fieldsAdd references
| fields
    id,
    vm = references[`runs_on.aws_ec2_instance`]
```

**Look up the name of a referenced node:**

`getNodeName` can be used on any smartscape ID object to retrieve the more human-friendly name.

```dql
smartscapeNodes "HOST"
| fields
    id,
    vm_name = getNodeName(references[`runs_on.aws_ec2_instance`][0])
```

**Expand to rows:**

```dql
smartscapeNodes "AWS_EC2_INSTANCE"
| fieldsAdd sg_ids = references[`uses.aws_ec2_securitygroup`]
| expand sg_ids
```

**Count relationships:**

```dql
smartscapeNodes "AWS_EC2_INSTANCE"
| fieldsAdd sg_count = arraySize(references[`uses.aws_ec2_securitygroup`])
| summarize avg_sgs = avg(sg_count)
```

______________________________________________________________________

## Task: Discover Edge Types

**Find outgoing edges (FROM entity):**

```dql
smartscapeEdges "calls"
| filter source_id == toSmartscapeId("SERVICE-XYZ")
| fields type, target_type
```

**Find incoming edges (TO entity):**

```dql
smartscapeEdges "calls"
| filter target_id == toSmartscapeId("SERVICE-XYZ")
| fields type, source_type
```

______________________________________________________________________

## Task: Debug Empty Traversal Results

| Issue           | Check             | Solution                                               |
| --------------- | ----------------- | ------------------------------------------------------ |
| Empty results   | Edge type exists? | Query `smartscapeEdges` first                          |
| Wrong direction | Try opposite      | Switch `forward` ↔ `backward`                          |
| Typo            | Spelling          | Check reference markdowns to understand field spelling |
| Wrong target    | Entity type       | Verify with `smartscapeNodes`                          |

______________________________________________________________________

## Common Patterns

### Process → Container → K8S Pod (Process to Kubernetes Pod)

```dql
smartscapeNodes "PROCESS"
| filter id == toSmartscapeId("PROCESS-XYZ")
| traverse edgeTypes: {runs_on}, targetTypes: {CONTAINER}
| traverse edgeTypes: {is_part_of}, targetTypes: {K8S_POD}
```

### Service Dependency Chain

```dql
smartscapeNodes "SERVICE"
| filter id == toSmartscapeId("SERVICE-XYZ")
| traverse edgeTypes: {calls}, targetTypes: {SERVICE}
| fields downstream_id = id, downstream_name = name
```

### Kubernetes: Pod → Node → Cluster

```dql
smartscapeNodes "K8S_POD"
| filter id == toSmartscapeId("K8S_POD-XYZ")
| traverse edgeTypes: {runs_on}, targetTypes: {K8S_NODE}
| traverse edgeTypes: {belongs_to}, targetTypes: {K8S_CLUSTER}
| fields cluster_id = id, cluster_name = name
```

### Load Balancer → Instances

```dql
smartscapeNodes "AWS_ELASTICLOADBALANCINGV2_LOADBALANCER"
| traverse edgeTypes: {balanced_by}, targetTypes: {AWS_ELASTICLOADBALANCINGV2_TARGETGROUP}, direction: backward
| traverse edgeTypes: {balances}, targetTypes: {AWS_EC2_INSTANCE}
```

### All Resources in VPC

```dql
smartscapeNodes "AWS_EC2_VPC"
| filter aws.vpc.id == "vpc-abc123"
| traverse edgeTypes: {is_attached_to}, targetTypes: {"AWS_*"}, direction: backward
| summarize count = count(), by: {type}
```

### Blast Radius (Security Group Impact)

```dql
smartscapeNodes "AWS_EC2_SECURITYGROUP"
| filter aws.resource.id == "sg-CRITICAL"
| traverse edgeTypes: {uses}, targetTypes: {"*"}, direction: backward
| summarize count = count(), by: {type}
```

### Cross-Account Usage

```dql
smartscapeNodes "AWS_IAM_ROLE"
| traverse edgeTypes: {uses}, targetTypes: {"AWS_*"}, direction: backward
| filter aws.account.id != "123456789012"
| fields role_id = dt.traverse.history[-1][`id`], resource_type = type, aws.account.id
```

### Network Topology (3-hop)

```dql
smartscapeNodes "AWS_EC2_NETWORKINTERFACE"
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_INSTANCE}
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_SUBNET}
| traverse edgeTypes: {is_attached_to}, targetTypes: {AWS_EC2_VPC}
| fields
    eni = dt.traverse.history[-3],
    instance = dt.traverse.history[-2],
    subnet = dt.traverse.history[-1],
    vpc = aws.resource.id
```

### Variable-Depth Search (All Reachable Nodes)

Chained `traverse` only returns nodes at an exact hop depth. To find all reachable nodes at any depth, use `append` to combine results from 1-hop, 2-hop, and 3-hop traversals:

```dql
smartscapeNodes "SERVICE"
| filter id == toSmartscapeId("SERVICE-XYZ")
| traverse edgeTypes: {calls}, targetTypes: {SERVICE}
| append [
    smartscapeNodes "SERVICE"
    | filter id == toSmartscapeId("SERVICE-XYZ")
    | traverse edgeTypes: {calls}, targetTypes: {SERVICE}
    | traverse edgeTypes: {calls}, targetTypes: {SERVICE}
  ]
| append [
    smartscapeNodes "SERVICE"
    | filter id == toSmartscapeId("SERVICE-XYZ")
    | traverse edgeTypes: {calls}, targetTypes: {SERVICE}
    | traverse edgeTypes: {calls}, targetTypes: {SERVICE}
    | traverse edgeTypes: {calls}, targetTypes: {SERVICE}
  ]
| dedup id
```

### Inferred Horizontal Edges (Deployment → Deployment via Service Calls)

Some entity types (e.g. K8S_POD, K8S_CLUSTER) have no explicit smartscape edge connecting to related SERVICE nodes. You can infer these relationships by navigating to the SERVICE layer, following `calls`, and navigating back:

```dql
smartscapeNodes "K8S_DEPLOYMENT"
| traverse edgeTypes: {belongs_to}, targetTypes: {SERVICE}, direction: backward, fieldsKeep: {type, name}
| traverse edgeTypes: {calls}, targetTypes: {SERVICE}
| traverse edgeTypes: {belongs_to}, targetTypes: {K8S_DEPLOYMENT}
| fields
    source_name = dt.traverse.history[0][name],
    target_name = name
```

______________________________________________________________________

## Guidelines

### `toSmartscapeId()` Usage

Do not use strings to filter Smartscape Ids. Strings representing SmartscapeId objects must be converted to data type SmartscapeId first. For example:

- When filtering by ID in `smartscapeEdges` or `smartscapeNodes`, or comparing IDs across hops, you must convert string IDs using `toSmartscapeId()`.
- Filtering `id` on `smartscapeNodes`
- Filtering `source_id` or `target_id` in `smartscapeEdges` queries
- Comparing IDs returned from `dt.traverse.history` against known values

**Example:**

```dql
smartscapeEdges "calls"
| filter source_id == toSmartscapeId("SERVICE-XYZ")
```

```dql
smartscapeNodes "SERVICE"
| filter id == toSmartscapeId("SERVICE-XYZ")
```

______________________________________________________________________

### Performance

1. **Filter before traverse** - Reduce dataset size early
1. **Use specific types** - Try to avoid `{"*"}` if not necessary
1. **Limit wildcards** - Add `| limit 100` for exploration
1. **Use references for counting** - No traversal overhead
1. **Use traverse for details** - When you need entity fields
1. **Select only needed fields** - `smartscapeNodes` includes large object fields by default that significantly increase result size. Avoid selecting these fields unless specifically required:
   - `k8s.object` — full Kubernetes object JSON
   - `aws.object` — full AWS resource description JSON
   - `azure.object` — full Azure resource JSON
   - `references` — nested object with all static forward edges

   Use `fields` to select only required columns, or `fieldsRemove k8s.object, aws.object` to drop them explicitly.

# String Matching Functions

Reference for `matchesValue`, `matchesPhrase`, `matchesPattern`, and `in()` — the main functions for string and array pattern matching in DQL.

---

## `matchesValue()`

`matchesValue` is the primary function for pattern matching against string and array fields.

**Wildcard semantics** (`*` is supported anywhere in the pattern):

| Pattern | Meaning |
|---------|---------|
| `"exact"` | Exact match (case-insensitive by default) |
| `"prefix*"` | Starts with — **case-insensitive** (unlike `startsWith()`) |
| `"*suffix"` | Ends with — **case-insensitive** (unlike `endsWith()`) |
| `"*contains*"` | Substring — **case-insensitive** (unlike `contains()`, which is case-sensitive by default) |
| `"in*fix"` | Mid-string wildcard — matches any string starting with "in" and ending with "fix" |

**Notes:**
- The wildcard character is `*` by default and can be changed via the `wildcard` parameter: `matchesValue(field, "pattern", wildcard: "?")`.
- The second parameter must be a **constant or array literal** — a field reference is a runtime error.

**Array support** — both parameters accept arrays, eliminating `iAny`:

```dql-snippet
// Array field in first param: no iAny or [] needed
| filter matchesValue(process.command_args, "--pool")

// Array literal {} in second param: replaces a chain of OR conditions
| filter matchesValue(process.command_args, {"--pool", "--algo", "--randomx-*", "stratum+tcp://*", "*monero*"})

// Both combined: replaces iAny(matchesValue(arr[], "x") OR matchesValue(arr[], "y"))
| filter matchesValue(process.command_args, {"x", "y*", "*z*"})

// Replaces: contains(f, "a") OR contains(f, "b") OR contains(f, "c") on the same field
// Note: this is only a readability win when multiple contains() calls share the same field
| filter matchesValue(process.command_line, {"*curl*", "*wget*", "*nc*"})
```

**Case sensitivity**: `matchesValue` is **case-insensitive by default**. To enforce case: `matchesValue(field, "Pattern", caseSensitive: true)`. This also replaces `lower()` workarounds:

```dql-snippet
// WRONG — lower() + iAny + in() is verbose and fragile
| filter iAny(in(lower(process.command_args[]), array("xmrig", "ccminer")))

// RIGHT — matchesValue is case-insensitive by default
| filter matchesValue(process.command_args, {"xmrig", "ccminer"})
```

---

## `matchesPhrase()`

`matchesPhrase` tokenizes the input string and matches **whole words** (word-boundary aware). Use it instead of `contains()` when matching short or common tokens where substring hits would cause false positives.

**Optional parameters**: `caseSensitive` (default `false`) enables case-sensitive phrase matching; `wildcard` lets you specify a custom wildcard character for the phrase pattern.

**Word-boundary caveat:** punctuation characters (e.g. `-`) are themselves word boundaries. `matchesPhrase(f, "-value")` returns `true` for `"test-value"` because the `-` acts as a boundary before "value". Phrase patterns starting or ending with punctuation can match mid-string.

```dql-snippet
// contains() fires on "pipenv", "pipeline", "piped"
| filter contains(process.command_line, "pip")

// matchesPhrase() matches only the whole word "pip"
| filter matchesPhrase(process.command_line, "pip")
```

**Array support**: the **first** parameter accepts an array field — no `iAny` or `[]` needed. The **second** parameter must be a **static string literal** (array unwrapping causes a runtime error):

```dql-snippet
// WRONG — iAny wrapper is redundant when first param is an array field
| filter iAny(matchesPhrase(process.command_args[], "pip"))

// RIGHT — matchesPhrase iterates the array natively
| filter matchesPhrase(process.command_args, "pip")

// WRONG — runtime error: second param does not accept arrays
| filter matchesPhrase(process.command_line, array("-e", "-c")[])

// RIGHT — OR the phrases individually
| filter matchesPhrase(process.command_line, "-e") OR matchesPhrase(process.command_line, "-c")
```

---

## `matchesPattern()` — regex matching

`matchesPattern(field, regex)` matches a string field against a regular expression. Unlike `matchesValue` and `matchesPhrase`, **neither parameter accepts an array** — both must be scalar strings. To iterate over an array field, use `iAny` with `[]`:

```dql-snippet
// WRONG — matchesPattern does not accept an array field directly
| filter matchesPattern(process.command_args, ".*--pool.*")

// RIGHT — use iAny + [] to iterate
| filter iAny(matchesPattern(process.command_args[], ".*--pool.*"))
```

---

## `in()` — set membership and array overlap

`in()` tests whether a value (or any element of an array) appears in a set.

```dql-snippet
// Scalar field — replaces field == "a" OR field == "b" OR field == "c"
| filter in(audit.action, array("PutObject", "DeleteObject", "GetObject"))

// Array field — true if any element of the array matches
| filter in(process.command_args, array("-e", "-c"))

// Array overlap — true if the two arrays share any element
| filter in(process.command_args, process.command_args_other)
```

Syntax variants for the haystack:

```dql-snippet
in(field, {"a", "b", "c"})            // set literal
in(field, array("a", "b", "c"))        // array() constructor
in(field, "a", "b", "c"))              // simplified: extra params treated as haystack
```

---

## Quick reference

| Goal | Verbose (avoid) | Idiomatic DQL |
|------|----------------|---------------|
| Field equals one of N values | `f == "a" OR f == "b"` | `in(f, array("a","b"))` |
| Array field contains one of N values | `iAny(f[] == "a" OR f[] == "b")` | `in(f, array("a","b"))` |
| Many substring checks on same field (3+) | `contains(f,"a") OR contains(f,"b") OR ...` | `matchesValue(f, {"*a*", "*b*", ...})` ¹ |
| Array field matches any of N patterns | `iAny(matchesValue(f[], "a") OR ...)` | `matchesValue(f, {"a", "b*"})` |
| Array field contains whole-word token | `iAny(contains(f[], "pip"))` | `matchesPhrase(f, "pip")` |
| Case-insensitive array match | `iAny(in(lower(f[]), array("a","b")))` | `matchesValue(f, {"a","b"}, caseSensitive: false)` |

> ¹ `matchesValue` is case-insensitive by default. If the original `contains()` calls relied on case-sensitive matching, add `caseSensitive: true` to preserve it.

# Various applications of summarize and makeTimeseries commands

## Table of Contents
- [General rules](#general-rules)
- [Examples](#examples)

## General rules
* `makeTimeseries` accepts timeframe from `from:` and `to:` or from `timeframe:` parameters, but if they are not present timeframe is inherited from query providing data. In case time values from expression/field provided in `time:` parameter is outside from this inherited timeframe, data will be ignored. For such cases providing proper timeframe is necessary.      
* `bin(timestamp, <interval>)` returns a `timestamp`, not a `timeframe` or `string`. If downstream processing requires a different type, apply an explicit conversion — e.g. `toString(bin(timestamp, 1h))` for a string representation.

## Examples

### How to aggregate data by calendar months
* Question: If there is data present for past year in bizevents table it is easy to aggregate by calendar month.
```dql
fetch bizevents, from: -1y
| summarize {cnt=count()}, by: { month=timestamp@M } 
```
Is the same possible for metrics?
* Answer: if data is available as metrics, first daily aggregates need to be retrieved, then they can be aggregated by calendar months:
```dql
timeseries {cnt=sum(dt.service.request.count), timestamp=start()}, from:-1y, interval:24h
| fieldsAdd d=record(cnt=cnt[], timestamp=timestamp[])
| expand d
| fields cnt=d[cnt], timestamp=d[timestamp]
| summarize {cnt=sum(cnt)}, by: { month=timestamp@M }
```
Data presented this way is chartable the same as if it were a time series.

### How to filter rows by property of group of them
* Question: I want to get only hosts if they belong to cluster smaller than N hosts. Cluster belonging is defined by `host.custom.metadata[Cluster]` 
```dql
    smartscapeNodes "HOST"
    | fieldsAdd cluster = host.custom.metadata[Cluster]
```
* Answer: during summarization besides calculation count, sums and averages we can preserve original data in arrays
```dql
    smartscapeNodes "HOST"
    | fieldsAdd cluster = host.custom.metadata[Cluster]
    | filter isNotNull(cluster)
    | summarize { host_count = count(), dt.smartscape.host=collectArray(id) } , by: {cluster}
    | filter host_count < 100  // Filter clusters with less than 100 hosts
    | fields dt.smartscape.host
    | expand dt.smartscape.host
```

### Histogram metric visualization
* Question: I have a histogram metric with "le" dimension representing less-or-equal so upper bounds of histogram bucket. How can I graph a histogram over time for this metric?
* Answer: Our heatmap visualization is perfect for this use case. You just need to prepare data in the right format:
```dql
timeseries {cnt = sum(istio_request_duration_milliseconds_bucket), timestamp=start()}, by:{le}, interval:15m
| fieldsAdd d = record(timestamp=timestamp[], cnt=cnt[])
| expand d
| summarize d = collectArray(record(le_s=toDouble(le), le, cnt=d[cnt])), by:{ timestamp=d[timestamp]}
| fieldsAdd d = arraySort(d, direction:"ascending")
| fieldsAdd cnt = arrayRemoveNulls(arrayDelta( arrayFlatten(arrayConcat(array(0),iCollectArray( d[][cnt] )))))
| fieldsAdd d = record(le = d[][le], cnt=cnt[])
| expand d
| makeTimeseries cnt=sum(d[cnt]), by: {le=d[le]}, interval:15m
| sort toDouble(le) desc
```

### Calculating percentages for results of summarization (summarize with count())
* Question: I have a summarize with count() and I want to calculate percentage of each value in total count. How can I do it? I have this DQL query:
```dql
fetch logs
| summarize {c=count()}, by: {loglevel}
```
* Answer:
```dql
fetch logs 
| summarize {c=count()}, by: {loglevel}
| summarize {d=collectArray(record(c, loglevel)), total=sum(c)}
| expand d
| fields loglevel=d[loglevel], c=d[c], perc=100.0*d[c]/total
```

### calculating difference between values for subsequent days
* Question: Let's assume that data looks like this and being aggregated this way:
```dql
data record(BusinessProcessDate = "2026-02-27", value = 110.000),
record(BusinessProcessDate = "2026-02-28", value = 115.500),
record(BusinessProcessDate = "2026-03-01", value = 112.300),
record(BusinessProcessDate = "2026-03-01", value = 112.300),
record(BusinessProcessDate = "2026-03-03", value = 119.800),
record(BusinessProcessDate = "2026-03-04", value = 122.100),
record(BusinessProcessDate = "2026-03-05", value = 118.600)
| summarize current = max(value), by: {BusinessProcessDate=toTimestamp(BusinessProcessDate)}
```
I want to calculate difference (absolute and relative) of current value for specific date and value for previous data (null if there was no data for previous date).
* Answer: Collect array gathers data in single arrays as records containing date and value (in this order). Array needs to be sorted, because collectArray does not guarantee any sorting. using arrayElement and iIndex()-1 we can look at array's previous elements and if this is for previous date we can take a value from there as "previous". After expanding array to records, calculation of differences is possible
```dql
data record(BusinessProcessDate = "2026-02-27", underlyingsInStream = 110.000),
     record(BusinessProcessDate = "2026-02-28", underlyingsInStream = 115.500),
     record(BusinessProcessDate = "2026-03-01", underlyingsInStream = 112.300),
     record(BusinessProcessDate = "2026-03-02", underlyingsInStream = 114.574),
     record(BusinessProcessDate = "2026-03-03", underlyingsInStream = 119.800),
     record(BusinessProcessDate = "2026-03-04", underlyingsInStream = 122.100),
     record(BusinessProcessDate = "2026-03-05", underlyingsInStream = 118.600)
| summarize current = max(underlyingsInStream), by: {BusinessProcessDate=toTimestamp(BusinessProcessDate)}
| summarize d = collectArray(record(BusinessProcessDate, current))
| fieldsAdd d = arraySort(d, direction:"ascending")
| fieldsAdd previous = if( d[][BusinessProcessDate] - arrayElement(d,iIndex()-1)[BusinessProcessDate] == 1d , arrayElement(d,iIndex()-1)[current])
| fields d = record(BusinessProcessDate=d[][BusinessProcessDate], current=d[][current], previous=previous[] )
| expand d
| fields BusinessProcessDate=d[BusinessProcessDate], current=d[current], previous=d[previous], absChange=d[current]-d[previous], relChange=(d[current]-d[previous])*100.0/d[previous]
| sort BusinessProcessDate desc
```

### Join operation expressed as summarization
* Question: Query below fails due to size limit related to result of right query 
```dql
  fetch user.events, scanLimitGBytes:-1
  | filter isNotNull(trace.id)
  | dedup dt.rum.session.id, trace.id
  | join [
      fetch spans, scanLimitGBytes:-1
      | filter isNotNull(host.name)
      | dedup trace.id, host.name
  ], on:{trace.id}, fields:{host.name}
  | summarize session_count=countDistinct(dt.rum.session.id), by:{hostname=host.name}
```
* Answer: With `append` and `summarize` same effect can be achieved 
```dql
fetch user.events, scanLimitGBytes:-1
| filter isNotNull(trace.id)
| dedup dt.rum.session.id, trace.id
| append [ 
    fetch spans, scanLimitGBytes:-1
    | filter isNotNull(host.name)
    | summarize host.name=collectDistinct(host.name), by:{trace.id}
]
| summarize { dt.rum.session.id=takeAny(dt.rum.session.id),
host.name=takeAny(host.name) }, by:{trace.id}
| filterOut isNull(dt.rum.session.id)
| summarize session_count=countDistinct(dt.rum.session.id), by:{hostname=host.name}
| sort session_count desc
```
* Question: what if data is coming from same source:
```dql
fetch logs
| filter matchesPhrase(messageIdentification,"pacs8")    
| fields timestamp, messageIdentification, transactionIdentification, id  
| join [
  fetch logs
  | filter matchesPhrase(messageIdentification,"pacs2")
  | fields timestamp, messageIdentification, transactionIdentification, id
], on: { transactionIdentification}
    
| fields transactionIdentification, pacs8time = timestamp, pacs2time = right.timestamp
| fieldsAdd diffDuration = pacs2time - pacs8time
```
* Answer in this case `append` is not needed
```dql
fetch logs
| filter matchesPhrase(messageIdentification,"pacs8") or matchesPhrase(messageIdentification,"pacs2")   
| fields timestamp, messageIdentification, transactionIdentification, id
| summarize {
  pacs8time = takeAny(if(matchesPhrase(messageIdentification, "pacs8"), timestamp)),
  pacs2time = takeAny(if(matchesPhrase(messageIdentification, "pacs2"), timestamp))
}, by: { transactionIdentification }
| fieldsAdd diffDuration = pacs2time - pacs8time
```

### Use of `spread:` parameter
* Question: how to graph over time number of running containers by workload.kind. Container has `lifetime` property being timeframe in which it was active
* Answer: `spread:` parameter of `makeTimeseries` together with `count()`:
```dql
smartscapeNodes "CONTAINER"
| makeTimeseries count(), spread:lifetime, by: {k8s.workload.kind}, interval:30m
```

### `switch/case` or `case/when` syntax in DQL
DQL does not have built-in syntax like this. As an alternative, a chain of `if/else` statements can be used.
```dql-snippet
| fieldsAdd bucket = if(dim == 0,        "0",
else: if(dim <= 3,         "1–3",
else: if(dim <= 18,        "4–18",
else: if(dim <= 32,        "19–32",
else: if(dim >= 100,       "100+",
else:                      "33–99")))))
```
To avoid having to close many `)` at the end, `coalesce` is useful:
```dql-snippet
| fieldsAdd bucket = coalesce(
  if(dim == 0,   "0"),
  if(dim <= 3,   "1–3"),
  if(dim <= 18,  "4–18"),
  if(dim <= 32,  "19–32"),
  if(dim >= 100, "100+"),
                 "33–99")
```

---
name: dt-dql-essentials
description: "Core DQL syntax, pitfalls, query patterns, and query optimization. Load to write, build, fix, or OPTIMIZE a DQL query — prevents syntax errors and makes queries faster, more efficient, and cheaper (less data scanned = lower query consumption/cost per run). Covers fetch commands, data models, field namespaces, time alignment, entity/smartscape patterns, metric discovery, and performance/cost optimization (filter early, bucket filters, short time ranges, field selection, sampling, cardinality). Trigger: \"write/build/fix a DQL query\", \"DQL syntax\", \"query logs/spans/metrics\", \"create a timeseries\", \"optimize my DQL\", \"make my query faster/cheaper\", \"reduce DQL cost/consumption/scanned data\", \"keep DQL cost under control\". Do NOT use to explain an existing query or answer product questions. For MONITORING a tenant's ACTUAL query consumption/billing (how much queries cost, who scanned most, cost trends) use dt-platform-costs — this tunes the query text, not billing data."
license: Apache-2.0
---

# DQL Essentials Skill

DQL is a pipeline-based query language. Queries chain commands with `|` to filter, transform, and aggregate data. DQL has unique syntax that differs from SQL — load this skill before writing any DQL query.

______________________________________________________________________

## When to Load References

Before working on specific tasks, load the relevant reference:

| Task                                                                          | Required Reading                                                                             |
| ----------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- |
| Field names, namespaces, data models, stability levels, query patterns        | [references/semantic-dictionary.md](references/semantic-dictionary.md)                       |
| Query optimization — make a query faster / more efficient / cheaper, reduce consumption & scanned data (filter early, bucket filters, time ranges, field selection, sampling, cardinality) | [references/optimization.md](references/optimization.md)                                     |
| Smartscape topology navigation for discovering relationships between entities | [references/smartscape-topology-navigation.md](references/smartscape-topology-navigation.md) |
| `summarize` and `makeTimeseries` patterns (bucketing, calendar months)        | [references/summarization.md](references/summarization.md)                                   |
| Array and timeseries manipulation (`arrayFilter`, `collectArray`, iterative)  | [references/iterative-expressions.md](references/iterative-expressions.md)                   |
| Conditional logic (`if/else` chains), `coalesce`, string/date helpers         | [references/useful-expressions.md](references/useful-expressions.md)                         |
| `in` operator (subquery), full `@` time alignment unit table                  | [references/operators.md](references/operators.md)                                           |
| `matchesValue`, `matchesPhrase`, `matchesPattern`, `in()` — string pattern matching, regex, array matching, wildcards, case sensitivity | [references/string-matching.md](references/string-matching.md)                               |

______________________________________________________________________

## DQL Reference Index

Use this index to route from a function group (e.g. time functions, conversions) to its detailed spec, or from a function name to its spec file.

| Description | Items |
|-------------|-------|
| [Data Types](references/dql/dql-data-types.md) | `array`, `binary`, `boolean`, `double`, `duration`, `long`, `record`, `string`, `timeframe`, `timestamp`, `uid` |
| [Parameter Value Types](references/dql/dql-parameter-value-types.md) | `bucket`, `dataObject`, `dplPattern`, `entityAttribute`, `entitySelector`, `entityType`, `enum`, `executionBlock`, `expressionTimeseriesAggregation`, `expressionWithConstantValue`, `expressionWithFieldAccess`, `fieldPattern`, `filePattern`, `identifierForAnyField`, `identifierForEdgeType`, `identifierForFieldOnRootLevel`, `identifierForNodeType`, `joinCondition`, `jsonPath`, `metricKey`, `metricTimeseriesAggregation`, `namelessDplPattern`, `nonEmptyExecutionBlock`, `prefix`, `primitiveValue`, `simpleIdentifier`, `tabularFileExisting`, `tabularFileNew`, `url` |
| [Commands](references/dql/dql-commands.md) | `append`, `data`, `dedup`, `describe`, `expand`, `fetch`, `fields`, `fieldsAdd`, `fieldsFlatten`, `fieldsKeep`, `fieldsRemove`, `fieldsRename`, `fieldsSnapshot`, `fieldsSummary`, `filter`, `filterOut`, `join`, `joinNested`, `limit`, `load`, `lookup`, `makeTimeseries`, `metrics`, `parse`, `search`, `smartscapeEdges`, `smartscapeNodes`, `sort`, `summarize`, `timeseries`, `traverse` |
| [Functions — Aggregation](references/dql/dql-functions-aggregation.md) | `avg`, `collectArray`, `collectDistinct`, `correlation`, `count`, `countDistinct`, `countDistinctApprox`, `countDistinctExact`, `countIf`, `max`, `median`, `min`, `percentRank`, `percentile`, `percentileFromSamples`, `percentiles`, `stddev`, `sum`, `takeAny`, `takeFirst`, `takeLast`, `takeMax`, `takeMin`, `variance` |
| [Functions — Array](references/dql/dql-functions-array.md) | `arrayAvg`, `arrayConcat`, `arrayCumulativeSum`, `arrayDelta`, `arrayDiff`, `arrayDistinct`, `arrayFirst`, `arrayFlatten`, `arrayIndexOf`, `arrayLast`, `arrayLastIndexOf`, `arrayMax`, `arrayMedian`, `arrayMin`, `arrayMovingAvg`, `arrayMovingMax`, `arrayMovingMin`, `arrayMovingSum`, `arrayPercentile`, `arrayRemoveNulls`, `arrayReverse`, `arraySize`, `arraySlice`, `arraySort`, `arraySum`, `arrayToString`, `vectorCosineDistance`, `vectorInnerProductDistance`, `vectorL1Distance`, `vectorL2Distance` |
| [Functions — Bitwise](references/dql/dql-functions-bitwise.md) | `bitwiseAnd`, `bitwiseCountOnes`, `bitwiseNot`, `bitwiseOr`, `bitwiseShiftLeft`, `bitwiseShiftRight`, `bitwiseXor` |
| [Functions — Boolean](references/dql/dql-functions-boolean.md) | `exists`, `in`, `isFalseOrNull`, `isNotNull`, `isNull`, `isTrueOrNull`, `isUid128`, `isUid64`, `isUuid` |
| [Functions — Cast](references/dql/dql-functions-cast.md) | `asArray`, `asBinary`, `asBoolean`, `asDouble`, `asDuration`, `asIp`, `asLong`, `asNumber`, `asRecord`, `asSmartscapeId`, `asString`, `asTimeframe`, `asTimestamp`, `asUid` |
| [Functions — Constant](references/dql/dql-functions-constant.md) | `e`, `pi` |
| [Functions — Conversion](references/dql/dql-functions-conversion.md) | `toArray`, `toBoolean`, `toDouble`, `toDuration`, `toIp`, `toLong`, `toSmartscapeId`, `toString`, `toTimeframe`, `toTimestamp`, `toUid`, `toVariant` |
| [Functions — Create](references/dql/dql-functions-create.md) | `array`, `duration`, `ip`, `record`, `smartscapeId`, `timeframe`, `timestamp`, `timestampFromUnixMillis`, `timestampFromUnixNanos`, `timestampFromUnixSeconds`, `uid128`, `uid64`, `uuid` |
| [Functions — Cryptographic](references/dql/dql-functions-cryptographic.md) | `hashCrc32`, `hashMd5`, `hashSha1`, `hashSha256`, `hashSha512`, `hashXxHash32`, `hashXxHash64` |
| [Functions — Entities](references/dql/dql-functions-entities.md) | `classicEntitySelector`, `entityAttr`, `entityName` |
| [Functions — Time series aggregation for expressions](references/dql/dql-functions-expression-timeseries.md) | `avg`, `count`, `countDistinct`, `countDistinctApprox`, `countDistinctExact`, `countIf`, `end`, `max`, `median`, `min`, `percentRank`, `percentile`, `percentileFromSamples`, `start`, `sum` |
| [Functions — Flow](references/dql/dql-functions-flow.md) | `coalesce`, `if` |
| [Functions — General](references/dql/dql-functions-general.md) | `jsonField`, `jsonPath`, `lookup`, `parse`, `parseAll`, `type` |
| [Functions — Get](references/dql/dql-functions-get.md) | `arrayElement`, `getEnd`, `getHighBits`, `getLowBits`, `getStart` |
| [Functions — Iterative](references/dql/dql-functions-iterative.md) | `iAny`, `iCollectArray`, `iIndex` |
| [Functions — Mathematical](references/dql/dql-functions-mathematical.md) | `abs`, `acos`, `asin`, `atan`, `atan2`, `bin`, `cbrt`, `ceil`, `cos`, `cosh`, `degreeToRadian`, `exp`, `floor`, `hexStringToNumber`, `hypotenuse`, `log`, `log10`, `log1p`, `numberToHexString`, `power`, `radianToDegree`, `random`, `range`, `round`, `signum`, `sin`, `sinh`, `sqrt`, `tan`, `tanh` |
| [Functions — Network](references/dql/dql-functions-network.md) | `ipIn`, `ipIsLinkLocal`, `ipIsLoopback`, `ipIsPrivate`, `ipIsPublic`, `ipMask`, `isIp`, `isIpV4`, `isIpV6` |
| [Functions — Smartscape](references/dql/dql-functions-smartscape.md) | `getNodeField`, `getNodeName` |
| [Functions — String](references/dql/dql-functions-string.md) | `concat`, `contains`, `decodeBase16ToBinary`, `decodeBase16ToString`, `decodeBase64ToBinary`, `decodeBase64ToString`, `decodeUrl`, `encodeBase16`, `encodeBase64`, `encodeUrl`, `endsWith`, `escape`, `getCharacter`, `indexOf`, `lastIndexOf`, `levenshteinDistance`, `like`, `lower`, `matchesPattern`, `matchesPhrase`, `matchesRegex`, `matchesValue`, `punctuation`, `replacePattern`, `replaceString`, `splitByPattern`, `splitString`, `startsWith`, `stringLength`, `substring`, `trim`, `unescape`, `unescapeHtml`, `upper` |
| [Functions — Time](references/dql/dql-functions-time.md) | `formatTimestamp`, `getDayOfMonth`, `getDayOfWeek`, `getDayOfYear`, `getHour`, `getMinute`, `getMonth`, `getSecond`, `getWeekOfYear`, `getYear`, `now`, `unixMillisFromTimestamp`, `unixNanosFromTimestamp`, `unixSecondsFromTimestamp` |
| [Functions — Time series aggregation for metrics](references/dql/dql-functions-timeseries.md) | `avg`, `count`, `countDistinct`, `end`, `max`, `median`, `min`, `percentRank`, `percentile`, `start`, `sum` |

______________________________________________________________________

## Syntax Pitfalls

| ❌ Wrong | ✅ Right | Issue |
| --- | --- | --- |
| `filter field in ["a", "b"]` | `filter in(field, {"a", "b"})` | `[` and `]` wrap sub-queries in DQL but do not wrap **static** array literals. Use `{}` or `array()` for static values. |
| `filter: { in(field, [sub-query]) }` (e.g. in `timeseries filter:`) | `filter: { field in [sub-query] }` | `in()` does not accept execution blocks as arguments. When the right-hand side is a sub-query (execution block), use the `in` operator: `field in [execution block]`. |
| `by: severity, status` | `by: {severity, status}` | List of fields must be grouped by curly braces in `by:` clauses (`summarize`, `makeTimeseries`, etc.). |
| `contains(toLowercase(field), "err")` | `contains(field, "err", false)` | Don't wrap in `lower()` for case-insensitive matching. `contains()` has a built-in third positional `caseSensitive` parameter (default `true`). |
| `filter name == "*serv*9*"` | `filter matchesValue(name, "*serv*") and matchesValue(name, "*9*")` | `==` does not support wildcards. `matchesValue()` supports `*` wildcards but only at the beginning and/or end of the pattern—split mid-string wildcard intent into multiple calls combined with `and`. |
| `matchesValue(field, "prod")` on string field | `contains(field, "prod")` | Without wildcards, `matchesValue()` performs an exact (case-insensitive) match — it will not find `"production"`. Use `contains()` for substring matching (or `matchesValue(field, "*prod*")` for wildcard matching). |
| `iAny(matchesValue(arr[], "x") OR matchesValue(arr[], "y"))` | `matchesValue(arr, {"x", "y"})` | `matchesValue` accepts an array field in the first param and an array literal `{}` in the second — no `iAny` or `[]` needed. The same applies when consolidating multiple `contains(f, x) OR contains(f, y)` on the same field: use `matchesValue(f, {"*x*", "*y*"})`. |
| `iAny(matchesPhrase(arr[], "phrase"))` | `matchesPhrase(arr, "phrase")` | `matchesPhrase` iterates array fields natively — drop `iAny(` and `[]`. Note: the **second** parameter must be a static string; `matchesPhrase(f, array("a","b")[])` is a runtime error. |
| `contains(field, "pip")` on a short or common token | `matchesPhrase(field, "pip")` | `contains` is a pure substring match — `"pip"` also fires on `"pipenv"`, `"gripping"`. `matchesPhrase` tokenizes the string and matches whole words only, giving fewer false positives. |
| `iAny(in(lower(arr[]), array("a", "b")))` | `matchesValue(arr, {"a", "b"}, caseSensitive: false)` | `matchesValue` is case-insensitive by default — no `lower()`, `in()`, or `iAny` wrapper needed. `caseSensitive: false` shown explicitly here only to mirror the intent of the `lower()` it replaces. |
| `iAny(f1[] == "a" AND f2[] == "b")` iterating two separate arrays | `in(f1, "a") AND in(f2, "b")` | Multi-array `iAny` is **pairwise**, not a cross-product: element `i` of `f1[]` is tested against element `i` of `f2[]`. If the arrays differ in length the result is `null`. Use independent `in()` checks instead. See [references/iterative-expressions.md](references/iterative-expressions.md). |
| `toLowercase(field)` | `lower(field)` | The function is `lower()`, not `toLowercase()`. Only type-casting functions use the `to` prefix (`toString()`, `toLong()`, etc.). |
| `arrayAvg(field[])` or `arraySum(field[])` | `arrayAvg(field)` or `field[]` | `field[]` = element-wise iterative expression (array→array); `arrayAvg(field)` = collapse to scalar (array→single value). Never mix both — `arrayAvg(field[])` is semantically wrong. |
| `my_field` after `lookup` or `join` | `lookup.my_field` / `right.my_field` | `lookup` prefixes added fields with `lookup.` by default (configurable via `prefix:`). `join` prefixes right-side fields with `right.`. |
| `substring(field, 0, 200)` | `substring(field, from: 0, to: 200)` | The first parameter (expression) is positional, but `from:` and `to:` are named optional parameters and must include their names. |
| `filter host = "A"` | `filter host == "A"` | DQL uses `==` for equality comparison, not `=`. Single `=` is assignment (e.g., in `fieldsAdd`, summarize aliases). |
| `fetch logs, from: toTimestamp('2026-01-01')` | `fetch logs, from: -24h` | `from:` / `to:` accept duration literals (e.g., `-24h`, `-7d`) or `now()` expressions — not `toTimestamp()`. For absolute ranges use `timeframe: "start/end"` (ISO 8601). |
| `filter log.level == "ERROR"` | `filter loglevel == "ERROR"` | Log severity field is `loglevel` (no dot) — `log.level` does not exist. |
| `sort count() desc` | `` sort `count()` desc `` | Fields with special characters (like parentheses) must be wrapped in backticks. |
| `length(field)` | `stringLength(field)` | DQL string length function is `stringLength` — there is no `length()`. |
| `metrics dt.host.cpu.usage` | `timeseries avg(dt.host.cpu.usage)` | `metrics` loads metric metadata, not values — use `timeseries` for data. |
| `join [...], on:{left.a.b == right.a.b}` | `` join [...], on:{left[`a.b`] == right[`a.b`]} `` | Dotted field names in join/lookup conditions require bracket notation with backticks. |
| `fieldsSummary` (no arguments) | `fieldsSummary field1, field2` | `fieldsSummary` requires at least one field parameter. |
| `timeseries` with `percentile`/`median`/`percentRank` — no results | Add `rollup: avg` (or `min`/`max`/`sum`) to the `timeseries` command | These three functions **require `rollup:`** on gauge/count metrics — without it the query silently returns empty. |
| `summarize p95 = percentile(duration, 95, rollup: avg)` | `summarize p95 = percentile(duration, 95)` | `rollup:` is a **`timeseries`-only** parameter. The same-named aggregations in `summarize` over logs/spans/events reject it with `UNKNOWN_PARAMETER_DEFINED`. Only add `rollup:` when aggregating a *metric* inside `timeseries`. |
| `filter array.contains(field, "v")` or `arrayContains(field, "v")` | `filter in(field, {"v"})` | Neither function exists in DQL — both are hallucinated from Python/Java/SQL. `in()` already matches **array-typed** fields natively (e.g. `k8s.namespace.name` on `dt.davis.problems`): it returns true if any element of the needle matches any haystack element. See [references/iterative-expressions.md](references/iterative-expressions.md). |
| `filter k8s.namespace.name == "ns"` where the field is array-typed | `filter in(k8s.namespace.name, {"ns"})` | `==` against an array-typed field matches **nothing** — it returns zero rows with no error, which reads as "no data" rather than a mistake. `k8s.*` fields are arrays on `dt.davis.problems`. Use `in()` for exact membership, or `matchesValue(field, {...})`. |
| `parseJson(field)` or `extractJsonField(field, jsonPath: "$.x")` | `parse field, "JSON:parsed"` then `parsed[x]` | Neither function exists. JSON embedded in a string field is unpacked with the `parse` command and the `JSON` DPL matcher, then accessed with bracket notation. |
| `filter hour(timestamp) == 4` / `minute(timestamp)` | `filter getHour(timestamp) == 4` / `getMinute(timestamp)` | There are no `hour()`/`minute()` functions. The `get*` family returns **numbers**, so numeric comparison and ranges work. Do not substitute `formatTimestamp(timestamp, format: "HH")` — that returns a *string*, so `== 4` silently matches nothing. |
| `fields fromRelationships, toRelationships, containerImageTag` on `dt.entity.*` | `describe dt.entity.<type>` first, then select real fields | Classic entity objects do **not** expose the Entities REST API's attribute names. Field names must be discovered with `describe <dataObject>`, not guessed from API payloads. |
| `by: {bin(timestamp, 1h)}` then `` sort `bin(timestamp,1h)` `` | `by: {t = bin(timestamp, 1h)}` then `sort t` | DQL normalizes the auto-generated group-key name to `bin(timestamp, 1h)` — with a space after the comma, regardless of how the expression was written. A backticked reference that omits the space raises `FIELD_DOES_NOT_EXIST`. Always alias group keys. |
| `fetch spans \| ... by: {bin(timestamp, 1h)}` | `fetch spans \| ... by: {t = bin(start_time, 1h)}` | `spans` has no `timestamp` field — its time fields are `start_time` and `end_time`. Referencing `timestamp` either errors or yields nulls depending on position. |
| `` lookup [...], fields: {`dotted.name`} `` | `lookup [...], fields: {dotted.name}` | Do not backtick field names inside the `fields:` parameter of `lookup` — causes PARSE_ERROR. |
| `data record(key: "val")` | `data record(key = "val")` | `record()` uses `=` for named fields, not `:` — `:` is for command parameters like `rollup:`. |
| `getNodeField(dt.smartscape.host, "tags")["tag.key"]` | `getNodeField(dt.smartscape.host, "tags")[tag.key]` | In this tag-map access pattern, bracket keys must use unquoted identifier syntax; quoted keys cause a parse error. |
| `by: {dt.entity.host}` or `dt.entity.*` | `by: {dt.smartscape.host}` or `dt.smartscape.*` | `dt.entity.*` is **deprecated** — always use `dt.smartscape.*` in new queries. |

______________________________________________________________________

## Fetch Command → Data Model

DQL queries start with `fetch <data_object>` or `timeseries`. There is **no `fetch dt.metric`** — metrics use `timeseries`.

| Fetch Command | Data Model | Key Fields / Notes |
|---------------|------------|--------------------|
| `fetch spans` | Distributed tracing | `span.*`, `service.*`, `http.*`, `db.*`, `code.*`, `exception.*` |
| `fetch logs` | Log events | `log.*`, `k8s.*`, `host.*` — message body is `content`, severity is `loglevel` (NOT `log.level`) |
| `fetch events` | DAVIS / infra events | `event.*`, `dt.smartscape.*` |
| `fetch bizevents` | Business events | `event.*`, custom fields |
| `fetch security.events` | Security events | `vulnerability.*`, `event.*` |
| `fetch user.sessions` | RUM sessions | `dt.rum.*`, `browser.*`, `geo.*` |
| `fetch user.events` | RUM individual events | page views, clicks, requests, errors |
| `fetch user.replays` | Session replay recordings | |
| `fetch application.snapshots` | Application snapshots | |
| `fetch dt.davis.events` | Davis-detected events | |
| `fetch dt.davis.problems` | Davis-detected problems | |
| `timeseries avg(metric.key)` | Metrics | NOT `fetch` — hyphenated keys need backticks: `` timeseries sum(`my.metric-name`) `` |
| `smartscapeNodes "HOST"` | Topology | NOT `fetch` — types: `HOST`, `SERVICE`, `K8S_CLUSTER`, etc. |

`dt.entity.*` is deprecated — use `dt.smartscape.*` and `smartscapeNodes` for new queries.

Discover all available data objects: `fetch dt.system.data_objects | fields name, display_name, type`

→ [references/semantic-dictionary.md](references/semantic-dictionary.md) for full field namespaces

______________________________________________________________________

## `samplingRatio` Parameter

`fetch` supports a `samplingRatio:` parameter to reduce the volume of data read — useful for improving query performance on large datasets.

```dql
fetch spans, samplingRatio:100   // reads ~1% of data
```

**Allowed values:** depend on the concrete data object and range from `1`, `10`, `100`, `1000`, `10000` to `100000`, the highest level only available for `logs` and `spans`.


Sampling is **hierarchical** for `spans`, `user.events` and `user.sessions`: a record included at a higher ratio (e.g. `100`) is guaranteed to also appear at lower ratios (e.g. `10`, `1`), but not vice versa. This means results at different ratios are  subsets of each other. All other non-metric data objects are sampled independently per record, so results at different ratios are not subsets.

The actual ratio applied is accessible via the `dt.system.sampling_ratio` field. Use it to extrapolate sampled counts back to true totals:

```dql
fetch logs, samplingRatio:10
| summarize count_extrapolated = sum(dt.system.sampling_ratio)
```

______________________________________________________________________

## Metric Discovery

To search for available metrics by keyword, use the command `metrics`:

```dql
metrics from: now() - 1h
| filter contains(metric.key, "replay")
| summarize count(), by: {metric.key}
| sort `count()` desc
```

There is **no `fetch dt.metric`** or `fetch dt.metrics` or `fetch dt.system.metrics` — those data objects do not exist.

______________________________________________________________________

## Timeseries Aggregation Functions

The `timeseries` command supports only these aggregation functions:

| Function | Description |
|----------|-------------|
| `sum` | Sum of metric data points per time slot |
| `avg` | Average of metric data points per time slot |
| `min` | Minimum of metric data points per time slot |
| `max` | Maximum of metric data points per time slot |
| `count` | Count of metric data points per time slot |
| `percentile(metric, N)` | Nth percentile per time slot. **Requires `rollup:`** — see below. |
| `median(metric)` | 50th percentile per time slot (= `percentile(metric, 50)`). **Requires `rollup:`**. |
| `percentRank(metric, value)` | Percentile rank of a value per time slot. **Requires `rollup:`**. |
| `countDistinct(metric)` | Approximate distinct count per time slot (cardinality metrics only; does NOT accept `rollup:`). |

Helpers (use alongside an aggregation): `start()`, `end()`.

**Not supported by `timeseries`:** `countIf`, `collectArray`, `stddev`, `variance`, `takeAny`, `takeFirst`, `takeLast` — use `summarize` or `makeTimeseries`.

### The `rollup:` parameter

Metrics are pre-aggregated at ingest time. `rollup:` controls how raw data points are combined per time slot. Required for `percentile`, `median`, `percentRank` — without it the query silently returns no results. `avg`/`min`/`max`/`sum`/`count` work without `rollup:`.

`rollup:` is a **`timeseries`-only** parameter — it belongs to metric aggregations and nothing else. The identically-named aggregation functions available in `summarize` over event data (logs, spans, events) do **not** accept it: `summarize p95 = percentile(duration, 95, rollup: avg)` fails with `UNKNOWN_PARAMETER_DEFINED`. In `summarize`, use `percentile(field, N)` with no `rollup:`.

Single aggregation — `rollup:` at command level. Multiple aggregations in `{}` — `rollup:` must go **inside each function call** (command-level `rollup:` causes `UNKNOWN_PARAMETER_DEFINED`):

```dql
timeseries p90 = percentile(dt.process.handles.file_descriptors_percent_used, 90), rollup: avg
```

```dql
timeseries {
  p90 = percentile(dt.process.handles.file_descriptors_percent_used, 90, rollup: avg),
  med = median(dt.process.handles.file_descriptors_percent_used, rollup: avg),
  avg_val = avg(dt.process.handles.file_descriptors_percent_used)
}, by: {dt.smartscape.host}
```

Values: `avg` (gauges), `min`, `max`, `sum` (counters), `total`.

### Timeseries-to-scalar conversion

There are two ways to collapse a timeseries to a scalar. Prefer the `scalar:true` parameter when you only need the single aggregated value — it is more efficient because no array is materialized. Fall back to array functions when you need both the full series and a derived scalar in the same query.

**Preferred: `scalar:true` on the aggregation function**

Pass `scalar:true` to any timeseries aggregation function. The result field contains a single value instead of an array, and no intermediate array is allocated:

```dql
timeseries avg_cpu = avg(dt.host.cpu.usage, scalar:true), by:{dt.smartscape.host}
```

```dql
timeseries {
  avg_cpu = avg(dt.host.cpu.usage, scalar:true),
  max_cpu = max(dt.host.cpu.usage, scalar:true)
}, by:{dt.smartscape.host}
```

**Fallback: array functions in `fieldsAdd`**

When you need the full time series array alongside a derived scalar, use array functions in a subsequent `| fieldsAdd`:

| Function | Description |
|----------|-------------|
| `arrayAvg(arr)` | Average of all values in the array |
| `arraySum(arr)` | Sum of all values |
| `arrayMin(arr)` | Minimum value |
| `arrayMax(arr)` | Maximum value |
| `arrayMedian(arr)` | Median value |
| `arrayPercentile(arr, N)` | Nth percentile (0–100) |
| `arrayLast(arr)` | Last non-null value (latest data point) |
| `arrayFirst(arr)` | First non-null value (earliest data point) |

```dql
timeseries cpu = avg(dt.host.cpu.usage), by:{dt.smartscape.host}
| fieldsAdd avg_cpu = arrayAvg(cpu), max_cpu = arrayMax(cpu)
```

______________________________________________________________________

## Time Alignment (@-operator)

The `@` operator aligns timestamps to a boundary — agents often get this wrong.

| Expression   | Meaning                                                     |
| ------------ | ----------------------------------------------------------- |
| `now()@h`    | Current time, aligned to the hour boundary                  |
| `now()@d`    | Midnight today                                              |
| `now()@w1`   | Monday this week                                            |
| `now()-2h@h` | 2 hours ago, aligned to the hour (offset first, then align) |

**Rules:**

- Order: offset before alignment — `now()-2h@h`, not `now()@h-2h`
- No space between `@` and the unit — `now()@h` not `now() @h`
- `m` = minutes, `M` = months — do not confuse them

→ [references/dql/dql-functions-timeseries.md](references/dql/dql-functions-timeseries.md) for the full list of `timeseries` aggregations and `rollup:` rules
→ [references/dql/dql-functions-array.md](references/dql/dql-functions-array.md) for `arrayAvg` / `arrayMax` / `arrayPercentile` / … spec

______________________________________________________________________

## Entity & Smartscape Patterns

Entity fields are scoped per type — `entity.id` does not exist. Use `smartscapeNodes` for topology queries.

| Entity      | ID field in data             | `smartscapeNodes` type |
| ----------- | ---------------------------- | ---------------------- |
| Host        | `dt.smartscape.host`         | `"HOST"`               |
| Service     | `dt.smartscape.service`      | `"SERVICE"`            |
| Process     | `dt.smartscape.process`      | `"PROCESS"`            |
| K8s cluster | `dt.smartscape.k8s_cluster`  | `"K8S_CLUSTER"`        |

Use `toSmartscapeId()` for ID conversion from strings (required!).

→ [references/smartscape-topology-navigation.md](references/smartscape-topology-navigation.md)

______________________________________________________________________

## makeTimeseries Command

`makeTimeseries` builds a time-bucketed series from event data (logs, spans, bizevents). Unlike `timeseries` (which queries pre-ingested metrics), `makeTimeseries` aggregates data in a pipeline.

**Do not pipe `timeseries` directly into `makeTimeseries`** — it fails with `INVALID_IMPLICIT_TIME_DEFAULT`. To re-aggregate metric data, use `start()` + expand (see [references/summarization.md](references/summarization.md)).

```dql
fetch logs
| makeTimeseries
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    interval: 5m,
    by: {k8s.cluster.name}
| fieldsAdd error_rate = errors[] * 100.0 / total[]
```

Key parameters: `interval:`, `by:{}`, `from:`/`to:`, `bins:`, `time:` (timestamp field), `spread:` (for `count`/`countIf` only), `nonempty:`.

→ [references/summarization.md](references/summarization.md) for full `makeTimeseries` patterns and `summarize` bucketing
→ [references/iterative-expressions.md](references/iterative-expressions.md) for timeseries array manipulation

______________________________________________________________________

## String Matching Functions

DQL has four main functions for string and array pattern matching. See [references/string-matching.md](references/string-matching.md) for the full guide and quick-reference table.

- **`matchesValue(field, {"pattern*", "*other*"})`** — wildcard matching (`*` at start/end). Accepts an array field in the first param and an array literal `{}` in the second — no `iAny` or `[]` needed. Case-insensitive by default (`caseSensitive: true` to enforce case-sensitive matching). Replaces `contains()` + `iAny` chains and `lower()` workarounds.
- **`matchesPhrase(field, "token")`** — tokenizes the string and matches whole words, unlike `contains()` which is a bare substring match. First param accepts an array field natively; second param must be a **static string** (array unwrapping causes a runtime error).
- **`in(field, array("a", "b"))`** — set membership. Both params accept arrays, making it an overlap/intersection check.

______________________________________________________________________

## Chained Lookup Pattern


Each `lookup` command without a `fields` parameter **removes all existing fields starting with the prefix (default: `lookup.`)** before adding new ones. When chaining multiple lookups, use `fields` parameter or custom prefixes to preserve the result:

**Option 1 (default)**: the desired fields are known.
```dql
fetch bizevents
// Step 1: First lookup — enrich orders with product info
| lookup [fetch bizevents
    | filter event.type == "product_catalog"
    | fields product_id, category],
  sourceField: product_id, lookupField: product_id, fields: {product_id, product_category = category}

// Step 2: Second lookup — specify fields with a different name
| lookup [fetch bizevents
    | filter event.type == "warehouse_stock"
    | fields category, warehouse_region],
  sourceField: product_category, lookupField: category, fields: {warehouse_region, warehouse_category = category}

```
All 4 lookup fields product_id, product_category, warehouse_region, and warehouse_category are available.
Without the `fields:{...}` parameter, the fields would be prefixed with `lookup.` and the second lookup command would delete the fields added by the first lookup.

**Option 2**: keep all fields from the lookup.
```dql
fetch bizevents
// Step 1: First lookup — enrich orders with product info
| lookup [fetch bizevents
    | filter event.type == "product_catalog"
    | fields product_id, category],
  sourceField: product_id, lookupField: product_id, prefix: "product."

// Step 2: Second lookup — specify fields with a different prefix
| lookup [fetch bizevents
    | filter event.type == "warehouse_stock"
    | fields category, warehouse_region],
  sourceField: product_category, lookupField: category, prefix: "warehouse."

```
The new fields are: `product.product_id`, `product.category`, `warehouse.category`, `warehouse.warehouse_region`.
All fields starting with `product.` or `warehouse.` are removed from the original source.
Without the dedicated `prefix`, both `lookup` commands would use the same prefix (`lookup.`) and the second `lookup` drops the first lookup's results — producing empty fields.

______________________________________________________________________

## makeTimeseries Command

`makeTimeseries` builds a time-bucketed series from event data (logs, spans, bizevents). Unlike `timeseries` (which queries pre-ingested metrics), `makeTimeseries` aggregates data in a pipeline.

**Do not pipe `timeseries` directly into `makeTimeseries`** — it fails with `INVALID_IMPLICIT_TIME_DEFAULT`. To re-aggregate metric data, use `start()` + expand (see [references/summarization.md](references/summarization.md)).

```dql
fetch logs
| makeTimeseries
    {total = count(),
    errors = countIf(loglevel == "ERROR")},
    interval: 5m,
    by: {k8s.cluster.name}
| fieldsAdd error_rate = errors[] * 100.0 / total[]
```

Key parameters: `interval:`, `by:{}`, `from:`/`to:`, `bins:`, `time:` (timestamp field), `spread:` (for `count`/`countIf` only), `nonempty:`. → [references/dql/dql-commands.md](references/dql/dql-commands.md) for full spec.

Entity existence timeline using `spread:`:

```dql
smartscapeNodes "HOST"
| makeTimeseries concurrently_existing_hosts = count(), spread: lifetime
```

→ [references/iterative-expressions.md](references/iterative-expressions.md) for timeseries array manipulation

______________________________________________________________________

## Timeframe Specification

Access to data requires specification of a timeframe.
It can be specified in the UI, as REST API parameters, or in a DQL query explicitly using a pair of parameters: `from:` and `to:` (if one is omitted it defaults to `now()`), or alternatively using a single `timeframe:` parameter.
Timeframe can be expressed using absolute values or relative expressions vs. current time. The time alignment operator (`@`) can be used to round timestamps to time unit boundaries — see [references/operators.md](references/operators.md) for full details.

### Examples

```dql-snippet
from:now()-1h@h, to:now()@h     // last complete hour
```
```dql-snippet
from:now()-1d@d, to:now()@d     // yesterday complete
```
```dql-snippet
from:now()@M                    // this month so far, till now
```
```dql-snippet
from:now()-2h@h                 // go back 2 hours, then align to hour boundary
```

See [references/operators.md](references/operators.md) for the full `@` alignment-unit table (including `m` vs. `M`, week-day variants `w1`–`w7`, and factor rules like `@3h`).

### Absolute timestamps

Use ISO 8601 format:

```dql-snippet
from:"2024-01-15T08:00:00Z", to:"2024-01-15T09:00:00Z"
```

______________________________________________________________________

## Modifying Time

### Key concepts

- DQL has 3 specialized types related to time:
    - **timestamp** — internally kept as number of nanoseconds since epoch, but exposed as date/time in a particular timezone
    - **timeframe** — a pair of 2 timestamps (start and end)
    - **duration** — internally kept as number of nanoseconds, but exposed as duration scaled to a reasonable factor (e.g. ms, minutes, days)

### Rules

- Subtracting timestamps yields a duration: `timestamp - timestamp → duration`
- Duration divided by duration yields a double: e.g. `2h / 1m` = `120.0`
- Scalar times duration yields a duration: e.g. `no_of_h * 1h → duration`
- For extraction of time elements (hours, days of month, etc):
    - ✅ Use [time functions](references/dql/dql-functions-time.md). They support calendar and time zones properly including DST.
    - ❌ Avoid using `formatTimestamp` for extracting time components.
    - ❌ Avoid converting timestamps and durations to double/long and using division, modulo, and constants expressing time units as nanoseconds.

## References

- **[references/useful-expressions.md](references/useful-expressions.md)** — Useful expressions in DQL
- **[references/semantic-dictionary.md](references/semantic-dictionary.md)** — Dynatrace Semantic Dictionary: field namespaces, data models, stability levels, query patterns, and best practices
- **[references/summarization.md](references/summarization.md)** — Various applications of summarize and makeTimeseries commands
- **[references/iterative-expressions.md](references/iterative-expressions.md)** — Array and timeseries manipulation (creation, modifications, use in filters) using DQL
- **[references/smartscape-topology-navigation.md](references/smartscape-topology-navigation.md)** — Smartscape topology navigation syntax and patterns
- **[references/optimization.md](references/optimization.md)** — DQL query optimization: making queries faster, more efficient, and cheaper to run (lower consumption / scanned data per execution) — filter placement, bucket filters, time ranges, field selection, sampling, cardinality, and performance best practices
- **[references/operators.md](references/operators.md)** — `in` operator (subquery syntax) and full `@` time alignment unit reference