Grafana v10.2, mySQL, Table visualization.
Query: There are 30 queries in which I calculate monthly consumption collected by 30 sensors.
Task: I’d like to create a table visualization in which I can see the monthly consumption of selected sensors and their summary for each year and month like this: Year / Month / Sensor2 / Sesor14 / Total - which are match the filter.
Process:
Step1: Concatenate fields - to see each query results in one table
Step2: Filter by name/Identifier: “.${Site}.|.Year.|.Month.” - Filter by one specific text or the whole name of sensor name which is selected in $Site variable
Step3: Add field from calculation/Reduce row/select all numeric fields/Total - to calculate the summary of selected rows and to see it in the last column.
It works, if I select only one option from the drop-down list. If I select 2 or more, it will result nothing.
How to handle multiple selection in regex where there is a “+” in between selected text and I want both to be considered separately.
Hi @fjanni
Can you share your query?
In SQL, when wanting to select multiple regions (New York, California, etc.), I do this:
SELECT * FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN (${region}) AND $__timeRange(time)
which looks like this when inspected in Grafana after executing:
SELECT * FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN ('California','Florida','New York','Tennessee') AND time >= '2024-04-17T09:38:11Z' AND time <= '2024-05-02T09:38:11Z'
Hi, this is one query from 30:
With
T0 as (
With T as (
SELECT $Year AS year, m AS month, null AS Consumption
FROM (
SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) AS months
)
Select Year, Month, Consumption from T
Where year in ($Year) and month in ($Month)
),
T1 as (
With T AS (
Select
Year (Zeit) as Year,
Month(Zeit) as Month,
Zeit as Time,
Zaehlerstand - LAG(Zaehlerstand) OVER (ORDER BY Zeit) as Consumption
FROM emspressiggas.002_prg_naturalgas_schlossereiheizung
)
select year, month,
CASE
when '$Energy' = 'm3' Then Sum(consumption)
when '$Energy' = 'nm3' Then Sum(Consumption)*(select gas_faktor from ems.gas_faktor_nm3 where db_spalte='002_prg_naturalgas_schlossereiheizung')
when '$Energy' = 'kWh' Then Sum(Consumption)*(select gas_faktor from ems.gas_faktor_nm3 where db_spalte='002_prg_naturalgas_schlossereiheizung')*(select brennwert_erdgas_pressig from ems.gas_zaehlerstaende where jahr = year and monat = month)
else null
END as Consumption
from T
Where year in ($Year) and month in ($Month)
Group by Year,Month
Order by Time
)
select
T1.Consumption as '002_prg_naturalgas_schlossereiheizung'
from T0
left outer join T1 on T1.year=T0.year and T1.month=T0.month
In the rest of queries table name is changing.
Here is the result when I select columns which contains “naturalgas”, “prg” and “tnv”
But when I select multiple value for example “prg + hdf” in Site variable, it doesnt work.
Or when I want to exclude one sensor by selecting by multiple selection all necessary sensor except one, it doesnt work.
where is the site variable used in your query. it is not clear
this is not scalable approach. what if tomorrow, you grow to 150 sensors, are you going to create 150 queries?
You are right, would you have an idea how to make it scalable? A DO … WHILE would be great but how to do it if each sensor data is in a different table. I can list the tables with SHOW TABLES but how to run this query in each table and show the results in one table chart?
The Site variable is setup at the variable with Custom type and value is “prg,hdf”
And filter is setup at Transform data / Filter by name / Identifier: .${Site}.|.Year.|.Month.
When only prg or hdf is selected it is ok, but in case of multiple selection “prg + hdf” it doesn’t work, I can see nothing.
from a db design pov why do you have a separate table per sensor?
schema wise are all of these tables identical ?
Separate table per sensor: it is company specific, no way to change.
Schema: all of the tables are identical as for the schema
1 Like
ok if tables same can you create a view that uses Union to tie them all up? one of the fields should be sensorname as metric
Thanks. I already tried it with UNION and LEFT OUTER JOIN and both were ok.
But the problem is that both of them are static query and if there is new sensor I have to update the query, by adding a new subquery with this new table name (FROM).
Your suggestion was to simplify it and I also looking for the solution how to make an automatic update.
For example if a new sensor table is created → sensor list is updated when in a query table list is run SHOW TABLES → and sensor data is considered by adding a new colums on the table dashboard. This is what I would like to do and I would need help how to do this.
I understand your idea, to put sensor name (table name) as a metric. This is good idea. How could i do it taking into consideration the aboves?
