Key -> value style for custom template variable configuration and usage

Grafana MySQL
1

Current implementation of custom template variable in grafana only expects cofiguration as a comma separated list of values, which is used later in dropdown widget ( component).
This works nicely in simple cases, but not if one wants to display user-friendly text in dropdown and use internal var value in queries and human-friendly - in label or title.

Proposal: Instead of this sample definition of template variable $timeshift:

{ 
        "hide": 0,
        "includeAll": false,
        "label": "Days ago",
        "multi": false,
        "name": "timeshift",
        "options": [
          {
            "selected": false,
            "text": "7",
            "value": "7"
          },
          {
            "selected": true,
            "text": "14",
            "value": "14"
          }
        ],
        "query": "7, 14",
        "type": "custom"
}

I’d like to have something like this:

{ 
        "hide": 0,
        "includeAll": false,
        "label": "Days ago",
        "multi": false,
        "name": "timeshift",
        "options": [
          {
            "selected": false,
            "text": "1 week",
            "value": "7"
          },
          {
            "selected": true,
            "text": "two weeks",
            "value": "14"
          }
        ],
        "query": "7: 1 week, 14: two weeks", //possible format for key-value list string
        "type": "custom"
}

And then use $timeshift.value in query like:
SELECT something AS metric_[[timeshift.text]]_ago FROM table WHERE d>now()-[[timeshift.value]]

"query": "7, 1 week; 14, two weeks", //alternative format for key-value list string definition in form

Ultimately, this might be extended to have required standard (value, text) attributes plus optional arbitrary attributes in “options” entry:

        "options": [
          {
            "selected": false,
            "text": "1 week",
            "value": "7",
            "some_custom_attribute": "This is a custom attribute",
            ...
          },
          .....

and “query” = some stringified version of “options” if needed.

Existing widget allows to enter values not listed in options and use text=value. With a proposed extended format missing attribute can be set to an empty string or entered value string in such case.

2

I think you can do what you want already. If you use MySQL as the data source for your template variable then you can do something like this:

SELECT '1 week' AS __text, 7 AS __value UNION SELECT 'two weeks' AS __text, 14 AS __value

image
image781×833 44.9 KB

1 Like
3

Thank you, this looks like a partial solution though.
Which version of Grafana supports this template var syntax?
Does it work with MySQL datasource only?
What to do if I don’t have MySQL datasource?

I tried
SELECT '1 week' AS __text, 7 AS __value UNION ALL SELECT 'two weeks' AS __text, 14 AS __value
with clickhouse datasource in grafana v4.4.1 and it doesn’t seem to work as expected: I got 4 separate entries in dropdown (1 week, 7, two weeks, 14) instead of 2.

Template var interpolation for $myVarName.__text or $myVarName.__value does not work too.

4

  • It only works with the MySQL data source (although Clickhouse could easily implement this feature) which was released in Grafana 4.5. Postgres which will be included in 4.6 will also support this syntax.

  • You can create a dummy MySQL data source, you do not need a db.

  • when using the selected template variable, then you can only use the value. So if you have selected ‘1 week’ in the template variable drop down, then $myVarName will be 7.

Hope that clears everything up. Not sure if that is exactly what you are looking for.

5

Interesting stuff.
But… when I create a dummy MySQL datasource, with no actual db, I get errors like dial tcp 127.0.0.1:3306: getsockopt: connection refused . What should I do to make it work?

Using Grafana v4.5.2 (commit: ec2b0fe)

6

Oh, you are right - sorry about leading you down the wrong path. You need access to MySQL or Postgres to execute the query obviously.

We should instead add something similar to the custom template variable that allows you to define key/value pairs.

7

Thank you for the clarification.

8

I have the same request for SqlServer. I would like the the drop down list with the __text while in the metric script it is $timeshift.value.

I

9

This worked like a charm!! Thanks!

10

After define the mytest query variable, how can i access it in my sql?
For example: select mytest[’$param’] from … where field1 = $param;

11

Any news on if/when key/value pairs can be expected?

1 Like
12

Thanks very much. Finally ,I found the solution .I’m very excited

13

thanks very much. You helped me successfully

14

I concur that it would be really nice if this was available for other data sources but even having key/value pairs in the custom template variable would be super useful.

15

Is there any update on this? I don’t have the option to create a dummy SQL of any kind as this is on a corporate central DB. Is there a way to use key->value in custom?

16

There will be in grafana 7.3 option to add custom variable as key-> value