Accessing variable value when using key/value variable

I am using a variable called category that is defined as a custom variable using a key:value pair. The reason for this is that I wanted to put a clean name to the actual value. My initial variable definition was

Rules Triggered : rules_triggered, Network Firewall : network_firewall, BOT : bot

In the SQL statement, I am referencing the variable using

sum(${category:text}

However, when doing so only the key portion of the variable definition gets used, which breaks the SQL query. In order to adjust I had to redefine by variable only using the value portion of the previously defined variable

rules_triggered, network_firewall, bot

Is there a way to continue using the key:value pair sequence and reference it using the ${category:text} syntax and be able to retrieve the associated value instead of the key?

are you showing this variable as a drop down choice?

:text is for key (nice human name - Rules Triggered), you need :value for value (human unfriendly, machine names/ids - rules_triggered).

1 Like

Yes, the variable is displayed as a drop down list

Hi. I have seen reference to this syntax before but I am a little confused. In my SQL statement within the panel I am using

sum(${category:text}

If I change the variable to allow Multi-value and then update the SQL to

sum(${category:value}

The resulting interpolation is

sum({unique_hits,rules_triggered})

The reason for using the :text syntax was to ensure the field separator would be a + sign. I do not understand your suggestion to use both :value and :text … how does that syntax look?

I don’t believe that key/value variable was designated for multi value.
Use additional “help” dashboard variable, where you clean string {unique_hits,rules_triggered} with SQL, e.g. (not real full SQL)

SELECT REPLACE(${category:value}, '{' ,'')

and use that cleaned SQL string in the query, so valid SQL will be generated at the end.

is this the same question that was asked in slack thread?

imo your issue as mentioned in the slack thread is db schema design issue (Not a grafana issue, you might get more mileage from a mysql dedicated forum), I would have done it differently. you have your metrics as columns. if new metrics come into play then you have to add yet another column. So to account for this specific db schema design, you will need to unpivot this table then do some crazy join to a table that parses the values in your $categories variable as follows.

WITH
  piv AS (
select bot as value,
       'bot' as metric
 from accountData
where switch_key = 'AANA-6KRISS'
union
select waf as value,
       'waf' as metric
 from accountData
where switch_key = 'AANA-6KRISS'
union
select dos as value,
       'dos' as metric
 from accountData
where switch_key = 'AANA-6KRISS'
union
select rules_triggered as value,
       'rules_triggered' as metric
 from accountData
union
select network_firewall as value,
       'network_firewall' as metric
 from accountData
where switch_key = 'AANA-6KRISS'
 ),
 cats as (select '${categories:csv}' as cats),
 cteparms as (
  SELECT jsontable.metric
FROM cats a
CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(cats, ',', '","'), '"]'),
                      '$[*]' COLUMNS (metric TEXT PATH '$')) jsontable
 )
 select a.* 
 From piv a
 join cteparms b on a.metric = b.metric