Is there any way to change the grafana query on the basis of value(variable) selected in grafana dropdown.
Assume that there is a dropdown in grafana dashboard which has values query1 and query2.
Note: We have used MySQL as database
If user selects query1 then query to be executed is:
select time, val1
from temperature
where temp = 10;
If user selects query2 then query to be executed is:
select time, humid
from humidity
where humidity = 50;
Hi Sarath, you can do that if you use templating variables.
If after reading this, you have doubts i can help you!
Hi @zahrazare313,
Dynamic queries can be achieved with dashboard variables.
Here are my two queries in InfluxDB (Flux language) for CPU idle usage and memory usage.
CPU idle:
from(bucket: "test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "cpu")
|> filter(fn: (r) => r["_field"] == "usage_idle")
|> filter(fn: (r) => r["cpu"] == "cpu-total")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
Memory usage:
from(bucket: "test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "mem")
|> filter(fn: (r) => r["_field"] == "used_percent")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
, with backslash \. All commas in queries must be escaped beacuse comma is used in Custom variable to separate different queries (i.e. CPU, MEM). E.g. my queries would become:CPU:
from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "cpu") |> filter(fn: (r) => r["_field"] == "usage_idle") |> filter(fn: (r) => r["cpu"] == "cpu-total") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean")
MEM:
from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "mem") |> filter(fn: (r) => r["_field"] == "used_percent") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean")
2. Add dashboard variable of type Custom (Settins 
→ Variables → New variable). Under Custom options add all one line formated queries (i.e. CPU and MEM) and separate queries with comma ,. You can also add labels that will show in drop-down instead of raw query e.g. CPU and MEM. So, final query that you should add in Custom options would look like:
CPU : from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "cpu") |> filter(fn: (r) => r["_field"] == "usage_idle") |> filter(fn: (r) => r["cpu"] == "cpu-total") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean") , MEM : from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "mem") |> filter(fn: (r) => r["_field"] == "used_percent") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean")
Create a panel and in Query field add only variable name e.g ${Query}.
Choose value from dashboard variable that contains query you would like to display. In right panel you can see raw query that is used in left panel:
Tested on:
Grafana OSS 9.4.7
InfluxDB OSS 2.7.0
OS: Alma linux 8
Additional info:
Useful variable syntax can be found at Grafana variable syntax.
If you found dynamic queries useful please support Grafana feature request that would allow usage of different delimiters, prefixes and suffixes for multi value variables. This would allow even better dynamic queries since you would be able to create queries with multi value variables. Feature request can be found on:
Best regards,
ldrascic
hi @ldrascic , thank you for your solution. I also use InfluxDB (Flux) and everything works fine until Flux queries include other variables.
For example, I have a variable test_var:
And I use it in my query so as to parametrize it (variable Query):
CPU : from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "cpu") |> filter(fn: (r) => r["_field"] == "usage_idle") |> filter(fn: (r) => r["cpu"] == "cpu-total") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean") , MEM : from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "mem") |> filter(fn: (r) => r["_field"] == "${test_var}") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean")
I get the following error:
invalid: error @1:180-1:188: undefined identifier test_var
If I change ${test_var} to hardcoded value everything is fine.
Is it possible somehow to change Grafana query with variables dynamically based on user inputs?
May be it is necessary to escape variables somehow in Flux in this case?
This query
from(bucket: "test") |> range(start: v.timeRangeStart\, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "mem") |> filter(fn: (r) => r["_field"] == "${test_var}") |> aggregateWindow(every: v.windowPeriod\, fn: mean\, createEmpty: false) |> yield(name: "mean")
works fine if I just use it in a panel, but I need it to be selected by user…
Looks like Grafana doesn’t replace variable test_var with its value in this case and sends “${test_var}” to InfluxDB instead of “used_percent”
I use Grafana 10.2.1
