<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body>
You don't need the file block I sent you, it was just an example of how to use the new name-value pairs.<br>
<br>
You should use the $my_... name-value pairs in your sql destination, in the values field.<br>
<br>
Attila
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> JAHANZAIB SYED <aacable@hotmail.com><br>
<b>Sent:</b> Thursday, January 9, 2020 5:02:50 AM<br>
<b>To:</b> Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com>; syslog-ng@lists.balabit.hu <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> Re: customized rewrite to mysql in syslog-ng</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div style="background-color:#FFEB9C; width:100%; border-style:solid; border-color:#9C6500; border-width:1pt; padding:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Black; text-align:left">
<span style="color:#9C6500; font-weight:bold">CAUTION:</span> 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.</div>
<br>
<div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
Now my <b>syslog-ng </b>config file looks like this, but getting error</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
*******************</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<span>@version: 3.25<br>
</span>
<div>@include "scl.conf"<br>
</div>
<div>options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);<br>
</div>
<div> owner("root"); group("adm"); perm(0640); stats_freq(0);<br>
</div>
<div> bad_hostname("^gconfd$");<br>
</div>
<div>};<br>
</div>
<div>source s_net { udp (); };<br>
</div>
<div># CUSTOM CODE STARTS HERE<br>
</div>
<div>filter f_router {<br>
</div>
<div> 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+).*'<br>
</div>
<div> flags(store-matches));<br>
</div>
<div>};<br>
</div>
<div><br>
</div>
<div>log {<br>
</div>
<div> source(s_net);<br>
</div>
<div> filter(f_router);<br>
</div>
<div> destination(d_mysql);<br>
</div>
<div>};<br>
</div>
<div>file("/dev/stdout" template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport \n"); );<br>
</div>
<div># CUSTOM CODE ENDS HERE<br>
</div>
<div><br>
</div>
<div>filter f_mikrotik_252 { host("101.11.11.252"); };<br>
</div>
<div>#filter f_mikrotik_252 { host("101.11.11.252") and match("NAT" value("MESSAGE")) };<br>
</div>
<div>log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };<br>
</div>
<div>destination df_mikrotik_252 {<br>
</div>
<div>file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"<br>
</div>
<div>template-escape(no));<br>
</div>
<div>};<br>
</div>
<div>source s_mysql {<br>
</div>
<div>udp(port(514));<br>
</div>
<div>tcp(port(514));<br>
</div>
<div>};<br>
</div>
<div>destination d_mysql {<br>
</div>
<div>sql(type(mysql)<br>
</div>
<div>host("localhost")<br>
</div>
<div>username("root")<br>
</div>
<div>password("XXXXXXX")<br>
</div>
<div>database("syslog")<br>
</div>
<div>table("${R_YEAR}_${R_MONTH}_${R_DAY}")<br>
</div>
<div>columns( "id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "date datetime", "message text not null")<br>
</div>
<div> values("0", "$FULLHOST", "$R_YEAR-$R_MONTH-$R_DAY $R_HOUR:$R_MIN:$R_SEC", "$MSG")<br>
</div>
<div> indexes("id"));<br>
</div>
<div>};<br>
</div>
<div>log {<br>
</div>
<div>source(s_net);<br>
</div>
<div>filter(f_mikrotik_252);<br>
</div>
<div>destination(d_mysql);<br>
</div>
<div>};<br>
</div>
<div>source s_src {<br>
</div>
<div> system();<br>
</div>
<div> internal();<br>
</div>
<div>};<br>
</div>
<span></span><br>
</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div id="x_Signature">
<div id="x_divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div>***********************]</div>
<div><span>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:<br>
</span>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 14 log {<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 15 source(s_net);<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 16 filter(f_router);<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 17 destination(d_mysql);<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 18 };<br>
</div>
<div><span style="color:rgb(237,92,87)"><b>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"); );</b></span><b><br>
</b></div>
<div><span style="color:rgb(237,92,87)"><b>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 19----> ^^^^</b></span><br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 20 # CUSTOM CODE ENDS HERE<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 21<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 22 filter f_mikrotik_252 { host("101.11.11.252"); };<br>
</div>
<div>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")) };<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: 24 log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: syslog-ng documentation: https://www.balabit.com/support/documentation?product=syslog-ng-ose<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test syslog-ng[7346]: contact: https://lists.balabit.hu/mailman/listinfo/syslog-ng<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test systemd[1]: syslog-ng.service: Main process exited, code=exited, status=1/FAILURE<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test systemd[1]: syslog-ng.service: Failed with result 'exit-code'.<br>
</div>
<div>Jan 09 09:01:27 agpis-linux-test systemd[1]: Failed to start System Logger Daemon.<br>
</div>
<span>-</span><br>
</div>
<div><br>
<font color="#0066ff"><strong><font color="#000000">Regards,</font> </strong></font><br>
<font color="#0066ff"></font></div>
<div align="left"><strong><font color="#0033ff">SYED JAHANZAIB</font></strong></div>
<div align="left"><strong><font color="#0033ff"><br>
</font></strong></div>
<div align="center"><a href="http:///" target="_blank"><b style="font-family:'times new roman','new york',times,serif; color:black; font-size:12pt"><span dir="rtl" lang="AR-SA" style="color:green; font-size:20pt"></span></b></a><font face="Verdana" size="2"></font></div>
</div>
</div>
<div id="x_appendonsend"></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com><br>
<b>Sent:</b> Wednesday, January 8, 2020 7:42 PM<br>
<b>To:</b> syslog-ng@lists.balabit.hu <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> Re: [syslog-ng] customized rewrite to mysql in syslog-ng</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
Hi,</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
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.</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
I just created one for you:</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<div style="font-family:"Droid Sans Mono",monospace,monospace,"Droid Sans Fallback"; font-weight:normal; font-size:14px; line-height:19px; color:rgb(212,212,212); background-color:rgb(30,30,30)">
<div><span style="color:rgb(197,134,192)">filter</span><span> f_router {</span></div>
<div><span> </span><span style="color:rgb(220,220,170)">message</span><span>(</span><span style="color:rgb(206,145,120)">'^\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+).*'</span></div>
<div><span> </span><span style="color:rgb(220,220,170)">flags</span><span>(store-matches));</span></div>
<div><span>};</span></div>
<br>
<div><span style="color:rgb(197,134,192)">log</span><span> {</span></div>
<div><span> </span><span style="color:rgb(197,134,192)">source</span><span>(s_test);</span></div>
<div><span> </span><span style="color:rgb(197,134,192)">filter</span><span>(f_router);</span></div>
<div><span> </span><span style="color:rgb(197,134,192)">destination</span><span>(d_sql);</span></div>
<div><span>};</span></div>
</div>
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
The values are available with the $ sign like:</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<div style="font-family:"Droid Sans Mono",monospace,monospace,"Droid Sans Fallback"; font-weight:normal; font-size:14px; line-height:19px; color:rgb(212,212,212); background-color:rgb(30,30,30)">
<div><span style="color:rgb(220,220,170)">file</span><span>(</span><span style="color:rgb(206,145,120)">"/dev/stdout"</span><span> </span><span style="color:rgb(197,134,192)">template</span><span>(</span><span style="color:rgb(206,145,120)">"</span><span style="color:rgb(156,220,254)">$my_host</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_username</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_userip</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_userport</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_wanip</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_wanport</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_dstip</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(156,220,254)">$my_dstport</span><span style="color:rgb(206,145,120)"> </span><span style="color:rgb(215,186,125)">\n</span><span style="color:rgb(206,145,120)">"</span><span>));</span></div>
</div>
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<span>[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'<br>
</span><span>101.11.11.252 pppoe-zaib 172.16.0.2 49482 101.11.11.252 2224 58.27.130.12 443</span><br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<span><br>
</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<span>Regards,</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<span>Attila</span></div>
<div id="x_x_appendonsend"></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of JAHANZAIB SYED <aacable@hotmail.com><br>
<b>Sent:</b> Wednesday, January 8, 2020 9:59 AM<br>
<b>To:</b> syslog-ng@lists.balabit.hu <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> [syslog-ng] customized rewrite to mysql in syslog-ng</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div style="background-color:#FFEB9C; width:100%; border-style:solid; border-color:#9C6500; border-width:1pt; padding:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Black; text-align:left">
<span style="color:#9C6500; font-weight:bold">CAUTION:</span> 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.</div>
<br>
<div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
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. <span style="color:rgb(0,0,0); font-family:"Courier New",monospace; font-size:12pt">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.</span></div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
Incoming message example:</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<span><b>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<br>
</b></span>
<div><b><br>
</b></div>
<div><b>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</b><br>
</div>
<span></span><br>
</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
The relevant part that I want to store is </div>
<span><br>
</span>
<div>HOSTNAME, DATETIME, AND message part 172.16.0.2:49482->101.11.11.252:2224)->58.27.130.12:443 * into following tables<br>
</div>
<div><br>
</div>
<div><b>hostname datatime username userip userport wanip wanport dst-ip dst-port<br>
</b></div>
<div><b>101.11.11.252 xxxx pppoe-zaib 172.16.0.2 49466 101.11.11.252 2223 172.217.19.10 443</b><br>
</div>
<div><br>
</div>
<div><img size="21205" tabindex="-1" style="max-width:100%" data-outlook-trace="F:4|T:4" src="cid:48adc02d-e37b-47af-aa2d-67e59b25f8d6"><br>
</div>
<span></span>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<b></b></div>
<div id="x_x_x_Signature">
<div id="x_x_x_divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div><br>
<font color="#0066ff"><strong><font color="#000000">Regards,</font> </strong></font><br>
<font color="#0066ff"></font></div>
<div align="left"><strong><font color="#0033ff">SYED JAHANZAIB</font></strong></div>
<div align="center"><a href="http:///" target="_blank"><b style="font-family:'times new roman','new york',times,serif; color:black; font-size:12pt"><span dir="rtl" lang="AR-SA" style="color:green; font-size:20pt"></span></b></a><font face="Verdana" size="2"></font></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>