Grafana + InfluxDB Flux - query for displaying multi-select variable inputs

Grafana InfluxDB
1

We’ve set up a Grafana cloud + InfluxDB 2.0 (Flux language) cloud instance. As part of this, we’ve created a custom variable list with “device IDs”, called devices .

In a panel, we wish to display parameter data where the user can select one or more device IDs from the devices list to have them displayed in the panel. This works fine for single device ID selection, but not for multiple devices.

How should the query be modified to display data from a variable number of devices based on a multi-select entry in the dropdown in Grafana?

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "${devices}")
  |> filter(fn: (r) => r["_field"] == "Speed")
  |> aggregateWindow(every: v.windowPeriod, fn: mean)
  |> yield(name: "mean")
1 Like
2

I’m not sure how Grafana expands variable selection into a query, but my guess is you’re going to need to use Flux’s contains function rather than ==.

3

Hi again,

I tried this as below, but I get an error as in attached image - maybe I’m setting up the query wrong?

My device IDs are of the format “952383F2”, “82F12243” etc.

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => contains(value: r["_measurement"], set: ${devices}))
  |> filter(fn: (r) => r["_field"] == "WheelBasedVehicleSpeed")
  |> aggregateWindow(every: v.windowPeriod, fn: mean)
  |> yield(name: "mean")

error
error777×250 17.8 KB

4

Does Grafana have a way for you to see what the final Flux query looks like that’s being sent to InfluxDB?

5

If I look in the Query inspector it looks as below. I guess maybe the issue is that the variable returns an object rather than a list. Not sure how to get around that part if that’s what causing the problem.

from(bucket: "test-bucket-new")
  |> range(start: 2020-08-10T13:31:42Z, stop: 2020-08-10T15:09:51Z)
  |> filter(fn: (r) => contains(value: r["_measurement"], set: {958D2219,958D2220}))
  |> filter(fn: (r) => r["_field"] == "WheelBasedVehicleSpeed")
  |> aggregateWindow(every: 5s, fn: mean)
  |> yield(name: "mean")

I also tried “manually” inserting two values as a list as below and that seems to work as intended:

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => contains(value: r["_measurement"], set: ["958D2219","958D2220"]))
  |> filter(fn: (r) => r["_field"] == "WheelBasedVehicleSpeed")
  |> aggregateWindow(every: v.windowPeriod, fn: mean)
  |> yield(name: "mean")

So I guess the trick is whether Flux has a way to turn an object like {958D2219,958D2220} into a list like this ["958D2219","958D2220"]?

6

Per https://grafana.com/docs/grafana/latest/variables/advanced-variable-format-options/#json I think you can use set: ${devices:json} and you should be set.

2 Likes
7

Thank you! That seems to have solved it :slight_smile:

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => contains(value: r["_measurement"], set: ${devices:json}))
  |> filter(fn: (r) => r["_field"] == "WheelBasedVehicleSpeed")
  |> aggregateWindow(every: v.windowPeriod, fn: mean)
  |> yield(name: "mean")
4 Likes
8

Hi!

I’m sitting here at a similar problem. Instead of using a variable for “_measurement” I need it for “_field”. I adapted the code above like this:

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => contains(value: r["_field"], set: ${devices:json}))
  |> aggregateWindow(every: v.windowPeriod, fn: last)

Basically the query runs but it is very slow, even for a 5 minute timespan. If I choose a longer timespan (for example 1 day) the query aborts due to a timeout.
Is there something I can do to speed up the query?

Thanks
Manfred

9

Using contains() seems to have quite severe performance implications. You might be better off utilizing regex in filter() and formatting your variable accordingly, i.e., ${devices:regex} (Advanced variable format options | Grafana Labs). Perhaps something like this could work for you?

from(bucket: "test-bucket-new")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] =~ /${devices:regex}/)

Depending on how your dashboard variables are created (queried or manually), you might end up with unwanted regex matches. In that case, you could go with something like /\b${devices:regex}\b/ in your query.

Worked for me and was way faster than contains().

Good luck!
Kaspar

11 Likes
10

Hi!

Yes, using regex did the trick for me. No mather of timespan the query is as fast as using the query without variables.

Thanks a lot and have a great weekend!
Manfred

11

This was fantastic. Thanks.

12

Amazing. Works perfectly

13

Hi everyone,
I’m still suffering the same issue… could you help to check where i missing, thank you!

variable setting

grafana_var
grafana_var1688×988 83 KB

Query

from(bucket: "python_amqp")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "line_protocol")
  |> filter(fn: (r) => r["pointName"] =~ ${v_pointname}:regex})
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Query inspector

from(bucket: "python_amqp")
  |> range(start: 2021-10-18T05:14:22Z, stop: 2021-10-18T08:14:22Z)
  |> filter(fn: (r) => r["_measurement"] == "line_protocol")
  |> filter(fn: (r) => r["pointName"] =~ {pointName1,pointName2}:regex})
  |> aggregateWindow(every: 5s, fn: mean, createEmpty: false)
  |> yield(name: "mean")
14

Hi @pgg
Looks like your pointName filter has an extra closing bracket and is missing regex delimiters /.
You can also add start/end of line matchers (^ and $) to ensure the filter will only match full pointName values.

Instead of:

  |> filter(fn: (r) => r["pointName"] =~ ${v_pointname}:regex})

you should have:

  |> filter(fn: (r) => r["pointName"] =~ /^${v_pointname:regex}$/ )

So your query will look like:

from(bucket: "python_amqp")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "line_protocol")
  |> filter(fn: (r) => r["pointName"] =~ /^${v_pointname:regex}$/ )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

And the query inspector should show:

from(bucket: "python_amqp")
  |> range(start: 2021-10-18T05:14:22Z, stop: 2021-10-18T08:14:22Z)
  |> filter(fn: (r) => r["_measurement"] == "line_protocol")
  |> filter(fn: (r) => r["pointName"] =~ /^((pointName1)|(pointName2))$/)
  |> aggregateWindow(every: 5s, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Hope that helps!

PS: Not tested.

2 Likes
15

hi @aloike
Big thanks for your help !
It’s work perfectly :slight_smile:

16

Thanks for sharing!! This approach got my queries down from > 4 sec to < 500 ms!!!