OK! So we have new sensors 👍
Also, they publish logs over MQTT.
Mosquitto works pretty well on Raspberry Pi. It's easy to install and Tasmota allows you to set up all your sensors (@see temperature sensors) in a couple of clicks.
Anyway, this piece of puzzle is mandatory to collect data and it's the best option if you want to collect your data.
Honestly, i didn't try to test platforms like Home Assistant or openHAB but it seems work on MQTT and offers a lot of graphs that could display your data.
Because I'm not interested by controlling my rolling shutter, bulbs or music with a central platform, I've decided to take the shortest path to get a complete dashboard.
So i've selected three pillars:
you can jump to this page and grab what you need. If you need help, please contact me 🙂
Well, on one side Mosquitto collects and dispatches data from sensors, on the other Grafana reads data and displays charts.
We have to find a way to store dispatched data in a database and link it to Grafana.
[sensors] --> [mosquitto] --> ([glue]+[database]) --> [grafana]
I've tested a lot of databases, MySQL, MongoDB, Elasticsearch and InfluxDB. All of them work with Grafana but needs large resources of CPU and Memory. Also, we have to think about backuping, ability to extract easily data to transfer them to an another (i've changed databases type 5 times!), etc.
At the end, I only need 24h of precise data (48h to fall back in case of issue) and some aggregates with a day accuracy to compute summaries.
We are talking about a dozen data per day. Not really a big deal. That's why i decided to create my own datastore system based on SQLite3.
At midnight, a process "reduces" data and stores results into "history.db". It calculates mean temperature and power consumption of the day.
The sqlite databases are directly linked into Grafana thank to the plugin of Sergej Herbert.
[sensors] --> [mosquitto] --> [datastore]>[sensors.db] --> [dashboard]
>[history.db] --> [dashboard]
Backuping a SQLite is pretty simple.
You have to order a .backup and target a backup file.
You could do that by using | pipe feature from Unix.
The result can be compressed with gzip 🙂.
> echo ".backup /path/to/backup/history.db" | /usr/bin/sqlite3 /path/to/current/history.db && /bin/gzip -f /path/to/backup/history.db
Personnaly, i've cooked 3 differents dashboard.
- a fine grained day to day analyze
- a 90days view to feel changes and gains
- a long term view to analyze yearly gains
Don't worry, i have a copy of my Grafana configuration! 🥳
You can download this backup. It contains my current configuration. You have to unzip and copy the directory on /var/lib/grafana.
Or you could use my docker-compose configuration🐳.