Using Variables to dynamically select Columns in MySQL

Grafana
1

Hello,
i am relative new to Grafana and i have a small issue, wich possibly is a bug in Grafana.
I am using MySQL and i want to dynamically show certain colums of my datatable. My first thought is to make a variable for this. Unfortunatly when i set it to be “multi-value” this is not working correctly.
This is my variable:
image
this is my SQL query.

error
error802×228 11.7 KB

If i not set it to be multi-value, everything works fine.
Another strange thing is, when i set the variable to muli-value and select all possible values and then deselect “multi-value”, grafana will still show all values as selected. In this case also it shows me the correct graph for each of the variables. This is exactly what i want, but it unfortunatly only works when i havent “multi-value” selected.

Correkt%20Graph
Correkt%20Graph1899×446 34.5 KB

Thanks in advance and kind regards

2

can you post the query generated when is not working??

3

This is how it looks when its not working.

image
image1896×451 21 KB

this is the autogenereated query when using the variables, and when “multi-select” is activated

SELECT
  zeit AS "time",
  'Machine4711','Machine1438','Machine1234','Machine5475'
FROM machinenzustandstabelle
ORDER BY zeit

It seems like it adds those " ’ ", and this is against the Syntax of mysql.

If multi-value is not selected i get the correct autogenerated query

SELECT
  zeit AS "time",
  Machine4711,Machine1438,Machine1234,Machine5475
FROM machinenzustandstabelle
ORDER BY zeit

But the Problem is that i cant dynamically select the machines that i want

1 Like
4

try changing the format to csv:

SELECT
zeit AS "time",
${Machine2:csv}
FROM machinenzustandstabelle
ORDER BY zeit
5 Likes
5

Thanks Daniel!
Your idea does work.
Would u mind explaining what this do? Is this formating something grafana offers, or is it from MySQL?

6

Grafana offer the posibility to change the form that the variables are interpolate.
I just thought that being specific could help. Now I see that in the documentation this is named.

Disabling Quoting for Multi-value Variables

Grafana automatically creates a quoted, comma-separated string for multi-value variables. For example: if server01 and server02 are selected then it will be formatted as: 'server01', 'server02' . Do disable quoting, use the csv formatting option for variables:

${servers:csv}

Read more about variable formatting options in the Variables documentation.

5 Likes
7

Thanks!
i was on that site a few times when searching for a solution, somehow i kinda missed it it seems.
have a nice day!

1 Like
8

Is there also a way to Show the average of the Value in $Machine2 over an interval?
I have trouble combining both.

9

change your variable to something like:

avg(column1),avg(column2),…,avg(column2)

and add other variable name interval,
and change the query to:

SELECT
$__timeGroupAlias(zeit,$interval),
${Machine2:csv}
FROM machinenzustandstabelle
WHERE $__timeFilter(zeit)
GROUP BY 1 
ORDER BY $__timeGroup(zeit,$interval)
1 Like
10

Good idea… I feel stupid now haha
thanks again Daniel!

1 Like
11

It’s a pleasure to be able to help!

12

sir, may I ask you about a similar topic to filter table with variable in Grafana???

1 Like
13

@farishalzaky dont ask to ask, just ask but maybe open a new thread