I am trying to make a dashboard that updates subsequent variables based on a previous variable. The issue is, I’m using a row repeat for the variable I am trying to base the subsequent variables off.
I’m running into an issue that since I have to enable the ‘All’ option to have the rows auto-generate, when I write the SQL for the next nested variable, I would typically just write the query as:
Select t.column1 from table t where t.column2 = $variable
That doesn’t work when you have an all parameter because the all option returns a list of all of the variables. I’ve had to change my query to:
Select t.column1 from table t where t.column2 in ($variable)
This returns an unordered list of all of the values from column1 that match on column2.
What I want to happen is I want each row to repeat with the variables from variable1 and then have a graph panel for the variable2 values that match each specific row variable1 in my table. Is that possible?
I think what you’d like to do is possible. Though from your question it’s not clear to me what it is exactly that’s causing you issues?
I assume that by variable1 and variable2 you mean column1 and column2. To do this you could create a template variable ($variable), which is based on a query like Select t.column1 from table t that returns all possible values contained in column1.
Then create a panel and set it to repeat for all values of $variable. Within that panel you can have a query like
Select t.column2 from table t where t.column1 = '$variable'
to get all values from column2 that correspond to the respective value of column1 used in that panel. By the way, it’s often useful to check out the query inspector if you’re seeing unexpected behavior.
To some of your other points:
Not sure where this query is actually being used? If it’s within a panel, then assuming that panel repetition has been properly set up (Variable syntax | Grafana documentation), $variable should just have a single value, rather than multiple values. Note that the value that $variable expands to in the query will in the general case be just the “bare” value, so you may need to enclose it in quotation marks for the above query to work.
If you want to use the above outside of a repeating panel, then in the template variable settings, when you enable the All option you can also set a custom all value (in the text box that appears). My SQL is rusty, but I believe you could set this to whatever wildcard value your SQL server recognizes, and use a LIKE operator instead of = in your query.
Is that not more or less the objective (though potentially with column1 and column2 reversed - at least based on the requirements you describe)? If the lack of order is an issue, you can of course address that by adding an ORDER operator to your query.
Just to follow up, maybe what you’re looking to do is have rows of panels for each value of column1, and then have side-by-side panels for each corresponding set of values from column2 in your table? If that’s the objective, then no, I don’t believe it’s possible to set up “hierarchical repetition” like that.
What you could do it have a selection of a desired value (or values) from column1 via a drop-down at the top of the dashboard, and then do panel repetition for all the corresponding values from column2.
Thank you for your help. Perhaps I’m misunderstanding but to use a variable to enable repeated rows you need to have the ‘All’ option enabled and then you need to select the all from the drop down for that particular variable to have the rows automatically repeat for all the variables.
I’ve done this to have the rows and titles of the rows change with the selection of a new variable. However there is another variable I need to use to query my opentsdb database to pull the RT data. The way I would normally query this second variable would be something along the lines of:
Select [param1] from [table] where [param2] = variable1 (the variable used to create the rows). The issue is when you have the ‘All’ selected to have the rows be dynamic when you query using that variable, a list is returned of those variables. So my original query for variable 1 is something like:
Select distinct [rownamevariable] from [table]
I tried the following code for variable2: Select [parameter1] from [table] where [rowname] = $rownamevariable
However the actual generated SQL query for this ends up being:
Select [parameter1] from [table] where [rowname] = {rowname1,rowname2,rowname3,rowname4...}
which doesn’t work. This is why I had to change my query to be in ($rownamevariable) rather than using an equal sign but now I get a list of all of the parameters with a rownamevariable for any of the rows on my dashboard not just a list of the parameters for each row separately if that makes sense.
Thanks for clarifying @damienclark. Reading the above, I’m oscillating between feeling that I get what the issue is, and feeling that I don’t get it. I think some of the potential misunderstanding is coming from the fact that it’s not clear whether the queries you’re describing are queries that you’re using to define template variables, or queries that are used in panels.
My current understanding is as follows:
You have a template variable $rownamevariable, which is used to determine the set of rows that will appear in the dashboard (like in the demo “Repeated rows” dashboard).
Each row that appears corresponds to a single value of $rownamevariable.
For each value of $rownamevariable there exists a list of values of another variable, $variable2, that you would like generated somehow. The set of values of $variable2 would be different for each row, based on the particular value of $rownamevariable for that row.
Within each of the rows you want to display data from OpenTSDB, which is filtered based on the set of values of $variable2 for that row.
I don’t believe the above - specifically as written - is possible, for the following reason: the template variable $variable2 will have a set of values that is “global” for the entire dashboard, rather than calculated on a row-by-row basis. So although the row-repetition engine will pick out an individual value of $rownamevariable that will be used in any queries within each row, the values of other template variables will remain constant across rows.
I haven’t tested this myself, but it’s my understanding of the way that template variable (re)calculation works.
I’d suggest either of the following alternatives:
You can of course create a single row of panels based on a single value of $variable1 (from a drop-down), and a corresponding set of values of $variable2. I know that’s not what you envisaged specifically; you’d still have access to all the data - just that it’ll be via a drop-down rather than repeated rows.
Find a way to not mix SQL and OpenTSDB queries. E.g. if your TS data was organized in a way that $variable1 could directly be applied a filter at the database engine level.
In general I’d say that Grafana isn’t an ideal tool for “chaining” queries from different datasources, the way you need to do here. While you’ll get some flexibility via template variables, Grafana will generally expect the database query engine to do most of the heavy lifting, e.g. in terms of pulling out the specific data you’re after. And when you’re relying on multiple different data sources to get you there, it gets tricky.
That is exactly what I was hoping to do. That is unfortunate that it won’t work. I will mess around with the queries and if worst comes to worst maybe have to combine everything into a single row. I’m not sure. I appreciate your help with this matter. It is unfortunate it isn’t possible since the queries would work if grafana didn’t return a list of all the rownamevariables when you select ‘All’.
Hi @stefaniradevska I’m actually just revisiting this with a colleague, since we have encountered a very similar use case internally (we also use InfluxDB). Based on our latest understanding, this is still not possible unfortunately. Again, for the simple reason that the values of template variables exist globally for the dashboard, and not separately for individual (repeated) rows.
@svetb directed me here as I’ve just started a new topic asking exactly the same thing, doh!
My scenario is: I want to display disk space information for different servers and have a graph per disk, per Server.
I thought the thing to do would be have a row that repeats for each server that is in a variable.
Then in the row have a repeating graph for each disk that is on the server using a separate variable. The problem is my disk variable has no visibility of the current server in the row so just returns all disks for all servers.
I see from the discussion above it is not possible which is frustrating.
Hi, I’m facing with a similar problem, if not the same one. I’m trying to find a solution with last release 8.0.6, but apparently with no success.
Basically I have two variables:
Variable1 on which is set the repeat panel option, and variable2 that can have a different value based on Variable1 value.
the problem is that on all the repeated panel variable2 has always the same value, even if of course Variable1 is different.
I expected that, since variable1 changes, even the related variable2 would change.
Variable2 returns the table’s name I need to use with a mysql select qwery within the panel
Is there any update on this topic?
What I don’t understand, and that could help to find a solution, is this (as template variable):
if you use these queries
SELECT $myvariable or SELECT ${myvariable} you get all possible values as expected.
But if you use something like this
SELECT column FROM table WHERE my_need = $myvariable
it doesn’t work. This return nothing. The only way I found is to use the IN() function and variable syntax with option. Which means something like this:
SELECT column FROM table WHERE my_need IN(’${myvariable:csv}’)
Here of course ‘${myvariable:csv}’ return a single value which is a string with all myvariable value separated by comma
@donatod to your first question, I’m not aware of there having been any updates since the previous posts. Fundamentally, the values of dashboard variables exist on a dashboard basis, and not a per-row basis. So even if you have rows repeated for each value of variable1, another (dependent) variable like variable2 will not have different values for different rows.
As to your second question, I’m not sure it’s directly related to the topic of this thread. You just need to make sure your query has correct SQL syntax. Something WHERE my_need = $myvariable basically just doesn’t work if $myvariable has multiple values - that’s down to SQL, not Grafana. Using IN() is indeed the correct way to implement the condition, as you found
At this point there is no chance The only solution is to have the panel queries based on the multi value variable used for the repeating panel option.
At this point there is a way to have its value as non string, I mean without quote?
I make several test with various option but nothing.
Basically mysql doesn’t accept " select column_name from ‘my_table’ ".
And the table name syntax is the goal and the problem. The only way to achieve what I need is to combine the value returned by the variable.
To be more clear, let’s picture I need to pick up values from tables named table_1, table_2 and table_3. I could set myvariable as multi value so that can be 1, 2 or 3. Than within the panel, as table name, I could combine table_ with myvariable value.
Can you imagine any solution?
Hi @donatod I can’t really tell if what you’re asking is related to the original topic. If it’s not, could you creat a new thread, so as to keep the discussion here related to jested variables?
To answer briefly: (a) yes, you can certainly have a query like SELECT x FROM table_${table_number}, where table_number is 1, 2, 3, etc; but (b) table_number can’t be a multi-value variable since you obviously can’t have a SELECT statement that queries multiple tables at once (at least not with this syntax). Maybe you’ve misunderstood what it means for a variable to be multi-value? I’d love to try and help, but I’m a bit lost as to where this is going, I’m sorry.
Thanks. I I thought to continue here because I see a relation to the original topic, as nested variables, as row replication and as database.
Anyway I’ll make some more test with your suggestion. Tell me if you think is better to open a new topic
Please help to configure Grafana dashboard with nested variable to proper save data to corresponding fields(keys/values). my data is in .json format.
In header it has value of jenkins job date and sub headers are: hits, total, coverage columns.
Three rows merged and named as projects/repo then each three rows have named: lines, functions, branches.
My concern is that data is not getting properly fitted in rows/columns project wise/date wise.
Shared the screen shot of table which is in html format and I want to see a similar kind of format or implement filters to show the correct data in table in Grafana dashboard.
The only solution is to have the panel queries based on the multi value variable used for the repeating panel option.