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