DQL language reference

A DQL query contains at least one or more commands, each of which returns tabular output containing records (lines or rows) and fields (columns). All commands are sequenced by a | (pipe). Data flows or is piped from one command to the next. The data is filtered or manipulated at each step and then streamed into the following step.

DQL Syntax

The syntax can be described as follows:

command parameter,.. [, optionalparameter],... | command …

A syntactically valid example of a DQL query is:

fetch bizevents | summarize count()

A DQL command consists of mandatory and optional parameters which are comma-separated:

| summarize [[field = ] aggregation [, ...]] [,by:{ [field=] groupexpression[, ...]}]

  • Mandatory parameters
    • aggregation
  • Optional parameters
    • field
    • groupexpression

The required parameter is aggregation. For this command to be syntactically valid, at least one call to an aggregation function has to be specified.

| summarize count()

Optionally, an assignment by using the equals sign (=) overrides the default field name from count() to event_count.

| summarize event_count = count()

The optional by: parameter defines a list of groupexpression. The output will have as many records as there are distinct values of all the groupexpression.

| summarize event_count = count(), by:{country=client.loc_cc, customer}

Field naming rules

DQL Syntax verification applies the following naming rules:

DQL language reference

  • Field names referenced in a DQL statement must match the a-zA-Z0-9_. character set.
  • Field names can not start with a number.
  • Fields containing a character outside of a-zA-Z0-9_. must be escaped using the ` (Backtick) symbol.

Otherwise, the DQL Syntax verification will fail.

Examples of valid field names are:

  • dt.entity.host
  • location_US_EAST_1
  • `my host*` by using Backticks.

Sequential data processing

The following DQL query uses seven pipeline steps to get from raw log data to an aggregated table showing performance statistics for task execution.

an example DQL query with explanations

  • Line 1

    fetch       logs, from:now()-1h

    You retrieve the log data using the fetch command. In addition, the optional from: parameter specifies the query start timestamp.

  • Line 2

    // fetched all logs from the last hour: now() – 1h to now()

    Commented out line. This line will be omitted in query execution.

  • Line 3

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

    The filter command filters the log records based on the endsWith function that retrieves log files whose names end with the predefined string (the pgi.log string).

  • Line 4

    | parse     content, "LD IPADDR:ip ':' LONG:payload SPACE LD 'HTTP_STATUS' SPACE INT:http_status  LD (EOL| EOS)"

    We use the parse command to extract key-value pairs containing execution statistics out of the raw log text string. In this case, it adds the IP address, payload and http_status fields to the result and transforms their data types into required formats.

  • Line 5, 6, 7, 8

    | summarize total_payload = sum(payload),
                failedRequests = countIf(http_status >= 400),
                successfulRequests = countIf(http_status <= 400),
                by:{ip, host.name}

    The summarize command is a key element of DQL as it allows multiple aggregations across one or more fields. This query groups the results by ip and host.name. The retrieved records include the total value of payload, calculated using the sum function, and two columns calculated using the countif function:

    • a column with numbers of failed requests (defined as those having http_status >=400)
    • a column with numbers of successful requests (defined as those having http_status <400)
      This query groups the retrieved records by ip and host.name.

  • Line 9

    |fieldsAdd total_payload_MB = total_payload/1000000

    With the fieldsAdd command, you add a new field showing the total payload converted into megabytes, basing on a mathematical expression.

  • Line 10

    |fields    ip, host.name, failedRequests, successfulRequests, total_payload_MB

    With the fields command, you can determine which fields you need to retrieve.

  • Line 11

    | sort  failedRequests desc

    The sort command is used to finalize the result. In this case, the results are sorted according to the number of failed requests, from the highest to lowest.

DQL key building blocks

  • Data types
    The Dynatrace Query Language operates with strongly typed data: functions and operators accept only declared types of data. The type is assigned to data during parsing or by using casting functions. DQL also recognizes value types expressed in literal notation (for example, using constant values in functions).

Related topics