wearesober

How to display data history

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:

• Mosquitto
• Grafana
• And a lot of glue to bind Mosquitto and Grafana together

TLDR;

you can jump to this page and grab what you need. If you need help, please contact me 🙂

Glue! 🖇

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.

It connects to Mosquitto, collects, splits and stores data from sensors/+/+ to a sqlite database “sensors.db”.

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

You’ll need dashboards 📊

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🐳.

This site is open source. Improve this page.