Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server
Part # 2 – Centralized Syslog-ng logging to MySql DB >> You are here
Part # 3 Minimized logging to mysql with dynamic tables & trimming
In continuation to existing post related to syslog-ng, Following post illustrates how you can push syslog logs entries to mysql DB for easy access and search functions.
We all know that if you have dozen’s of switches / routers / Linux systems to manage, its not an easy task to look at each device’s log for inspection and health check. This is surely an Daunting task for any administrator or support personnel. Recently I was facing some difficulty in troubleshooting remote switch. therefore I made an syslog server and made all switches/routers info logging to this syslog which then put a copy of log in mysql DB as well as in local file too.
This post is not made for likes, dislikes or sharing purposes. Its just simple knowledge sharing on how I managed to achieve the task that looks difficult in the beginning but actually was easy when it got deployed finally.
First make sure you have an working syslog-ng installation. for more information look at syslog-ng part#1
Once you have working syslog-ng, then use the following sample /etc/syslog-ng/syslog-ng.conf
OS : Ubuntu 14 / 64bit
Syslog-NG: 3.5.3 [using default apt-get install package]
#Prerequisites
Install various packages including syslog-ng server, phpmyadmin, mysql server to store logs in DB, supporting libraries etc.
apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl phpmyadmin libdbd-pgsql aptitude libboost-system-dev libboost-thread-dev libboost-regex-dev syslog-ng libmongo-client0 libesmtp6 syslog-ng-mod-sql libdbd-mysql libdbd-mysql
During installation of above packages it may ask you to enter mysql/phpmyadmin password, do so as required. Once all the packages are installed, edit the syslog-ng file by
nano /etc/syslog-ng/syslog-ng.conf
& use below sample file to start with …
syslog-ng sample file
Note: Make sure to modify/add/remove entries like mikrotik router ip , sql credentials as per your requirements.
# Syslog NG Config file for Ubuntu 12.4
# Syed Jahanzaib / aacable at hotmail dot com
@version: 3.3
@include "scl.conf"
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
owner("root"); group("adm"); perm(0640); stats_freq(0);
bad_hostname("^gconfd$");
};
source s_src {
system();
internal();
};
destination d_auth { file("/var/log/auth.log"); };
destination d_cron { file("/var/log/cron.log"); };
destination d_daemon { file("/var/log/daemon.log"); };
destination d_kern { file("/var/log/kern.log"); };
destination d_lpr { file("/var/log/lpr.log"); };
destination d_mail { file("/var/log/mail.log"); };
destination d_syslog { file("/var/log/syslog"); };
destination d_user { file("/var/log/user.log"); };
destination d_uucp { file("/var/log/uucp.log"); };
destination d_mailinfo { file("/var/log/mail/mail.info"); };
destination d_mailwarn { file("/var/log/mail/mail.warn"); };
destination d_mailerr { file("/var/log/mail/mail.err"); };
destination d_newscrit { file("/var/log/news/news.crit"); };
destination d_newserr { file("/var/log/news/news.err"); };
destination d_newsnotice { file("/var/log/news/news.notice"); };
destination d_debug { file("/var/log/debug"); };
destination d_error { file("/var/log/error"); };
destination d_messages { file("/var/log/messages"); };
destination d_console { usertty("root"); };
destination d_console_all { file("/dev/tty10"); };
destination d_xconsole { pipe("/dev/xconsole"); };
destination d_ppp { file("/var/log/ppp.log"); };
filter f_dbg { level(debug); };
filter f_info { level(info); };
filter f_notice { level(notice); };
filter f_warn { level(warn); };
filter f_err { level(err); };
filter f_crit { level(crit .. emerg); };
filter f_debug { level(debug) and not facility(auth, authpriv, news, mail); };
filter f_error { level(err .. emerg) ; };
filter f_messages { level(info,notice,warn) and
not facility(auth,authpriv,cron,daemon,mail,news); };
filter f_auth { facility(auth, authpriv) and not filter(f_debug); };
filter f_cron { facility(cron) and not filter(f_debug); };
filter f_daemon { facility(daemon) and not filter(f_debug); };
filter f_kern { facility(kern) and not filter(f_debug); };
filter f_lpr { facility(lpr) and not filter(f_debug); };
filter f_local { facility(local0, local1, local3, local4, local5,
local6, local7) and not filter(f_debug); };
filter f_mail { facility(mail) and not filter(f_debug); };
filter f_news { facility(news) and not filter(f_debug); };
filter f_syslog3 { not facility(auth, authpriv, mail) and not filter(f_debug); };
filter f_user { facility(user) and not filter(f_debug); };
filter f_uucp { facility(uucp) and not filter(f_debug); };
filter f_cnews { level(notice, err, crit) and facility(news); };
filter f_cother { level(debug, info, notice, warn) or facility(daemon, mail); };
filter f_ppp { facility(local2) and not filter(f_debug); };
filter f_console { level(warn .. emerg); };
log { source(s_src); filter(f_auth); destination(d_auth); };
log { source(s_src); filter(f_cron); destination(d_cron); };
log { source(s_src); filter(f_daemon); destination(d_daemon); };
log { source(s_src); filter(f_kern); destination(d_kern); };
log { source(s_src); filter(f_lpr); destination(d_lpr); };
log { source(s_src); filter(f_syslog3); destination(d_syslog); };
log { source(s_src); filter(f_user); destination(d_user); };
log { source(s_src); filter(f_uucp); destination(d_uucp); };
log { source(s_src); filter(f_mail); destination(d_mail); };
log { source(s_src); filter(f_news); filter(f_crit); destination(d_newscrit); };
log { source(s_src); filter(f_news); filter(f_err); destination(d_newserr); };
log { source(s_src); filter(f_news); filter(f_notice); destination(d_newsnotice); };
log { source(s_src); filter(f_debug); destination(d_debug); };
log { source(s_src); filter(f_error); destination(d_error); };
log { source(s_src); filter(f_messages); destination(d_messages); };
log { source(s_src); filter(f_console); destination(d_console_all);
destination(d_xconsole); };
log { source(s_src); filter(f_crit); destination(d_console); };
@include "/etc/syslog-ng/conf.d/"
source s_net { udp (); };
#Mikrotik IP
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));
};
# Play with below, some confusion here
destination d_mysql { pipe("/var/log/mysql.pipe" template("INSERT INTO
logs (host,facility,priority,level,tag,datetime,program,msg) VALUES
('$HOST','$FACILITY','$PRIORITY','$LEVEL','$TAG','$YEAR-$MONTH-$DAY
$HOUR:$MIN:$SEC','$PROGRAM','$MSG');\n") template-escape(yes)); };
#destination d_mysql {
#sql(type(mysql)
#host("localhost")
#username("root")
#password("SQLPASSWORD")
#database("syslog")
#table("_${R_YEAR}_${R_MONTH}_${R_DAY}")
#columns("id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null")
#values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG")#
#indexes("id","host","priority"));
#};
log {
source(s_net);
filter(f_mikrotik);
destination(d_mysql);
};
Save & Exit.
MYSQL DB to store syslog-ng logs
Create DB in mysql where are logs will be stored.
LOGIN to mysql & create DB/tables
mysql-uroot -pMYSQL_OR_ROOT_PASS
#Create DB / Syed Jahanzaib 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 make mysql mysql.pipe
Create a file name mysql-2-syslog.sh and make sure its start with system startup
mkdir /temp touch /temp/mysql-2-syslog.sh chmod +x /temp/mysql-2-syslog.sh nano /temp/mysql-2-syslog.sh
# Paste following in this file
#!/bin/bash SQLID="root" SQLPASS="zaib1234" 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 <span style="color: var(--color-neutral-600);">< /var/log/mysql.pipe </span>> /dev/null done
WordPress is not letting proper pasting of codes this is why I am attaching image below just for reference.
Save & Exit.
Add it in startup like /etc/rc.local (before exit line) Now run it manually by
/temp/mysql-2-syslog.sh &
Now perform any activity @ mikrotik like open new terminal or login to winbox, or plug-unplug any cable from Cisco switch, and then issue following command from mysql cli (or use phpmyadmin)
Fetch information from MYSQL
Now get info from table
mysql> select * from logs; +---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+ | host | facility | priority | level | tag | datetime | program | msg | seq | +---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+ | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 | | 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:09 | 050112 | .Nov 9 10:24:04: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 6 | | 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:09 | 050113 | .Nov 9 10:24:05: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 7 | | 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:25:28 | 050114 | .Nov 9 10:24:23: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 8 | | 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:25:29 | 050115 | .Nov 9 10:24:24: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 9 | | 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:26:28 | 050116 | .Nov 9 10:25:23: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to up | 10 | | 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:26:28 | 050117 | .Nov 9 10:25:24: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to up | 11 | | 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 10:27:20 | 050118 | .Nov 9 10:26:15: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/1, changed state to down | 12 | | 10.0.0.3 | local7 | err | err | bb | 2017-11-10 10:27:21 | 050119 | .Nov 9 10:26:16: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/1, changed state to down | 13 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 | | 10.0.0.3 | local7 | notice | notice | bd | 2017-11-10 11:15:52 | 050120 | .Nov 9 11:14:47: %LINEPROTO-5-UPDOWN: Line protocol on Interface GigabitEthernet2/0/12, changed state to down | 22 | | 10.0.0.3 | local7 | err | err | bb | 2017-11-10 11:15:52 | 050121 | .Nov 9 11:14:48: %LINK-3-UPDOWN: Interface GigabitEthernet2/0/12, changed state to down | 23 | +---------------+----------+----------+--------+------+---------------------+-----------------+----------------------------------------------------------------------------------------------------------------+-----+ 23 rows in set (0.00 sec)
-
- Some examples of logs fetching command
Show all log files select * from logs; OR to look for speicific HOST select * from logs where host='10.0.0.1';
mysql> select * from logs where host='10.0.0.1'; +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | host | facility | priority | level | tag | datetime | program | msg | seq | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:23:04 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 1 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 2 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 3 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 4 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:24:33 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 5 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ 13 rows in set (0.00 sec)
OR to look for speicific HOST between specific dates select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:00:26' and '2017-11-10 10:50:26' ;
mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' ; +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | host | facility | priority | level | tag | datetime | program | msg | seq | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:27 | system,info,acc | user admin logged in from 101.11.11.161 via winbox | 15 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 16 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged in from 101.11.11.161 via telnet | 17 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via winbox | 18 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 19 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:28 | system,info,acc | user admin logged out from 101.11.11.161 via telnet | 20 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ 8 rows in set (0.00 sec)
Or look for particular error on Mikrotik log for incorrect login in specific date time range select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr';
mysql> select * from logs where host='10.0.0.1' AND datetime between '2017-11-10 10:40:00' and '2017-11-10 10:50:26' and program='system,error,cr'; +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | host | facility | priority | level | tag | datetime | program | msg | seq | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:40:26 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 14 | | 10.0.0.1 | user | notice | notice | 0d | 2017-11-10 10:46:18 | system,error,cr | login failure for user admin1 from 101.11.11.161 via winbox | 21 | +--------------+----------+----------+--------+------+---------------------+-----------------+-------------------------------------------------------------+-----+ 2 rows in set (0.00 sec)
– Simple PHP page to show results from mysql table
Following is a php page I made to fetch results from the mysql table and display it in browser.
<?php
// by Syed Jahanzaib
$host="localhost";
$username="root";
$password="PASSWORD"; // Mysql password
$db_name="syslog"; // Database name
$tbl_name="logs"; // Table name
// Connect to server and select databse
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
//$sql="SELECT * FROM $tbl_name ORDER BY `LOGS` datetime LIMIT 0 , 10";
$sql = "SELECT * FROM `logs`\n"
. "ORDER BY `logs`.`datetime` DESC LIMIT 0, 50 ";
$result=mysql_query($sql);
// Define $host_column=1
echo '</pre>
'; echo ' '; while($rows=mysql_fetch_array($result)){ //$host_column assign here from result $host_column=$rows['host']; $priority_column=$rows['priority']; if($priority_column=='err'){ echo " "; }else if($host_column=='101.11.11.36'){ echo " "; }else if($host_column=='101.11.12.225'){ echo " "; } } echo '
<table border="1" width="1400" cellspacing="1" cellpadding="1" align="left">
<tbody>
<tr>
<th>ID</th>
<th>Host</th>
<th>Date</th>
<th>Info</th>
<th>Type</th>
<th>Messgae</th>
</tr>
<tr bgcolor="#FFA07A">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
<tr bgcolor="#bbbbbb">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
<tr bgcolor="#cccccc">
<td>".$rows['seq']."</td>
<td>".$rows['host']."</td>
<td>".$rows['datetime']."</td>
<td>".$rows['priority']."</td>
<td>".$rows['program']."</td>
<td>".$rows['msg']."</td>
</tr>
</tbody>
</table>
<pre>';
mysql_close();
?>
– Another PHP / Mysql & AJAX base example
to show record with pagination , Date selection and Live Search button option.
http://PHP MYSQL AJAX – WITH PAGINATION / DATE / SEARCH OPTION – BY SYED JAHANZAIB
Sample Output
TIP
Query log from multiple tables in syslog DB
Sent by by Mr. Faryad
Q:
For example if your SYSLOG creates daily date wise table on daily basis then how we will write query to search in multiple tables?
In this case simple one line query will not work. For Example you saves 3 days data in 3 tables i.e. `2017-11-10`, `2017-11-11` and `2017-11-12`, and you need to search in these 3 tables, in this case you should need UNION method of MYSQL. I am giving little code here:
select * from `2017-11-10` where host=’10.0.0.1′ AND datetime between ‘2017-11-10 10:00:26’ and ‘2017-11-10 10:50:26′ UNION select * from `2017-11-11` where host=’10.0.0.1’ AND datetime between ‘2017-11-11 10:00:26’ and ‘2017-11-11 10:50:26′ UNION select * from `2017-11-12` where host=’10.0.0.1’ AND datetime between ‘2017-11-12 10:00:26’ and ‘2017-11-12 10:50:26’ ;
this multi line query will fetch records from 3 tables just like one line query.
TIP:
Unable to initialize database access (DBI); rc=’-1′, error=’No such file or directory (2)’
If you are seeing error on syslog-ng service start/restart
apt-get install libdbd-mysql
Persist names error when using multiple hosts
Error checking the uniqueness of the persist names, please override it with persist-name option. Shutting down.; persist_name=’affile_dd_writers(
To settle this add persistent name in config like this
Working example for 5 hosts
filter f_mikrotik10 { host("192.168.0.10") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik10 ); destination ( df_mikrotik10 ); };
destination df_mikrotik10 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
filter f_mikrotik14 { host("192.168.0.14") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik14 ); destination ( df_mikrotik14 ); };
destination df_mikrotik14 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
filter f_mikrotik26 { host("192.168.0.26") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik26 ); destination ( df_mikrotik26 ); };
destination df_mikrotik26 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
filter f_mikrotik34 { host("192.168.0.34") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik34 ); destination ( df_mikrotik34 ); };
destination df_mikrotik34 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
filter f_mikrotik42 { host("192.168.0.42") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik42 ); destination ( df_mikrotik42 ); };
destination df_mikrotik42 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
log {
source(s_net);
filter(f_mikrotik10);
destination(d_mysql);
};
log {
source(s_net);
filter(f_mikrotik14);
destination(d_mysql);
};
log {
source(s_net);
filter(f_mikrotik26);
destination(d_mysql);
};
log {
source(s_net);
filter(f_mikrotik34);
destination(d_mysql);
};
log {
source(s_net);
filter(f_mikrotik42);
destination(d_mysql);
};
Regard’s
Syed Jahanzaib
Syed Jahanzaib - Personal Blog to Share Knowledge ! 
[…] Part # 2 – Centralized Syslog-ng logging to MySql DB […]
LikeLike
Pingback by Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server | Syed Jahanzaib Personal Blog to Share Knowledge ! — November 23, 2017 @ 9:06 AM
Assalam-o-Alaikum!
Sir I really appreciate your work. I got some Mikrotik and Linux related knowledge from you site. In the mean time I came here for some research regarding SYSLOG Server but I don’t Googled, just come to your site directly and “Search Mara” :). You explained everything very well, and I got what I want, and InshaAllah soon I will work on it. جزاک اللہ۔
Sir I have a little code for your post. Sir for example SYSLOG server creates new table on daily basis then how we will write query to search in multiple tables? In this case simple one line query will not work. For Example you saves 3 days data in 3 tables i.e. `2017-11-10`, `2017-11-11` and `2017-11-12`, and you need to search in these 3 tables, in this case you should need UNION method of MYSQL. I am giving little code here:
select * from `2017-11-10` where host=’10.0.0.1′ AND datetime between ‘2017-11-10 10:00:26’ and ‘2017-11-10 10:50:26′ UNION
select * from `2017-11-11` where host=’10.0.0.1’ AND datetime between ‘2017-11-11 10:00:26’ and ‘2017-11-11 10:50:26′ UNION
select * from `2017-11-12` where host=’10.0.0.1’ AND datetime between ‘2017-11-12 10:00:26’ and ‘2017-11-12 10:50:26’ ;
this multi line query will fetch records from 3 tables just like one line query.
Sir hope you will say “SHABASH” as students like “SHABASHI” in every stage on life from their USTAD :).
LikeLike
Comment by Muhammad Faryad — January 7, 2018 @ 8:01 AM
I do appreciate your tip. Actually you are probably taking example from DMA which creates daily table according to date.
in this post, there is no multiple tables. but , yes , for daily tables, this code will help 🙂
I have added this TIP in the post with your name as credit. Thanks.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — January 8, 2018 @ 11:46 AM
Thank u so much sir for adding my code as TIP.
LikeLike
Comment by Muhammad Faryad — January 10, 2018 @ 4:03 AM
Assalam O Alaikum!
Sir is there anyway to store users activity (visited sites data and from where) into database. I tried web proxy enabling technique but I didn’t know that how to store these information into remote database. ِAfter googling I found a tutorial in which mikrotik is configured to send data to remote server but on the other hand there is windows based software which receives data and store it into file and then show logs. But problem is that “How to handle incoming logs on remote server?” I tried my best but no result. Sir will you help me, I shall be thankful for this.
جزاک اللہ
LikeLike
Comment by Muhammad Faryad — January 12, 2018 @ 7:13 AM
Boa Tarde,
estou tentar utilizar o syslog + mysql segiundo o tutoral e quando adiciono a sessão “destination para mysql e reinicio o serviço syslog-ng ocorre um erro,
e o serviço syslog-ng nao inicia ate eu remover o mysql da configuração.
LikeLike
Comment by Marcos Henrique — December 1, 2018 @ 1:00 AM
[…] (either windows or linux base) to store hundreds of GB’s of logs. I would prefer linux base SYSLOG-NG). Tracking of users for legal reasons means hundreds GB’s of extra logging is required, as […]
LikeLike
Pingback by Mikrotik CGNAT | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 1, 2020 @ 5:34 PM
[…] Part # 2 – Centralized Syslog-ng logging to MySql DB […]
LikeLike
Pingback by Syslog-ng – Part 3: Minimized logging to mysql with dynamic tables & trimming | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 8, 2020 @ 1:28 PM