11.3 DQL Aggregations: Summarize, MakeTimeseries, Sorting & Visualizations
Key Takeaways
- The summarize command aggregates discrete records across one or more dimensions into tabular summary statistics, serving as the core engine for operational metrics and categorical group-by analytics.
- The makeTimeseries command aggregates discrete events into continuous, equidistant time-series buckets, outputting native time-series data structures required for line charts, area charts, and alerting.
- High-performance statistical aggregation functions in DQL include count(), countIf(), sum(), avg(), min(), max(), median(), percentile(), collectDistinct(), and collectArray().
- The default: parameter in makeTimeseries prevents visualization gaps and mathematical errors across sparse data intervals by defining explicit fallback values such as default: 0.
- DQL query outputs map directly to Dynatrace Dashboards and Notebooks visualizations, translating tabular summarize outputs into tables, bar charts, and stat tiles, and makeTimeseries outputs into line and area charts.
While filtering and parsing individual log records is essential for ad-hoc debugging, enterprise observability demands high-level statistical aggregation and continuous trend analysis. Site reliability engineers and operations teams must answer questions such as: Which microservices are producing the highest volume of errors? What is the 95th percentile request latency across API gateways? How has the error rate evolved hour-over-hour following the latest Kubernetes canary deployment?
Dynatrace Query Language (DQL) provides two fundamental aggregation commands to synthesize millions of granular log events into actionable analytical insights:
summarize: Aggregates records into a collapsed tabular summary across grouping dimensions (analogous to SQLGROUP BY).makeTimeseries: Aggregates records into a continuous time-series dataset segmented into fixed time buckets (such as 1 minute or 5 minutes), generating the native temporal data structure required for line charts, stacked area charts, and dynamic alerting.
Understanding the mathematical functions, dimensional grouping mechanics, sparse data handling, and visual tile mappings of these commands is essential for the Dynatrace Certified Associate examination.
1. Tabular Grouping & Statistical Aggregations: The summarize Command
The summarize command collapses multiple incoming records into single summary rows based on one or more grouping dimensions declared in the by: { ... } clause.
General Syntax
fetch logs
| filter status in ("ERROR", "WARN")
| summarize <alias> = <aggregation_function>(<expression>), by: { <field1>, <field2> }
Core Aggregation Functions
DQL offers a rich suite of statistical and set aggregation functions:
| Function Category | Function Syntax | Description | Example Usage |
|---|---|---|---|
| Counting | count() | Counts total matching records in the group | total_logs = count() |
| Conditional Counting | countIf(<boolean>) | Counts records only if condition evaluates to true | error_count = countIf(status == "ERROR") |
| Arithmetic Sum | sum(<numeric>) | Calculates the total sum of a numeric field | bytes_served = sum(response_bytes) |
| Arithmetic Mean | avg(<numeric>) | Computes the average value | avg_latency = avg(duration_ms) |
| Extrema | min(<val>), max(<val>) | Identifies minimum and maximum values | slowest_req = max(duration_ms) |
| Percentiles | percentile(<numeric>, <p>) | Calculates the p-th percentile value (e.g., P50, P95, P99) | p95_latency = percentile(duration_ms, 95) |
| Median | median(<numeric>) | Computes the 50th percentile (median) | median_dur = median(duration_ms) |
| Distinct Values | collectDistinct(<field>) | Returns a deduplicated array/set of unique values | active_users = collectDistinct(user_id) |
| Distinct Count | countDistinct(<field>) | Returns the total count of distinct values | unique_ips = countDistinct(client_ip) |
| Array Collection | collectArray(<field>) | Collects all values (including duplicates) into an array | error_codes = collectArray(http_code) |
Multi-Dimensional Grouping Example
In the following query, DQL groups application errors simultaneously by host name and service name, computing total errors, unique users affected, and the 99th percentile request duration:
fetch logs
| filter status == "ERROR"
| parse content, "LD 'duration=' DOUBLE:duration_ms ' user=' WORD:user_id"
| summarize total_errors = count(),
affected_users = countDistinct(user_id),
p99_duration = percentile(duration_ms, 99),
by: { host.name, dt.entity.service }
| sort total_errors desc
2. Continuous Time-Series Generation: The makeTimeseries Command
A common misconception among engineers learning DQL is attempting to build line charts by grouping a summarize query with a binned timestamp (e.g., summarize count(), by: { bin(timestamp, 5m) }). While this produces a table with timestamp columns, it does not create a true time series.
The Need for makeTimeseries
In a distributed computing environment, event streams are naturally sparse. During off-peak periods (such as 3:00 AM), a critical microservice may emit zero error logs for several consecutive intervals. A standard summarize query simply omits those zero-count intervals, resulting in missing rows. When fed into a charting visualizer, these missing rows produce broken, fragmented line segments or misaligned comparative metrics.
The makeTimeseries command solves this by enforcing an equidistant temporal grid spanning the entire query window. It guarantees that every single time bucket exists in the output array, automatically bridging inactive periods.
fetch logs
| filter status == "ERROR"
| makeTimeseries error_rate = count(), by: { dt.entity.service }, interval: 5m, default: 0
Key Parameters of makeTimeseries
- Metric Definition: One or more aggregation expressions, such as
count(),avg(duration), orpercentile(latency, 90). by: { ... }: Dimensional slicing attribute. Grail creates a separate, independent time-series stream for each unique value of the grouping fields (e.g., one line per service).interval: <duration>: Specifies the temporal resolution of the buckets (e.g.,1m,5m,15m,1h,1d). If omitted, Grail dynamically calculates an optimal interval based on the global query timeframe.default: <value>: The fallback value used to populate empty intervals where zero matching records were detected. Settingdefault: 0ensures that inactive time buckets are explicitly populated with zero, producing a continuous, unbroken baseline on line and area charts.
3. Advanced Analytical Pipelines: Top-N Analysis & Slicing
A critical pattern in operations dashboards is Top-N Analysis—isolating the top 5 or 10 entities responsible for the majority of errors or latency, rather than overwhelming dashboards with hundreds of lines.
The Tabular Top-N Pattern
To find the top 5 error-producing Kubernetes namespaces over the last 24 hours:
fetch logs, from: now() - 24h
| filter status == "ERROR"
| summarize error_count = count(), by: { k8s.namespace.name }
| sort error_count desc
| limit 5
In this pipeline, summarize first aggregates the entire dataset across all namespaces; sort error_count desc orders the aggregated records from highest to lowest; and limit 5 truncates the stream to the top 5 offenders.
The Time-Series Top-N Slicing Pattern
When visualizing multi-series line charts, displaying 50 lines simultaneously creates unreadable "spaghetti" charts. DQL supports combining makeTimeseries with subqueries or dimensional limits to display only the Top-N series over time while grouping the remainder into an "Other" category or filtering them out.
4. Mapping DQL Outputs to Dynatrace Visualizations
In Dynatrace Dashboards and Notebooks, DQL serves as the unified data engine. The visual tile rendering options available to an engineer depend directly on the structural data type produced by the terminal command in the DQL pipeline.
+---------------------------------------------------------------------------------------------------+
| DQL COMMAND TO VISUALIZATION MAPPING MATRIX |
+---------------------------------------------------------------------------------------------------+
| DQL Pipeline Output Available Visualization Types Primary Operational Purpose|
+---------------------------------------------------------------------------------------------------+
| Raw / Projected Records • Table Ad-hoc log search, audit |
| (fetch | filter | fields) forensics, individual traces
+---------------------------------------------------------------------------------------------------+
| Tabular Aggregation • Table SLA scorecards, Top-N lists|
| (summarize ... by: {..}) • Single Value (Stat/Metric Tile) KPI counters, health meters|
| • Honeycomb Cluster/host fleet health |
| • Donut / Pie Chart Status code proportions |
| • Categorical Bar Chart Cross-service comparisons |
+---------------------------------------------------------------------------------------------------+
| Continuous Time-Series • Line Chart Trend analysis, baselines |
| (makeTimeseries ... • Stacked Area Chart Cumulative volume over time|
| interval: .. default: 0) • Time-binned Bar Chart Hourly/daily error spikes |
+---------------------------------------------------------------------------------------------------+
Choosing the Optimal Visualization
- Single Value (Stat Tile): Best used when
summarizeoutputs a single scalar value without grouping dimensions (e.g.,summarize total_errors = count()). Displays a prominent numeric badge with optional threshold coloring (green, yellow, red). - Honeycomb View: Ideal for representing fleet-wide health across hundreds of nodes or services. Each hexagon represents a grouping dimension value (e.g., host), shaded by error density or CPU saturation.
- Donut / Pie Chart: Best used to visualize proportional categorical distributions (e.g., HTTP response codes: 2xx vs 3xx vs 4xx vs 5xx), provided the number of slices is small (under 7).
- Line Chart: The gold standard for continuous trend tracking over time. Requires
makeTimeseriesto provide equidistant temporal points. - Stacked Area Chart: Visualizes both the total volume and the proportional composition of multi-series metrics over time (e.g., total log volume stacked by log severity:
INFO,WARN,ERROR).
Dashboards vs. Notebooks
- Dynatrace Notebooks: Interactive, exploratory document workspaces where engineers chain DQL queries, markdown documentation, and interactive charts. Used primarily for post-incident root cause forensics, exploratory data science, and team runbooks.
- Dynatrace Dashboards: Structured, auto-refreshing operational control planes designed for NOC displays, team overview monitors, and executive KPI tracking. Queries powering dashboard tiles should be strictly optimized using temporal bounds, partition pruning, and efficient aggregation to minimize continuous compute consumption.
An operations engineer configures a Dynatrace Dashboard tile to display a line chart tracking the rate of HTTP 500 errors per minute for an authentication service over the past 24 hours. During off-peak hours (01:00 AM - 05:00 AM), no HTTP 500 errors occurred. The engineer notices that the dashboard line chart shows disconnected line segments with gaps during off-peak hours instead of a continuous line along the zero axis. What is the root cause of this visualization defect, and how can it be fixed in the DQL query?
A lead platform architect wants to build a Dynatrace Dashboard showing the Top 5 Kubernetes namespaces generating the highest volume of WARN and ERROR logs over the past 7 days, along with the total count of distinct hosts operating within each namespace. Which DQL query produces the exact tabular dataset required for this visualization?
An administrator is reviewing a DQL query created by a junior analyst that attempts to generate a continuous 24-hour line chart of application exceptions grouped by service name: fetch logs | filter status == 'ERROR' | summarize error_count = count(), by: { bin(timestamp, 15m), dt.entity.service } When this query is added to a Dynatrace Dashboard, the line chart visualizer is unavailable, and only a static Table visualization can be rendered. Why does this query fail to produce a native line chart, and how should it be corrected?