How to use Grafana variables to make more interactive dashboard visualizations (aka a lot less boring, a lot more useful)

Learn how to add features that allow you, your teammates, and your stakeholders to drill into specific details, see all results, and quickly get the info you need.

The (all too common) Problem: Boring, kind of useful, static dashboards

Those of us that work with data often want to make useful dashboards that make it easier for ourselves and other people within our team and organization to make sense and get insight about the data we collect.

A common problem I’ve run into (both when creating dashboards and using them as a stakeholder) is that many dashboards aren’t interactive enough for non-technical stakeholders to answer their questions without asking engineers to write new code or change the underlying queries powering the dashboard.

Or worse, stakeholders try to dig into the code and accidentally break things...

Solution: Make your dashboards interactive (and user-friendly!)

Fortunately, many visualization tools have features to make your graphs, maps, tables, and other visualizations interactive via the tool's native UI. It’s a win-win situation: more usability for the stakeholders who rely on your dashboards, and less of your time spent attending to minor customization changes.

Here’s an example in Grafana, an open-source visualization tool, where I’ve created “pickers” (filters) that allow me and others to choose values from a drop down menu and immediately see our selections appear in the visual - without having to change any of the underlying SQL queries powering the dashboard.

However, enabling features like pickers in the Grafana UI can be tricky.

In the rest of this post, I'll show you how to use Grafana’s variables feature to build your own interactive dashboards. I’ll use the example of monitoring the live locations of buses going on different routes in New York City to illustrate, but the steps I follow will work for any scenario.

Try it yourself: Implementation in Grafana

Ready to learn how to use variables in Grafana dashboards, powered by PostgreSQL queries?

Pre-requisites:

• Grafana instance
• PostgreSQL datasource with TimescaleDB enabled, connected to your Grafana instance. See here for how to connect one.

I’ll use the example of visualizing the real-time location in New York City, using data from the Metropolitan Transportation Authority.

I have an existing Grafana World Map panel setup, pictured below. To replicate my initial setup (I use PostgreSQL with TimescaleDB enabled as my datasource), you can clone and follow the steps in this GitHub repo.

• Panel with visualization, using PostgreSQL as the data source.

Here’s what my panel looks like before we make it interactive.

• You can download the JSON to replicate the dashboard in this GitHub repo.

Here’s the SQL query I used to generate the data for the above panel:

SELECT
  max(time) as "time",
  vid AS "vehicle_id",
  route_id,
  CASE WHEN route_id LIKE 'M%' THEN 1
       WHEN route_id LIKE 'B%' THEN 2 
       WHEN route_id LIKE 'Q%' THEN 3 
       WHEN route_id LIKE 'S%' THEN 4 
       ELSE 0
   END AS "color",
  ST_X(geom) AS "longitude",
  ST_Y(geom) AS "latitude"
FROM mta WHERE time > now()-interval '1.5 minutes' 
GROUP BY vid, route_id, geom 
ORDER BY 1;

In this query, I use the variable color to distinguish between different types of buses based on their route. There are 4 types of bus routes: M, B, S, and Q - corresponding to New York’s boroughs of Manhattan, Brox and Brooklyn, Staten Island and Queens.

Then, we use Grafana’s threshold settings to assign each bus type to a color:

Notice that there are 5 colors - one for each bus route M,B,Q and S, as well as one for routes that don’t fall into those categories.

While the standard static panel tells us the live location of the buses, there’s not much that we can do to interact and explore the data more, apart from zooming in and out.

Let’s change that by creating a variable to alter which bus routes we display on the map.

Step 1: Create a variable as a query

Our goal here will be to create a variable that controls the type of buses we display in the visual, based on the bus’ route.

For simplicity sake, let’s define 4 types of bus routes: M, B, Q, and S.

Grafana includes many types of variables, and variables in Grafana function just like variables in programming languages. We define a variable, and then when we reference it, we’re referring to the thing we defined the variable as.

To create a new variable, go to your Grafana dashboard settings, navigate to the Variable option in the side-menu, and then click the Add variable button.

In this case, we use the Query type, where our variable will be defined as the result of an SQL query.

Under the General section, we name our variable route. Then, we assign it the label of “MTA Bus Route.”

Labels in Grafana are the human readable descriptors that appear next to your dashboard’s drop down (picker) menu (see below image).

Secondly, under Query options, we define the query that will define the variable.

Here, we select our variable’s data source, which is the database that the query will execute against. In this case, we use “MTA Bus DB”, the PostgreSQL database that houses our MTA Bus data.

Now, we define a SQL query whose results will define our route variable

SELECT * from (values ('M'),('B'),('Q'),('S')) v;

This query returns the letters M, B, Q, S, which are the types of buses, based on their route_id (for a refresher, see the SQL query in the prerequisites section).

We could also use a query that’s more advanced, but gives the additional benefit of human readable names rather than using symbols or acronyms:

SELECT k AS "__text", v AS "__value" from (values ('Manhattan','M'),('Bronx/Brooklyn' ,'B'),('Queens','Q'),('Staten Island','S')) v(k,v);

Here, we define four key value pairs to be the set of possible values for our route variable, where the key is the human readable name of the bus type and the value is the letter corresponding to the route type.

• As an aside: This variable could also have been of type Custom, as we have a static list of values that never changes, allowing us to specify the values directly without the need to specify them through SQL.
• However, if you want the mapping from key to values, in order to express both symbols and human readable names, a SQL query is required. I’ve used a SQL query in the example, since in practice you often want variables to take on values that aren't hard-coded but that change based on data in the database, such as customer names, cluster names, etc.

Next, let’s define how we select our variable.

Since we want to see many different types of buses, we enable the multiple selections option, as it’s reasonable to want to see many different types of buses at once.
We also want an “All” option to quickly select all the bus types, rather than selecting them one by one.

• Sometimes you might not want to select multiple options – like if you're selecting metrics from different databases.
• But, in this case, I find it valuable to see data from multiple bus routes at the same time.
  To see a preview of the resulting options, we scroll to Preview of Values. In our case, we have ‘All’, ‘M’, ‘B’, ‘Q’, ‘S’, which are the options we want (i.e., our 4 individual bus routes and the “select all” option).

Step 2: Modify your graph query to use your new variable

In this step, we modify our query to use the variable we created in Step 1.

For a SQL query, we do this by modifying the WHERE clause to filter out undesirable results. In our case, we want to show only the bus types that are selected through the drop down menu picker in the UI.

Here’s the modified query:

SELECT
  max(time) as "time",
  vid AS "vehicle_id",
  route_id,
  CASE WHEN route_id LIKE 'M%' THEN 1
       WHEN route_id LIKE 'B%' THEN 2 
       WHEN route_id LIKE 'Q%' THEN 3 
       WHEN route_id LIKE 'S%' THEN 4 
       ELSE 0
   END AS "color",
  ST_X(geom) AS "longitude",
  ST_Y(geom) AS "latitude"
FROM mta WHERE time > now()-interval '1.5 minutes' 
AND substring(route_id,1,1) IN ($route)
GROUP BY vid, route_id, geom 
ORDER BY 1;

The relevant part of the query, where we reference our new variable by the name we defined in Step 1, is this line:

WHERE time > now()-interval '1.5 minutes' 
AND substring(route_id,1,1) IN ($route)

This line says that our visualization displays only if the first letter of its route_id is in the set of allowed routes (as selected by the user through the drop down picker, defined by the route variable). The values selected in the picker will define what our $route variable will be.

For example:

• If a user selects all routes then route = (M, B, S, Q), the set of all the types possible.
• But, if a user selects only the M and B routes, then route = (M,B) and we automatically filter out S and Q buses and display only buses with route_id starting with M and B.

Once we’ve modified the query, we save our changes and check if our variable works as expected.

To do this, we select various routes in the picker and verify that only our selections appear, like so:

As you can see, our map automatically changes the bus types we see on our NYC map, based on the selection we make in the drop down! You can also see the change takes place in multiple panels, since they all use the variable, $route in their query.

That’s it - we’ve successfully created an interactive Grafana visual using variables 🎉.

Learn More

Found this tutorial useful? Here are two more resources to help you build Grafana dashboards like a pro:

Grafana Webinar

Join me on April 22nd at 10am PT/1pm ET/4pm GMT where I’ll demo how to:

• Create 5+ different visualizations for data from IoT, infrastructure monitoring, and public datasets
• Combine various data types, including geo-spatial and time-series data, in our dashboards
• Take my demo and customize it for your project, team, or organization

I’ll focus on code and step-by-step live demos – and I and my dashboarding expert colleagues will be available to answer questions throughout the session, plus share ample resources and technical documentation.

Signup even if you’re unable to attend live, and I’ll make sure you receive the recording, slides, and resources - and answer any questions you may have along the way.

All-in-One Grafana Tutorial

We’ve compiled all our tutorials, tips, and tricks for visualizing PostgreSQL data in Grafana into this one doc.

You’ll find everything from how to create visuals for Prometheus metrics to how to visualize geo-spatial data using a World Map.

• Check it out here