How can I use key/value dashboard variables in Grafana + InfluxDB?

Question

I’m trying to suss out how to format my key/value pair dashboard variable. I’ve got a variable whose definitions are:

sensor_list = 4431,8298,11041,13781
sensor_kv = 4431 : Storage,8298 : Stairs,11041 : Closet,13781 : Attic

However, I can't seem to use it effectively for queries and dashboard formatting with InfluxDB. For example, I've got a panel whose query is this:

SELECT last("battery_ok") FROM "autogen"."Acurite-Tower" WHERE ("id" =~ /^$sensor_list$/) AND $timeFilter GROUP BY time($__interval) fill(null)

That works, but if I replace it with the KV, I can't get the value:

SELECT last("battery_ok") FROM "autogen"."Acurite-Tower" WHERE ("id" =~ /^$sensor_kv$/) AND $timeFilter GROUP BY time($__interval) fill(null)

^ that comes back with no data.

I'm also at a loss as to how to access the value of the KV pair in, say, the template values for a repeating panel. ${sensor_kv:text} returns the word "All" but ${sensor_kv:value} actually causes a straight up error: "Error: Variable format value not found"

My goal here is twofold:

1. To use the key side of the kv map as the ID to query from in the DB
2. To use the value side as the label of the stat panel and also as the alias of the measurement if I'm querying in a graph

I’ve read the formatting docs and all they mention are lists; there are no key/value examples on there, and certainly none that do this. It’s clearly a new-ish feature (here is the GH issue where its implementation is merged) so I’m hoping there’s just a doc miss somewhere.

Landa

Answer 1

In PR that you linked there is a tiny comment that key/value pair has to contain spaces. So when you're defining a pairs in Values separated by comma it should be like

• key1 : value1, key2 : value2

These will not work

• key1:value1, key2:value2
• key1 :value1, key2 :value2
• key1: value1, key2: value2

Let's say that name of the custom variable is var1

Then you can access the key by ${var1} ,$var1, ${var1:text} or [[var1:text]] (some datasources will be satisfied with $var1 - some will understand only ${var1:text})

And you can access the value by ${var1:value} [[var1:value]]

Tested in Grafana 8.4.7

Tella

well great post. thanks a lot. The one who posted please mark it as solution – Echolalia

I don't understand how to use the ${var1:value}. If I previously had a metric with account="abcd" what do I replace it with a variable? account="${var1:value}" ? – Toulon

Answer 2

I realise this might not be all the information you're after, but hope it will be useful. I came across this question when trying to implement something similar myself (also using InfluxDB), and I have managed to access both keys and values in a query

My query looks like this:

SELECT "Foo.${VariableName:text}.Bar.${VariableName:value}" FROM "db" WHERE (filters, filters) AND $timeFilter GROUP BY "bas"

So as you see, my use case was a bit different from what you're trying to achieve, but it demonstrates that it's basically possible to access both the key and the value in a query.

Terina

Answer 3

Key/values are working with some timeseries DB where it makes sense, e.g. MySQL https://grafana.com/docs/grafana/latest/datasources/mysql/:

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value. The __text column value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allows you to have a friendly name as text and an id as the value.

But that's not a case for InfluxDB: https://grafana.com/docs/grafana/latest/datasources/influxdb/ InfluxDB can't return key=>value result - it returns only timeseries (that's not a key=>value) or only values or only keys.

Workarounds:

1.) Use supported DB (MySQL, PostgreSQL) just to have correct key=>value results. You really don't need to create table for that, just combination of SELECT, UNION, ... and you will get desired result.

2.) Use hidden variable which will be "translating" value to key, which will be used then in the query. E.g. https://community.grafana.com/t/how-to-alias-a-template-variable-value/10929/3

Of course everything has pros and cons, for example multi value variable values may not work as expecting.

Lalo

This doesn't answer my question; I know how to get the variable -- it's a custom one that I have explicitly defined in the admin panel for my dashboard, by manually providing exactly the values I included in the question -- but I don't know how to refer to the two parts of it in queries or dashboard panel title templates. – Landa

@ChrisR with option 2.) you have 2 variables: one with ID for query, one with human friendly name for panel title. np. – Lalo