A Guide to Logging MQTT Sensor Data

Most MQTT brokers don’t provide any mechanism for logging historical data for later analysis.

However due to the publish/subscribe nature of MQTT is is easy to monitor, and log a data stream or streams using an MQTT client.



In fact this method may in many circumstances better than logging the data on the broker/server especially when the broker is being shared.

Logging Methods

There are two common ways of storing sensor data.

  • A file
  • A Database

Generally logging to a text file is faster and easier than logging to a database.

However if the data needs to be queried at a later date then logging to a database makes this much easier.

ref Using a database for logging

Database Types

There are two database types to choose from

  • SQL – SQlite, MYSQL etc
  • NOSQL -MongoDB etc

refSQL or NoSQL

Sensor Data

Many sensors like state sensors (ON/OFF), temperature etc will fluctuate very little over the course of a day so does it makes sense to store the same sensor value every x seconds?

Take for example a light sensor that publishes its status every second.

After 1 hour it has published 3600 status messages.

If we assume that the light status hasn’t changed during that period then it doesn’t make much sense to store the 3600 status messages.

The only messages that need to be stored are changed messages.

With this method if our light changed state twice in the hour then we would store 2 messages.

Over a day assuming 20 state changes we would store 20 messages rather than 86,400(24*3600) messages.

Preferably it would be better if the sensor only sent changes as this would also reduce the network traffic.

However even if the sensor does send data a regular intervals regardless of state then it doesn’t make sense to store it.

Therefore a data logger should have the ability to only log changes.

Notes:

1. The sensor needs to publish with the retain message flag set.

2. A sensor shouldn’t publish a timestamp with the sensor data if it needs to be logged.

If it does the message will appear to have changed and the data logger would need to take this into account.

MQTT Data and topics

MQTT data is organised in topics and if you are monitoring several  MQTT topics then you may want to log each topic stream to its own file rather than logging all topics to a single file.

Data Loggers

I have created several data loggers for logging IOT data and specifically MQTT data.

Simple Python MQTT Data logger Python script Logs data to text file as JSON encoded Data.

SQL MQTT data Logger  Python script which logs data to a sqlite database.

Simple MQTT Topic logger -Python script Logs data by topic to a text file as JSON encoded Data

Node-Red Data and Topic Logger- Node Red Flow with web user interface that lets you choose to log by topic or not.

Resources and related tutorials

Please rate? And use Comments to let me know more
[Total: 9    Average: 2.4/5]

Leave a Reply

Your email address will not be published. Required fields are marked *