How to have multiple queries (i.e. graph lines) in one panel based on multi-valued variable settings

Grafana PostgreSQL
,
1

So I have a table like this:

 time | host  | metric
-------|--------|----------
  t0   |    1   |    v0
  t0   |    2   |    v1
  t0   |    3   |    v2
  t1   |    1   |    v3
 ... etc ...

In essence, the time is in the first column, the host is in the second column, and the metric for that {host,time} pair is in the 3rd.

I have a variable that represents the host that I would like to make multi-valued, so you can display comparative graphs per host on the same panel. I’m using the postgresql data source and the single value host variable works just fine with the query builder. As soon as I make this multi-valued, the panel breaks and displays nothing.

How do I set up grafana to handle multiple values for the host variable, such that it will display multiple host graphs on the same panel for comparison?

Thanks in advance for any reply.

2

I think I have a solution. It involves returning subqueries from the Grafana variable. For the above example assume the host column is a foreign key into a table called host which looks like this:

id | name
---|-----
 1 | host1
 2 | host2
... etc ...

and the name of the example table is called metric. The Grafana variable would look like this:

SELECT name AS __text, format('(SELECT t1.metric FROM metric t1 WHERE t1.time=t2.time AND t1.host=''%s'') AS "%s"', id, name) AS __value FROM host

The first %s has two pairs of single quotes around it, while the second has a single pair of double quotes (at least this is what I had to do the get the SQL syntax to work for me). Make this variable multi-value and optionally turn on the All option. Let’s call this variable metric.

Now the query for the panel would look like this:

SELECT
  t2.time AS "time",
  ${metric:raw}
FROM metric t2
WHERE
  $__timeFilter("time")
GROUP BY 1
ORDER BY 1

Hope this helps.

3

Unfortunately, this runs extremely slow. Every data point requires a query that returns a single value. Does not scale well for long time samples and/or many variables.

4

I have a solution that runs reasonably well and that addresses the scaling problem for the number queries and the number of points per history. To deal with the number of queries we construct a temporary table where each column contains the time history for a host. Each column requires a single query to populate it. The table would be constructed with the following subqueries:

FROM
(SELECT DISTINCT time FROM metric) t
LEFT JOIN (SELECT time,metric FROM metric WHERE host=1) t1 ON t.time=t1.time
LEFT JOIN (SELECT time,metric FROM metric WHERE host=2) t2 ON t.time=t2.time
etc.

To address the time point scaling we use the Grafana $__interval variable and $__timeGroupAlias macro:

SELECT
  $__timeGroupAlias(t.time, $__interval),
  avg(t1.metric) AS host1,
  avg(t2.metric) AS host2,
  etc.
FROM <use-subquery-from-above>
WHERE
  $__timeFilter(t.time)
GROUP BY 1
ORDER BY 1

To allow the user to manage the dashboard we will use 3 variables, one to present a drop down with host names and the other two chained from the first to fill in the SELECT and FROM subqueries. The drop down would be created with a query variable called hosts with the following definition:

SELECT name FROM host

To define the subqueries in the SELECT we create a variable called columns with the following:

SELECT string_agg(format('avg(t%s.cost) AS "%s"', id, name), ',')
FROM host
WHERE name IN ($hosts)

Then the subqueries in the FROM clause would be from a variable called tables with the following definition:

SELECT '(SELECT DISTINCT time FROM metric) t ' || string_agg(format('LEFT JOIN (SELECT time,metric FROM metric WHERE host=%s) t%1$s ON t.time=t%1$s.time', id), ' ')
FROM host
WHERE name IN ($hosts)

Finally to put it all together the query with variables looks like:

SELECT
  $__timeGroupAlias(t.time, $__interval),
  ${columns}
FROM ${tables}
WHERE
  $__timeFilter(t.time)
GROUP BY 1
ORDER BY 1