Re: [syslog-ng] customized rewrite to mysql in syslog-ng

9 Jan 2020

      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
LinkedIn: http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407
<http:///>

________________________________
From: Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com>
Sent: Thursday, January 9, 2020 11:04 AM
To: JAHANZAIB SYED <aacable@hotmail.com>; syslog-ng@lists.balabit.hu <syslog-ng@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@hotmail.com>
Sent: Thursday, January 9, 2020 5:02:50 AM
To: Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com>; syslog-ng@lists.balabit.hu <syslog-ng@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@lists.balabit.hu> on behalf of Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com>
Sent: Wednesday, January 8, 2020 7:42 PM
To: syslog-ng@lists.balabit.hu <syslog-ng@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@lists.balabit.hu> on behalf of JAHANZAIB SYED <aacable@hotmail.com>
Sent: Wednesday, January 8, 2020 9:59 AM
To: syslog-ng@lists.balabit.hu <syslog-ng@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:///>