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

Thu Aug 8 12:29:00 UTC 2019

Thanks for sharing an example log.

I see it is a pretty complex log structure, but it is built up by parsable elements: I see JSON part, parts where a key=value parsing is possible.

> As far as I see, wazuh 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

I don't know if this supposed to be the JSON format, if it is I had expected only some header parts (timestamp, hostname) and a JSON part of a log message.
But this one has a header + key-value structured fields + JSON payload.

I've created a complex parser block, it needs to follow source that is parsing the message with BSD format (this is the default):
parser p_wazuh {
  channel {
    parser {
      csv-parser( columns(level, rule, location, classification, json_msg)
                  delimiters(chars(";")) );
      kv-parser( value-separator(":") pair-separator(";") template("${level}; ${rule}; ${location} ${classification};") );
      json-parser( template("$json_msg") );
    };
  };
};

It's far from perfect, especially the key-value parsing, where the key or value sometimes is not quoted:
Example 1: Alert Level: 3;
here only "Level" will be used as a key,
Example 2: Rule: 60137 - Windows User Logoff;
Here only the 60137 will be used as a value.

Please run syslog-ng with -Fedtv in the foreground and check the set name-value pairs.
I suggest the "Setting value;",  "Initial message parsing follows"  "message processing begin" debug lines to see how does the parsing process go.

Also you can use a template on the destination side as well (well not with sql destination), it can help too to see every name-value pair in the log message.

If we can create a production-ready version of this parser we can put this into an scl and use it in default-network-drivers() driver as an app-parser.

________________________________
From: syslog-ng <syslog-ng-bounces at lists.balabit.hu> on behalf of Allen Olivas <allen.olivas at infodefense.com>
Sent: Wednesday, August 7, 2019 17:18
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

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,

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://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%7C236b53fe7b454a720a8f08d71b4a8fff%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637007879399759960&sdata=ZW76EE1osfv6UBbLHiORy4YWvbyNBKvUFGrem25p3Go%3D&reserved=0>

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<https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.syslog-ng.com%2Ftechnical-documents%2Fdoc%2Fsyslog-ng-open-source-edition%2F3.22%2Fadministration-guide%2F15%23TOPIC-1209122&data=02%7C01%7Cgabor.nagy%40oneidentity.com%7C236b53fe7b454a720a8f08d71b4a8fff%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637007879399769951&sdata=diJKSBSij41VmEq%2BzPANxmDQ5GcGaoepD9mXFOnnGYk%3D&reserved=0>

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<https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocumentation.wazuh.com%2Fcurrent%2Fuser-manual%2Freference%2Fossec-conf%2Flogging.html&data=02%7C01%7Cgabor.nagy%40oneidentity.com%7C236b53fe7b454a720a8f08d71b4a8fff%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637007879399769951&sdata=39gqe8%2B8QIq4pmZfWXvO89h4Yr1iNaJtl7ziR3WIO%2BA%3D&reserved=0>

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%7C236b53fe7b454a720a8f08d71b4a8fff%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637007879399769951&sdata=in8I%2FOfBm%2F1GbUskq4q2L%2Be4nkp7NqRbsGSfXxIkThQ%3D&reserved=0>

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