Pro-Tip: DQL Query Snippets Collection

[r_weber]

I started to collect various useful DQL query snippets to make it easier for users to get started with DQL. Internally I did this on a notebook, but I also wanted to share this with the community and lete everybody contribute their snippets as well. So why not on an ongoing thread here.

One reply for each snippet and use case, with a little description and a screenshot.

[r_weber]

Honeycomb representation of specific service's health status

This code snippet creates a honeycomb visualization (like we had on old dashboards) of the service health status. It uses a) the service entity information and the existing davis problem events and combines them. You can filter services by tags (e.g. a dt.owner tag).

To get red/green status indicators, set the color mapping in the honeycomb visualization:

DQL Query:

fetch dt.entity.service, from: -30m
| filter arrayIndexOf(entityAttr(type:"dt.entity.service",id, "tags"),"dt.owner:purchase") > -1 
| join [ fetch dt.davis.events
         | filter arrayIndexOf(affected_entity_types,"dt.entity.service") > -1
         | filter event.status != "CLOSED"
         | expand affected_entity_ids
         | fieldsKeep event.status, affected_entity_ids
         | summarize affected_entity_id=takeFirst(affected_entity_ids), by: {event.status, affected_entity_ids}
       ],
       kind:leftOuter,
       on: left[id] == right[affected_entity_id]
| fieldsAdd health=if(isNull(right.event.status),"OK",else: "NOT OK")
| fieldsRename service=entity.name
| fieldsKeep service,id,health
| sort service asc

[samgar-kali]

Running operations in DQL with several metrics

timeseries {
  {used=avg(dt.host.memory.used)},
  {available=avg(dt.host.memory.avail.bytes)}
}, by:{dt.entity.host}
| fieldsAdd total = (used[] + available[])
| fieldsAdd used_percentage = (used[] / total[] * 100)
| fieldsRemove used, available, total

[marco_irmer]

Split timeseries data into one record per interval (useful for data export)

Metric data sometimes needs to be displayed as one row per time interval for use in tables or for export to other systems. Dynatrace's native format for timeseries events can complicate this process because the data values for the entire timeframe of a given timeseries are represented as an array within a single record.

We can overcome this challenge by using an iterative expression and applying some transformations to generate a separate record for each discrete interval. This data can then be shown in table format, or exported to CSV, where each row contains a single interval and its data value(s).

DQL Query:

timeseries value = sum(dt.service.request.count), interval: 1h

// *** timeseries interval splitter start ***
| fields  interval=record(
          timeframe.start = timeframe[start] + interval * iIndex(),
          value[]
          /* add any dimension split (by:) field names here */
          )
| expand interval
| fieldsFlatten interval
| fieldsRemove interval
// *** timeseries interval splitter end ***

Query Output: