Grafana SQLite plugin: Graphs Dashboard without InfluxDB datapush

[waltervl]

@Waltervl

Quick Example in Grafana. There are more instructions on the internet to create Domoticz Grafana Dashboards.

Can you give me the link on this. Are there any SQLite examples, almost all use the IfluxDB connection?

Sorry, I can not. I just wanted to give an example what is possible. It are google search screenshots.....
Unfortunatly you cannot import these without the influxdb datasource connection.
Perhaps make a dummy influx datasource to see how it works (if possible)? Then you should be able to check how the graphs are set up.
The influx datasource usage within graphs is completely different from the SQLite so for sure not reusable.

When creating new Grafana Panels with graphs, gauges, text etc this link can help you
https://grafana.com/docs/grafana/latest ... lizations/
for example
Gauge https://grafana.com/docs/grafana/latest ... uge-panel/
Bar Gauge https://grafana.com/docs/grafana/latest ... uge-panel/

[McMelloW]

@waltervl
Thanks,I will dig into it further .
BTW to install the SQlite plugin on a Raspberry Pi 3B+ worked OK with the command.

Code: Select all

sudo grafana-cli plugins install frser-sqlite-datasource

[dallas]

Hi. Maybe it helps - https://github.com/fr-ser/grafana-sqlit ... ana_add_ds
And look at my result on collecting information in Windows in another branch - but suddenly it may help - viewtopic.php?p=271981#p271981

[geertvercamer]

I don't seem to have the right path to the db on a raspberry pi.
Anyone can shed a light?
Thanks

[waltervl]

There should be a domoticz.db file in the installation folder of Domoticz, something like home/pi/domoticz

Better make a copy first and try it on that copy. If you have automatic backup switched on there are copies of the database in the domoticz/backups folder.

[McMelloW]

Grafana - SQLite Plugin - domoticz.db
When click on Save & Test the following error comes up.

Code: Select all

error checking db: unable to open database file: out of memory (14)

Do I have to configure something on domoticz.db to get access by Grafana?

[waltervl]

I could open my database (including my automatic backups) without this error and without setting something else than the location of the database in grafana. Perhaps an access rights issue?

[McMelloW]

I could open my database (including my automatic backups) without this error and without setting something else than the location of the database in grafana. Perhaps an access rights issue?

Did the same as you location and name of domotictz.db (home/pi/domoticz/domoticz.db) and the path (home/pi/domoticz)
domoticz.db owner is pi and rights are rwxr-xr-x What else can I do?

[McMelloW]

Nobody has a clue to solve this problem?

[hemant5400z]

Hi All,

Finally after some struggeling to get the Database History in grafana I'm not also using the SQL Lit plugin. Initially I had some issues with getting erros after refresing the dashboard "Database malformed 11". Even when the database was in readonly mode, so solve this i'm making a daily copy (realtime data is commingg in using influx for daily stats). I have a Synology job which runs every night so i have Today-1 which is more than enough for Monthly and Yearly statistics. I could use Grafana for this but but had 2 problems 1. Missing Historicat data and 2. FluxQL is not month away so the graphs look strange.

I'm a beginner when it comes to writing SQL, not my core competence, however i mages to create a Monthly and a Yearly totals dashboard directly using the SQL plugin and using Meter_calendar and MultiMeter_calendar.

Results Attached.

If people are interested in the queries, let me know, happy to share them. Also if someone has a bright idea on how to do the Bruto Verbruik, in a smarter way please share, currently did a transform in Grafana with a nasty outerjoin, should be a smarter way.

Cheers,
Hemant

[hemant5400z]

Nobody has a clue to solve this problem?

Hi,

Maybe a bit late but the grafana user probably needs access to that file, so make sure it has read permission, also make sure you open de database in Read olny mode: ?mode=ro (in the patsh options).

Not sure with the PI's but I run all in dockers on my synology (Domotiz, influxdb,grafana)

Cheers,
Hemant

[waltervl]

If you have automatic database backup switched on you can use the daily backup in folder backups\daily

Edit: typo

[hemant5400z]

Thanx done, using night backup

Hemant

[NvBgm55]

Hello,
I still get a error when i wnt to install datasource

error checking db: error checking for valid SQLite file: attempt to write a readonly database (1544)

I have make a backup of the database and store it . The ownerschip is root with all rights 755
Is this plugin still working with beta of domoticz ??

[waltervl]

This error has nothing to do with stable or beta. Probably related to connected to a running database? But write access is not needed on the database as you should read the Domoticz database with this Grafana plugin.

[hemant5400z]

Hello,
I still get a error when i wnt to install datasource

error checking db: error checking for valid SQLite file: attempt to write a readonly database (1544)

I have make a backup of the database and store it . The ownerschip is root with all rights 755
Is this plugin still working with beta of domoticz ??

Hi,

Initially I had the same issue with the backup file even with 777. I did mount the volume to my docker as read-only and also passed the flag in grafana as read-only. Once I unticked the box in docker volume for readonly I was able to connect the database using the readonly parameters as described by the plugin.

Hemant

[NvBgm55]

It works ....
I have change the journal_mode from wall to delete and now i can acces the domoticz.db without any problem..
Find info here
https://www.sqlite.org/pragma.html#pragma_journal_mode

[hemant5400z]

Cool,

Not sure what the impact is on the database but assuming this is a backup and not your live db.

Hemant

[NvBgm55]

Correct I use a backup of the database which is updated every night. I don't have the data of the current day, but that's no problem for me. And so I stay on the safe side.

[NvBgm55]

When i put the databse on the root the plugin can find it but when i put it in the tmp dir is does not .

/backup.db all is ok
/tmp/backup.db error checking db: no file exists at the file path