postgresqlgrafanadashboardgrafana-variable

postgresql + grafana panel implementation question


i have the following table in a postgresql database, there is a script running every 5 minutes inserting this values in the table.

name(varchar) used(int) time(timestamptz)
monitoreado1 100 2023-07-19 10:59:15
monitoreado2 200 2023-07-19 10:59:15
monitoreado3 150 2023-07-19 10:59:15

also a i have a variable in grafana named TRUNKS that has this diferent values:
'monitoreado1','monitoreado2','monitoreado3' and is posible to select the all value tha gives you something like this: "monitoreado1,monitoreado2,monitoreado3"

what im trying to do is something like this picture

to make the panel from the picture i used 4 diferent querys hardcoding every name in the query but i want to make it more dynamical, the query must return the used and time values acording to the name selected in the "TRUNKS" variable, but if the "TRUNKS" variable has the "all" option selected i need the query to return the value of used grouped by each name as diferent aliases, is this posible? i cant find any example tha looks like this i even tryed chatgpt but afeter a while of corrections each query was farther away from the expected result, i also tryed to implement this usign case when and subquerys but subquerys can only manage 1 row and as this is a timeseries panel i need to have multiple row acording to the time value

enter image description here


Solution

  • You can achieve this with query like this:

    SELECT
      $__timeGroupAlias("time",'5m'),
      name,
      sum(used) as value,
      'used' as metric
    FROM test_data
    WHERE $__timeFilter("time")
    AND ('$TRUNKS' = '' or name in ($TRUNKS))
    GROUP BY 1, 2
    ORDER BY 1
    

    Here filtering of the names is done with usual in SQL clause. Additionally if no values are selected all available names will be shown.


    This official manual on using Postgres as data source has many useful information about creation of queries to your database.