Question 249754 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – How to create a Grafana variable that uses only one element of a Grafana query variable containing a list

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @markalex Thanks for your answer!

    Actually, I would need to use all the values of a list/array variable rather than only the first one.

    What I am trying to do is to create a panel with a pie chart which contains an amount of elements that depends on a variable.

    Currently, I have create a number of SQL queries in the pie chart that look as follows:

    SELECT SUM(mvalue) AS "room $room1" FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room1 AND metric = 5 AND $__timeFilter(mtimestamp))

    where room1 is a query variable defined as follows:

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

    I have created 10 of such variables for the first 10 rooms and each of the 10 queries in the Grafana pie chart panel uses one of such variables.

    Each of such SQL queries in the Grafana panel provides a single numeric value.

    However, each apartment has a different number of rooms so I am trying to find a more dynamic way.

    Once the value of the variable apartment has been assigned, Grafana should show the energy of all the rooms of the apartment in the pie chart.

    Is there any way Grafana could accept a SQL query containing a loop as input for a pie chart?

    I was thinking something like this (tested in SQL CLI):

    DO $$
DECLARE
    number_of_rooms INT;
    room_ids INT [];
    room_energy FLOAT(6);
BEGIN
    SELECT count(DISTINCT roomid) INTO number_of_rooms FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0;
    SELECT ARRAY(SELECT DISTINCT roomid INTO room_ids FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0 ORDER BY roomid);
    FOR counter IN 1..number_of_rooms LOOP
        SELECT SUM(mvalue) INTO room_energy FROM periodic_measurements WHERE (apartmentid = 4 AND roomid = room_ids[counter] AND metric = 5 AND mtimestamp::TEXT LIKE '2023-05-15%');
        RAISE NOTICE 'room %: %', room_ids[counter], room_energy;
    END LOOP;
END $$;

    where the value of room_energy would be used in the pie chart and room %, room_ids[counter] would go in the legend.


  2. 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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search