[syslog-ng] Parse message fields for use as columns in MySQL

[Allen Olivas]

Hello,

I'm really scratching my head trying to make this work and thought maybe the community has experienced this before. I'm collecting logs from Wazuh and Syslog-NG. Those logs are sent from my Wazuh server with Sylog-NG configured to send to my MySQL server. The Syslog-ng.conf file on the MySQL server is configured with a destination to mysql.

In the declared the destination and list out columns and values.
# MySQL define destination
destination d_mysql {
sql(
type(mysql)
username("syslog")
password("xxxxxxx")
database("syslog")
host("127.0.0.1")
table("logs")
columns("host", "id", "location", "facility", "rule", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$ID", "$LOCATION","$FACILITY", "$RULE", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host", "id", "location", "rule")
);
};

So here's the problem. The Message data contains information like Rule and Location that really equate to the Wazuh Rule and Location = the Wazuh Agent that's reporting it. I had hoped "location" column would populate with the Location date from the message. Same with Rule.

SO my question to the community is how on earth do I parse the data in the message field to populate columns (existing or new)? Any thoughts, guidance, recommendations are greatly appreciated.

Thanks,

Allen Olivas
InfoDefense
Office: (972) 848-7910
Email: allen.olivas at infodefense.com
Toll Free: (877) INFODEFENSE
www.infodefense.com<https://l.shatrk.com/r/e/DblvLSPvKY2IxMPE?r=https://app.salesforceiq.com/r?target=5c77291cc9e77c007aa6cb3e&t=AFwhZf0O7sC6c6N-x691ne-Q9q_27TNhu1ayis_kAJ00Z7HL-lH9bPLytoPohWYrCc5EpGO_mM--1dDX-GDgklCQ_2ZINq3F1wwLoCnz9aRhfWm9RG1fC4RVQcHYR5hMwHruEmd00J_U&url=http://www.infodefense.com/>


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20190801/316c729b/attachment.html>