How to Create a New Drop-Down Menu/Filter (Query Variable) in Grafana Based on PostgreSQL Table Column
Grafana is a popular open-source data visualization and analytics platform that enables creating dashboards and monitoring metrics from various data sources, for example, a database.
One of the useful features of Grafana is the ability to create drop-down panels that enable selecting specific values to filter data in other panels on the dashboard.
If there is a timestamp column on a database table and we are choosing the
time series metrics, by default, we get a drop-down filter of selecting data for the last few minutes to months.
In this tutorial, we will see how to create a drop-down panel in Grafana that offers the unique IDs of a column in a PostgreSQL table and use the selected ID to show graphs based on SQL queries.
- Grafana installed on your machine (usually, port:3000).
- A PostgreSQL database with a table that has a column with distinct IDs (in my example, there was a column containing unique device IDs, I wanted to pull data based on selected device ID from the drop-down menu)
- A data source in Grafana that connects to your PostgreSQL database
Step 1: Create a Query Variable
The first step is to create a query variable in Grafana that retrieves the unique device IDs from your PostgreSQL table. Here’s how:
- Open your Grafana dashboard and click on the “Variables” icon in the top navigation bar.
- Click on the “New” button to create a new variable.
- Choose “Query” as the type of variable.
- In the “Data Source” field, select the data source you have configured for your PostgreSQL database.
- In the “Query” field, enter the following SQL query to retrieve the unique device IDs:
SELECT DISTINCT "device_id" FROM mytable
- Replace “mytable” with the name of your PostgreSQL table and “device_id” with the column name containing the unique device IDs.
- In the “Refresh” section, select “On Time Range Change” to ensure that the options in the drop-down are updated whenever the time range changes.
- Click the “Save & Test” button to save the variable and test the query.
Now, we should see a list of unique device IDs returned by the query. If the query returns the expected results, let’s click the “Back” button to return to the dashboard.
Step 2: Add a Drop-Down Panel
The next step is to add a drop-down panel to your dashboard that displays the list of unique device IDs. Here’s how:
- Click on the “Add Panel” button on the dashboard and select “Singlestat”.
- In the “Singlestat” panel, go to the “Options” tab and scroll down to the “Value” section.
- In the “Value” section, select the “Variable” option and choose the query variable you created earlier.
- The drop-down panel is now added to your dashboard, and it will show the selected device ID.
Step 3: Modify SQL Queries for Graph Panels
The final step is to modify the SQL queries in the other panels on the dashboard to include a filter based on the selected device ID.
- Go to the panel you want to modify and click on the “Edit” button in the top right corner.
- In the “Metrics” section, modify the SQL query to include a filter based on the selected device ID. You can use the following syntax to include the filter:
WHERE "device_id" = '$device_id'We need to replace “$device_id” with the name of the query variable that you created.
- Click on the “Apply” button to save the changes.
- Repeat these steps for all the graph panels that display data based on the selected device ID.
Now, whenever we choose a device from the drop-down panel, the other panels on the dashboard will update to display data for that device.
That’s it! Now, we have created a drop-down panel in Grafana that shows the unique IDs of a column in a PostgreSQL table and uses the selected ID to show graphs based on SQL queries.
Grafana is a powerful data visualization and analytics tool that allows us to create custom dashboards and monitor metrics from various data sources.
In this tutorial, we have seen how to create a drop-down panel in Grafana that offers the unique IDs of a column in a PostgreSQL table and uses the selected ID to deliver graphs based on SQL queries. This type of filter help when we have a lot of data points.
That’s all for today! Cheers!!! 😎
Leave a comment