Revision: 7th-JAN-2020
- Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server
- Part # 2 – Centralized Syslog-ng logging to MySql DB
- Part # 3 Minimized logging to mysql with dynamic tables & trimming >> You are here
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
Hardware Software used in this post:
- Mikrotik Routerboard – firmware 6.46.1
- Ubuntu 18.4 Server x64 along with syslog-ng version 3.25.1 on some decent hardware
Requirements:
I assume that you already have working setup for Syslog-ng& your remote devices are already sending logs and they are storing in the mysql already. See Part 1 & 2
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 install syslog-ng
at my system I got this
syslog-ng 3 (3.25.1) Config version: 3.25 Installer-Version: 3.25.1 Revision: 3.25.1-1 Compile-Date: Dec 12 2019 12:00:29 Module-Directory: /usr/lib/syslog-ng/3.25 Module-Path: /usr/lib/syslog-ng/3.25 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: add-contextual-data,afsmtp,tfgetent,afsql,cryptofuncs,http,confgen,sdjournal,system-source,cef,syslogformat,json-plugin,afprog,riemann,csvparser,affile,afsocket,afamqp,redis,examples,disk-buffer,xml,linux-kmsg-format,map-value-pairs,hook-commands,kafka,tags-parser,dbparser,graphite,appmodel,afstomp,pacctformat,afmongodb,pseudofile,basicfuncs,geoip2-plugin,kvformat,stardate,timestamp,mod-python,afuser,snmptrapd-parser 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@agpis-linux-test:/var/log/zlogs# 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 Wed 2020-01-08 12:46:52 PKT; 27min ago Docs: man:syslog-ng(8) Main PID: 7086 (syslog-ng) Tasks: 2 (limit: 2290) CGroup: /system.slice/syslog-ng.service ââ7086 /usr/sbin/syslog-ng -F Jan 08 12:46:52 agpis-linux-test systemd[1]: Starting System Logger Daemon... Jan 08 12:46:52 agpis-linux-test syslog-ng[7086]: [2020-01-08T12:46:52.362728] Macro escaping can only be specified for inline templates; Jan 08 12:46:52 agpis-linux-test syslog-ng[7086]: [2020-01-08T12:46:52.364052] WARNING: With use-dns(no), dns-cache() will be forced to 'no' too!; Jan 08 12:46:52 agpis-linux-test 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.25
@include "scl.conf"
# Syslog-ng CUSTOMIZED configuration file
# Syed Jahanzaib / aacable at hotmail dot com /https://aacable.wordpress.com
# First, set some global options.
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$");
};
######## 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
filter f_mikrotik_252 { host("101.11.11.252"); };
# This entry will LOG ONLY particular message that contains word NAT, useful to minimize CGNAT logging, Enable one entry at a time # ZAIB
#filter f_mikrotik_252 { host("101.11.11.252") and match("NAT" value("MESSAGE")) };
log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
# add info in LOG (Part1)
destination df_mikrotik_252 {
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("XXXXXXXX")
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_252);
destination(d_mysql);
};
########################
# 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();
};
IMPORTANT:
Create ‘zlogs‘ folder in /var/log , so that mikrotik logs will be saved in separate file.
mkdir /var/log/zlogs
Mikrotik rule to LOG Forward chain
/system logging action set 1 disk-file-count=50 disk-lines-per-file=5000 set 3 remote=101.11.11.254 /system logging add action=remote topics=info /system logging action set 3 remote=101.11.11.254
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')
Regard’s
Syed Jahanzaib
