[syslog-ng] customized rewrite to mysql in syslog-ng
JAHANZAIB SYED
aacable at hotmail.com
Thu Jan 9 09:17:20 UTC 2020
With this config, syslog-ng starts fine, but its not logging any information in the mentioned file.
@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 {
file("/var/log/zlogs/101.11.11.252.2020.01.09.log" flags(no-parse));
};
filter f_mikrotik_252 {
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));
};
destination df_mikrotik_252 {
file("/var/log/zlogs/101.11.11.252.2020.01.09.log"
template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport\n")
template-escape(no));
};
log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
source s_src {
system();
internal();
};
Whereas when If I revert back to following, the log starts inserting in the file.
filter f_mikrotik_252 { host("101.11.11.252"); };
log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
destination df_mikrotik_252 {
file("/var/log/zlogs/101.11.11.252.2020.01.09.log"
template-escape(no));
};
________________________________
From: Attila Szakacs (aszakacs) <Attila.Szakacs at oneidentity.com>
Sent: Thursday, January 9, 2020 11:59 AM
To: JAHANZAIB SYED <aacable at hotmail.com>; syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: Re: customized rewrite to mysql in syslog-ng
First: add flags(no-parse) to your s_net source.
Second: add template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport\n") to your df_mikrotik_252 destination.
Example:
source s_net {
file("/tmp/test.log" flags(no-parse));
};
filter f_mikrotik_252 {
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));
};
destination df_mikrotik_252 {
file("/var/log/zlogs/101.11.11.252.2020.01.09.log"
template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport\n")
template-escape(no));
};
log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };
Regards,
Attila
________________________________
From: JAHANZAIB SYED <aacable at hotmail.com>
Sent: Thursday, January 9, 2020 7:18 AM
To: Attila Szakacs (aszakacs) <Attila.Szakacs at oneidentity.com>; syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: Re: 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.
ok to simplify things at my part, what if I want to log to file only. Example this code is not adding any info in the file
# CUSTOM CODE STARTS HERE
filter f_mikrotik_252 {
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_mikrotik_252 ); destination ( df_mikrotik_252 ); };
destination df_mikrotik_252 {
file("/var/log/zlogs/101.11.11.252.2020.01.09.log"
template-escape(no));
};
Regards,
SYED JAHANZAIB
Web : http://aacable.wordpress.com<https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Faacable.wordpress.com&data=02%7C01%7CAttila.Szakacs%40oneidentity.com%7Cfd89450083524664ae0208d794cbcd81%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637141475362142375&sdata=a84bv%2BWWrgiyue6ZW4SLvNuVfORXVcOmWXxHHjW80UQ%3D&reserved=0>
LinkedIn: http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407<https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fpk.linkedin.com%2Fpub%2Fsyed-jahanzaib%2F24%2F3b%2F407&data=02%7C01%7CAttila.Szakacs%40oneidentity.com%7Cfd89450083524664ae0208d794cbcd81%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637141475362152374&sdata=NU6YuOU6gBKHA6meM3qnv8RQXBP6sXPsJK%2ByekyjGj8%3D&reserved=0>
<http:///>
________________________________
From: Attila Szakacs (aszakacs) <Attila.Szakacs at oneidentity.com>
Sent: Thursday, January 9, 2020 11:04 AM
To: JAHANZAIB SYED <aacable at hotmail.com>; syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: Re: customized rewrite to mysql in syslog-ng
You don't need the file block I sent you, it was just an example of how to use the new name-value pairs.
You should use the $my_... name-value pairs in your sql destination, in the values field.
Attila
________________________________
From: JAHANZAIB SYED <aacable at hotmail.com>
Sent: Thursday, January 9, 2020 5:02:50 AM
To: Attila Szakacs (aszakacs) <Attila.Szakacs at oneidentity.com>; syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: Re: 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.
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/65f24598/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/65f24598/attachment-0001.png>
More information about the syslog-ng
mailing list