<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: 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>
</body>
</html>