How to us Templating variable 'All'?

Grafana
1

Note: Using SQL Server
Grafana 5.4.1 (testing 6.2 so if this is a possibility after the upgrade we will speed up proofing to get this on our production server)

With templating, is there a way to have a variable depend on another variable that has the ‘Include All option’ option enabled?

For example say variable1 is set up as such:

SELECT DISTINCT [Name] FROM table

And then I enable the all option via grafana.

I want to have variable2 set up something like:

SELECT DISTINCT [LastName] FROM table WHERE [Name] = $variable1

But I get an error because All passes in a list for $variable1…

Essentially, I’m trying to create a dashboard that has row repeat for variable1 and then within each row there are other panels based on variable1. How can I do that?

2

I dont know on SQL server, but on MySQL and PostgreSQL is something like:

SELECT DISTINCT [LastName] FROM table WHERE [Name] IN($variable1)

If you set “Custom all value” to "All" (better performance)

SELECT DISTINCT [LastName] FROM table WHERE $variable1 = "All" OR  [Name]  IN($variable1)
1 Like
3

The problem with doing that is I would retrieve every [LastName]…

For example imagine with this table:
image

SELECT DISTINCT [LastName] FROM table WHERE [Name] IN ($variable1)
Would give me every single entry in [LastName] because it is effectively doing:

SELECT DISTINCT [LastName] FROM table WHERE [Name] IN ('Paul', 'Adam', 'Bobby', 'Margaret')

This is because $variable1 is set to ‘Include All Option’… Doesn’t work as far as I can see that way.

I don’t understand what you mean by the second query.

4

Sorry i dont understant to you, can you explain more detail?

5

I’ll give real examples because maybe that will make more sense:

I have this dashboard that pulls in batterynames (variable!) and I am trying to just list all the apis associated with each batteryname but the problem is, to have the repeating row functionality based on batteryname I need to enable the ‘include all option’ which means I can’t get a specific list of apis associated with each battery (see images)
image

image

It’s because my query is SELECT DISTINCT [api] FROM [table] WHERE Battery = $battery

Which is then translated to SELECT DISTINCT [api] FROM [table] WHERE Battery = LISTOF_ALL_OF_THE_BATTERYS (see imagine below for screenshot)

image
image920×74 6.14 KB

And doing SELECT DISTINCT [api] FROM [table] WHERE Battery in (LISTOF_ALL_OF_THE_BATTERYS) doesn’t help me because then I just have a list of APIs that don’t map to the correct batterynames…

6

If I am understanding well, I would say that you should consider the following:
when you activate the “repeating row”, option the variable $battery is not translated in the same way, and within each row $battery is translated as the name of the battery. That said, you could change your query to something like:

for variable query

SELECT DISTINCT [api] FROM [table] WHERE Battery in($battery)

for metrics

SELECT value, metric, time FROM MetricsTable Where baterry = $battery and api in($api)
2 Likes
7

I test with the following:

CREATE TABLE `TestA` (
	`ID` INT(11) NULL DEFAULT NULL,
	`Text` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci'
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
;

CREATE TABLE `TestB` (
	`ID` INT(11) NULL DEFAULT NULL,
	`A_id` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
;

image
image1307×902 49.4 KB

image
image1289×889 48.4 KB

image
image900×641 15.8 KB

8

That is very interesting but I’m going to be honest I don’t understand the sql command you’ve done with the SELECT ID __value, Text as __text. What is this doing?

1 Like
9

This is a feature of grafana

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value . The __text column value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allows you to have a friendly name as text and an id as the value. An example query with hostname as the text and id as the value:

10

Oh ok I will take a look at that.

From your example, I’m trying to figure out if the TestB values are specific to the TestA id or not?

For example:

$ TestA : 1

$ TestB : {1,2,3,4,5,6}

$ TestA : 2

$ TestB : {1,2,3,4,5,6}

TestB is showing the same values which is my issue with the doing this the way I was previously doing it. The second variable doesn’t seem to actually be specific to the first variable.

Maybe I’m misunderstanding though?