Syslog-ng logging from Mikrotik to Mysql

Posting this primarily as reference to self

So, why would you do this? Well first of all Mikrotik routers may have very limited memory, so for storing logs, searching entries and specify your custom view on a Web page is one reason.

Other reasons: A nice learning process. I find it somewhat challenging and rewarding in the end. Especially the php coding and sql queries to a html table had me going for some hours..as I am not a programmer ?

OS: Debian Jessie Release 8.11

Php: v. 7.3.5

Syslog-ng: v. 3.5.6

Apache: v.2.4.10

MySQL: v. 14.14

Tool tips: Use Atom when editing code, with the “remote FTP” package for uploading.

Mikrotik terminal code, add syslogging rules:

You can also add furhter logging actions using the graphical Winbox, in system/logging, “rules” and “actions”

/system logging action
set 3 remote=192.168.100.1
add name=ZLogServer remote=192.168.1.2 target=remote
/system logging
add action=ZLogServer topics=pppoe,ppp,info
add action=ZLogServer topics=system,info

Syslog-ng config, add to /etc/syslog-ng/syslog-ng.conf

@include "/etc/syslog-ng/conf.d/"
# Accept connection on UDP
source s_net { udp (); };
 
# MIKROTIK ###########
# Add Filter to add our mikrotik
filter f_mikrotik { host( "192.168.1.1" ); };
log { source ( s_net ); filter( f_mikrotik ); destination ( df_mikrotik ); };
destination df_mikrotik {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
source s_mysql {
udp(port(514));
tcp(port(514));
};

log {
source(s_net);
filter(f_mikrotik);
destination(d_mysql);
};

Create Zlogs folder mkdir /var/log/zlogs

service syslog-ng restart

verify incoming syslog data:

tail -f /var/log/zlogs/#HOST.YYYY.MM.DD.log

Mysql, create database

Logon to mysql in terminal or via PhpMyAdmin, to run T-SQL code for creating DB and tables:

#Create DB
create database syslog;
use syslog;
 
#Create tables in syslog db
CREATE TABLE `logs` (
`host` varchar(32) DEFAULT NULL,
`facility` varchar(10) DEFAULT NULL,
`priority` varchar(10) DEFAULT NULL,
`level` varchar(10) DEFAULT NULL,
`tag` varchar(10) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`program` varchar(100) DEFAULT NULL,
`msg` text,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `datetime` (`datetime`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
exit;

Bash script to create Mysql.pipe:

mkdir /temp
touch /temp/mysql-2-syslog.sh
chmod +x /temp/mysql-2-syslog.sh
nano /temp/mysql-2-syslog.sh

containing:

#!/bin/bash
SQLID="username"
SQLPASS="password"
export MYSQL_PWD=$SQLPASS
if [ ! -e /var/log/mysql.pipe ]
then
mkfifo /var/log/mysql.pipe
fi
while [ -e /var/log/mysql.pipe ]
do
mysql -u$SQLID syslog "the char less than"/var/log/mysql.pipe "the char greater than"  /dev/null
done

run the script manually as a test: /temp/mysql-2-syslog.sh &

Add to startup like sudo crontab -e
@reboot /temp/mysql-2-syslog.sh &

Check SQL data in the table “logs” using mysql:

select * from logs;

Write a beautiful php page to display you syslog data on a lamp server: