[syslog-ng] Syslog-NG Mysql Parsing to separate column

[Antal Nemes (anemes)]

  Hello,

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.

@version: 3.26
log {
  source { example-msg-generator(template("example message") num(1)); };
  rewrite { set("example-program-name" value("PROGRAM")); };
  # [maybe more rewrite-sets to prepare the rest of the necessary input message]

  # You need to insert the parsers or rewrite rules here

  destination { file(/dev/stdout template("$(format-json --scope all-nv-pairs)\n")); };
};

As for how to prepare the name-value pairs that you can use as columns in the destination:

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:

For example:
You can use csv-parser to split the message into columns (you can configure the delimiter of course).
https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/apache/apache.conf#L42
You can parse time and date with the date-parser:
https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L54
You can parser key-value like structures with the kv-parser:
https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/checkpoint/plugin.conf#L73
You can parse message as regexp with the match filter:
https://github.com/syslog-ng/syslog-ng/blob/2b679e9d9ef2930cf346ec20b57c4d1e2c7a142b/scl/cisco/plugin.conf#L103

Ultimately there is the admin guide, which we try to continuously improve:
https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.25/administration-guide

Br,
   Antal
________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of Sync IT <syncit-bd at live.com>
Sent: Tuesday, March 31, 2020 12:40
To: syslog-ng at lists.balabit.hu <syslog-ng at lists.balabit.hu>
Subject: [syslog-ng] Syslog-NG Mysql Parsing to separate column

CAUTION: 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.

Hi
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
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

This is my syslog-ng config

source s_net{
udp( );
};

destination d_mtksql {
sql(type(mysql)
host("localhost") username("dbuser") password("dbpasss")   database("syslog")
table("${R_YEAR}_${R_MONTH}_${R_DAY}")
columns( "id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "date datetime", "message text not null")
values("0", "$FULLHOST", "$R_YEAR-$R_MONTH-$R_DAY $R_HOUR:$R_MIN:$R_SEC", "$MSG")
indexes("id"));
};

log {
source(s_net);
destination(d_mtksql);
};

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200401/946ea3a3/attachment.html>