1

In order to improve the performance of my dashboard, I would like to find a way to create a Grafana variable by using another Grafana variable that contains a list of elements.

The source variable is room_ids, which is a Query variable that uses a PostgreSQL data source. This is the query that defines the source variable:

SELECT DISTINCT roomid 
FROM periodic_measurements 
WHERE apartmentid = $apartment 
AND roomid != 0 
ORDER BY roomid;

Now let’s say I would like to use the variable room_ids to create a second variable called room1 which takes the first element of the list contained in room_ids. The variable room1 should provide the same result of the following Query variable :

SELECT DISTINCT roomid 
FROM periodic_measurements 
WHERE apartmentid = $apartment 
AND roomid != 0 
ORDER BY roomid 
LIMIT 1 OFFSET 0;

I am struggling to find out how shall to define the variable room1. I have tried different combinations such as $room_ids[1] or $room_ids(1) with no result.

Shall room1 be a Grafana Query variable or a Grafana Custom variable?

Shall I change room_ids to provide an array rather than a list?

Any hints?

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

markalex
  • 8,623
  • 2
  • 7
  • 32
Berni Hacker
  • 37
  • 8

1 Answers1

0

It seems like you are confusing what variable contains and what variable can contain.

Result of query you showed in your question are possible values that your variable room_ids can have. Usually it's only one of them, but if you'll enable Multi-value option, it can contain one or more room_ids (or none) of possible value.

So your second variable room can only be based on what variable room_ids contains right now (usually selected by user).

With that out of way: if you want your second variable to be based on actual value of another variable, you need a query (and subsequently you variable must be of type Query) that will make this transformation. Such variables are called chained variables.

For idea you described "get a variable that contains multiple values and get only first of them" you can utilize coalesce function of SQL.

SELECT COALESCE($room_ids)
markalex
  • 8,623
  • 2
  • 7
  • 32