Ask Us Anything: How to Alias Dashboard Variables in Grafana in SQL
Recently a question came up from a customer, and I was surprised we didn’t have an easy answer for it:
How can you translate some esoteric ID or serial number, such as fe03-s3-x883, into a user-friendly name such as “harry” or “alice”?
In a regular templating language, it would be easy to do via a map file or similar, but to do this with Grafana is a little more complicated. If you’re using SQL (which Grafana now supports), there’s an easy trick to get this done.
The customer was asking in the context of repeating over a row, but the following answer applies for any variables.
Let’s assume some contrived, but simple, data to explain the concept.
| service_id | display_name |
|---|---|
| x0032s24 | Alice |
| x123s997 | Bob |
| y83l93 | Charlie |
We need to store this data in a datasource that Grafana can access. I used a MariaDB/MySQL database, but Postgres will also work.
In my lab environment, I just did a quick install of the MariaDB packages on my Grafana server and used the test database with no authentication. In production you would of course set things up with more security in mind. Pay particular attention to the warning message at the bottom there.
Next, we’ll put the data into the database. If you’ve got more than a handful of entries, look into the many ways of getting data into MySQL tables.
So, now we have the data in there we want.
In your dashboard, go into the Dashboard Settings menu and the Variables section and click new for a new variable and fill it in similarly to what’s below.
The critical bits are in the query. Whatever database column is assigned as __text is used whenever the variable is displayed and whatever is assigned to __value is used as the actual value when Grafana makes a query. The documentation of the mysql data source has a bit more information on what you can do with this.
Let’s try it on a simple dashboard.
Here you can see that although the dropdown is showing the display names we want, the query variable, $service_id, is actually using the value stored in the database to do the query.
It works for the graph title also.
And you can even loop over it.
Got a question for us about monitoring best practices? Email us at help@grafana.com.
