| name | dt-dql-essentials |
| description | Core DQL syntax rules, common pitfalls, and query patterns. Load this skill when you need to write, build, or fix a DQL query — it prevents syntax errors and guides correct usage. Covers fetch commands, data models, field namespaces, time alignment, entity patterns, metric discovery, and smartscape topology navigation. Trigger: "write a DQL query", "build me a query", "DQL syntax", "how do I query logs/spans/metrics in Dynatrace", "create a timeseries", "fix my DQL", "fetch logs", "smartscapeNodes", "query optimization". Do NOT use for explaining an existing query or answering Dynatrace product questions — those do not require query-construction guidance. |
| 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 |
| Query optimization (filter early, time ranges, field selection, performance) | references/optimization.md |
| Smartscape topology navigation for discovering relationships between entities | references/smartscape-topology-navigation.md |
summarize and makeTimeseries patterns (bucketing, calendar months) | references/summarization.md |
Array and timeseries manipulation (arrayFilter, collectArray, iterative) | references/iterative-expressions.md |
Conditional logic (if/else chains), coalesce, string/date helpers | references/useful-expressions.md |
in operator (subquery), full @ time alignment unit table | references/operators.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 | array, binary, boolean, double, duration, long, record, string, timeframe, timestamp, uid |
| Parameter Value Types | 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 | 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 | 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 | 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 | bitwiseAnd, bitwiseCountOnes, bitwiseNot, bitwiseOr, bitwiseShiftLeft, bitwiseShiftRight, bitwiseXor |
| Functions — Boolean | exists, in, isFalseOrNull, isNotNull, isNull, isTrueOrNull, isUid128, isUid64, isUuid |
| Functions — Cast | asArray, asBinary, asBoolean, asDouble, asDuration, asIp, asLong, asNumber, asRecord, asSmartscapeId, asString, asTimeframe, asTimestamp, asUid |
| Functions — Constant | e, pi |
| Functions — Conversion | toArray, toBoolean, toDouble, toDuration, toIp, toLong, toSmartscapeId, toString, toTimeframe, toTimestamp, toUid, toVariant |
| Functions — Create | array, duration, ip, record, smartscapeId, timeframe, timestamp, timestampFromUnixMillis, timestampFromUnixNanos, timestampFromUnixSeconds, uid128, uid64, uuid |
| Functions — Cryptographic | hashCrc32, hashMd5, hashSha1, hashSha256, hashSha512, hashXxHash32, hashXxHash64 |
| Functions — Entities | classicEntitySelector, entityAttr, entityName |
| Functions — Time series aggregation for expressions | avg, count, countDistinct, countDistinctApprox, countDistinctExact, countIf, end, max, median, min, percentRank, percentile, percentileFromSamples, start, sum |
| Functions — Flow | coalesce, if |
| Functions — General | jsonField, jsonPath, lookup, parse, parseAll, type |
| Functions — Get | arrayElement, getEnd, getHighBits, getLowBits, getStart |
| Functions — Iterative | iAny, iCollectArray, iIndex |
| Functions — Mathematical | 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 | ipIn, ipIsLinkLocal, ipIsLoopback, ipIsPrivate, ipIsPublic, ipMask, isIp, isIpV4, isIpV6 |
| Functions — Smartscape | getNodeField, getNodeName |
| Functions — String | 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 | formatTimestamp, getDayOfMonth, getDayOfWeek, getDayOfYear, getHour, getMinute, getMonth, getSecond, getWeekOfYear, getYear, now, unixMillisFromTimestamp, unixNanosFromTimestamp, unixSecondsFromTimestamp |
| Functions — Time series aggregation for metrics | 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). |
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. |
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 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.
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:
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:
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:.
Single aggregation — rollup: at command level. Multiple aggregations in {} — rollup: must go inside each function call (command-level rollup: causes UNKNOWN_PARAMETER_DEFINED):
timeseries p90 = percentile(dt.process.handles.file_descriptors_percent_used, 90), rollup: avg
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:
timeseries avg_cpu = avg(dt.host.cpu.usage, scalar:true), by:{dt.smartscape.host}
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) |
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 for the full list of timeseries aggregations and rollup: rules
→ 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
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).
fetch logs
| makeTimeseries
total = count(),
errors = countIf(loglevel == "ERROR"),
interval: 5m,
by: {k8s.cluster.name}
| fieldsAdd error_rate = errors / total * 100
Key parameters: interval:, by:{}, from:/to:, bins:, time: (timestamp field), spread: (for count/countIf only), nonempty:.
→ references/summarization.md for full makeTimeseries patterns and summarize bucketing
→ references/iterative-expressions.md for timeseries array manipulation
matchesValue() Usage
Use matchesValue() for array fields such as dt.tags:
| filter matchesValue(dt.tags, "env:production")
- Not for string fields with special characters — use
contains() for those
matchesValue() on a scalar string field does not behave like a wildcard or fuzzy match
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.
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.
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).
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 for full spec.
Entity existence timeline using spread::
smartscapeNodes "HOST"
| makeTimeseries concurrently_existing_hosts = count(), spread: lifetime
→ 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 for full details.
Examples
from:now()-1h@h, to:now()@h // last complete hour
from:now()-1d@d, to:now()@d // yesterday complete
from:now()@M // this month so far, till now
from:now()-2h@h // go back 2 hours, then align to hour boundary
See 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:
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. 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
