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

Fri Aug 2 08:49:26 UTC 2019

Hi Allen,

Maybe just I don't get it entirely, but I guess your issue is that $LOCATION $RULE nv-pairs are empty, because Wazuh log messages are not parsed correctly, right?

We can usually solve custom parsing issues with SCLs, which are built up by common filters/parsers according to the custom message format.
Some examples of these

  *   netskope parser, websense-parser, checkpoint-parser

(you can find these in syslog-ng's install directory, in "<install_prefix>/share/syslog-ng/include/scl")

Some documentation about creating your on SCL:
https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.22/administration-guide/15#TOPIC-1209122

If you can share an example log iof Wazuh, we can help how you should parse it.
As far as I see, it can log in "plain" and "JSON" formats, the latter sounds easy as well, as syslog-ng does have a json-parser.
https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/logging.html

Regards,
Gabor
________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of Allen Olivas <allen.olivas at infodefense.com>
Sent: Thursday, August 1, 2019 23:35
To: Syslog-ng users' and developers' mailing list <syslog-ng at lists.balabit.hu>
Subject: [syslog-ng] Parse message fields for use as columns in MySQL

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.

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://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fl.shatrk.com%2Fr%2Fe%2FDblvLSPvKY2IxMPE%3Fr%3Dhttps%3A%2F%2Fapp.salesforceiq.com%2Fr%3Ftarget%3D5c77291cc9e77c007aa6cb3e%26t%3DAFwhZf0O7sC6c6N-x691ne-Q9q_27TNhu1ayis_kAJ00Z7HL-lH9bPLytoPohWYrCc5EpGO_mM--1dDX-GDgklCQ_2ZINq3F1wwLoCnz9aRhfWm9RG1fC4RVQcHYR5hMwHruEmd00J_U%26url%3Dhttp%3A%2F%2Fwww.infodefense.com%2F&data=02%7C01%7Cgabor.nagy%40oneidentity.com%7C06aa5cd3ebd14695670808d716c83cdb%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637002921605460940&sdata=3VdqqIxIp9Ls2xz8odH%2B%2FD6iHutUZ%2FzkmZ81mLN9Vxc%3D&reserved=0>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20190802/59d0ab57/attachment-0001.html>