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

Wed Aug 7 15:18:47 UTC 2019

Hello,

So heres an example of a message I'd like to parse and drop in MySQL. The goal is really to parse the data in the message block. The log entry below is coming from /var/log/wazuh-log.log and is a destination I have in the Syslog-ng.conf file.

Aug  7 10:07:50 wazuhtest ossec: Alert Level: 3; Rule: 60137 - Windows User Logoff; Location: (SERVERNAME) 10.235.1.10->EventChannel; classification: windows, windows_security,pci_dss_10.2.5,gdpr_IV_32.2,; {"win":{"system":{"providerName":"Microsoft-Windows-Security-Auditing","providerGuid":"{54849625-5478-4994-A5BA-3E3B0328C30D}","eventID":"4634","version":"0","level":"0","task":"12545","opcode":"0","keywords":"0x8020000000000000","systemTime":"2019-08-07T14:07:49.201957200Z","eventRecordID":"5831404","processID":"656","threadID":"700","channel":"Security","computer":"SERVERNAME.company.com","severityValue":"AUDIT_SUCCESS","message":"An account was logged off."},"eventdata":{"targetUserSid":"S-1-5-18","targetUserName":"SERVERNAME$","targetDomainName":"SDN","targetLogonId":"0x83db027","logonType":"3"}}}

Here is the config for syslog-ng.

@version: 3.22
@include "scl.conf"

# First, set some global options
   options {
        keep-timestamp(yes);
        keep-hostname(yes);
       };

########################
# Sources
########################
# This is the default behavior of syslogd package
# Logs may come from unix stream, but not from another machine.
#
#source s_src { system(); internal();  };

# If you wish to get logs from remote machine you should uncomment
# this and comment the above source line.

#source s_network { syslog ( transport(udp) port(514)); };
source s_external { network( transport(udp) port(514)); };

parser p_mysql {
       csv-parser(
               columns(host, id, location, facility, rule, priority, tag, datetime, program, msg)
               delimiters(chars(";")));
              };

########################
# Destinations
########################
#Wazuh Destination
destination wazuh_syslog { file("/var/log/wazuh_syslog.log"); };

# MySQL define destination
destination d_mysql {
sql(
type(mysql)
username("remote")
password("remote")
database("syslog")
host("127.0.0.1")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host")
);
};

########################
# Filters
########################
filter f_info { level(info); };

########################
# Log paths
########################

#Wazuh Log
log { source(s_external); parser(p_mysql); destination(wazuh_syslog); destination(d_mysql); };

# MySQL log to destination
log {source(s_external); parser(p_mysql); destination(d_mysql);};

###
@include "/etc/syslog-ng/conf.d/*.conf"

I created the csv-parser to see if it would parse the data and place it into mysql. What I don't know is what all that parser is dependent upon and what needs to be included to A) make the parser work, and B) parse the data from the message block into its own column. For example in the log entry above there's "Location: (SERVERNAME) 10.235.1.10->EventChannel;"

Ideally I'd like to parse the data in a way that pulls multiple items from that field, so Location.hostname: SERVERNAME, Location.ip: 10.235.1.10, etc. I think once I understand how to do this I can apply similar configurations to the rest of the message block.

Reading through the documentation I think the csv-parser can do this but I'm not sure how best to approach it. I also think patterndb could do it too but I also don't know anything about that either. I'm sorry for the lack of knowledge with this and really appreciate any help/guidance you can offer.

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

From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> On Behalf Of Gabor Nagy (gnagy)
Sent: Friday, August 2, 2019 3:49 AM
To: Syslog-ng users' and developers' mailing list <syslog-ng at lists.balabit.hu>
Subject: Re: [syslog-ng] Parse message fields for use as columns in MySQL

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<mailto:syslog-ng-bounces at lists.balabit.hu>> on behalf of Allen Olivas <allen.olivas at infodefense.com<mailto: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<mailto: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<mailto: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/20190807/46a76279/attachment-0001.html>