Change Grafana query dynamically based on user inputs

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!

1 Like

hi @danielgonzalez ,can you help me, I need exactly the same thing

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")

 

Steps for dynamic queries with variables:

  1. Convert queries into one line queries and escape all commas , 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 :gear: → 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")

  1. Create a panel and in Query field add only variable name e.g ${Query}.

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

1 Like

hi @ldrascic, Thank you very much for your detailed explanation and for taking the time

1 Like

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:

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

Hi @ebabeshko did you manage to find any solution for solving this issue? I’m facing exactly the same in my PromQL query now. Thanks

@ldrascic do you have any ideas? My problematic query is
label_replace(rate(hb_in_req_count{host=~“($Host)”}[1h]), “custom_legend”, “Metrics ($1)”, “host”, “(.*)”). When I set it as an option for variable - the “Host” is not being caught from another variable.
Is it possible to fix?

I had to hardcode values in queries instead of variables. So now user can choose queries, but none of them contains variables. Otherwise it didn’t work out.

1 Like