Allow custom mapping of template variable value -> display text

[mbell697]

Use Case: You may store metrics based on an 'ID' property but wish to have the template variable selection UI use a more human friendly label. e.g. You track metrics by domain with an internal domain ID but wish to use the domain's URL in the template variable selector UI.

@torkelo I can take a cut at implementing this, what are your thoughts on implementation? For my specific use case I would want to be able to provide an arbitrary JS function to perform the value -> text conversion as I need to hit an external service for the lookup. I was thinking an initial implementation could be adding a config value in the dashboard JSON that defines the mapping function. UI support could be added later to handle more trivial mappings with pre built mapping functions (e.g. regex substitutions).

Also connected to this would be the ability to edit the full dashboard JSON via the UI, although export -> edit -> import would function as a workaround if this proves to be difficult.

[torkelo]

You could do this with scripted dashboards. But you are welcomed to try to implement it into regular/saved json dashboards.

[dongshengbc]

+1

[Iker-Jimenez]

I too could use at least a simpler version of this. Something like a configured mapping from A -> B

In my scenario I want to select an entity name in the variable dropdown (CustomerName1, CustomerName2, etc.) but use a numeric id internally when it comes to the metric name.
app.requests.$customer1_ID.count

[timgriffiths]

+1

[Krinkle]

Merging from #3138

E.g. when creating a custom template variable with values fooBar and baz_quuxInternal, to be able to have the UI display the checkboxes as "Foo bar" and "Baz".

This is especially common when using repeated rows and a custom variable to reduce duplication, but the top level metrics may not be user-friendly.

One could potentially support this for queried template values (e.g. graphite properties) as well by using a regex (if the replacement is generic). Regex support already exists, but it applies to both the value used and the label. Having it used for the value only would be valuable.

For example, if a graphite query expands kafka.messagesByTopic.myservice_* for use in templates, then one may want the user interface to strip the prefix. But when used in the actual panels, the prefix should be included. This can be worked around (in Grafana 2.x and later) now that template variables can be embedded in a metric property, so one could hardcode the prefix in all metrics in all panels and rows, but that's better to avoid.

Once these "label" values exist, it would be useful to be able to access them inside the panels as well. Such when embedding a variable in a Row and/or Panel title. Either we can make it use the label by default (if embedded in a title field), or perhaps with some alternate syntax (e.g. $$Variable, or some whatever)

[bluecmd]

http://play.grafana.org/dashboard/db/test?editview=templating shows "Variable Label" as an option. Can this be closed?

EDIT: I misunderstood the bug, sorry! :) Carry on.

[torkelo]

that is just an option to have a friendly name for the variable, not the variable values

[malnor]

+1

[rjromay]

+1

[m4dc4p]

+1

[aalleexxeeii]

+1

[tboeghk]

+1

[GautamGupta]

+1

[sbarale]

What's the status of this ?
Is it possible to do so ?

[cosmos78]

Is this possible for the 3.0 final ?
It looks like a feature many people are waiting for. Including me :-)

[musskopf]

+1
I normally use part of regular expressions which looks awful for users.

[redredgroovy]

+1

[srpatatas]

+1

[inbilla]

+1
Without this, regex expressions are unusable in template variables.

[vincenzomos]

+1

[blak3r2]

+1

[daaru00]

+1

[lucadistefano]

+1

[MikeKulls2]

I'm very surprised this hasn't been included - I spent quite a bit of time trying to figure out how do this "obvious" thing and eventually found my way here to discover it doesn't exist.

I have solved this by creating a MySQL database, I create a table with the items I want in the dropdown, eg Europe, North America etc. In a second field I have a regex that will match the entries I want to match. Then add the MySQL as a datasource and use them to create the variables. It is a hack but actually works quite well. I use it for pretty much the exact thing you are trying to do.

[johnhtodd]

I appreciate the clever hack, but for us it's not really a solution. Setting up an entirely new database (we don't use MySQL at all, which means operationally this is impossible) to perform a simple key/value substitution that is quite static seems like a lot of hoops to jump through.

I was thinking about this a bit more, and there is an even more elegant way of providing this functionality than what I describe above. I'd call it a "variable macro". This again looks like a Custom list, except it allows the administrator to specify that when one (or more) of these macros are selected, then the Variables named will be set, and the Values given will be appended to the existing set of values. This would entirely be a UI-driven model, and would not change the actual variable concept at all - it would just create an auto-completion sugar layer on top of the existing variables. This makes it backwards-compatible with no additional variables needed for creation or integration into queries.

A macro that sets the variables would allow the user to see the values as they're being selected, and then would allow the user to open up each variable and see/manipulate the selections or data instead of creating a separate variable as my previous comments imply. This would be much more intuitive.

Example:

So a variable macro called "North America - Primary Cluster" would set my "Country" variables to "us,ca,mx" and would set my "Cluster:" variable to "primary". Those settings would be visible if I were to pull down each named variable (or not, if they're hidden) so I could add or subtract countries to the Country: list as long as I didn't touch the Macro variable pulldown again.

Possibly there is a boolean of "clear named variables before setting" so that if a change is made in the picklist for this Macro, then any other settings of the variables specified would be cleared. This could be useful for lists where it's not obvious that you might include something that was previously set. I suppose that if more than one Variable Macro option were chosen, then the last one in the list to be examined "wins" if there are competing settings of a particular value; no way around that problem. (it's arguable that this clearing action should be specified on a variable-by-variable basis, but that seems to sound a little cluttered... but is it?)

Here's my hypothetical example again, where I have pre-existing variables of "Country" and "Cluster".

Variable Macro Name: Region

Name1: North America - Primary Cluster
Clear Named variables before setting: Y
Variable1: Country
Value1: us,ca,mx
Variable2: Cluster
Value2: primary

Name2: Nordics - Secondary Cluster
Clear Named variables before setting: Y
Variable1: Country
Value1: se,fi,no,dk,is
Variable2: Cluster
Value2: secondary

[MikeKulls2]

I appreciate the clever hack, but for us it's not really a solution. Setting up an entirely new database (we don't use MySQL at all, which means operationally this is impossible) to perform a simple key/value substitution that is quite static seems like a lot of hoops to jump through.

I was expecting you would say something like this. The reality is you asked for a hack and this hack does solve the problem, it's not difficult to setup and it's not difficult to reverse in the future. I actually find it quite handy having the MySQL there as we keep adding new data sets to it, it's a convenient place to keep track and update them. If you think about it, if you're going to use these data sets in multiple dashboards and want to have them maintained centrally, then they need to be stored somewhere. If your time series database can't store them then you need something setup to store them. So it actually makes perfect sense to have MySQL. The added bonus is its also very easy to automate the population of MySQL.

[GlennMatthys]

If you have PostgreSQL, you don't need to create an actual table for the mapping, you can do something like:

SELECT *
FROM
(
	VALUES
		('London server 1', 'london_srv_1'),
		('London server 2', 'london_srv_2'),
		('New York server 1', 'ny_srv_1'),
		('New York server 2', 'ny_srv_2')
) AS t (__text, __value)

[crodriguez-cl]

But it doesn't work with numeric values:

SELECT * FROM ( VALUES ( 'OK', '0'), ( 'ERROR', '1') ) AS t (__text, __value)

When loading dashboard:

And when selecting another parameter

When selecting: OK + ERROR

[GlennMatthys]

If you want numeric values you need to remove the single quotes, otherwise it is interpreted as text, ie

SELECT * FROM ( VALUES ( 'OK', 0), ( 'ERROR', 1) ) AS t (__text, __value)

[crodriguez-cl]

Thanks @GlennMatthys glenn for the answer, but i already found where the problem occurs.

SELECT * FROM ( VALUES ( 'OK', 0), ( 'Warning', 1), ('Critical', 2) ) AS t (__text, __value)

Configuring Multi-value on:

It happens

Selecting 3 states

And Multi-value off:

[fitovic]

GlennMatthys - your solution is perfect, many thanks

[BasvanH]

For MySQL its:
SELECT * FROM ( VALUES row('a', 1), row('b', 2) ) AS t (__text, __value)

[radoeka]

What about Mariadb? It is not working for me (mariaDB version: 10.5.5)

SELECT * FROM ( VALUES row('a', 1), row('b', 2) ) AS t (__text, __value);
ERROR 1064 (42000)..............

Or the one before:

SELECT * FROM ( VALUES ( 'OK', 0), ( 'Warning', 1), ('Critical', 2) ) AS t (__text, __value);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your 
MariaDB server version for the right syntax to use near '(__text, __value)' at line 1

It looks like the Values statement is no longer available...

Or is there something else that I do wrong?

[GlennMatthys]

@radoeka For older MariaDB/MySQL

SELECT * FROM
(
    SELECT 'London server 1' AS '__text', 'london_srv_1' AS '__value'
    UNION ALL SELECT 'London server 2', 'london_srv_2'
    UNION ALL SELECT 'New York server 1', 'ny_srv_1'
    UNION ALL SELECT 'New York server 2', 'ny_srv_2'
) AS t;

[radoeka]

@GlennMatthys wow wow wow, what a swift response! And this works.
I thought that I was running a rather new version of mariadb, but that is not case (using a linux distribution that is only 2 months old).
Thanks.

[nickdelnano]

This seems to not be supported for Prometheus.

[hugohaggmark]

Reopening this issue as #27829 only solves this for static data.

[McPo]

Requirements

• Given an ID I wish to display it as a friendly name to the end user
• It must work on all panel types (A Log Panel was used during experimentation)
• The ID may originate from anywhere (ie. We cant rely on having the user select a Dashboard variable with Name->ID mapping)
• The Name may come from any datasource (In my case MYSQL)

Heres how I solved this issue. Gonna list the "Journey" as different solutions might be useful to different people (and hopefully someone can improve on them).

Attempt #1

Problem #1

• Given two CSV lists (ie. id1,id2,id3 name1,name2,name3). Which may originate from Dashboard Variables (ie. {myids:csv} {mynames:csv}))
• Using https://grafana.com/docs/grafana/latest/variables/advanced-variable-format-options/ and https://grafana.com/docs/loki/latest/logql/template_functions/#replace-string
• Replace the ids in the id csv with their corresponding names

Solution to #1

• We have limited template functions, however if we managed to merge the two lists into a single key-value string we could use regex. (ie. id1=name1;id2=name2;id3=name3)
• If we had the above format we could do {{ regexReplaceAllLiteral \";.*\" (regexReplaceAllLiteral \".*${myids:regex}:\" \"${key_value_string}\" \"\") \"\" }}"
• It removes everything up to the id (and colon), then removes everything after the semi-colon. This can then be passed to line_format.

Problem #2

I want to use a datasource to get my key value pairings. so how will I generate the id1=name1;id2=name2;id3=name3 string?

Solution #2

• We could use a Query Dashboard Variable to pull the required information from the DB.
• We could then hide this variable so it isnt shown on the dashboard.
• However the query will return on multiple rows i.e

    id1=name
    id2=name
    id3=name

• And we would not be able to access any of the other pairing apart from the first one
• The solution to this is to use CONCAT AND GROUP_CONCAT i.e

SELECT
    GROUP_CONCAT(CONCAT_WS(':', id, name) SEPARATOR ';' )
FROM soloportal_device;

• We will have now generated the key-value string into a single row/option, which can be fed into the above regex. However theres a few "edge" cases that need solved

Problem #3

• GROUP_CONCAT has limited column width, as such the key-value string may be truncated

Solution #3

• A quick solution to this (So we can run into the next roadblock quicker) is to create a new hidden Query Dashboard Variable, place it above the key-value query and execute the following query
  SET SESSION group_concat_max_len = ~0

Problem #4

• If the key-value string is too large, you will hit a 414 HTTP error when returning to the Dashboard. This is due to the Query Variable values being added to the URL

Solution #4

• Here you might wish to filter the key-value pair to only return the mappings you need depending on the context
• I didn't want to do that
• Another option is to place the key-value string into __name and use {$key_value_string:text} instead, this way it wont be passed within the URL

(I only discovered the text filter while writing this issue, for some reason I kept skipping over it in the docs despite looking for it)

Problem #5

• If you clicked "Explore" on the panel, the Explore page would open with a very large regex in the query which isnt ideal

Solution #5

• I didn't like the fact we were using Loki to process the Regex, As such (and for all the nasty hackiness so far) I decided it was time to rethink my approach.

Attempt #2

• Ideally this feature would run after everything else and would simply replace the values within the DOM itself.
• If it worked as such, then we could use all the existing panels (No need to extend each one), we would not have large queries being fed into the datasources, and datasources would not have to implement their own solution.
• As such the only option was JavaScript.

Problem #1

• How to get the custom JavaScript to run

Solution #1

• I disabled the disable_sanitize_html option so that JavaScript could be added to Text Panel using the HTML option.
• Also removed the title from the panel, enabled transparent background, reduced its size as far as it could go and moved it to the top left corner under variables.

Problem #2

• How do we get the key-value mapping now?

Solution #2

• Initially thought about doing something similar to the above (using template variables).
• However on further inspection Grafana uses a HTTP API to query the datasource, GREAT!
• Created a Table Panel, entered my query, used Chromes Network Inspector, and copied the request as fetch.
• Removed any redundant information in the fetch request (Depending on your setup, it may not be so redundant).
• Created a script that used the fetch command, to return the mapping and using querySelector and Regex, replace the relevant sections of the page (Regex provides considerable performance boost over looping over the array)
• This was then entered into the text/html panel
• The script is below

<script>

function fetchDeviceUUIDMappingConfig() {
    return fetch("https://localhost/logs/api/tsdb/query", {
        "method": "POST",
        "headers": {
            "content-type": "application/json",
        },
        "body": JSON.stringify({
                "queries":[
                    {
                        "refId": "MAPPING",
                        "datasourceId": 2,
                        "rawSql": "SELECT id, name FROM mappings",
                        "format": "table"
                    }
                ]
        }),
    })
    .then(data => data.json())
    .then(json => json.results.MAPPING.tables[0].rows)
    .then(rows => rows.reduce((o, row) => ({ ...o, [row[0]]: row[1] }), {}))
    .then(map => ({
        rexp: new RegExp(Object.keys(map).join('|')),
        map
    }));
}

function remapDeviceUUIDPanel(mappingConfig) {
    document.querySelectorAll("#panel-4 tbody tr span").forEach(el => {
        el.innerHTML = el.innerHTML.replace(mappingConfig.rexp, id => mappingConfig.map[id]);
    });
}

// Fetch Remap Config
let deviceUUIDMappingConfig = null;
fetchDeviceUUIDMappingConfig().then(config => deviceUUIDMappingConfig = config);

// Remap On Changes
const obeserveEl = document.querySelector("#panel-4");
const observeConfig = { childList: true, subtree: true };
new MutationObserver(function(mutationsList, observer) {
    observer.disconnect();
    remapDeviceUUIDPanel(deviceUUIDMappingConfig);
    observer.observe(obeserveEl, observeConfig);
}).observe(obeserveEl, observeConfig);

</script>

• The script takes into account the panels changing (It will rerun when required).
• Overall the experience is very good, it quickly replaces all values before they are made visible (Im using 6000 key-value mappings, yes its overkill).
• No doubt it could be improved in areas. One improvement would be to wrap it into a generalised function remap("SELECT id, name FROM mappings", ["#panel-4 tbody tr span"])

[phemmer]

It looks like this feature should be addressed by the functionality that was added in 7.4 for regex captures: https://grafana.com/docs/grafana/latest/variables/filter-variables-with-regex/#filter-and-modify-using-named-text-and-value-capture-groups

For example, for a database source, you can select a column which is a concatenation of key and value, and then use the regex to split it back apart.
Query: SELECT CONCAT(foo, ' ', bar) FROM baz
Regex: /^(?<text>\S+) (?<value>.*)/

Are there any data sources where this doesn't work?

[bitfactory-henno-schooljan]

How would I do this with Prometheus where label_values() is normally used? I have a number of label values such as aws and gcp which I want to make more easy on the eyes in these drop down menus. I'm fine with a static mapping as I know which values will exist.

[hugohaggmark]

How would I do this with Prometheus where label_values() is normally used? I have a number of label values such as aws and gcp which I want to make more easy on the eyes in these drop down menus. I'm fine with a static mapping as I know which values will exist.

Doesn't this work for you @bitfactory-henno-schooljan https://grafana.com/docs/grafana/latest/variables/filter-variables-with-regex/#filter-and-modify-using-named-text-and-value-capture-groups

[SudoNova]

Please reconsider implementing this as it can compensate for Elasticsearch datasource's lack of filters issue:

      {
        "allValue": null,
        "current": {
          "selected": true,
          "text": "No",
          "value": "AND NOT _exists_: exception"
        },
        "description": null,
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": "Is exception?",
        "multi": false,
        "name": "is_exception",
        "options": [
          {
            "selected": false,
            "text": "Doesn't matter",
            "value": ""
          },
          {
            "selected": false,
            "text": "Yes",
            "value": "AND _exists_: exception"
          },
          {
            "selected": true,
            "text": "No",
            "value": "AND NOT _exists_: exception"
          }
        ],
        "query": "Doesn't matter, Yes, No",
        "queryValue": "",
        "skipUrlSync": false,
        "type": "custom"
      }

[Crinon]

If you have PostgreSQL, you don't need to create an actual table for the mapping, you can do something like:

SELECT *
FROM
(
	VALUES
		('London server 1', 'london_srv_1'),
		('London server 2', 'london_srv_2'),
		('New York server 1', 'ny_srv_1'),
		('New York server 2', 'ny_srv_2')
) AS t (__text, __value)

Thank you very much, I still don't understand why this feature is not built-in

[miguel-atenea]

If you have PostgreSQL, you don't need to create an actual table for the mapping, you can do something like:

SELECT *
FROM
(
	VALUES
		('London server 1', 'london_srv_1'),
		('London server 2', 'london_srv_2'),
		('New York server 1', 'ny_srv_1'),
		('New York server 2', 'ny_srv_2')
) AS t (__text, __value)

Thank you very much, I still don't understand why this feature is not built-in

It is now! (At least for static data), take a look at this PR

[st316]

If you have PostgreSQL, you don't need to create an actual table for the mapping, you can do something like:

SELECT *
FROM
(
	VALUES
		('London server 1', 'london_srv_1'),
		('London server 2', 'london_srv_2'),
		('New York server 1', 'ny_srv_1'),
		('New York server 2', 'ny_srv_2')
) AS t (__text, __value)

Thank you very much, I still don't understand why this feature is not built-in

It is now! (At least for static data), take a look at this PR

Excellent!

[Koriit]

I found a way to do this with Prometheus. The only limitation is that map keys must be valid Prometheus labels, so characters like - are not allowed.

For some reason, label_join still adds a separator even if source labels don't exist. Capitalizing on this behavior, we can add labels with values to vector(0) like this:

label_join(vector(0), "label", "value", "a", "b")

And more:

label_join(label_join(vector(0), "label", "value", "a", "b"), "label2", "value2", "a", "b")

Etc.

Unfortunately, you can't use Grafana's label_values on that, but fortunately, you can use variables in the regex field so you can then choose a value based on a label with this:

.*(?<label>${variable_name})="(?<value>.*?)".*

---

In my case, I wanted to select an env and for this choice to populate other hidden variables. Tricky.

First, my custom variable. Note that I can't use . because it's not accepted in Prometheus labels, so I'm replacing it with _, which is accepted.

Second, my hidden variable. Prometheus query from above needs to be wrapped with Grafana's query_result(...). No new lines are allowed or it won't work. Note how I use my previous variable in regexp.

And now I can use the hidden variable which gets automatically chosen when I select value for my first variable:

[SudoNova]

Please! This is a very needed and common request. Suppose that we have naming mismatch for different services in different data sources (e.g. MSSQL: MyService elasticsearch: my_service). We need to map between these two variables in a dynamic fashion (I'm not talking about value mapping in each dashboard, that's a heck of redundant work) .
If we can have this feature then 1to1 mapping of services in different data sources will be possible.
Note this is not feasible with chained variables especially in multi-option scenarios.