Python 3 implementation of a simple UDP syslog server which inserts the recieved messages into a MariaDB [1] or MySQL [2] database.
The Python script will start a syslog server which inserts every recieved message and write it into a MariaDB [1] or MySQL [2] database. The used timestamp is in UTC.
Every time the script is executed it will check if the database and table exist and if not create them. Then the script is executed until it will be manually closed. Every time a message is recieved on the defined port the data will be decoded as UTF-8, inserted in the database and printed out in the terminal.
Be aware the that the UDP packages are not encypted. It is taken care off avoiding SQL injections because the server might be facing the internet.
The script uses the python module socketserver [3] and the external module
mysql-connector-python [4] . See requirements.txt for installed packages and the
used versions. The file is created with pip3 freeze > requirements.txt.
First install the required mysql-connector-python module in the global Python 3 environment or in a virtual Python 3 environment. The latter has the advantage that the packages are isolated from other projects and also from the system wide installed global once. If things get messed up, the virtual environment can just be deleted and created from scratch again. For more informations about virtual environments in Python 3, see venv1 [5] and venv2 [6] .
pip3 install mysql-connector-pythonThen modify the PORT, db_name, table_name, db_user, db_password,
db_host and db_port parameters in the syslogserver.py script and
execute it.
python3 syslogserver.pyOr use e.g. tmux [7] to execute it in the background.
An example .service file is also included to show how to run the syslog server as a systemd service at startup. For more informations, see systemd.service [8] . In the example .service file a virtual Python 3 environment is used to execute the script. The local user name and the path to the virtual Python 3 environment needs to be adjusted before it can be used.
To activate the systemd service execute following commands.
sudo cp syslogserver.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl start syslogserver.service
sudo systemctl enable syslogserver.serviceThe script was developed to recieve syslog messages from a MikroTik [9] wAP LTE kit [10] and insert them into a MariaDB [1] database to be able to display them via Grafana [12] .
The following settings are used in RouterOS. They need to be applied via CLI [11] .
Change remote and remote-port to the one where the syslog server is
listening.
/system logging action>
set 3 remote=123.123.123.123 remote-port=12312Next command just prints out the settings of /system logging action .
/system logging action> print
Flags: * - default
0 * name="memory" target=memory memory-lines=1000 memory-stop-on-full=no
1 * name="disk" target=disk disk-file-name="flash/log"
disk-lines-per-file=1000 disk-file-count=2 disk-stop-on-full=no
2 * name="echo" target=echo remember=yes
3 * name="remote" target=remote remote=123.123.123.123 remote-port=12312
src-address=0.0.0.0 bsd-syslog=no syslog-time-format=bsd-syslog
syslog-facility=daemon syslog-severity=autoIt is also needed to add the remote syslog server as a destination for logs
on the wanted topics. The prefix= option is optional but useful to distinguish
between different devices as it is added in the logs which are sent to the remote
syslog server.
/system logging>
add action=remote prefix=example_prefix topics=info
add action=remote prefix=example_prefix topics=error
add action=remote prefix=example_prefix topics=warning
add action=remote prefix=example_prefix topics=criticalGrafana [12] is used to display the syslog messages.
In Grafana [12] the database has to be added as a datasource [13] . Then a table [14]
with following SQL query in the Metrics tab can be added to a dashboard. The
SQL query has to be adjusted to the used database/table/columns structure, see
script syslogserver.py for more details how the database/table/columns are
created. The AND message LIKE '%example_prefix%' part of the SQL query is
used to display only a certain device based on the above used prefix=
option.
SELECT
inserted_utc,
message
FROM logging.logs
WHERE $__timeFilter(inserted_utc) AND message LIKE '%example_prefix%'
ORDER BY inserted_utc DESChttps://gist.github.com/marcelom/4218010
| [1] | (1, 2, 3) https://mariadb.org/ |
| [2] | (1, 2) https://www.mysql.com/ |
| [3] | https://docs.python.org/3/library/socketserver.html |
| [4] | https://pypi.org/project/mysql-connector-python/ |
| [5] | https://docs.python.org/3/tutorial/venv.html |
| [6] | https://docs.python.org/3/library/venv.html |
| [7] | https://en.wikipedia.org/wiki/Tmux |
| [8] | https://www.raspberrypi.org/documentation/linux/usage/systemd.md |
| [9] | https://mikrotik.com/ |
| [10] | https://mikrotik.com/product/wap_lte_kit |
| [11] | https://wiki.mikrotik.com/wiki/Manual:First_time_startup#CLI |
| [12] | (1, 2, 3) https://grafana.com/ |
| [13] | http://docs.grafana.org/features/datasources/mysql/ |
| [14] | http://docs.grafana.org/features/panels/table_panel/ |