Example Dashboard Query

TODO: This is based on a dashboard for another vendor. Make all this make sense with the "Hits per status code" widget that we'll most likely have for Hydrolix for AWS.

You can write your own queries for your own customized widgets. What follows is an examination of an existing dashboard query. These queries make heavy use of the Grafana macros available from the ClickHouse Driver, documented on the Grafana ClickHouse Datasource page.

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

TODO: Is "Hydrolix for AWS Observability" the name of the below dashboard?

This example is from the "Hits per status code" widget that's at the top of the "Hydrolix for AWS Observability" dashboard.

Here's a breakdown of the SQL behind the chart:

1
2
3
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 timestamp of the data. This uses the Grafana macro to automatically calculate the graphing interval for the chart:

    1
      $__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:

1
  toStartOfInterval(toDateTime(column), INTERVAL 20 second)

  • count: the number of requests

    1
    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 on “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.

1
WHERE $__timeFilter(reqTimeSec)

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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():

1
AND $\_\_conditionalAll( statusCode ${AND_statusCode} (${statusCode:sqlstring}), $statusCode)

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

Filter bar

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 so on.

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

Filter Bar Conditionals

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: 

1
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 timeseries chart:

1
2
GROUP BY HTTP_Status_Code, time  
ORDER BY time

Finally, the very 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.

1
SETTINGS hdx_query_max_execution_time=60, hdx_query_admin_comment='aws - statuscode - ${\_\_user.login}'

1
SETTINGS hdx_query_max_execution_time=60, hdx_query_admin_comment='aws - statuscode - ${\_\_user.login}'