Is it possible to use the MQTT Broker Mosquitto on Ubuntu 12.04 to log all events such as messages published to all channels, subscriptions, client connections/disconnections and errors to a log file with a time stamp and then have program insert this into a database either Mongodb or SQL?
If so, how could this be achieved?
Yes you can do this.
You can add timestamp data to the Mosquitto output by doing the below for example:
mosquitto_sub -v -t '#' | xargs -d$'\n' -L1 sh -c 'date "+%D %T $0"'
You could obviously output this to a text file as well via doing the following:
mosquitto_sub -v -t '#' | xargs -d$'\n' -L1 sh -c 'date "+%D %T $0"' > /var/tmp/My_Mosquitto.log
You can view the log file via:
cat /var/tmp/My_Mosquitto.log
You would then have to write say a php script that reads this text file and adds the data to a database table.
Another (much better) option would be to use node.js to log all the MQTT data to a mysql database table. There are examples of how to do this all over the internet - this is how I do it.
AFAIK this isn't possible right now with mosquitto. For use cases like this I would recommend to use a MQTT broker which has a plugin and message interception system like HiveMQ. To see all the interactions, there are some plugins available as open source like the Message Log Plugin. The source code is also available on Github.
To write e.g. the messages to a database, look at this blog post, this should help you get started.
Note that I may be biased because I work on HiveMQ.
