Grafana: Template Variables: Unterschied zwischen den Versionen
Admin (Diskussion | Beiträge) (Die Seite wurde neu angelegt: „ == InfluxDB: "SHOW TAG VALUES" does not support a WHERE time clause == === Issue === If you use a InfluxDB Query "SHOW TAG VALUES" it returns all possible va…“) |
Admin (Diskussion | Beiträge) K |
||
(17 dazwischenliegende Versionen des gleichen Benutzers werden nicht angezeigt) | |||
Zeile 1: | Zeile 1: | ||
+ | = 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.''' <br> | ||
+ | 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: | ||
+ | <pre> | ||
+ | //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) | ||
− | == InfluxDB: "SHOW TAG VALUES" does not support a WHERE time clause == | + | //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) | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | ==== Workaround 2 with "_measurement" filter ==== | ||
+ | <pre> | ||
+ | //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>") | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | |||
+ | === Workaround v1 === | ||
+ | With the following workaround you are able to get Tag-Values with timerange selection and filter options: | ||
+ | <pre> | ||
+ | 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") | ||
+ | </pre> | ||
+ | '''host''' = is the field I want in my Grafana template variable <br> | ||
+ | '''v.timeRangeStart''' = Grafana start time <br> | ||
+ | '''v.timeRangeStop''' = Grafana stop time <br> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | == InfluxDB InfluxQL: "SHOW TAG VALUES" does not support a WHERE time clause == | ||
=== Issue === | === Issue === | ||
− | If you use a InfluxDB Query "SHOW TAG VALUES" it returns all possible values instead of the | + | '''If you use a InfluxDB Query "SHOW TAG VALUES.." it returns all possible values instead of the ones present in the selected time period.''' <br> |
There is a open GitHub "Feature Request": https://github.com/influxdata/influxdb/issues/5668 | There is a open GitHub "Feature Request": https://github.com/influxdata/influxdb/issues/5668 | ||
− | === Workaround " | + | === Workaround 1 - "Using fields" (recommended) === |
− | SELECT "host" FROM ( | + | Using fields instead of tags. -> You have to write your "asset" data as fields. <br> |
+ | 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 <br> | ||
+ | '''${__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).'' <br> | ||
+ | '''${__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).'' <br> | ||
+ | ''Because I want to search the data in minimum 24h back, I add "- 24h".'' <br> | ||
+ | |||
+ | |||
+ | === 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 <br> | '''host''' = is the tag I want to limit by time <br> | ||
− | '''value''' = | + | '''value''' = any Influx FieldKey (just needs to contain any value) <br> |
+ | '''asset_powershell_direct''' = my example measurement-name | ||
Aktuelle Version vom 9. Dezember 2021, 16:10 Uhr
Inhaltsverzeichnis
[Verbergen]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