Templating a different MySQL value

Grafana Configuration
1

Hi all, so I’ve created templating to get a name from a column, which works fine as it’s a select statement that brings up the list.

What I’m wanting to do now is to actually change the value being selected as a template

Eg:

SELECT
  UNIX_TIMESTAMP(r.created_at) as time_sec,
  r.`rsi_5_min` as value,
  e.name as metric

for r.rsi_5_min I have different time periods here such as 5, 15, 30 min and 4hour and 1 day.

I’d like for this to be a template that the user can choose on the Dashboard instead of having to create 5 separate dashboards to have the different interval options.

Is this possible? As I can’t seem to find a way to bring this up as it’s not the results of an sql statement

I am using Grafana 4.6.2 and MySQL as a main datasource.

Thanks a lot.

2

If I understand correctly, you want 1) a template variable with hard coded values “5, 15, 30 min and 4hour and 1 day” and 2) you want to use it in your sql query.

  1. Create a custom variable:

image
image568×630 28.8 KB

  1. Use it in the where clause (the template variable is called custom but you can name it to what ever you want):
SELECT
  UNIX_TIMESTAMP(r.created_at) as time_sec,
  r.`rsi_5_min` as value,
  e.name as metric
FROM ...
WHERE $__timeFilter(r.created_at) AND r.`rsi_5_min` = $custom
3

Thanks for the reply. it is much appreciated.

What I’m after specifically, is for the SELECT statement

I have different tables in my DB with different values, eg rsi_5_min, rsi_15_min etc. I’d like to be able to change what’s in the SELECT query through the use of templating which does a different query against my database.

Is this possible currently?

Thanks in advance

4

Did you test this? Yes, it works. The only thing to think about is that the template variable should not be multi-value otherwise the variable value is wrapped in single quotes.

Template Variable:

image
image579×614 31.3 KB

Query:

image

5

Thank you so much. I was playing around with it but I didn’t even know where to begin as I’m having a huge learning curve with migrating what I have in MySQL and Python scripts to Grafana. Love the tool a lot though which is why I’ve come here for help.

1 Like
6

Writing queries for MySQL is a harder than using a specialized Time Series database as SQL is more general and requires more massaging to get the data into the time series format.

7

Hi @daniellee, is there a way to query for template varibles getting from MySQL database?

8

I’m not sure if it is included in the latest release. Support for template variables is in the latest nightly builds and it will be included in Grafana 5.0 which we are releasing in the next few weeks:

http://docs.grafana.org/features/datasources/mysql/#templating

1 Like
9

Thank you for your response

10

Is there a way I can modify complex SQL queries to have them display graphs accordingly in grafana? Say for instance if I would want to loop over certain number of constraints.

11

Hello Daniellee,
i am currently using a variable in the exact same way as in your second example, also using MySQL.
And as u described here, the query does not work if i select “multi-value”. Unfortunatly this is what i Need.

Also i found a strange behaviour. Steps to reproduce:

  1. turning on “multi-value”
  2. Selecting all values, so that i should see the Graphs for every value. (it doesnt Show any because of Errors)
  3. Turning off “multi-value”

Now, even tho i disabled multi-value, all values are still selected. And also i can see the correct graph for all selected values in my variable. This is exactly how i want it to look, only Problem is that i cant select multiple values now.

my SQL-Query shortend:

SELECT
  zeit AS "time",
  $Machine2
FROM machinenzustandstabelle
ORDER BY zeit

where $Machine2 variable that has the possible Values “Machine1, Machine2, Machine3, Machine4”

hope u can help me here, and sorry for bumping an old thread

Kind regards

12

What are the errors and what is the generated SQL?

$Machine2 is probably generating a comma separated string with quoted values like this:

image
image1072×340 20 KB

You can control the formatting of variables. So if I just want comma separated without the quoting then use the csv format option: Variable syntax | Grafana documentation

image
image1090×588 28.4 KB

1 Like
13

Thanks danielle, this is the solution that worked for me.
DanielGonzalez already gave me this hint, thanks!