[syslog-ng] customized rewrite to mysql in syslog-ng
JAHANZAIB SYED
aacable at hotmail.com
Thu Jan 9 04:02:50 UTC 2020
Now my syslog-ng config file looks like this, but getting error
*******************
@version: 3.25
@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_net { udp (); };
# CUSTOM CODE STARTS HERE
filter f_router {
message('^\w+\s+\d+\s+\d+:\d+:\d+ (?<my_host>\d+.\d+.\d+.\d+).+<(?<my_username>[^>]*)>[^(]*\((?<my_userip>\d+.\d+.\d+.\d+):(?<my_userport>\d+)->(?<my_wanip>\d+.\d+.\d+.\d+):(?<my_wanport>\d+)\)->(?<my_dstip>\d+.\d+.\d+.\d+):(?<my_dstport>\d+).*'
flags(store-matches));
};
log {
source(s_net);
filter(f_router);
destination(d_mysql);
};
file("/dev/stdout" template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport \n"); );
# CUSTOM CODE ENDS HERE
filter f_mikrotik_252 { host("101.11.11.252"); };
#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 ); };
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));
};
destination d_mysql {
sql(type(mysql)
host("localhost")
username("root")
password("XXXXXXX")
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);
};
source s_src {
system();
internal();
};
***********************]
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: Error parsing config, root plugin file not found in /etc/syslog-ng/syslog-ng.conf:19:1-19:5:
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 14 log {
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 15 source(s_net);
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 16 filter(f_router);
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 17 destination(d_mysql);
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 18 };
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 19----> file("/dev/stdout" template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport \n"); );
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 19----> ^^^^
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 20 # CUSTOM CODE ENDS HERE
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 21
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 22 filter f_mikrotik_252 { host("101.11.11.252"); };
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 23 #filter f_mikrotik_252 { host("101.11.11.252") and match("NAT" value("MESSAGE")) };
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 24 log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: syslog-ng documentation: https://www.balabit.com/support/documentation?product=syslog-ng-ose
Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: contact: https://lists.balabit.hu/mailman/listinfo/syslog-ng
Jan 09 09:01:27 agpis-linux-test systemd[1]: syslog-ng.service: Main process exited, code=exited, status=1/FAILURE
Jan 09 09:01:27 agpis-linux-test systemd[1]: syslog-ng.service: Failed with result 'exit-code'.
Jan 09 09:01:27 agpis-linux-test systemd[1]: Failed to start System Logger Daemon.
-
Regards,
SYED JAHANZAIB
<http:///>
________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of Attila Szakacs (aszakacs) <Attila.Szakacs at oneidentity.com>
Sent: Wednesday, January 8, 2020 7:42 PM
To: syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: Re: [syslog-ng] customized rewrite to mysql in syslog-ng
Hi,
If this log format is the same for all messages, you can write a custom filter with store-matches flag. It uses pcre expression by default.
I just created one for you:
filter f_router {
message('^\w+\s+\d+\s+\d+:\d+:\d+ (?<my_host>\d+.\d+.\d+.\d+).+<(?<my_username>[^>]*)>[^(]*\((?<my_userip>\d+.\d+.\d+.\d+):(?<my_userport>\d+)->(?<my_wanip>\d+.\d+.\d+.\d+):(?<my_wanport>\d+)\)->(?<my_dstip>\d+.\d+.\d+.\d+):(?<my_dstport>\d+).*'
flags(store-matches));
};
log {
source(s_test);
filter(f_router);
destination(d_sql);
};
The values are available with the $ sign like:
file("/dev/stdout" template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport \n"));
[2020-01-08T15:39:48.259469] Outgoing message; message='101.11.11.252 pppoe-zaib 172.16.0.2 49482 101.11.11.252 2224 58.27.130.12 443 \x0a'
101.11.11.252 pppoe-zaib 172.16.0.2 49482 101.11.11.252 2224 58.27.130.12 443
Regards,
Attila
________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of JAHANZAIB SYED <aacable at hotmail.com>
Sent: Wednesday, January 8, 2020 9:59 AM
To: syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: [syslog-ng] customized rewrite to mysql in syslog-ng
CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.
I have syslog-ng version 3.25.1 on Ubuntu 18.04 server. All logs from remote mikrotik router device is stored in local file and in mysql DB as well. I want to know if there is any method in which we can customize the incoming message and write it to mysql table in different columns.
Incoming message example:
Jan 8 13:50:24 101.11.11.252 firewall,info forward: in:<pppoe-zaib> out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49466->172.217.19.10:443, NAT (172.16.0.2:49466->101.11.11.252:2223)->172.217.19.10:443, len 1023
Jan 8 13:53:09 101.11.11.252 firewall,info forward: in:<pppoe-zaib> out:ether1-agp-wan, src-mac d0:bf:9c:f7:88:76, proto UDP, 172.16.0.2:49482->58.27.130.12:443, NAT (172.16.0.2:49482->101.11.11.252:2224)->58.27.130.12:443, len 1152
The relevant part that I want to store is
HOSTNAME, DATETIME, AND message part 172.16.0.2:49482->101.11.11.252:2224)->58.27.130.12:443 * into following tables
hostname datatime username userip userport wanip wanport dst-ip dst-port
101.11.11.252 xxxx pppoe-zaib 172.16.0.2 49466 101.11.11.252 2223 172.217.19.10 443
[cid:48adc02d-e37b-47af-aa2d-67e59b25f8d6]
Regards,
SYED JAHANZAIB
<http:///>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200109/d5b78f78/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: customquery for syslogng.PNG
Type: image/png
Size: 21205 bytes
Desc: customquery for syslogng.PNG
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200109/d5b78f78/attachment-0001.png>
More information about the syslog-ng
mailing list