0

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

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:

  • internal_temperature defined as:

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

  • 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_1 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.

Alternative approaches.

I have also tried the option mentioned in Grafana documentation (https://grafana.com/docs/grafana/latest/panels-visualizations/query-transform-data/share-query/) about using a source panel rather than variables and then using -Dashboard- data source in the panel that reuses the variables.

There is also the possibility to filter which query you want to share by using Transformation “Filter data by query” (Filter shared query results with another panel in Grafana).

However, unfortunately

this approach does not work for me since 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 does not work since -Mixed- data source at panel level does not allow to select -Dashboard- data source at query level.

Any hints?

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

Thanks,

Bernardo Di Chiara

Berni Hacker
  • 37
  • 8
  • Variables are a wrong way to go here 100%. They are not supposed for query results caching,and cannot be used in that way. – markalex Aug 31 '23 at 15:53
  • 1
    AFAIK, Grafana doesn't support any way of query caching. Additionally, queries you shared being so simple make me think that it would be way easier to optimize you Postgres, than construct something you describe in question. I recon a couple of indexes in right places will result in significant query time improvement. – markalex Aug 31 '23 at 20:46
  • Grafana supports query caching, but that's paid (Enterprise, Pro) feature - but that will be usefull for absolute time ranges, not relative (e.g. last X hours). I would also vote for proper table indexes. Use `EXPLAIN ANALYZE/EXPLAIN` to analyze generated SQL queries. – Jan Garaj Aug 31 '23 at 23:15

0 Answers0