[syslog-ng] customized rewrite to mysql in syslog-ng

[JAHANZAIB SYED]

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

Incoming message example:
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

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

The relevant part that I want to store is

HOSTNAME, DATETIME, AND message part 172.16.0.2:49482->101.11.11.252:2224)->58.27.130.12:443 * into following tables

hostname datatime username userip userport wanip wanport dst-ip dst-port
101.11.11.252 xxxx pppoe-zaib 172.16.0.2 49466 101.11.11.252 2223 172.217.19.10 443

[cid:48adc02d-e37b-47af-aa2d-67e59b25f8d6]

Regards,
SYED JAHANZAIB
<http:///>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200108/2a6bcf97/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: customquery for syslogng.PNG
Type: image/png
Size: 21205 bytes
Desc: customquery for syslogng.PNG
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200108/2a6bcf97/attachment-0001.png>