Custom variable key:value pairs - list keys in csv format

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    Linux v8.4.6

  • What are you trying to achieve?

I have a table (my_stats) which contains rows like below
ts,src_ip,src_port, dst_ip,dst_port,metric

I also have a table (hostnames) for mapping ip to hostname
ip,hostname

I have a multi-select variable on a dashboard for a user to select the fields they want to aggregate the metric by
In order to provide the hostname for the src_ip and dst_ip I do joins but both columns come back as ā€˜hostnameā€™ which Grafana complains about and would also be confusing to a user.

  • How are you trying to achieve it?

One approach is to use a custom variable with key:value pairs like this:
src_ip : stats.src_ip,src_hostname : src.hostname AS src_hostname, src_port : stats.src_port, etcā€¦

I can then reference the values in the selected columns and the keys for the group by

Select ${TABLE_FIELDS:raw},sum(metric) AS total_metric
FROM my_stats AS stats
INNER JOIN hostnames AS src ON stats.src_ip = src.ip
INNER JOIN hostnames AS dst ON stats.dst_ip = dst.ip
WHERE
$__timeFilter(ts)
GROUP BY ${TABLE_FIELDS:text}
ORDER BY total_metric DESC
LIMIT 10;

  • What happened?

The keys are listed as src_ip + src_hostname + src_port + dst_ip + dst_hostname + dst_port

  • What did you expect to happen?

The keys to be listed as src_ip,src_hostname,src_port,dst_ip,dst_hostname,dst_port

Is this possible?

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

What is your data source?
You want them concatenated? What are the + signs for?

3

Data source is MySQL. The + signs are showing when I use ${TABLE_FIELDS:text}, I donā€™t want the + sign I want comma separated but I canā€™t see how to do this when listing the variable keys.

The query ends up like this:

SELECT stats.src_ip,src.hostname AS src_hostname,stats.src_port,stats.dst_ip,dst.hostname AS dst_hostname,stats.dst_port,sum(metric) AS total_metric
FROM my_stats AS stats
INNER JOIN hostnames AS src ON stats.src_ip = src.ip
INNER JOIN hostnames AS dst ON stats.dst_ip = dst.ip
WHERE
$__timeFilter(ts)
GROUP BY src_ip + src_hostname + src_port + dst_ip + dst_hostname + dst_port
ORDER BY total_metric DESC
LIMIT 10;

I want the query to end up like this:

SELECT stats.src_ip,src.hostname AS src_hostname,stats.src_port,stats.dst_ip,dst.hostname AS dst_hostname,stats.dst_port,sum(metric) AS total_metric
FROM my_stats AS stats
INNER JOIN hostnames AS src ON stats.src_ip = src.ip
INNER JOIN hostnames AS dst ON stats.dst_ip = dst.ip
WHERE
$__timeFilter(ts)
GROUP BY src_ip, src_hostname, src_port, dst_ip, dst_hostname, dst_port
ORDER BY total_metric DESC
LIMIT 10;

1 Like
4

So you want to do dynamic query. I would recommend you do all this in mysql for example in a stored procedure.

5

All I need is to be able to list the keys in csv format instead of with a + sign but there doesnā€™t seem to be a formatting option to do this as ${variable:text} is the only option Iā€™ve found which lists the keys and unfortunately lists them with a + sign. All other formatting options like ${variable:raw} and ${variable:csv} list the values not the keys.

6

how about

${servers:csv}
7

Unfortunately, that prints out the values and not the keys.

My variable is ā€œsrc_ip : stats.src_ip,src_hostname : src.hostname AS src_hostname, src_port : stats.src_portā€ as per this improvement - Variables: Adds support for key/value mapping in Custom variable by sartaj10 Ā· Pull Request #27829 Ā· grafana/grafana Ā· GitHub

The issue is I can format how the values are listed in the query using all the options in that page you linked to, but there is only one option to output the list of keys which is ${variable:text} and that is using a + sign whereas I want to use a comma.

Is there any setting file where I can change the + sign being the default for listing they keys? Or even somewhere in the code I can change it?

Thanks

1 Like