Creating Query Variables

In the previous lesson, you created your first Grafana dashboard with multiple panels monitoring various metrics. You learned how to write queries that Grafana understands and how to organize visualizations on a single screen. However, if you look closely at your queries, you'll notice something that limits their usefulness: hard-coded values directly in the query filters.

Imagine you're building a dashboard to monitor web traffic across different pages of your website. You create a panel showing the number of hits for your homepage by writing WHERE path = '/' in your query. This works fine for that one page, but what happens when you want to check your login page, product pages, or API endpoints? You have two options, and neither is ideal. First, you could create separate panels for every page you want to monitor—one for /, another for /login, another for /products, and so on. With dozens or hundreds of routes, your dashboard would become impossibly long and difficult to navigate. Second, you could keep the hard-coded WHERE path = '/' value, but that means you can only see metrics for the homepage. To check another page, you'd have to edit the query, save the dashboard, and repeat this process every time you need to switch perspectives.

The solution to this problem is template variables. Template variables transform your static dashboards into interactive ones by adding dropdown selectors at the top of the screen. Instead of hard-coding values or duplicating panels, you create a single set of panels that dynamically filter based on user selections. When someone selects "/products" from a dropdown menu, all panels on the dashboard instantly update to show traffic data for that page. This approach scales effortlessly, whether you're monitoring three pages or three hundred.

Now let's look at the table where this web traffic data is stored. Your PostgreSQL database contains a table called web_traffic that logs HTTP requests to your website. Here's what the table structure looks like:

CREATE TABLE public.web_traffic (
  ts timestamptz NOT NULL,
  path text NOT NULL,
  hits int NOT NULL
);

Every minute, this table records how many requests each page on your website received. If you were to query the most recent data, you might see results like this:

The ts column contains the timestamp, the path column shows which page was accessed (like /, /login, or /products), and the hits column counts how many requests that page received during that minute. Your website has seven main pages being tracked, and new data arrives every minute for each page.

To visualize traffic for your homepage, you would write a query like this:

SELECT ts AS "time", hits AS value
FROM public.web_traffic
WHERE path = '/'
AND $__timeFilter(ts)
ORDER BY ts;

This query works perfectly for the homepage. But here's where the limitation becomes clear: the WHERE path = '/' clause is hard-coded. If your site has seven different pages and you want to monitor all of them, you'd need seven separate panels with seven nearly identical queries, each differing only in which path they filter for. As your site grows to dozens or hundreds of routes, this approach becomes completely unmanageable.