SHOW TAG VALUES WITH KEY with filter in Flux

Hi,

How can I create Chained or Linked Variables using flux in “Grafana Variables”?
How can I write the following in Flux:
Using influxql
1)
I have the first variable called Site_Name (pull it from the query)
SHOW TAG VALUES WITH KEY=“Site”

Second variable called Env (pull it from the query based on filter using Site_Name)
SHOW TAG VALUES WITH KEY=“Environment” WHERE Site =~ /$Site_Name/

Thanks

1 Like

I could get the output, but do not see how to filter based on $Site_ID variable!

import “influxdata/influxdb/schema”
schema.tagValues(bucket: v.bucket, tag: “Environment”)

import “influxdata/influxdb/schema”

schema.measurementTagValues(
bucket: “amsv_bucket”,
tag: “Environment”,
measurement: “Processor”

Hello ! I write the query below in InfluxQL and the same in Flux:

InfluxQL:

SHOW TAG VALUES WITH KEY="Site"

Flux:

from(bucket: "mybucket")
|> range(start: v.timeRangeStart)
|> keyValues(keyColumns: ["Site"])
|> group()

InfluxQL:

SHOW TAG VALUES WITH KEY="Environment" WHERE Site =~ /$Site_Name/

Flux:

from(bucket: "mybucket")
|> range(start: v.timeRangeStart)
|> filter(fn: (r) => contains(value: r["Site"], set: ${Site_Name:json}))
|> keyValues(keyColumns: ["Environment"])
|> group()

You just have to replace mybucket with the name of your database (bucket).

Hi @delapuentem,

Thank you for your reply.

I get Site_ID output but nothing for Environment

I tried to see what it prints in FLUX UI, seems it cannot recognize the variable!

Site_ID = from(bucket: “amsv_bucket”)
|> range(start: -10d)
|> filter(fn: ® => r._measurement == “Processor”)
|> keyValues(keyColumns: [“Site_ID”])
|> group()
|> keep(columns: [“Site_ID”])
|> distinct(column: “Site_ID”)
//|> map(fn: ® => ({r with Site_ID: r._value}))
//|> yield()


from(bucket: "amsv_bucket")
//|> range(start: -30d)
|> filter(fn: (r) => contains(set: ${Site_ID:json}, value: r["Site_ID"]))
|> keyValues(keyColumns: ["Environment"])
|> group()
|> keep(columns: ["Environment"])
|> distinct(column: "Environment")

You’re right. In the Grafana variables menu, check the Multi-value option and it works. But if it is deactivated it does not work. I’ll do my research on this.

Try to activate the Multi-value option and tell me.

Multi-value option needs to be disabled cause we allow only single item selection.
But I did check after enabling “Multi-value” it did not return values.

If you are not going to use Multi-value, replace

|> filter(fn: (r) => contains(set: ${Site_ID:json}, value: r["Site_ID"])) 

with

|> filter(fn: (r) => r["Site_ID"] == "${Site_ID}")

And why you commented this line? |> range(start: -30d)

2 Likes

It works! :smiley: :v:

from(bucket: “amsv_bucket”)
|> range(start: -10d)
//|> filter(fn: ® => contains(set: {Site_ID:json}, value: r["Site_ID"])) |> filter(fn: (r) => r["Site_ID"] == "{Site_ID}")
|> keyValues(keyColumns: [“Environment”])
|> group()
|> keep(columns: [“Environment”])
|> distinct(column: “Environment”)

I mistook it, thinking something else…

:partying_face: :partying_face: :partying_face: :clap: :clap: :clap:

Thanks a lot :wave: :boom:!!!

1 Like

@delapuentem One more thing how could we translate this

SHOW TAG VALUES WITH KEY=“Roles” WHERE Site_ID =~ /$Site_ID/ AND Environment =~ /$Environment/

Now Roles here is multu-value select

This worked :smiley:

from(bucket: “amsv_bucket”)
|> range(start: -10d)
|> filter(fn: ® => r[“Site_ID”] == “{Site_ID}") |> filter(fn: (r) => r["Environment"] == "{Environment}”)
|> keyValues(keyColumns: [“Roles”])
|> group()
|> keep(columns: [“Roles”])
|> distinct(column: “Roles”)

1 Like

Hello all,

I am migrating form InfluxDB 1.8 to InfluxDB 2.5 so far the experience has been good.
The one thing I am struggeling with is the conversion of some Grafana Dashboard variable InfluQL queries to Flux.

The InfluxQL query takes a second to complete, the Flux query I came up with takes around 30 seconds to complete.

Is there a smarter and/or faster way to translate the InfluxQL example to Flux?

InfluxQL examples (just take a split second to complete)

show tag values with key = cluster where project =~  /^$project$/
show tag values with key = system where platform =~  /^$platform$/ AND cluster =~  /^$cluster$/
show tag values from /^$source$/ with key = "idx" where controller = '0' AND system =~ /^$system$/

Flux query - as I would like it to run - takes 30 seconds to complete:

from(bucket: "${bucket}")
  |> range(start: -1)
  |> filter(fn: (r) => r["site"] == "${site}")
  |> filter(fn: (r) => r["project"] == "${project}")
  |> keyValues(keyColumns: ["cluster"])
  |> keep(columns: ["cluster"])
  |> distinct(column: "cluster")
  |> group()
  |> yield()

Flux with an additional filter on e.g. “controller” takes about 5 seconds to complete (since I can’t rely on any other tag to filter on, this is not my preference - still 5 seconds is long compared to the InfluxQL examples):

from(bucket: "${bucket}")
  |> range(start: -1)
  |> filter(fn: (r) => r["controller"] == "0")
  |> filter(fn: (r) => r["site"] == "${site}")
  |> filter(fn: (r) => r["project"] == "${project}")
  |> keyValues(keyColumns: ["cluster"])
  |> keep(columns: ["cluster"])
  |> distinct(column: "cluster")
  |> group()
  |> yield()

The scheme looks like this:

{
	"measurement": STR,
	"tags": {
		"site": STR,
		"project": STR,
		"cluster": STR,
		"system": STR,
		"platform": STR,
		"alias": STR,
		"system_ip": STR,
		"metric": "rate",
		"controller": "0",
		"idx": STR,
		},
	"fields": {
		"IOs/sec": FLOAT,
		"KiB/sec": FLOAT,
		"KiB/IO": FLOAT,
		"Fwd IO/s": FLOAT,
		"Fwd KiB/s": FLOAT,
		"R KiB/s": FLOAT,
		"W KiB/s": FLOAT,
		"Unmap KiB/s": FLOAT,
		"deltaSec": FLOAT
		},
	"time": DATETIME,
},
{
	"measurement": STR,
	"tags": {
		"site": STR,
		"project": STR,
		"cluster": STR,
		"system": STR,
		"platform": STR,
		"alias": STR,
		"system_ip": STR,
		"metric": "rate",
		"controller": "1",
		"idx": STR,
		},
	"fields": {
		"IOs/sec": FLOAT,
		"KiB/sec": FLOAT,
		"KiB/IO": FLOAT,
		"Fwd IO/s": FLOAT,
		"Fwd KiB/s": FLOAT,
		"R KiB/s": FLOAT,
		"W KiB/s": FLOAT,
		"Unmap KiB/s": FLOAT,
		"deltaSec": FLOAT
		},
	"time": DATETIME,
}

To partially answer my own question:

from(bucket: "${bucket}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["site"] == "${site}")
  |> filter(fn: (r) => r["project"] == "${project}")
  |> filter(fn: (r) => r["cluster"] =~ /^${cluster:regex}$/)
  |> keyValues(keyColumns: ["system"])
  |> keep(columns: ["system"])
  |> distinct(column: "system")
  |> group()

Can be simplified with:

import "influxdata/influxdb/schema"
 
schema.tagValues(
  bucket: "${bucket}",
  tag: "system",
  predicate: (r) => r["site"] == "${site}" and r["project"] == "${project}" and r["cluster"] =~ /^${cluster:regex}$/
)

What I couldn’t figure out if there is a predicate function for

schema.measurements()

to simplify and speed up this query:

from(bucket: "${bucket}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["site"] == "${site}")
  |> filter(fn: (r) => r["project"] == "${project}")
  |> filter(fn: (r) => r["cluster"] =~ /^${cluster:regex}$/)
  |> keyValues(keyColumns: ["_measurement"])
  |> keep(columns: ["_measurement"])
  |> distinct(column: "_measurement")
  |> group()