Posted on 15 Comments

#41: Datalogging with MQTT, Node-RED, InfluxDB, and Grafana

Note: This page is still a draft! The instructions are still incomplete.

Resources

The plan

Whatever data source you want to record, there are a few common building blocks that you will need. Each of these building blocks has multiple alternatives. There are also totally different architectures that do things in a different way.

However, this particular combination of software is ideal for home automation, because these software building blocks can also be used for other purposes within your overall system.

You can install the various software elements on different computers, or in virtual machines, or using Docker containers, or on a NAS, or in many other ways.

To keep it simple and because many people already have one spare, we’re going to use a Raspberry Pi.

Prepare Raspberry Pi

Begin by installing Raspberry Pi OS (formerly known as “Raspbian”) on your Raspberry Pi. The easiest way to do it these days is to use the Raspberry Pi Imager, which runs on Windows, MacOS, or Linux and can be downloaded from www.raspberrypi.org/downloads/.

Use the Imager to download and install Raspberry Pi OS onto a micro SD card, insert it into your Pi, and start it up.

TODO: logging in first time.

Make sure your Pi has the latest packages by applying all updates:

sudo apt update
sudo apt upgrade

You now have a Raspberry Pi with a basic Raspbian OS installation, ready for customisation.

Step 0: Get the IP address

We’ll need to know the IP address of the Raspberry Pi later. If you’ve connected using the hostname or you’re using a screen and keyboard, you can get the

Step 1: Install the Mosquitto MQTT broker and clients

There are many different MQTT brokers available, but Mosquitto is the most popular and it has served me well for many years. Install both the “mosquitto” package and the “mosquitto-clients” package, so that you have both the broker and some handy command line clients that you can use for testing:

sudo apt install mosquitto mosquitto-clients

The Mosquitto broker will be set up with a default configuration and will work fine out of the box, but we’re going to change the configuration to make it more secure.

You can leave your MQTT broker unprotected, but it’s a good idea to set a username and password on it.

First, create a text file that contains the username and password with a colon to separate them. You can do this on the command line with a single command:

echo "mqtt_username:mqtt_password" > pwfile

This will create a text file called “pwfile” with the details in them.

Use the passwd utility provided as part of the Mosquitto package to encrypt the file:

mosquitto_passwd -U pwfile

Display the contents of the file to verify that it has now been encrypted:

cat pwfile

You will see your username in plaintext, followed by a string of gibberish which is the encrypted form of the password. If the password is still plaintext, it means the encryption command above didn’t work.

Move the encrypted file into the Mosquitto configuration directory:

sudo mv pwfile /etc/mosquitto/

Mosquitto needs to be told where the new file is located. Use an editor such as Nano to open the Mosquitto configuration file:

sudo nano /etc/mosquitto/mosquitto.conf

Near the bottom of the file, just above the “include_dir” line, add these lines:

allow_anonymous false
password_file /etc/mosquitto/pwfile

The first line tells Mosquitto to reject all anonymous connections, and require a password.

The second line tells Mosquitto where to find the list of allowed usernames and passwords.

To save your changes and exit, type CTRL+X, then Y, then ENTER.

Restart Mosquitto so that it uses the new configuration:

sudo /etc/init.d/mosquitto restart

From now on, all connections to your MQTT broker will need to supply the username and password that you configured.

Step 2: Install the InfluxDB time-series database

To install InfluxDB we can use its official repository, because the developers have provided a packages specifically for different operating systems on the Raspberry Pi.

Start by fetching the official repository key and adding it to the local keyring:

wget -qO- https://repos.influxdata.com/influxdb.key | sudo apt-key add -

Now you can add the repository. There are a few different versions available, so you need to copy and paste the command that matches your operating system.

To find out what version you’re running, you can type the following command:

lsb_release -a

This is will report the operating system type and version, usually with a codename like “Stretch” or “Buster”. Copy and paste the command that matches your system.

For Raspbian Stretch:

echo "deb https://repos.influxdata.com/debian stretch stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

For Raspbian Buster:

echo "deb https://repos.influxdata.com/debian buster stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

For Ubuntu 20.04LTS:

echo "deb https://repos.influxdata.com/ubuntu focal stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

For Ubuntu 18.04LTS:

echo "deb https://repos.influxdata.com/ubuntu bionic stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

Now that the repository has been added, we need to update the list of packages that are available:

sudo apt update

Install the database package:

sudo apt install influxdb

Tell the systemctl service manager to enable InfluxDB at startup:

sudo systemctl unmask influxdb
sudo systemctl enable influxdb

Start InfluxDB manually this time. In future, it will be started automatically whenever your Raspberry Pi boots up:

sudo systemctl start influxdb

Let’s set up access control for InfluxDB before we do anything else. The default installation of InfluxDB leaves the system wide open, so we’ll start by creating an admin user and setting a password.

Connect to InfluxDB by running the client. We don’t need to use a username or password this time, because nothing has been set yet:

influx

Create a user called “admin”, and put in the password you want to use for it:

CREATE USER admin WITH PASSWORD 'adminpassword' WITH ALL PRIVILEGES

Now you can exit out of InfluxDB. Simply type “exit” and press ENTER.

exit

The InfluxDB configuration needs to be edited so that it will use authentication. Otherwise the admin user that we just created will be ignored. Use a text editor to open the InfluxDB config file:

sudo nano /etc/influxdb/influxdb.conf

Press CTRL+W to search for the section called [HTTP].

auth-enabled = true
pprof-enabled = true
pprof-auth-enabled = true
ping-auth-enabled = true

To save your changes and exit, type CTRL+X, then Y, then ENTER.

The config change won’t be applied until InfluxDB has been restarted, so restart it manually:

sudo systemctl restart influxdb

From now on, any time you want to connect to the InfluxDB command line you will need to supply the username and password.

We need to do that now, so substitute the password you set:

influx -username admin -password <adminpassword>

If the previous changes worked, you should now be connected to InfluxDB again and authenticated as the admin user that you just created.

Next we need to tell InfluxDB to create a database where we can store sensor data. In this example I’ve simply called the database “sensors”:

CREATE DATABASE sensors

That was easy! Because of the way InfluxDB works, there’s no need to create a schema with tables and columns like you would be a relational database such as MariaDB, MySQL, Postgres, or SQL Server.

All you need to do is create an empty database, and it will be automatically populated when you start sending data to it.

Leave the InfluxDB client by typing exit, as before:

exit

Step 3: Install Node-RED

There are several different ways to install Node-RED, and it’s readily available in the Raspbery OS packaging system. However, the Node-RED team recommend that you do NOT use the packaged version.

Instead, they provide a handy script that installs the latest version of Node-RED from the official release, and helps you keep it updated. The installation script goes far beyond that, however. It also:

  • makes sure there’s no existing installation of Node-RED and removes any that it finds in order to prevent conflicts
  • installs the latest Node.js
  • gives you the option of installing a set of useful nodes specifically designed to run on a Pi
  • sets up Node-RED to run as a service and installs tools to help you manage it

Before running the Node-RED installation script, install the tools needed by NPM to build binary modules:

sudo apt install build-essential git

Now you can run the official Node-RED installation script:

bash <(curl -sL https://raw.githubusercontent.com/node-red/linux-installers/master/deb/update-nodejs-and-nodered)

The script will ask you a couple of questions about whether you are sure you want to proceed, and whether to install Pi-specific nodes. Say “y” (yes) to both questions.

Node-RED can be extended by installing modules to give it extra features. We need to do that now so that it can connect to your InfluxDB database. Use NPM to install the InfluxDB nodes:

npm install node-red-contrib-influxdb

At this point Node-RED is be installed, but just like with InfluxDB you need to configure it to be automatically started on boot:

sudo systemctl enable nodered.service

You can start the service manually this time, but in future it will happen automatically when your Pi starts up:

sudo systemctl start nodered.service

Step 4: Install Grafana

Just like with InfluxDB, we can install Grafana by adding the official repository and installing the package. Start by fetching the public key for the repository and adding it to the local keyring:

wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -

Now add the repository itself:

echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee -a /etc/apt/sources.list.d/grafana.list

Update the package list (again!) and install the Grafana package:

sudo apt update
sudo apt install grafana

Just like the other packages we’ve installed, we need to enable the service so that it will start automatically:

sudo systemctl enable grafana-server

That takes care of starting Grafana at boot. For now, let’s start it manually:

sudo systemctl start grafana-server

Step 5: Configure your sensors to send data to MQTT

For each sensor that you want to log and report, you will need to go through a series of steps to ensure that the data is received by your Raspberry Pi, processed by Node-RED, stored in InfluxDB, and then charted using Grafana. This is a process that you will repeat multiple times as your build up your home automation system.

Let’s break it down into a series of simple steps.

Begin watching MQTT for messages. There won’t be any messages yet, but this is a handy technique for discovering new devices when they begin reporting to MQTT. On your Raspberry Pi, open a terminal and run the Mosquitto client command that we ran earlier:

mosquitto_sub -u mqtt_username -P mqtt_password -v -t "#"

This launches the Mosquitto client in “verbose” mode (the “-v” flag) and subscribes to all topics using the wildcard character. What this means is the client will display every message that is published by any client to any topic, and report not just the message but also the topic that it appeared in.

On a busy MQTT broker this is like drinking from a fire-hose!

However, we don’t have any sensors using our MQTT broker yet so you shouldn’t see anything happen: the terminal will just sit there waiting to display messages. Leave the terminal open so that you can see any messages that are published.

Configure sensor to report using MQTT. The exact process for this will depend on your device. Typically you will need to configure three things in your sensor:

  1. The IP address (or hostname) of your MQTT broker. In this case, it’s the IP address of the Raspberry Pi.
  2. The username and password for MQTT. We configured this way back at the start of the process. If you don’t have a username and password on your broker, you can skip this.
  3. The MQTT topics for reporting. In many devices this should default to something sensible, and you may not need to change it. For our Air Quality Sensor project, the topic will be generated automatically based on the chip ID of the ESP chip.

If you are using the example Arduino sketch for the Air Quality Sensor, open it in the Arduino IDE and go to the tab called “config.h”. Edit the broker IP address and the MQTT username / password to match your own settings:

Compile the sketch and flash it to the Air Quality Sensor, just like in the previous episodes.

After the sensor starts up with the new settings, you should see some action in the Mosquitto client! You’ll see a startup message from the sensor, and then it will periodically begin publishing its readings.

Step 6: Receiving and storing single-value sensor readings

aoeu

Step 7: Receiving and storing JSON-formatted sensor readings

aoeu

Step 8: Create a dashboard and widgets in Grafana

You can access the Grafana user interface using a web browser, by connecting to port 3000 on your Raspberry Pi. The URL will look something like:

http://192.168.1.248:3000

Substitute the IP address or hostname for your Raspberry Pi.

To log in, use the username and password “admin” and “admin”. Grafana will ask you to change the password on first login, so set it to something secure.

15 thoughts on “#41: Datalogging with MQTT, Node-RED, InfluxDB, and Grafana

  1. Hello Jonathan,
    Thanks a lot for this marvelous esplanation and instructions. Im allmost done with all my installations. However it seems there is sometihng missing at step 6 & 7 or am i wrong?

    Greetings from Holland,
    Paul Marcus.

  2. You may want to look at using /etc/mosquitto/conf.d to contain the changes to the configuration file as this is where Mosquitto recommend them to go (see the README in the directory).

    This allows the main file to be replaced during an update and hopefully not impact your changes.

  3. Great doc. I follow all the steps and install it to work. However, I have a question as I don’t see anything on Grafana web page after adding the data source from influxdb (DB sensors). My device is sending data to the MQTT server and I can see the data by subscribe to it from another computer. As the data coming in every minute, I was expecting some graph changing (simple dash board) but did not see anything. Any hint on how to examine the data in influx? or if the problem is with Grafana?

    Thanks,

  4. Hi Jonathan,
    Great tutorial! Always nice to see how others are setting up their environment.
    I’m running an automated house for over 7 years now and I have ‘evolved’ from one tool to another.
    My current stack consists of NodeRed, HomeAssistant, RabbitMQ (Mqtt), InfluxDb, PostgreSQL and Grafana. I’m not only tracking my lights turning on and off (KNX, Hue, MiLight) but also track all kinds of data like sun azimuth/altitude, indoor temperatures/CO2/humidiy, outdoor weather, produced energy from solar pannels etc.

    I noticed you store each sensor value in a separate measurement. In my setup most sensor values are stored in the same measurement. This simplifies querying over multiple sensors because each sensor produces values at different times. Using the ‘last’ function of InfluxDB allows to retrieve the actual/last recorded value of each sensor at requested timestamp.
    This allows my to query for example the sun position and weather conditions and produced energy and display this in a single graph. Of course this could also be accomplished by creating separate queries in Grafana but this is not efficient towards InfluxDB, especially when having many graphs/queries/dashboards.
    A long time wish for me is to use machine leaning AI to ‘automagically’ manage my house and I think that being able to get latest values for all sensors at a specific timestamp would allow me to easily feed the AI with values for learning.

    Looking forward to new videos.
    Kind regards

  5. Great instructions, but why not just use Home Assistant and make life a lot easier.

    Mike

  6. Thanks for the so far great tutorial. Are you planning on adding a guide on how to setup the rest including node red and grafana so it all works together?

    PS: npm install node-red-contrib-influxdb threw me some warnings and I had to install it via the web interface.

    Cheers

  7. Hi Jonathan it’s an amazing tutorial, thanks for the help.
    I have a problem, maybe you can help me since I am not able to. Node – Red has this problem:

    “Error saving flows: ENOSPC: no space left on device, copyfile ‘/home/pi/.node-red/flows_mateo.json’ -> ‘/home/pi/.node-red/.flows_mateo.json.backup'”

    Which indicates that the raspberry pi has full memory and I have checked. How do I solve this problem, what is filling my memory? .

    My best Regards .

  8. Great tutorial, thanks Jonathan – I keep coming back to it when I run into issues with setting up databases within InfluxDB.

    Just so everyone following has a fighting chance – it seems there is some privilege issues with the latest InfluxDB install. A fresh install on a Raspberry Pi 4 didn’t allow me to run influx after installing – I couldn’t get to the ADD USER part. After LOTS of Googling and consulting the Influx Community Forum, I cam across this reddit post which saved my issue.

    https://www.reddit.com/r/influxdb/comments/ovulei/cant_start_influxdb_after_last_update/

    I hope it calms some frustrations of people following this 😂

  9. Great tutorial. What is the device used to read the sensors?

  10. Hi Jonathon,
    Thanks for a great tutorial – your efforts are much appreciated.
    I have just discovered your YouTube channel and blog and have been using it to update my old network of hand coded sensors using the Tasmota/MQTT/Influx/Grafana stack. I am sure you don’t go back and update the blog posts but I just noticed that an install of mosquitto today requires the parameter “listener 1883” to be added to the mosquitto.conf file in order for it to start listening. At least that was my experience on a Pi. Perhaps someone else following this tutorial might find my comment of use.

  11. I have been enjoying learning with your channel. Thanks. I did some electronics 40 years ago at school and I am dipping my toe into electronics, IOT and Pi things.

    My project (called SPAR sewage pump anxiety reducer) is to move a sewage pump to low voltage switching and to add a pi zero w to add monitoring capability.

    I have some python publishing events the pi is observing to mosquito and I have some node red flows (is that the right term?) doing some experimental things like alerting, detecting missing heartbeats and logging to influxdb.

    My question is … What’s the best call in terms of putting the stack. I am almost certainly going to put grafana and influx on a different pi but part of me says keep node red on the pi w to keep it self contained.

    I think it’s likely I will build some other new IOT devices with my new gained skills, but I don’t yet know what. This project’s inception is the epitomy of the expression “necessity is the mother of invention”, just not sure what to make next … What will be the next necessity?

  12. Here are some changes I had to make to make the instructions work in the latest Raspian OS.

    Step 2) Install the InfluxDB time-series database
    Running this will fail (warn) :
    >wget -qO- https://repos.influxdata.com/influxdb.key | sudo apt-key add –
    with an error saying :
    >Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
    >OK
    So Instead you need to do this
    >wget -qO- https://repos.influxdata.com/influxdb.key | gpg –dearmor | sudo tee /usr/share/keyrings/influx-archive-keyring.gpg

  13. Step 4: Install Grafana
    (kind of the same problem as with Influx)
    i.e. you cannot use apt-key add anymore, instead you need to use
    wget -q -O – https://packages.grafana.com/gpg.key | gpg –dearmor | sudo tee /usr/share/keyrings/grafana-archive-keyring.gpg

    Further when trying to do
    >sudo apt update
    Hit:1 http://raspbian.raspberrypi.org/raspbian bullseye InRelease
    Hit:2 http://archive.raspberrypi.org/debian bullseye InRelease
    Hit:3 https://deb.nodesource.com/node_14.x bullseye InRelease
    Get:4 https://packages.grafana.com/oss/deb stable InRelease [12.1 kB]
    Hit:5 https://repos.influxdata.com/debian bullseye InRelease
    Err:4 https://packages.grafana.com/oss/deb stable InRelease
    The following signatures couldn’t be verified because the public key is not available: NO_PUBKEY 8C8C34C524098CB6
    Reading package lists… Done
    W: GPG error: https://packages.grafana.com/oss/deb stable InRelease: The following signatures couldn’t be verified because the public key is not available: NO_PUBKEY 8C8C34C524098CB6
    E: The repository ‘https://packages.grafana.com/oss/deb stable InRelease’ is not signed.
    N: Updating from such a repository can’t be done securely, and is therefore disabled by default.
    N: See apt-secure(8) manpage for repository creation and user configuration details.

    To solve this you need to set the signing explicitly like so
    >echo “deb [signed-by=/usr/share/keyrings/grafana-archive-keyring.gpg] https://packages.grafana.com/oss/deb stable main” | sudo tee -a /etc/apt/sources.list.d/grafana.list

    Now the apt update will work much better 🙂

  14. Hi, great tutorial thank you. I am stuck at influxdb though on add user with error:
    ERR: unable to parse authentication credentials
    Warning: It is possible this error is due to not setting a database.
    Please set a database with the command “use ”
    Any advice will be appreciated please

  15. Not complaining, but this tutorial is not complete, since December 2020. I am sure you know there are missing items starting in step 3 regarding Node-Red configuration. Then steps 6, 7 and 8 are not complete for sure. I do like the video and instructions up to the missing parts though.

    In my system I use Telegraf as the interface between MQTT and InfluxDB. All of my environmental sensors are sending data using MQTT. If using Telegraf for MQTT in, there is no need for Node-Red. However, I do use Node-Red for receiving data from my electricity consumption monitor and then posting it to InfluxDB.

    I have had my system running for about 7 years. In the past year my Influx v1 database got corrupted several times. I am not sure what caused the problems, so I am switching to a newer Raspi, a new storage device and InfluxDB V2.

Leave a Reply

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