Difficulties in sharing queries among different panels with enough flexibility

Grafana Time Series Panel
1

Hi!

I am struggling to find a way to use a Grafana Query variable that contains a list of elements into a SQL query in a Grafana panel.

This is needed in order to optimize the loading time of the dashboard. I have certain variables that are used in multiple panels. One example is internal_temperature. Each plot that uses such a variable uses also other variables which might or might not be shared with other panels.

Currently in the panel, which uses Time Series visualization type, I have the following query to visualize the plot:

SELECT mtimestamp, mvalue AS “internal temperature” FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room AND metric = 1 AND $__timeFilter(mtimestamp))

I have tried to define a Query variable and to refer to such a variable in the panel with no success.

I have tried, among others, the following variables:

  1. internal_temperature defined as:

SELECT mvalue AS “internal temperature” FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room AND metric = 1 AND $__timeFilter(mtimestamp))

  1. internal_temperature_1 defined as:

SELECT mtimestamp, mvalue AS “internal temperature” FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room AND metric = 1 AND $__timeFilter(mtimestamp))

Which of those 2 variable is the correct one (if any) and how shall I refer to it in the panel SQL query?

The SQL query expects a time series so internal_temperature_2 sounds more correct but, if I write in the SQL query $internal_temperature_1, I get the following error:

db query error: pq: syntax error at or near “1680296400000”

(the numerical value corresponds to the first value of mtimestamp).

I have tried many different variants with both variables with no result.

I have also tried the option mentioned in Grafana documentation (Learn Grafana: Share query results between panels to reduce load time | Grafana Labs) about using a source panel rather than variables and then using -Dashboard- data source in the panel that reuses the variables but this approach does not work since:

  1. I have a set of queries to share and each panel where I want to re-use queries needs only a subset of them.

  2. Each panel where I want to re-use queries needs other queries that are not shared and therefore I need to use directly my data source for such queries. Using -Mixed- data source does not work since -Mixed- data source does not allow to select -Dashboard- data source.

Any help would be appreciated.

I am running Grafana version 9.5.1. on Linux Ubuntu 22.04.2.

Thanks,

Bernardo Di Chiara