<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);">
Hello,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
You need to deal with each assignments one by one. What I usually do is I "stub" the source, run syslog-ng in foreground, print the name value pairs to stdout, and tweak the configuration with trial and error. Besides foreground, you can add debug and trace
logs (syslog-ng -Fevdt), if you get stuck. Those can give a hint what went wrong with the parsing.<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<span style="font-family: Consolas, Courier, monospace;">@version: 3.26</span><span><br>
</span>
<div><span style="font-family: Consolas, Courier, monospace;">log {</span><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;"> source { example-msg-generator(template("example message") num(1)); };</span><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;"> rewrite { set("example-program-name" value("PROGRAM")); };</span><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;"> # [maybe more rewrite-sets to prepare the rest of the necessary input message]</span><br>
</div>
<div><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;"> # You need to insert the parsers or rewrite rules here</span><br>
</div>
<div><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;"> destination { file(/dev/stdout template("$(format-json --scope all-nv-pairs)\n")); };</span><br>
</div>
<div><span style="font-family: Consolas, Courier, monospace;">};</span><br>
</div>
<span></span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
As for how to prepare the name-value pairs that you can use as columns in the destination:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
There are various rewrites or parsers that you can use, depending what you want to achieve. You can find examples in the scl directory: probably /usr/share/syslog-ng/include/scl/. Or you can find them in the source code:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
For example:<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
You can use csv-parser to split the message into columns (you can configure the delimiter of course).</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/apache/apache.conf#L42" id="LPNoLP136570">https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/apache/apache.conf#L42</a><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
You can parse time and date with the date-parser:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L54" id="LPNoLP439457">https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L54</a></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
You can parser key-value like structures with the kv-parser:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L73" id="LPNoLP289086">https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L73</a><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
You can parse message as regexp with the match filter:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/cisco/plugin.conf#L103" id="LPlnk894774">https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/cisco/plugin.conf#L103</a><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Ultimately there is the admin guide, which we try to continuously improve:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.25/administration-guide" id="LPNoLP712768">https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.25/administration-guide</a></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Br,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Antal<br>
</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 Sync IT <syncit-bd@live.com><br>
<b>Sent:</b> Tuesday, March 31, 2020 12:40<br>
<b>To:</b> syslog-ng@lists.balabit.hu <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> [syslog-ng] Syslog-NG Mysql Parsing to separate column</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:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Hi</div>
<div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
I am new to syslog-ng. I am receiving my logs perfectly to mysql. now i want to separate the message log into different column in mqsql. Currently i am receiving messages like this <br>
R2_DST_RTR forward: in:<pppoe-user1> out:vlan100-Default-Internet, src-mac 11:22:33:44:55:66, proto TCP (ACK,FIN), 172.16.41.159:42670->23.46.16.231:80, NAT (172.16.41.159:42670->9.9.9.9:42670)->23.46.16.231:80, len 52<br>
<br>
This is my syslog-ng config <br>
<span><br>
</span></div>
<div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span><span>source s_net{<br>
</span>
<div>udp( );<br>
</div>
<div>};<br>
</div>
<span></span> </span></div>
<div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span>destination d_mtksql {<br>
</span>
<div>sql(type(mysql)<br>
</div>
<div>host("localhost") username("dbuser") password("dbpasss") 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>
<span></span>
<div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span>log {<br>
</span>
<div>source(s_net);<br>
</div>
<div>destination(d_mtksql);<br>
</div>
<div>};</div>
<div><span style="font-weight:normal"><br>
</span></div>
<div><span style="font-weight:normal">i want to separate the message data in different column like- time,username,src mac,src ip,dst ip, nat ip. Any idea how can i achieve that? I am not a pro so if possible kindly write in details how to parse them. Many thanks </span><br>
</div>
<span></span></div>
</div>
</div>
</div>
</body>
</html>