version: "1.0.3" 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:

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.

Syntax Pitfalls

Fetch Command → Data Model

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

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:

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

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:

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.

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.

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[] * 100.0 / total[]

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

String Matching Functions

DQL has four main functions for string and array pattern matching. See 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.

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

• [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