<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body 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="appendonsend"></div>
<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> 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:2|T:2" 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_Signature">
<div id="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>
</body>
</html>