11.2 Dynatrace Query Language (DQL): Fetch, Filter, Fields & String Operations

To unlock the power of the Grail data lakehouse, Dynatrace introduced the Dynatrace Query Language (DQL). Unlike traditional SQL, which relies on declarative statements and rigid tabular schemas, or Lucene-based search syntax, which depends on inverted keyword indices, DQL is a functional, pipelined query language engineered specifically for high-performance, schema-on-read analytics.

In DQL, queries are expressed as a continuous sequence of data-processing operations connected by the pipe operator (|). Data flows from left to right (or top to bottom), where each stage receives an incoming tabular stream from the preceding stage, applies a transformation, filter, extraction, or aggregation, and streams the resulting records to the next operation. Mastering DQL foundational syntax—including data fetching, record filtering, field projection, and string parsing—is a primary objective for the Dynatrace Certified Associate examination.

---

1. The Pipelined Query Execution Model

The fundamental design pattern of a DQL query follows an ordered pipeline:

fetch <table-name> [, from: <time>, to: <time>]
| filter <boolean-condition>
| parse <field>, <dpl-pattern>
| fieldsAdd <new-field> = <expression>
| fields <field1>, <field2>, <field3>
| sort <field> [asc | desc]
| limit <record-count>

Pipeline Determinism and Performance Mechanics

Every command in DQL transforms an intermediate tabular dataset. Because DQL executes deterministically stage-by-stage, the order of operations profoundly affects query performance, execution time, and resource consumption:

1. Early Filtering Reduces Compute Overhead: Placing a filter command immediately after fetch allows Grail's Massively Parallel Processing (MPP) engine to leverage storage partition pruning and drop millions of irrelevant rows before expensive string parsing or regex evaluation begins.
2. Late Parsing Saves CPU: Parsing unstructured strings with Dynatrace Pattern Language (DPL) is computationally intensive. If a query parses 10 million raw lines before filtering down to 100 errors, the query wastes massive compute capacity. Conversely, filtering for errors first and then parsing only the matching 100 lines completes in milliseconds.
3. Projection Pruning: Using fields or fieldsRemove drops large payload fields (like the raw content string) once variables have been extracted, minimizing memory serialization overhead between worker nodes.

---

2. Initializing Telemetry Retrieval: The fetch Command

Every DQL query begins with a fetch command, which specifies the target Grail data table and the temporal boundary of the query.

Core Grail Tables

• fetch logs: Retrieves log records collected by OneAgent, ActiveGates, or Log Ingestion APIs.
• fetch events: Retrieves lifecycle events, deployment markers, configuration changes, and Davis problem events.
• fetch spans: Retrieves distributed tracing spans and OpenTelemetry traces.
• fetch dt.entity.<type>: Retrieves topological entities from Smartscape, such as dt.entity.host, dt.entity.process_group_instance, or dt.entity.service.

Timeframe Scoping (from: and to:)

Grail organizes physical data storage chronologically. Specifying temporal boundaries in the fetch command enables Grail to scan only the micro-partitions that fall within the specified window, dramatically accelerating execution:

// Relative timeframe (e.g., last 2 hours)
fetch logs, from: now() - 2h, to: now()

// Explicit ISO-8601 absolute timeframe
fetch logs, from: "2026-09-10T08:00:00Z", to: "2026-09-10T12:00:00Z"

If temporal parameters are omitted in the DQL code itself, Grail applies the default timeframe currently selected in the Dynatrace UI (e.g., last 2 hours, last 24 hours).

---

3. Record Filtering & Boolean Logic: The filter Command

The filter command evaluates boolean expressions against incoming records. Only records that evaluate to true are passed down the pipeline.

Comparison and Logical Operators

DQL supports standard comparison operators: == (equality), != (inequality), <, <=, >, and >=. Multiple conditions are combined using logical operators and, or, and not:

fetch logs
| filter status == "ERROR" and (loglevel == "CRITICAL" or loglevel == "FATAL")
| filter not contains(content, "healthcheck")

Pattern and String Matching Operators

Filtering unstructured log strings often requires partial text matching. DQL provides specialized operators optimized for different search patterns:

• like: Evaluates wildcard expressions where * matches zero or more characters and ? matches exactly one character.

  | filter content like "*Connection reset by peer*"
  

• matchesPhrase(): Performs high-speed, case-insensitive phrase matching across text fields. This is significantly faster than regular expressions for searching specific error messages or transaction tokens.

  | filter matchesPhrase(content, "OutOfMemoryError")
  

• matchesRegex(): Evaluates standard regular expression patterns against target fields.

  | filter matchesRegex(content, "(?i)user_id=[A-Z0-9]{8}")
  

Set Membership and Null Checking

• in(): Evaluates whether a field matches any value within a set:

  | filter status in ("ERROR", "WARN", "FATAL")
  

• isNull() & isNotNull(): Validates whether an attribute exists on the record:

  | filter isNotNull(dt.entity.host) and isNull(aws.lambda.arn)
  

---

4. Schema-on-Read Extraction: Dynatrace Pattern Language (DPL)

In Grail, the raw log text is stored inside the content field. Because Grail does not enforce write-time indexing, extracting structured attributes (such as IP addresses, status codes, user IDs, or elapsed execution times) requires schema-on-read parsing via the parse command and Dynatrace Pattern Language (DPL).

DPL Syntax and Pattern Tokens

DPL expressions define a pattern of literal text and typed pattern tokens. Each token matches a specific syntactic data type and binds the extracted substring into a new typed variable: TOKEN:variable_name.

Practical DPL Parsing Example

Consider an NGINX access log line:

10.200.4.15 - [2026-09-10 14:22:01] "GET /api/v1/checkout HTTP/1.1" 500 142ms

The DQL pipeline extracts the client IP, timestamp, method, path, status, and duration as typed fields:

fetch logs
| filter status == "ERROR"
| parse content, "IPADDR:client_ip ' - [' TIMESTAMP('yyyy-MM-dd HH:mm:ss'):req_time '] "' WORD:method ' ' LD:path ' HTTP/1.1" ' INT:status_code ' ' INT:duration 'ms'"
| filter status_code >= 500
| fields client_ip, req_time, method, path, status_code, duration

Parsing Structured JSON Payloads

Many modern applications output logs formatted as JSON structures. DQL handles this natively using the JSON token or the json() parsing function:

fetch logs
| parse content, "JSON:data"
| fieldsAdd customer_id = data[user][account_id],
            payment_status = data[transaction][status]
| filter payment_status == "FAILED"

Once parsed into a JSON record object, nested properties are directly accessible using bracket notation (data[parent][child]).

---

5. Field Projection, Renaming & String Manipulation

Once fields are parsed, DQL provides powerful commands to shape the final tabular output.

Field Management Commands

• fields: Acts as a projection operator (similar to SELECT in SQL). Retains only the explicitly declared columns and drops all other fields.

  | fields timestamp, status, client_ip, path, status_code
  

• fieldsAdd: Computes or appends new columns to the record set without discarding existing fields.

  | fieldsAdd response_seconds = duration / 1000,
              environment_tag = "Production"
  

• fieldsRemove: Explicitly drops designated columns to free memory or remove noisy attributes.

  | fieldsRemove content, raw_headers
  

• fieldsRename: Renames existing attributes to cleaner, user-friendly column names.

  | fieldsRename client_address = client_ip, endpoint = path
  

String Manipulation Functions

DQL includes an extensive suite of string functions that can be used inside fieldsAdd or filter expressions:

• concat(str1, str2, ...): Concatenates multiple strings or variables.
• substring(str, start [, length]): Extracts a substring based on 0-indexed character offsets.
• toLower(str) & toUpper(str): Converts character casing for normalization.
• trim(str): Strips leading and trailing whitespace characters.
• replaceString(source, search, replacement): Replaces all occurrences of a target substring.
• indexOf(source, target): Returns the integer position of the first occurrence of a target string.

---

6. Result Ordering & Truncation: sort & limit

The final stages of an exploratory DQL pipeline typically order and constrain the returned record set.

The sort Command

The sort command orders incoming records by one or more attributes in ascending (asc, default) or descending (desc) order:

fetch logs
| filter status == "ERROR"
| sort timestamp desc, duration desc

The limit Command

The limit command sets the maximum number of records returned to the client (e.g., limit 100).

Exam Best Practice: In analytical troubleshooting, always place limit after sort when seeking outliers (e.g., top 10 slowest requests). If limit 10 is applied before sort, Grail arbitrarily truncates the stream to the first 10 scanned records and then sorts only those 10, failing to identify the global top 10.