Grafana: Template Variables

Troubleshooting

InfluxDB Flux: The function "schema.tagValues()" does not support a "stop time range" and more filter options

Issue

If you use a the Flux function "schema.tagValues()" it returns all possible values instead of the ones present in the selected time period.
There is a open GitHub "Feature Request": https://github.com/influxdata/flux/issues/1071

Workaround v2 (recommended)

I rewrite the function "schema.tagValues()" to support filtering with time ranges:

//define function
customTagValues = (bucket, tag, predicate=(r) => true, start=v.timeRangeStart, stop=v.timeRangeStop) =>
  from(bucket: bucket)
    |> range(start: start, stop: stop)
    |> filter(fn: predicate)
    |> keep(columns: [tag])
    |> group()
    |> distinct(column: tag)

//using the function (simple)
customTagValues(bucket: "<YOUR_BUCKET>", tag: "host")

//using the function (with start date)
customTagValues(bucket: "<YOUR_BUCKET>", tag: "host", start: -2d)

//using the function (with date range)
customTagValues(bucket: "<YOUR_BUCKET>", tag: "host", start: -2d, stop: 2021-12-09T15:00:00Z)

Workaround 2 with "_measurement" filter

//define function
customTagValues = (bucket, measurement, tag, predicate=(r) => true, start=v.timeRangeStart, stop=v.timeRangeStop) =>
  from(bucket: bucket)
    |> range(start: start, stop: stop)
    |> filter(fn: predicate)
    |> filter(fn: (r) => r._measurement == measurement)
    |> keep(columns: [tag])
    |> group()
    |> distinct(column: tag)

//using the function (simple)
customTagValues(bucket: "<YOUR_BUCKET>", tag: "host", measurement: "<YOUR_MEASUREMENT>")

Workaround v1

With the following workaround you are able to get Tag-Values with timerange selection and filter options:

from(bucket: "<YOUR_BUCKET>")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => 
    r._measurement == "<YOUR_MEASUREMENT>"
  )
  |> keyValues(keyColumns: ["host"])
  |> group()
  |> keep(columns: ["host"])
  |> distinct(column: "host")

host = is the field I want in my Grafana template variable
v.timeRangeStart = Grafana start time
v.timeRangeStop = Grafana stop time

InfluxDB InfluxQL: "SHOW TAG VALUES" does not support a WHERE time clause

Issue

If you use a InfluxDB Query "SHOW TAG VALUES.." it returns all possible values instead of the ones present in the selected time period.
There is a open GitHub "Feature Request": https://github.com/influxdata/influxdb/issues/5668

Workaround 1 - "Using fields" (recommended)

Using fields instead of tags. -> You have to write your "asset" data as fields.
This workaround is recommended because you can use functions like "DISTINCT" to get the unique value of a field (its like "dedup" in Splunk).

The InfluxQL looks like this:

SELECT DISTINCT("host") FROM "asset_powershell_direct" WHERE "time" > (${__from} * 1000000) - 24h AND "time" < (${__to} * 1000000)

host = is the field I want to limit by time and dedup the value
${__from} = Grafana "from" Timestamp variable (its uses the timepicker in the Grafana GUI). Because Grafana uses miliseconds and InfluxDB uses nanoseconds, you have to convert the value (* 1000000).
${__to} = Grafana "to" Timestamp variable (its uses the timepicker in the Grafana GUI). Because Grafana uses miliseconds and InfluxDB uses nanoseconds, you have to convert the value (* 1000000).
Because I want to search the data in minimum 24h back, I add "- 24h".

Workaround 2 - "Subquery"

Instead of using the following query:

SHOW TAG VALUES FROM "asset_powershell_direct" WITH KEY = "host"

use a subquery as a workaround:

SELECT "host" FROM (SELECT "<value>", "host" FROM "asset_powershell_direct" WHERE $timeFilter)

host = is the tag I want to limit by time
value = any Influx FieldKey (just needs to contain any value)
asset_powershell_direct = my example measurement-name