Grafana SQLite plugin: Graphs Dashboard without InfluxDB datapush
Posted: Wednesday 06 January 2021 0:23
I wanted to share this with you, but I have to tell you that I am no Grafana user, I just tested it for feasibility/usability.
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
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:
I see the following usable tables (but it could be more):
convention:
Set datetime as "time formatted column"
Quick Example in Grafana. There are more instructions on the internet to create Domoticz Grafana Dashboards.
Domoticz Temperature table (screenshot made with application sqlitebrowser)
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.
Edit 28/1: I found an easy way to have multiple sensors show in one graph (Grafana time-series).
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....
Have Fun!
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 10611 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 10710 times
- Screenshot from 2021-01-05 23-51-50.png (85.54 KiB) Viewed 10710 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