Syed Jahanzaib Personal Blog to Share Knowledge !

January 8, 2020

Syslog-ng – Part 3: Minimized logging to mysql with dynamic tables & trimming

Filed under: Linux Related, Mikrotik Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:27 PM

syslog cgnat

Revision: 7th-JAN-2020


In continuation to existing posts related to syslog-ng, Following post illustrates on how you can log only particular messages with pattern matching and let syslog-ng creates dynamic table based on the dates so that searching/querying becomes easy.

This task was required in relation to CGNAT logging. you may want to read it here

https://aacable.wordpress.com/2020/01/01/mikrotik-cgnat/

Hardware Software used in this post:

  • Mikrotik Routerboard – firmware 6.46.x
  • Ubuntu 16.4 Server x64 along with syslog-ng version 3.25.1 on some decent hardware

Requirements:

Ubuntu OS


Ref: Installing latest version of syslog-ng

#Make sure to change the version, I have used this CMD on Ubuntu 16.04 , for version 18, you may change this to 18.04

wget -qO - http://download.opensuse.org/repositories/home:/laszlo_budai:/syslog-ng/xUbuntu_16.04/Release.key | sudo apt-key add -
touch /etc/apt/sources.list.d/syslog-ng-obs.list
echo "deb http://download.opensuse.org/repositories/home:/laszlo_budai:/syslog-ng/xUbuntu_16.04 ./" > /etc/apt/sources.list.d/syslog-ng-obs.list
apt-get update
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 libmongo-client0 libesmtp6 syslog-ng-mod-sql libdbd-mysql libdbd-mysql syslog-ng

Note: during above packages installation, it will ask you to enter mysql/phpmyadmin password, you can use your root password to continue the installations. It may download around  after installation finishes, you can check syslog-ng version.

At the time I did installation I got this

syslog-ng -V

root@nab-syslog:~# syslog-ng -V
syslog-ng 3 (3.30.1)
Config version: 3.29
Installer-Version: 3.30.1
Revision: 3.30.1-2
Compile-Date: Nov 19 2020 16:33:22
Module-Directory: /usr/lib/syslog-ng/3.30
Module-Path: /usr/lib/syslog-ng/3.30
Include-Path: /usr/share/syslog-ng/include
Error opening plugin module; module='mod-java', error='libjvm.so: cannot open shared object file: No such file or directory'
Available-Modules: syslogformat,azure-auth-header,hook-commands,linux-kmsg-format,kafka,afmongodb,json-plugin,cef,secure-logging,afsocket,pseudofile,kvformat,add-contextual-data,afamqp,riemann,http,appmodel,stardate,tfgetent,redis,cryptofuncs,sdjournal,afuser,pacctformat,graphite,confgen,geoip2-plugin,affile,basicfuncs,xml,mod-python,examples,afsmtp,timestamp,map-value-pairs,disk-buffer,afsnmp,system-source,afsql,afstomp,csvparser,tags-parser,afprog,dbparser
Enable-Debug: off
Enable-GProf: off
Enable-Memtrace: off
Enable-IPv6: on
Enable-Spoof-Source: on
Enable-TCP-Wrapper: on
Enable-Linux-Caps: on
Enable-Systemd: on

Status:

root@nab-syslog:~# service syslog-ng status
syslog-ng.service - System Logger Daemon
Loaded: loaded (/lib/systemd/system/syslog-ng.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2021-01-25 00:20:55 EST; 1min 26s ago
Docs: man:syslog-ng(8)
Main PID: 21596 (syslog-ng)
CGroup: /system.slice/syslog-ng.service
21596 /usr/sbin/syslog-ng -F

Jan 25 00:20:55 nab-syslog systemd[1]: Starting System Logger Daemon...
Jan 25 00:20:55 nab-syslog systemd[1]: Started System Logger Daemon.

Create Database in mySQL to store dynamic tables

Create Base Database for storing dynamically created date wise tables

mysql -uroot -pXXX -e "create database syslog;"

Now edit the syslog-ng file

nano /etc/syslog-ng/syslog-ng.conf

& use following as sample. I would recommend that you should add only relevant part, just dont do blind copy paste. This is just sample for demonstration purposes only …


Syslog-ng Sample File

@version: 3.30
@include "scl.conf"
# First, set some global options.
options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
dns_cache(no); owner("root"); group("adm"); perm(0640);
stats_freq(0); bad_hostname("^gconfd$");
};
########################
# Sources
########################
# This is the default behavior of sysklogd package
# Logs may come from unix stream, but not from another machine.
#
source s_src {
system();
internal();
};
########################
# Destinations
########################
# First some standard logfile
#
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.info"); };
destination d_mailwarn { file("/var/log/mail.warn"); };
destination d_mailerr { file("/var/log/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(`tty10`); };
destination d_xconsole { pipe("/dev/xconsole"); };
destination d_ppp { file("/var/log/ppp.log"); };
########################
# Filters
########################
# Here's come the filter options. With this rules, we can set which
# message go where.

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 paths
########################
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/*.conf"

######## Zaib Section Starts here
# Accept connection on UDP
source s_net { udp (); };

# Adding filter for our Mikrotik Routerboard, store logs in FILE as primary
# MIKROTIK ###########

# This entry will LOG all information coming from this IP, change this to match your mikrotik NAS
filter f_mikrotik_192.168.0.1 { host("192.168.0.1"); };
# add info in LOG (Part1)
destination df_mikrotik_192.168.0.1 {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};
source s_mysql {
udp(port(514));
tcp(port(514));
};

# Store Logs in MYSQL DB as secondary # add info in MYSQL (Part2)
destination d_mysql {
sql(type(mysql)
host("localhost")
# MAKE SURE TO CHANGE CREDENTIALS
username("root")
password("XXXXX")
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", "date datetime", "message text not null")
values("0", "$FULLHOST", "$R_YEAR-$R_MONTH-$R_DAY $R_HOUR:$R_MIN:$R_SEC", "$MSG")
indexes("id"));
};
log {
source(s_net);
filter(f_mikrotik_192.168.0.1);
destination(d_mysql);
};

IMPORTANT:

Create ‘zlogs‘ folder in /var/log , so that mikrotik logs will be saved in separate file if required by you

mkdir /var/log/zlogs

Mikrotik rule to LOG Forward chain

Now we need to create a rule in mikrotik FILTER section so that it can log all packets being forward to/from pppoe users. Make sure you in source address list you select your local pppoe users pool there to avoid un-related excessive logging. In below example we are doing only TCP base connection for NEW tcp connections only.

LOG SIZE Example: at one ISP who had around 1200+ online users , its log size for TCP connection was around 25 GB. to lower the size, I configured it log only new TCP connections which reduced the DB Size by 50%.

/ip firewall filter
add action=log chain=forward connection-state=new protocol=tcp src-address-list=pppoe_allowed_users

Mikrotik rule to send LOG to SYSLOG-NG Server

/system logging action
add name=syslogng remote=192.168.101.1 target=remote
# Change IP address pointed towards syslog server

/system logging
set 0 topics=info,!firewall
add action=syslogng topics=firewall

Restart Syslog-ng server

Now restart syslog-ng service

service syslog-ng restart

and you will see the dynamic tables created as follows

mysql -uroot -pXXXXX
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 411
Server version: 5.7.28-0ubuntu0.18.04.4-log (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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
mysql> show tables;
+------------------+
| Tables_in_syslog |
+------------------+
| 2020_01_08 |
+------------------+
1 row in set (0.00 sec)

mysql> describe 2020_01_08;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| host | varchar(40) | NO | | NULL | |
| date | datetime | YES | | NULL | |
| message | text | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

& you can then see data insertion into the table as soon LOG is received from remote devices

2020-01-08T07:49:43.020811Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:28', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK,PSH), 172.16.0.2:57193->172.217.19.174:443, NAT (172.16.0.2:57193->101.11.11.252:2244)->172.217.19.174:443, len 79')
2020-01-08T07:49:43.031281Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:28', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK,FIN), 172.16.0.2:57096->3.228.94.102:443, NAT (172.16.0.2:57096->101.11.11.252:2219)->3.228.94.102:443, len 40')
2020-01-08T07:49:43.041420Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:38', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49247->216.58.208.234:443, NAT (172.16.0.2:49247->101.11.11.252:2202)->216.58.208.234:443, len 1378')
2020-01-08T07:49:43.051112Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:38', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49247->216.58.208.234:443, NAT (172.16.0.2:49247->101.11.11.252:2202)->216.58.208.234:443, len 1378')
2020-01-08T07:49:43.061280Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:39', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49760->172.217.19.1:443, NAT (172.16.0.2:49760->101.11.11.252:2202)->172.217.19.1:443, len 1378')
2020-01-08T07:49:43.071449Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:39', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49760->172.217.19.1:443, NAT (172.16.0.2:49760->101.11.11.252:2202)->172.217.19.1:443, len 1378')
2020-01-08T07:49:44.828993Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:44', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:53503->216.58.208.234:443, NAT (172.16.0.2:53503->101.11.11.252:2203)->216.58.208.234:443, len 827')
2020-01-08T07:49:44.851034Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:49:44', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:53503->216.58.208.234:443, NAT (172.16.0.2:53503->101.11.11.252:2203)->216.58.208.234:443, len 827')
2020-01-08T07:51:37.518276Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:51:37', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.2:57202->91.195.240.126:80, NAT (172.16.0.2:57202->101.11.11.252:2260)->91.195.240.126:80, len 41')
2020-01-08T07:51:37.522015Z 430 Query INSERT INTO 2020_01_08 (id, host, date, message) VALUES ('0', '101.11.11.252', '2020-01-08 12:51:37', 'forward: in: out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto TCP (ACK), 172.16.0.2:57202->91.195.240.126:80, NAT (172.16.0.2:57202->101.11.11.252:2260)->91.195.240.126:80, len 41')

syslog-ng dynamic table data from phpmyadmin.PNG


TIPS

Deleting all tables inside particular DB


#!/bin/bash
# drop tables matching filter
force=1;
u=root;
p=SQLPASS;
db=syslog;
filter=users_;
for t in $(mysql -u $u -p$p -D $db -Bse 'show tables' | grep $filter); do
echo Dropping $t;
[[ $force -eq 1 ]] && mysql -u root -p$p -D $db -Bse "drop table \`$t\`"
done

Regard’s
Syed Jahanzaib

November 10, 2017

Centralized Syslog-ng logging to MySql DB

Filed under: Linux Related — Tags: , , , , — Syed Jahanzaib / Pinochio~:) @ 11:59 AM

configure-centralized-syslog-server-2.jpg


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);">&lt; /var/log/mysql.pipe </span>&gt; /dev/null
done

WordPress is not letting proper pasting of codes this is why I am attaching image below just for reference.

syslog-2-mysql.PNG

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.

&lt;?php
// by Syed Jahanzaib
$host=&quot;localhost&quot;;
$username=&quot;root&quot;;
$password=&quot;PASSWORD&quot;; // Mysql password
$db_name=&quot;syslog&quot;; // Database name
$tbl_name=&quot;logs&quot;; // Table name
// Connect to server and select databse
mysql_connect(&quot;$host&quot;, &quot;$username&quot;, &quot;$password&quot;)or die(&quot;cannot connect&quot;);
mysql_select_db(&quot;$db_name&quot;)or die(&quot;cannot select DB&quot;);
//$sql=&quot;SELECT * FROM $tbl_name ORDER BY `LOGS` datetime LIMIT 0 , 10&quot;;
$sql = &quot;SELECT * FROM `logs`\n&quot;
. &quot;ORDER BY `logs`.`datetime` DESC LIMIT 0, 50 &quot;;
$result=mysql_query($sql);
// Define $host_column=1
echo &#039;</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();
?&gt;

resulkt


– 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

sample


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


dua.jpg

%d bloggers like this: