<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<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);">
With this config, syslog-ng starts fine, but its not logging any information in the mentioned file.</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<span><br>
</span></div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<span><b>@version: 3.25<br>
</b></span>
<div><b>@include "scl.conf"<br>
</b></div>
<div><b>options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);<br>
</b></div>
<div><b> owner("root"); group("adm"); perm(0640); stats_freq(0);<br>
</b></div>
<div><b> bad_hostname("^gconfd$");<br>
</b></div>
<div><b>};<br>
</b></div>
<div><b>source s_net {<br>
</b></div>
<div><b> file("/var/log/zlogs/101.11.11.252.2020.01.09.log" flags(no-parse));<br>
</b></div>
<div><b>};<br>
</b></div>
<div><b>filter f_mikrotik_252 {<br>
</b></div>
<div><b> 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>
</b></div>
<div><b> flags(store-matches));<br>
</b></div>
<div><b>};<br>
</b></div>
<div><b>destination df_mikrotik_252 {<br>
</b></div>
<div><b> file("/var/log/zlogs/101.11.11.252.2020.01.09.log"<br>
</b></div>
<div><b> template("$my_host $my_username $my_userip $my_userport $my_wanip $my_wanport $my_dstip $my_dstport\n")<br>
</b></div>
<div><b> template-escape(no));<br>
</b></div>
<div><b>};<br>
</b></div>
<div><b>log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252 ); };<br>
</b></div>
<div><b>source s_src {<br>
</b></div>
<div><b> system();<br>
</b></div>
<div><b> internal();<br>
</b></div>
<div><b>};<br>
</b></div>
<b><span></span></b><br>
</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);">
<br>
</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
Whereas when If I revert back to following, <span style="font-family: "Courier New", monospace; background-color: rgb(255, 255, 255); display: inline !important">the log starts inserting in the file.</span></div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<code style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: normal !important; font-style: normal !important; font-size: 1em !important; box-shadow: none !important; display: inline !important; color: black !important"><br>
</code></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<code style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-style: normal !important; font-size: 1em !important; box-shadow: none !important; display: inline !important; color: black !important;"><b>filter
f_mikrotik_252 { host("101.11.11.252"); };</b></code></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<b><span style="background-color: initial; color: black; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; font-size: 1em; font-style: normal;">log { source ( s_net ); filter( f_mikrotik_252 ); destination ( df_mikrotik_252
); };</span><br>
</b></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<b><span style="background-color: initial; color: black; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; font-size: 1em; font-style: normal;">destination df_mikrotik_252 {</span><br>
</b></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<code style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-style: normal !important; font-size: 1em !important; box-shadow: none !important; display: inline !important; color: black !important;"><b>file("/var/log/zlogs/101.11.11.252.2020.01.09.log"</b></code></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<code style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-style: normal !important; font-size: 1em !important; box-shadow: none !important; display: inline !important; color: black !important;"><b>template-escape(no));</b></code></div>
<div style="border-radius: 0px !important; background: none white !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px 1em !important; right: auto !important; text-align: left; top: auto !important; vertical-align: baseline !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr !important; box-shadow: none !important">
<code style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-style: normal !important; font-size: 1em !important; box-shadow: none !important; display: inline !important; color: black !important;"><b>};</b></code></div>
<br>
</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<div id="divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div><br>
</div>
<div><br>
</div>
</div>
<div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com><br>
<b>Sent:</b> Thursday, January 9, 2020 11:59 AM<br>
<b>To:</b> JAHANZAIB SYED <aacable@hotmail.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>
<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)">
First: add flags(no-parse) to your s_net source.</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
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.</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)">
Example:</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)">source</span><span> s_net {</span></div>
<div><span> </span><span style="color:rgb(220,220,170)">file</span><span>(</span><span style="color:rgb(206,145,120)">"/tmp/test.log"</span><span> </span><span style="color:rgb(220,220,170)">flags</span><span>(no-parse));</span></div>
<div><span>};</span></div>
<br>
<div><span style="color:rgb(197,134,192)">filter</span><span> f_mikrotik_252 {</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)">destination</span><span> df_mikrotik_252 {</span></div>
<div><span> </span><span style="color:rgb(220,220,170)">file</span><span>(</span><span style="color:rgb(206,145,120)">"/var/log/zlogs/101.11.11.252.2020.01.09.log"</span></div>
<div><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(215,186,125)">\n</span><span style="color:rgb(206,145,120)">"</span><span>)</span></div>
<div><span> </span><span style="color:rgb(220,220,170)">template-escape</span><span>(</span><span style="color:rgb(86,156,214)">no</span><span>));</span></div>
<div><span>};</span></div>
<br>
<div><span style="color:rgb(197,134,192)">log</span><span> { </span><span style="color:rgb(197,134,192)">source</span><span> ( s_net );
</span><span style="color:rgb(197,134,192)">filter</span><span>( f_mikrotik_252 );
</span><span style="color:rgb(197,134,192)">destination</span><span> ( df_mikrotik_252 ); };</span></div>
<br>
<br>
</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)">
Regards,</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
Attila</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> JAHANZAIB SYED <aacable@hotmail.com><br>
<b>Sent:</b> Thursday, January 9, 2020 7:18 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>
<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)">
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</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)">
<span># CUSTOM CODE STARTS HERE<br>
</span>
<div><br>
</div>
<div>filter f_mikrotik_252 {<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 { 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/101.11.11.252.2020.01.09.log"<br>
</div>
<div>template-escape(no));<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_x_Signature">
<div id="x_x_divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div><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>
<strong></strong>
<div align="left"> <br>
<b><font color="#0c0c0c" style="">Web : </font><font color="#0c0c0c" style=""><a href="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" originalsrc="http://aacable.wordpress.com" shash="uC3H3O20p/VOry+gLe8ciEzThTp62xDNiJyPc1m4NQWrQE5Yk1q/1QAqaVwyg6SHuQhRucFl3//kA/bchMJnZbUOy1n88UdoyfvsSb4gAUoYER0VITnH37x+aQuQdtxWGlBI5+yFoFg7FbM1hmspGkvPuuuwDwoJpUss7fIn1Bc=" target="_blank">http://aacable.wordpress.com</a></font><font color="#0c0c0c" style=""><br>
</font><font color="#0c0c0c" style="">LinkedIn: </font><font color="#0c0c0c" style=""><a href="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" originalsrc="http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407" shash="H+TGDyM9flAxMXvTSfG2KdFWKwzSIZRek11oUEMgkVBhwsQ2RECnDqfjjTNXYycNbBy+IXXzWt+HdaV5UY8GfC57hT/zGh4qV+iieqh92HNY7/29JU8KEGANLyuaTu1YJICY7lNHjjg9EynzPfs4ZpF5ZsiYeae7PWMxUmm9OBs=" target="_blank">http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407</a></font></b></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_x_appendonsend"></div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<br>
</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> Attila Szakacs (aszakacs) <Attila.Szakacs@oneidentity.com><br>
<b>Sent:</b> Thursday, January 9, 2020 11:04 AM<br>
<b>To:</b> JAHANZAIB SYED <aacable@hotmail.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>
<div>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 tabindex="-1" style="display:inline-block; width:98%">
<div id="x_x_x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><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>
<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_x_x_x_Signature">
<div id="x_x_x_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_x_x_x_appendonsend"></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_x_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 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>
<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_x_x_x_appendonsend"></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_x_x_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>
<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%; height:auto" data-outlook-trace="F:7|T:7" 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_x_x_x_Signature">
<div id="x_x_x_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>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>