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:

{
   "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.

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:

bank_card	bank_cardPer	apple_pay	apple_payPerc	paypal	paypalPerc	google_pay	google_payPerc	unpaid_booking	unpaid_bookingPerc	total
345	0.19425675675675674	353	0.19876126126126126	360	0.20270270270270271	364	0.20495495495495494	354	0.19932432432432431	1776

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.

{
      "kiosk": "LAOBAUA729"
}

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

Results:

kiosk	kioskLoc
LAOBAUA729	LAO

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:

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

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

domain	serial	object	transaction	code
bca_icas_soa	1467	example-Firewall	6187	0x01130007

DQL Use cases

Distribution of records based on predefined criteria

Extract the first 'n' characters from the field.

Extract information from an XML element.

Related topics