Learn how to keep track of all your smart IoT devices by having them all log to a single MySQL database using rsyslog.
It doesn’t take long after you decide to start smart-ifying your house before you have more devices doing more things than you can reasonable keep track of! I’m sure I am a bit of an extreme example, but as I write this I have no less than eight Raspberry Pis running various things throughout the house like my smart-mirror, gaming console, 3Ds printers, etc. I’m not even going to try and bother to count the number of ESP8266’s and of course there are my routers, modem, and other devices too.
Each one of these devices generates logs of what it’s doing. By default these logs get written on the devices themselves, typically in a file stuck away in some directory, where they are almost entirely and completely useless. In fact, the only time you even bother to look at them is when something goes wrong — or you need to lay waste to them because they’ve eaten up your disk space. It’s not really ideal.
Logs should be useful. They should at least be easily accessible and structured in a way that allows you to dig into them relatively painlessly.
Enter Syslog. You may not even know it, but even if you don’t have a single machine your aware of running Linux in your home Syslog is probably a part of your life. Most routers for example, use or support Syslog as the standard by which they log what’s going on with themselves. It’s an extremely old platform, dating back to the very beginning of UNIX and it serves as the de-facto standard way the world creates log files. Moreover, it supports out-of-box the ability to consolidate logs as well, transmitting them over the network to a centralized server!
In modern versions of Linux (including on Raspberry Pis) Syslog is implemented using the rsyslog package. Today we’re going to show you how to configure this package to consolidate your logs into a single location on your network and store them in a MySQL database so you can query them.
Configuring The Central Server
The first thing you will need to do is have a central server for all of your logs to be transmitted to. In my case, I already have an in-home server running Debian Linux that was an obvious choice. Raspberry Pis can also make good central servers (depending on how serious your logging is) if you don’t have a dedicated Debian machine to use. Oh, and by the way, this should actually work on any relatively modern linux machine — please tweak these instructions as necessary for your particular flavor. I also assume you already have a MySQL server on this machine set up as well to store your logs.
Note: A central logging server needs to have a static IP address, or a hostname that resolves to the correct IP address – otherwise how could your various devices know where to send logs?!
Installing Rsyslog and MySQL support
Just to be safe, you should start by making sure you have the proper packages installed. For us this means executing the following as a super user (i.e. using root or ‘sudo’):
$ sudo apt-get install rsyslog $ sudo apt-get install rsyslog-mysql
This will make sure you have the proper packages installed. When you execute the installation for the rsyslog-mysql package, it will prompt you if you want it to set up the database. In my case I said yes, and it took care of all the hard work for me!
Confirming your local logs are now being stored in MySQL
Once you’ve installed these packages, rsyslog should basically immediately start logging things to MySQL for you in a database called SysLog. Log into your mysql from the command line and check it out to make sure it’s working:
$ mysql -u <your username> -p Enter password: MariaDB [(none)]> use Syslog Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [Syslog]> show tables; +------------------------+ | Tables_in_Syslog | +------------------------+ | SystemEvents | | SystemEventsProperties | +------------------------+ 2 rows in set (0.01 sec) MariaDB [Syslog]> select * from SystemEvents limit 1; +----+------------+---------------------+---------------------+----------+----------+----------+-------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-------------+--------------+-----------------+----------+ | ID | CustomerID | ReceivedAt | DeviceReportedTime | Facility | Priority | FromHost | Message | NTSeverity | Importance | EventSource | EventUser | EventCategory | EventID | EventBinaryData | MaxAvailable | CurrUsage | MinUsage | MaxUsage | InfoUnitID | SysLogTag | EventLogType | GenericFileName | SystemID | +----+------------+---------------------+---------------------+----------+----------+----------+-------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-------------+--------------+-----------------+----------+ | 1 | NULL | 2018-06-07 12:28:57 | 2018-06-07 12:28:57 | 3 | 6 | sarah | Stopping System Logging Service... | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | systemd[1]: | NULL | NULL | NULL | +----+------------+---------------------+---------------------+----------+----------+----------+-------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-------------+--------------+-----------------+----------+ 1 row in set (0.00 sec) MariaDB [Syslog]> quit $
Awesome! Now, we need to configure rsyslog to accept logs from other sources than just what is produced locally.
Enabling remote logging
To enable accepting of remote logs into your MySQL database we need to configure rsyslog to do so. This can be done by editing the /etc/rsyslog.conf file as super user and enabling a few modules:
$ sudo vim /etc/rsyslog.conf
Look for these lines near the type of the configuration file, and uncomment them by removing the # symbol at the start of the line:
# provides UDP syslog reception module(load="imudp") input(type="imudp" port="514") # provides TCP syslog reception module(load="imtcp") input(type="imtcp" port="514")
Note that the default syslog port is 514. Unless you have a good reason you want to change that I recommend you leave it as-is, but make note of it regardless for the following steps. Either way, save the configuration, and then restart rsyslog using:
$ sudo service rsyslog restart
You can make sure that rsyslog is still running and happy by checking it’s status:
$ sudo service rsyslog status
● rsyslog.service - System Logging Service
Loaded: loaded (/lib/systemd/system/rsyslog.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2018-06-07 13:31:06 EDT; 28s ago
Docs: man:rsyslogd(8)
RSyslog Documentation
Main PID: 695 (rsyslogd)
Tasks: 10 (limit: 4915)
CGroup: /system.slice/rsyslog.service
└─695 /usr/sbin/rsyslogd -n
Jun 07 13:31:06 sarah systemd[1]: Starting System Logging Service...
Jun 07 13:31:06 sarah liblogging-stdlog[695]: [origin software="rsyslogd" swVersion="8.24.0" x-pid="695" x-info="http://www.rsyslog.com"] start
Jun 07 13:31:06 sarah systemd[1]: Started System Logging Service.
Let’s also make sure that rsyslog is listening on the proper ports as well using netstat:
$ sudo netstat -tulpn | grep rsyslog tcp 0 0 0.0.0.0:514 0.0.0.0:* LISTEN 14343/rsyslogd tcp6 0 0 :::514 :::* LISTEN 14343/rsyslogd udp 0 0 0.0.0.0:514 0.0.0.0:* 14343/rsyslogd udp6 0 0 :::514 :::* 14343/rsyslogd
Finally, in order to actually accept remote logs you need to make sure your firewall (if configured) is setup to to allow incoming connections to the syslog port (514 by default) on both UDP and TCP:
$ sudo ufw allow 514/tcp $ sudo ufw allow 514/udp
(note, there are a lot of different ways to do firewall things, if the above commands don’t work for you, use the correct ones!)
That’s it! You now have a central syslog server setup and storing log files into MySQL for easy querying. Now, let’s talk a little bit about logging clients.
Configuring Syslog clients
There are as many different devices that support syslog as a logging mechanism as there are devices. My routers all support it, my AT&T modem supports it, and of course every variety of linux basically supports it as well. How you will configure your devices to log to your central server will vary wildly. Basically you will just want to poke around the configuration settings until you find a way to enable Syslog support at which point it will ask you for a server & port to transmit the logs to. Just type in the hostname or IP address of your server (and the port if you changed it from it’s standard 514) and that’s it!!
For linux machines running their own rsyslog (read: your Raspberry Pis), you will need to do a little more manual configuration to have them start forwarding their logs to your central server. On each of those machines you will need to open up your /etc/rsyslog.conf file for them and make some changes. This time, instead of enabling the modules to accept remote logs as you did in your central server, you will instead add a new rule to forward the logs to your central server:
$ sudo vim /etc/rsyslog.conf <scroll down until you find> ############### #### RULES #### ############### And add this rule: *.* action(type="omfwd" target="<SYSLOG SERVER IP ADDRESS>" port="514" protocol="tcp" action.resumeRetryCount="100" queue.type="linkedList" queue.size="10000")
This rule is configured to forward all of your logs to the central server. If the central server can’t be reached for any reason, it will queue up the logs up to a maximum of 10,000 entries and retry 100 times before throwing those logs away. So keep that in mind, because if your central server can’t be reached it’s entirely possible that logs will not be recorded! That said, they should always still be available on the local machine.
What about IoT devices like the ESP8266?
What about all of our other smart devices? Well as it turns out, that’s easy enough to handle to. An Arduino-compatible library that supports ESP8266 devices already exists. Just include that in your firmware to connect to your central server and you’ll be logging IoT events in no time. If that sounds like too much for you don’t worry, I’ll be adding syslog support to CoogleIOT very soon as well.
Happy logging!