Select data from multiple tables based on dashboard variable

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?
    – Grafana V 11.2.0 on Windows Server

I have a dashboard that uses a repeat panel tied to a dashboard variable to select which machines we want to pull data from. Each machine is in a separate table due to how the data collector stores its results. Table names are in a format of “area_1_machine_101”, where the 1 and 101 can be replaced by other values.

The dashboard variable (machineList) is a custom, multi-value variable with the “custom options” values having the key being the machine number, and the value being the name of the table.

Using repeated panels works great with the query:

SELECT `time@timestamp` AS "time", 
  data_format_0 AS ${machineList}
FROM production.${machineList:csv} 

WHERE `time@timestamp` > ($__from / 1000) AND `time@timestamp` <= ($__to / 1000)

This produces a table of values that I transform using a “MEAN” function to get an average of data_format_0, which is uptime for the machine. Yay.

What I need now is to create a table that has a row for each machine that is selected in the dashboard variable. First column will be the machine number (I can get that from the dashboard variable key) and second column is the averaged uptime.

I can do this by having multiple queries in the table panel if I hard code the machine name and table name into the query.

How do I dynamically run queries for only the selected values in the dashboard variable? I think I can do the data transforms once I get that far.

If there is any other information I need to provide, I will happily do so.

2

Create a view that unions all of the tables, with one the columns on this view being the table name that matches the variable

Also use this exact same view to build the variable picker

3

Just to make sure I understand since I’m pretty new to this:

Create a panel with a grid view, and the query does a UNION on all 120 data tables. One column of the grid has the variable as the source. This will make the query results only populate into the grid where the variable is selected? Do I use as single transform to do the averaging, or will I need one for each row?

Thanks for your time and patience as I figure all this out.

1 Like
4

In that case use a stored procedure and do your calculations there

create view vw_machinestats
as
select 1 machine_number, 
`time@timestamp` AS "time", 
'area_1_machine_101' as table_name
from area_1_machine_101
union
select 2 machine_number, 
`time@timestamp` AS "time", 
'area_1_machine_102' as table_name
from area_1_machine_102

create proc machine_averaged_uptime
(
   @machine varchar(max)
)
as
begin
select machine_number, avg(time)
from vw_machinestats
where table_name in ( @machine )
--in this where clause you will need to
-- figure out how to parse maybe  comma delimited values?

group by machine_number
end
go
grant execute on machine_averaged_uptime to grafana

just improving here but hope if helps guide you.