DQL Use cases

This page describes the most common use cases of Dynatrace Query Language.

Distribution of records based on predefined criteria

In this use case, let's assume that you need to check how many transactions were conducted by each payment service provider, and the share of each provider in the total number of transactions.

The content field for every record looks as below:

jsoncopydownload
{
   "country_code":"US",
   "session_id":"6a6c6b6d6a7c7b7f7a7c7b7a7f7",
   "invoicing_data":null,
   "bill_to":{
      "first_name":"John",
      "last_name":"Doe",
      "email":"john.doe@gmail.com",
      "phone":null
   },
   "payment_provider":"paypal"
}

The query parses the payment_provider field from the JSON file and groups the number of transactions by each provider using the summarize command, as well as calculates the total count of transactions. The share is calculated in a separate column, next to each provider's number of transactions.

dqlcopydownload
fetch logs
| parse content, "JSON:json"
| fields payment = json[payment_provider]
| summarize
bank_card=countIf(payment=="bank_card"), bank_cardPer=toDouble(countIf(payment=="bank_card"))/toDouble(count()),
apple_pay=countIf(payment=="apple_pay"),apple_payPerc=toDouble(countIf(payment=="apple_pay"))/toDouble(count()),
paypal=countIf(payment=="paypal"),paypalPerc=toDouble(countIf(payment=="paypal"))/toDouble(count()),
google_pay=countIf(payment=="google_pay"),google_payPerc=toDouble(countIf(payment=="google_pay"))/toDouble(count()),
unpaid_booking=countIf(payment=="unpaid_booking"),unpaid_bookingPerc=toDouble(countIf(payment=="unpaid_booking"))/toDouble(count()),
total=count()

Results:

Extract the first 'n' characters from the field.

In this example, you have a field called kiosk and need to extract the first three characters to identify the location abbreviation of the kiosk.

jsoncopydownload
{
      "kiosk": "LAOBAUA729"
}

dqlcopydownload
...
| parse kiosk, "DATA{3}:kioskLoc"
| fields kiosk, kioskLoc

Results:

Extract information from an XML element.

In this use case, an API gateway creates logs in XML format and you want to extract some information from it.

The XML field for every record looks as below:

xmlcopydownload
<log-entry serial='1467' domain='bca_icas_soa'>
   <date>Fri Sep 21 2023</date>
   <time utc='1380295304719'>11:21:44</time>
   <date-time>2012-09-21T11:21:44</date-time>
   <type>xmlfirewall</type>
   <class>xmlfirewall</class>
   <object>example-Firewall</object>
   <level num='3'>error</level>
   <transaction-type>error</transaction-type>
   <transaction>6187</transaction>
   <client>127.0.0.1</client>
   <code>0x01130007</code>
   <file></file>
   <message>Failed to establish back-end connection</message>
</log-entry>

In the DQL query, you need to parse the attributes of the root XML element and some of the values of the child XML elements:

dqlcopydownload
...
| PARSE xml,"
$tag = [^> ]+;
'<' $tag DATA*:xmlattributes '>'
SPACE*
KVP {
 (
  (
  '<'
  $tag:key
  SPACE*
  '/>'
  SPACE*
  )
|
  (
  '<'
  $tag:key
  DATA*
  '>'
  SPACE*
  DATA*:value
  SPACE*
  '</'
  $tag
  '>'
  SPACE*
  )
 )
}:xml
"
| PARSE xmlattributes,"KVP{SPACE* WORD:key '=' SPACE* STRING:value SPACE*}:xmlattributes"
| fields  domain=xmlattributes[domain], serial=xmlattributes[serial], object=xml[object],transaction=xml[transaction], code=xml[code]

Results: