[FreeBSD]: Send RTL433 sensor data to MariaDB in EZJail.

I want to monitor temperatures and humidity throughout our house. Thanks to the great work of the people building rtl-sdr and rtl_433, the major part of the work has already been done.

Getting rtl-433 up and running on FreeBSD 11.0 is quite simple. I'm using it to write all sensor data received into a MariaDB database. Just because it's possible, I'm running it inside a FreeBSD EZJail.

Here's how I did it:

# Add the following lines to your /etc/rc.conf to create a new clone of your
# loopback interface and to assign additional IPs to it:
cloned_interfaces="lo1"
ifconfig_lo1="inet 10.0.0.1 netmask 255.255.255.0"
# reboot, if necessary - or try to add the cloned interface on the command
# line, I can't remember how to do this, sorry...
#
# Create a new EZJail, setup the jails DNS, start and enter the jail
[root@dagger ~]# ezjail-admin create LOGGER 10.0.0.1
[root@dagger ~]# cp /etc/resolv.conf /usr/jails/LOGGER/etc/resolv.conf
[root@dagger ~]# ezjail-admin start LOGGER
[root@dagger ~]# ezjail-admin console LOGGER
#
# Install rtl-sdr (answering 'y' to the questions about pkg)
# Install other required packages
root@LOGGER:~ # pkg install rtl-sdr curl unzip cmake gcc
# ...
# Download rtl433 from github and unzip it
root@LOGGER:~ # curl -o rtl433.zip https://codeload.github.com/merbanan/rtl_433/zip/master
root@LOGGER:~ # unzip rtl433.zip
root@LOGGER:~ # cd rtl_433-master/
# Build it
root@LOGGER:~/rtl_433-master # mkdir build
root@LOGGER:~/rtl_433-master # cd build
root@LOGGER:~/rtl_433-master/build # cmake ../
root@LOGGER:~/rtl_433-master/build # make
root@LOGGER:~/rtl_433-master/build # make install
# Test it
root@LOGGER:~ # rtl_433
Registering protocol [1] "Rubicson Temperature Sensor"
[...]
Found 1 device(s):
  0:  Generic, RTL2832U, SN: 77771111153705700

Using device 0: Generic RTL2832U
Found Rafael Micro R820T tuner
Exact sample rate is: 250000.000414 Hz
Sample rate set to 250000.
Bit detection level set to 0 (Auto).
Tuner gain set to Auto.
Reading samples in async mode...
Tuned to 433920000 Hz.
2017-04-26 19:38:39 : Prologue sensor  : 5  : 96
 Channel:  1
 Battery:  OK
 Button:  0
 Temperature:  22.60 C
 Humidity:  34 %
root@LOGGER:~ # 

If you would like to run rtl_433 as a non-root user inside the jail, then you need to grant access to user applications to usb devices. With the information from https://forums.freebsd.org/threads/49946/ I got this up and running, too. I had to add the following to /etc/defaults/devfs.rules:

# Devices usually found in a jail.
#
[devfsrules_jail=1]
add include $devfsrules_hide_all
add include $devfsrules_unhide_basic
add include $devfsrules_unhide_login
add path zfs unhide
add path ttyU* unhide
add path ugen* unhide
add path usb* unhide
add path usb/* unhide

And restart devfs and the jail

[root@dagger ~]# service devfs restart
[root@dagger ~]# ezjail-admin restart LOGGER

Install requirements for the bash script that inserts rtl_433 sensor data into MySQL:

[root@dagger ~]# pkg install mariadb101-client bash jq

Create your database table with:

CREATE TABLE `sensorreadings` (
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  `humidity` int(11) NOT NULL,
  `temp` float NOT NULL,
  PRIMARY KEY (`timestamp`,`hrid`),
  KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And finally the script I'm using to read and parse rtl_443 sensor data and push them into my MariaDB database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/usr/local/bin/bash

MYSQL_HOST="[your mysql host]"
MYSQL_USER="[your mysql username]"
MYSQL_PWD="[your mysql password]"
MYSQL_DB="[your mysql db]"
while :
do
        /usr/local/bin/rtl_433 -f 433871000 -s 1000000 -l 4000 -F json -R 03 |
        while read json ;
        do
                ID=`echo $json | /usr/local/bin/jq '.id'`
                RID=`echo $json | /usr/local/bin/jq '.rid'`
                TEMP=`echo $json | /usr/local/bin/jq '.temperature_C'`
                HUMIDITY=`echo $json | /usr/local/bin/jq '.humidity'`
                SQL="INSERT INTO sensorreadings (id, rid, humidity, temp) 
                          VALUES ($ID, $RID, $HUMIDITY, $TEMP);"
                echo $SQL
                /usr/local/bin/mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PWD \
                                     --database $MYSQL_DB --execute "$SQL"
        done
        sleep 10
done