Using Variables to dynamically select Columns in MySQL

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.

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.

Thanks in advance and kind regards

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

This is how it looks when its not working.

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

try changing the format to csv:

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

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

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

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

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

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

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

1 Like

It’s a pleasure to be able to help!

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

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