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:
1fetch 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.
1| summarize count()
Optionally, an assignment by using the equals sign (=) overrides the default field name from count() to event_count.
1| 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.
1| summarize event_count = count(), by:{country=client.loc_cc, customer}
Field naming rules
DQL Syntax verification applies the following naming rules:
- 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.hostlocation_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.
-
Line 1
1fetch logs, from:now()-1hYou retrieve the log data using the
fetchcommand. In addition, the optionalfrom:parameter specifies the query start timestamp. -
Line 2
1// fetched all logs from the last hour: now() – 1h to now()Commented out line. This line will be omitted in query execution.
-
Line 3
1| filter endsWith(log.source, "pgi.log")The
filtercommand filters the log records based on theendsWithfunction that retrieves log files whose names end with the predefined string (thepgi.logstring). -
Line 4
1| parse content, "LD IPADDR:ip ':' LONG:payload SPACE LD 'HTTP_STATUS' SPACE INT:http_status LD (EOL| EOS)"We use the
parsecommand to extract key-value pairs containing execution statistics out of the raw log text string. In this case, it adds theIP address,payloadandhttp_statusfields to the result and transforms their data types into required formats. -
Line 5, 6, 7, 8
1| summarize total_payload = sum(payload),2 failedRequests = countIf(http_status >= 400),3 successfulRequests = countIf(http_status <= 400),4 by:{ip, host.name}The
summarizecommand is a key element of DQL as it allows multiple aggregations across one or more fields. This query groups the results byipandhost.name. The retrieved records include the total value of payload, calculated using thesumfunction, and two columns calculated using thecountiffunction:- 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 byipandhost.name.
- a column with numbers of failed requests (defined as those having
-
Line 9
1|fieldsAdd total_payload_MB = total_payload/1000000With the
fieldsAddcommand, you add a new field showing the total payload converted into megabytes, basing on a mathematical expression. -
Line 10
1|fields ip, host.name, failedRequests, successfulRequests, total_payload_MBWith the
fieldscommand, you can determine which fields you need to retrieve. -
Line 11
1| sort failedRequests descThe
sortcommand 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
- Commands
- Functions
Functions can be used to perform any desired computation on fields of DQL commands.
- 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).