Warning: I have no clue what the impact on performance is when Grafana is reading the Domoticz DB on regular time!!
Example dashboards made with Grafana
- Selection_021.png (188.2 KiB) Viewed 10596 times
I found this Grafana plugin for SQLite https://github.com/fr-ser/grafana-sqlite-datasource
It makes the Domoticz SQLite database directly accessible in Grafana. So no need to use the Data Push to influxDB https://www.domoticz.com/wiki/Influxdb
You have to be knowledgeable of SQL to get usable data out of the Domoticz DB into Grafana.
Usage:
- Install Grafana
Install in Grafana the SQLite-datasource plugin (see instructions in plugin github)
- Add an SQLite datasource in Grafana.
Set the path to the domoticz SQLite database (=domoticz.db in installation folder)
Save the datasource and use it.
I see the following usable tables (but it could be more):
- Fan
Meter
Multimeter
Percentage
Rain
SceneLog
SetpointTimers
Temperature
UV
Wind
Code: Select all
WITH converted AS (
--- DeviceRowID value is Domoticz IDX sensor reference
--- Conversion of Domoticz Date notation to Grafana Date notation
SELECT Temperature, REPLACE(Date, ' ','T') || 'Z' AS datetime FROM Temperature WHERE DeviceRowID=213
)
SELECT datetime, Temperature FROM converted ORDER BY datetime ASCCode: Select all
WITH converted AS (
--- DeviceRowID value is Domoticz IDX sensor reference
--- Conversion of Domoticz Date notation to Grafana Date notation
SELECT <value to chart>, REPLACE(Date, ' ','T') || 'Z' AS datetime FROM <Domoticz Table> WHERE DeviceRowID=<Domoticz sensor IDX>
)
SELECT datetime, <value to chart> FROM converted ORDER BY datetime ASCQuick Example in Grafana. There are more instructions on the internet to create Domoticz Grafana Dashboards.
- Screenshot from 2021-01-06 00-01-08.png (75.71 KiB) Viewed 10695 times
- Screenshot from 2021-01-05 23-51-50.png (85.54 KiB) Viewed 10695 times
Long term data
You can also use the long term data (per day) as present in the xxxxxx_Calender tables (xxxxx being the data source like temperature, multimeter etc)
So to display the avarage temperature of sensor IDX=213 you can follow the same method but as the date notation is different in this SQLite table, you have to convert it differently.
Code: Select all
WITH converted AS (
--- DeviceRowID value is Domoticz IDX sensor reference
--- rename temp value to temp_idx to allow multiple queries (and so graph lines) labels
--- Conversion of Domoticz Date notation to Grafana Date notation
SELECT Temp_Avg AS Temp_213, Date || 'T00:00:00Z' AS datetime FROM Temperature_Calendar WHERE DeviceRowID=213
)
SELECT datetime, Temp_213 FROM converted ORDER BY datetime ASC
Example: To show temperature devices 11, 97 and 213 in one graph I used the following query.
There is an extra conversion needed as metric (in domoticz IDX) has to be a string and not a number. So I added Temp_ as a prefix. You are offcourse free to use your own prefixes....
Code: Select all
WITH converted AS (
SELECT Temperature , REPLACE(Date, ' ','T') || 'Z' AS time, 'Temp_' || DeviceRowID AS metric FROM Temperature Where DeviceRowID in (11, 97, 213)
)
SELECT time, metric, Temperature FROM converted ORDER BY time
