Guide

Example dashboard query

Suggest Edits

You can write your own queries for your own customized widgets on dashboards.

This section examines an existing dashboard query. These queries use the Grafana macros available from the ClickHouse Driver, documented in Grafana ClickHouse Datasource.

To see the query powering any widget in your own dashboard, click the widget you want to examine and click the e key to go to the widget editor.

This example is from the Hits per status code widget that’s at the top of the Akamai Observability dashboard. It produces a widget that looks like this.

Hits per widget query.

Find it and click the e key to view the widget editor to follow along.

Here’s a breakdown of the SQL behind the chart.

SELECT toString(statusCode) as HTTP_Status_Code, $__timeInterval(reqTimeSec) as time, count(*) as http

The lines above select three things.

  • HTTP_Status_Code. The HTTP status code from the response.

  • Time. The initial timestamp of the time interval for the data. This uses a Grafana macro to automatically calculate the graphing interval for the chart.

    $__timeInterval(reqTimeSec)

    That macro evaluates to an SQL function like this one, depending on the time interval selected in the UI and the size of the chart.

    toStartOfInterval(toDateTime(column), INTERVAL 20 second)

    To learn more about the toStartOfInterval function and related SQL function, see the ClickHouse documentation.

  • Count. The number of requests

FROM ${table}

This ${table} variable comes from the dashboard’s settings. To see these and other variables, click the Gear icon. in the upper right corner of the dashboard, then click Variables on the left-hand side of the page.

The WHERE clause in this query has two parts, the time range selector and a list of conditionals to make the query obey the filter bar at the top of the dashboard.

WHERE $__timeFilter(reqTimeSec)

This Grafana macro is expanded to the time window selected in the UI. For example,

time >= '1480001790' AND time <= '1482576232'

The large list of conditionals is to make the filter bar at the top of the page affect this query.

AND $__conditionalAll( statusCode ${AND_statusCode} (${statusCode:sqlstring}), $statusCode) AND $__conditionalAll( reqHost ${AND_reqHost} (${reqHost:sqlstring}), $reqHost) AND $__conditionalAll( cacheStatus ${AND_cacheStatus} (${cacheStatus:sqlstring}), $cacheStatus) AND $__conditionalAll( reqMethod ${AND_reqMethod} (${reqMethod:sqlstring}), $reqMethod) AND $__conditionalAll( rspContentType ${AND_rspContentType} (${rspContentType:sqlstring}), $rspContentType) AND $__conditionalAll( errorCode ${AND_errorCode} (${errorCode:sqlstring}), $errorCode) AND $__conditionalAll( transferTimeMSec >= ${transferTimeMSec_percentile}, $transferTimeMSec_percentile) AND $__conditionalAll(${metric_name} ${metric_filter} ${metric_value}, $metric_filter) AND $__conditionalAll(reqPath ${AND_reqPath} (${reqPath:sqlstring}), $reqPath) AND $__conditionalAll(cliIP ${AND_cliIP} (${cliIP:sqlstring}), $cliIP) AND $__conditionalAll(UA ${AND_UA} (${UA:sqlstring}), $UA)

Each of these $__conditionalAll() macros evaluate to what the customer has selected in the filter bar. If nothing has been selected, then it evaluates to the placeholder 1=1, which is optimized out by the query engine.

Let’s take a close look at the first $__conditionalAll().

AND $__conditionalAll( statusCode ${AND_statusCode} (${statusCode:sqlstring}), $statusCode)

The ${AND_statusCode} is the result of the “include/exclude” dropdown in the filter bar:

Include/exclude icon.

It evaluates to in or not in for this query, due to the rules set up in the Variables configuration of the dashboard.

The (${statusCode:sqlstring}) variable expands according to the dashboard configuration as well, a list of possible status codes such as 200, 201, 202, and others.

The $statusCode variable represents what the customer has selected in the UI, if anything.

Note that other lines in this large list of conditionals include SQL statements that provide dynamic selections in the filter bar. For example, the reqHost conditional includes this query: SELECT reqHost FROM ${table} WHERE $__timeFilter(reqTimeSec) GROUP BY reqHost ORDER BY COUNT() DESC LIMIT 50 Keep in mind that these queries are run whenever the dashboard page is refreshed, so when writing your own, keep them as low overhead as possible.

The next few lines in the query perform grouping and order to satisfy the time series chart.

GROUP BY HTTP_Status_Code, time ORDER BY time

Finally, the end of the query includes a SETTINGS section. This where you can include database settings like hdx_query_max_execution_time (in seconds) and hdx_query_max_memory_usage (per query, in bytes) if needed.

SETTINGS hdx_query_max_execution_time=60, hdx_query_admin_comment='akamai - statuscode - ${__user.login}'

Updated 13 days ago