Select case from variable to query

Grafana InfluxDB
1

Hello,
I’ve read the documentation (PostgreSQL data source | Grafana documentation) but I don’t understand how I can code what I want.

I’ve created a custom variable, name TEST, with 2 values A and B.

Depending of the value of TEST, I would like to plot the measurement C or D, I suppose that I need to use a select case, but I don’t succeed :frowning:

The sql code without variable/select case is below (and simple):

SELECT mean(“EngineeringValue”) FROM “C” WHERE $timeFilter GROUP BY time($__interval) fill(null)
or
SELECT mean(“EngineeringValue”) FROM “D” WHERE $timeFilter GROUP BY time($__interval) fill(null)

And I try find a solution to have this final result:

Case:
If /^$A$/=A then CASE= C
If /^$A$/=B then CASE= D

SELECT mean(“EngineeringValue”),
FROM CASE
WHERE $timeFilter
GROUP BY time($__interval) fill(null)

Thank you

2

It looks overcomplicated. Why not:

  • custom variable: name TEST, with 2 values C and D
  • query: SELECT mean(“EngineeringValue”) FROM "${TEST:raw}" WHERE $timeFilter GROUP BY time($__interval) fill(null) (of course, pls resolve any syntax issues)
    ?
3

Actually, TEST value is the name of one equipment and for each equipment (A, B, C… max 100 equipments), I have different measurements and I want to plot different measurements in a panel.

For example, If I choose equipment A, the panel will be:

Panel 1
Plot 1:
SELECT mean(“EngineeringValue”) FROM “A_VOLTAGE1” WHERE $timeFilter GROUP BY time($__interval) fill(null)
Plot 2:
SELECT mean(“EngineeringValue”) FROM “A_VOLTAGE2” WHERE $timeFilter GROUP BY time($__interval) fill(null)
Plot 3:
SELECT mean(“EngineeringValue”) FROM “A_CURRENT1” WHERE $timeFilter GROUP BY time($__interval) fill(null)
Plot 4:
SELECT mean(“EngineeringValue”) FROM “A_CURRENT2” WHERE $timeFilter GROUP BY time($__interval) fill(null)

Panel 2
Plot 1:
SELECT mean(“EngineeringValue”) FROM “A_POWER1” WHERE $timeFilter GROUP BY time($__interval) fill(null)
Plot 2:
SELECT mean(“EngineeringValue”) FROM “A_POWER2” WHERE $timeFilter GROUP BY time($__interval) fill(null)

And if I choose TEST=B, of course I want the measurments B_XXXX etc…
All of this dynamically of course :slight_smile:

The real value will be:
TEST = (101, 102, 103…201, 202…)
Meaurements: XXXX_101A_VOLTAGE, XXXX_101B_VOLTAGE, XXXX_101_CURRENT1,…

4

Still confused. You tagged topic as PostgreSQL, but you are using InfluxDB terms. Still complicated data structure.

Keep it simple:
one measurement (metrics) where you will have tags equipment (A, B, C, D…) metric (voltage1, voltage2, current1, current2, …) and field value

Then you can have dashboard variable equipment generated from equipment tag values (no hard coded values). Panel query can be:

SELECT 
  mean(value) 
FROM metrics 
WHERE 
  $timeFilter 
  AND equipment="${equipment:raw}"
  AND metric="voltage1"
GROUP BY time($__interval) fill(null)

I guess it is easier to change your data structure than hacking a tool (e.g. Grafana). Think also in advance, so you have also suitable data structure for future queries (e.g. aggregations on different levels). My random example, how it can be complicated:

Capture.PNG
Capture.PNG1177×754 63.8 KB

5

Hello,
Thanks a lot for your different replies.
Someone edit my post to PostgreSQL but you’re right I’m using InfluxDB.

Unfortunatly I can’t change the data structure, I’m just an “user”.
I will try your example.

6

Create dashboard variable equipment and hard code list of equipments there. Then panel query can be:

SELECT mean("EngineeringValue") 
FROM "${equipment:raw} _VOLTAGE1" 
WHERE $timeFilter 
GROUP BY time($__interval) fill(null)

Please format your codes/queries.