4 Steps to Configure InfluxDB and Grafana

If you have been following my post on setting up Home Assistant with Docker in Raspberry Pi. This is the continuation of the post.

Once you have the InfluxDB and Grafana installed, you would want to configure it to be store the MQTT message that was sent by the ESP8266.

So lets get started by configuring the InfluxDB that was installed in Docker.

Step 1. Configuring InfluxDB

To configure InfluxDB in docker first you will need to know the InfluxDB containerID, you can do this by performing the following command

sudo docker container ls -a

Then you can access the shell of the container using the following command

docker exec -it ca115f8382cf sh

Where ca115f8382f is the container name in my case this is my docker container, but yours will be different.

Now that you are in the shell of InfluxDB container, you can connect to the local influx db instance by using the following command.

influx -precision rfc3339

You should be presented with the output similar to the following:

Now you can create a new database, in this example I am creating the database called “home_automation”. You can type the following commands:

CREATE DATABASE home_automation

If it is done successfully you can check it with the following command

SHOW DATABASES

You should see that the databases has been created similar to the following:

To allow user access to the database the good practice is to create a database username and password. In this example I am creating the user mqtt with the same as password

CREATE USER mqtt WITH PASSWORD 'mqtt'
GRANT ALL ON home_automation to mqtt

Now that InfluxDB was configured we can exit the command line with exit.

Step 2. Creating MQTT InfluxDB Bridge

The purpose of the MQTT InfluxDB Bridge is to subscribe to the MQTT message from the Mosquitto message broker and saved it into the InfluxDB database. For this we would need python3.

To install Python3 MQTT language bindings type the following command

sudo pip3 install paho-mqtt

To install Python3 InfluxDB package, type the following command

sudo pip3 install influxb

If all goes well, you should get the result similar to the following:

The following phyton script is what I used to listen to MQTT and post into the InfluxDB. I didn’t create the script myself, but I will try to explain how the script works. You can download the full script here.

At the start of the script, we initialise NamedTyple, MQTT and InfluxDBClient. We also configure the address for your Raspberry Pi, in my case it is 192.168.8.20, use the username and password set in Step 1 above and also the database set in Step 1, in this case it is ‘home_automation‘.

The next part is the MQTT broker, in this case also the Raspberry Pi, I am using Mosquitto, and the user name and password is what used during the setup of Mosquitto.

import re
from typing import NamedTuple

import paho.mqtt.client as mqtt
from influxdb import InfluxDBClient

INFLUXDB_ADDRESS = '192.168.8.20'
INFLUXDB_USER = 'mqtt'
INFLUXDB_PASSWORD = 'mqtt'
INFLUXDB_DATABASE = 'home_automation'

MQTT_ADDRESS = '192.168.8.20'
MQTT_USER = 'effendy'
MQTT_PASSWORD = 'yourmqttpassword'
MQTT_TOPIC = 'home/+/+'
MQTT_REGEX = 'home/([^/]+)/([^/]+)'
MQTT_CLIENT_ID = 'MQTTInfluxDBBridge'

influxdb_client = InfluxDBClient(INFLUXDB_ADDRESS, 8086, INFLUXDB_USER, INFLUXDB_PASSWORD, None)

As you can see the Topic to monitor is ‘home/+/+’, this is consistent with the previous post on how to send the MQTT data from ESP8266 to your MQTT broker. In that post I am using the following topic

home/livingroom/temperature

You can use the Regular expression to define the wildcard by importing package “re”.

The next class SensorData define what what data we captured, in this case the location and measurement defined as string and value defined as float.

class SensorData(NamedTuple):
   location: str
   measurement: str
   value: float

On_connect function handle the subscription of to all topic defined in MQTT_TOPIC variable, in this case all that starts with ‘home’

def on_connect(client, userdata, flags, rc):
   """ The callback for when the client receives a CONNACK response from the server."""
   print('Connected with result code ' + str(rc))
   client.subscribe(MQTT_TOPIC)

On_message function calls the _parse_mqtt_message for processing and also call _send_sensor_data_to_influxdb function to save the data (payload) into influx db.

def on_message(client, userdata, msg):
   """The callback for when a PUBLISH message is received from the server."""
   print(msg.topic + ' ' + str(msg.payload))
   sensor_data = _parse_mqtt_message(msg.topic, msg.payload.decode('utf-8'))
   if sensor_data is not None:
      _send_sensor_data_to_influxdb(sensor_data)

_parse_mqtt_message function will break up the message into location, measurement and then encapsulate this into NamedTuple.

def _parse_mqtt_message(topic, payload):
   match = re.match(MQTT_REGEX, topic)
   if match:
      location = match.group(1)
      measurement = match.group(2)
      if measurement == 'status':
         return None
      return SensorData(location, measurement, float(payload))
   else:
      return None

_send_sensor_data_to_influxdb function will create a json structure and saved the measurement directly, the location is stored as a ‘tag’ and the value is stored in the ‘field’. This json structure is saved into the database.

def _send_sensor_data_to_influxdb(sensor_data):
   json_body = [
      {
         'measurement': sensor_data.measurement,
         'tags': {
            'location': sensor_data.location
         },
         'fields': {
            'value': sensor_data.value
         }
      }
   ]
   influxdb_client.write_points(json_body)

The _init_influxdb_database function, will initialise the InfluxDB database and create it if the database doesn’t exist.

def _init_influxdb_database():
    databases = influxdb_client.get_list_database()
    if len(list(filter(lambda x: x['name'] == INFLUXDB_DATABASE, databases))) == 0:
        influxdb_client.create_database(INFLUXDB_DATABASE)
    influxdb_client.switch_database(INFLUXDB_DATABASE)

The main() block of the code, is where the code will get executed by initialising the InfluxDB calling the _init_influxdb_database function. Then it initialise the mqtt_client and assigning which function to run on connecting and when the message is received. The code will loop forever to listen to the MQTT message

def main():
    _init_influxdb_database()

    mqtt_client = mqtt.Client(MQTT_CLIENT_ID)
    mqtt_client.username_pw_set(MQTT_USER, MQTT_PASSWORD)
    mqtt_client.on_connect = on_connect
    mqtt_client.on_message = on_message

    mqtt_client.connect(MQTT_ADDRESS, 1883)
    mqtt_client.loop_forever()


if __name__ == '__main__':
    print('MQTT to InfluxDB bridge')
    main()

Step 3. Run the MQTT InfluxDB Bridge

Once you have downloaded the code, you can now execute the code using the following command

python3 MQTTInfluxDBBridge.py

If you have the Temperature sensor ESP8266 running as per previous post, you should see the message similar to the following:

In my example above, I also have two hallway PIR sensor, one on the left and one on the right that are posting messages. I will post this as a separate project if I get a lot of people asking for it.

Now that we can see we are getting the messages, we can check that the data is also being stored into the InfluxDB. First we interact with the InfluxDB docker container, then use the InfluxDB command influx to get to the command line. To enter the database we use USE home_automation command. You should see the result similar to the following:

From the result above you can see I use the command SHOW measurements to see what information is stored. Then Using the SQL SELECT command to look into the leftpir, rightpir and temperature table. Because the table is just being populated there are not many entry, you might want to limit the SELECT command using the following if you have the python script running for quite sometime.

SELECT * from temperature LIMIT 10

Now that we know our python script is doing what it is supposed to do, we can let it run to collect data and populate the database. Once this is done we can visualise it in Grafana.

Step 4. Configuring Grafana

If you have installed Grafana as per my previous post, it is not time to configure it to work with InfluxDB. Now you can simply point your browser to the following address:

http://192.168.8.20:3000

You might have to replace 192.168.8.20 with your own Raspberri pi IP address. You should be presented with the following screen:

If this is the first time logging in, enter the username admin and the password admin. You will then be prompted to change your password. You can choose to update this, which I recommend or skip this step.

Then you can choose your default data sources, in this case we will select InfluxDB. To do this click on the “Configuration->Data Sources” from the left hand menu, you will be presented with the following screen:

Enter the URL of the Raspberry Pi where InfluxDB is installed, in this case I installed it in the same location

http://192.168.8.20:8086

If you scroll down, you will see the setting to configure the database:

In this case we are using home_automation database, and don’t forget to enter the user mqtt and password mqtt as per setup in step 1 above.

Then click on “Save & Test” button, if all goes well you should see the following message that the Data Source is working:

If you experience the error message gateway error, you should use the IP address to access the Raspberry Pi and not localhost.

Then click on “Explore” from the left hand menu, you should see the screen similar to below:

You can play around with this until you get the graph right, click on “select measurement” to select temperature. Then click on “$_interval” and select 1s (you will get dots) or 1minute (to get a smoother line). You should see something similar to the below:

If you didn’t see any graph, you might want to adjust the time on the top to expand to much longer time frame. Or check that your IoT device is still sending data to the MQTT broker.

Once you are happy with the way it looks you can add this to your Dashboard. To do this you can click on the “Dashboard” icon on the left and click on add panel at the top, and you can define your query again as per shown below:

Feel free to play around with the settings to suit your need. And when you are done, click on “Apply” button to add this to your Dashboard.

Final thoughts

You will need to make sure the MQTTInfluxDBBridge.py is running all the time to make sure that it listens to the MQTT message and saved it into the InfluxDB database. To make sure the script runs automatically when Raspberry Pi is being rebooted you can add the script to the cron job.

To execute the python script we need to create a shell script, let’s call this runMQTTInfluxDBBridge.sh, so we can execute the following command in the prompt

nano runMQTTInfluxDBBridge.sh

You can paste the following code into nano editor

#!/bin/sh
# launcher.sh

sleep 60

cd /
cd home/pi
sudo python3 MQTTInfluxDBBridge.py
cd /

The script will wait for 60 seconds before executing the MQTTInfluxDBBridge.py which is located int he home directory of pi user. If you moved this file else where you might have to modify the above location.

Now saved the script using Ctrl+X and make sure you saved it, then you can make the script executable using the chmod command.

chmod 755 runMQTTInfluxDBBridge.sh

Now for debugging purposes we would want to saved the error log into a separate folder, for this we create the logs folder using the following:

mkdir logs

Now we can add the script to the crontab. Crontab is the background process that automatically execute scripts based on preconfigured time. To do this we use the following command

sudo nano crontab -e

Then you can add the following to the editor

@reboot sh /home/pi/runMQTTInfluxDBBridge.sh >/home/pi/logs/cronlog 2>&1

Then saved the file using Ctrl+X again and you can reboot the Raspberry Pi to test that the cronjob will successfully execute the MQTTInfluxDBBridge.

You can check the error log by using the following command

cat logs/cronlog

If all goes well you shouldn’t have any thing in the cronlog file.

That’s it, you have now successfully being able to visualise the MQTT message that was posted to the HomeAssistant. Please drop me a line if you like this post, and let me know if you have any questions, I don’t claim to know all the answers, but I will see what I can do.