Hi,
So If I have two tables in my MySQL DB as the pictures attached, is it possible to have a variable where I can query the two departments (or choose from the dropdown menu either 1 or 2)?
I tried to do it with MySQL with no success, and the documentation doesn’t provide examples for MySQL variables.
So in short, I want to create two variables as follow:
-Variable (drop down menu) and query to choose different tables, where in my graph I can combine and compare for example (value1_dep2.department2 and value1_dep1.department1)
-Variable (drop down menu) and query to choose the year, so I can compare the same table value for different years for example (value1_dep1 in 2019 vs value1_dep1 in 2020)
Maybe create a “view” within MySQL which is a union of the two tables, then within Grafana you just reference the view which is in effect a virtual single table, so it might be something like this:
create or replace view departments as
select 1 as department,
id as id,
date_dep1 as date_dep,
value1_dep1 as value1,
value2_dep1 as value2,
value2_dep1 as value2
from department1
UNION
select 2 as department,
id as id,
date_dep2 as date_dep,
value1_dep2 as value1,
value2_dep2 as value2,
value2_dep2 as value2
from department2
Then you can query you view just like a table with where clauses however you want.
If you don’t want a view you can do the same thing directly within your sql queries using subselect and a union like the example above.
If you have a single view then you can define variables using that single view (e.g. a department variable as select distinct department from departments order by 1)
Do that for all your variables and if your query is using the view then the users can select whatever they want and have the results from the view.
Thanks codlord,
Assume I have all the data in one table, so no views needed, how and what to write in the variable field and link it to the MySQL query in the panel? this is actually where I’m failing to make it, and if it happens there is an online example for that it would be great ( where I can see both SQL query and the variable query), just like other data sources but I couldn’t find any.
Thanks for your patience, I’m somehow new to grafana.
