mysqlregexgrafanagrafana-templatinggrafana-variable

Use variable in MYSQL query Grafana Returns Column Name instead of metrics

I have created a variable named $mypair in Grafana, I have added some options manually and I can view those in dashboard. I select an option (example: AUDCAD).

In query part of Graph settings I am entering the below query:

SELECT UNIX_TIMESTAMP(time) AS time, LEFT([[mypair]], 3) FROM allpairs

What I am trying to accomplish from the above query is actually below query

SELECT UNIX_TIMESTAMP(time) AS time, AUD FROM allpairs

I am trying to get first 3 letters from the $mypair variable so I can construct the graph. But I am not successfull. When I set the first query on the query box of graph settings, it gives a table in following format

2020-09-04 02:00:00    AUD
2020-09-04 03:00:00    AUD
2020-09-04 04:00:00    AUD
2020-09-04 05:00:00    AUD
2020-09-04 06:00:00    AUD
2020-09-04 07:00:00    AUD

But what I need is use first 3 letters of $mypair variable selecting the column so the output is as follows

2020-09-04 02:00:00    63
2020-09-04 03:00:00    63
2020-09-04 04:00:00    62
2020-09-04 05:00:00    62
2020-09-04 06:00:00    62
2020-09-04 07:00:00    60

I would appriciate feedback.

Solution

  • Actually I have used Views in mysql and accomplished what I want.

    In my case I have created a view as follows

    CREATE VIEW `AUDCAD` AS SELECT UNIX_TIMESTAMP(time) AS "time", `AUD`, `CHF` AS FROM `allpairs` ORDER by time ASC;

    Then used the below query in Grafana

    SELECT * FROM $mypair