<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.xmsonormal0, li.xmsonormal0, div.xmsonormal0
{mso-style-name:x_msonormal0;
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.xxmsonormal, li.xxmsonormal, div.xxmsonormal
{mso-style-name:x_xmsonormal;
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.xxmsochpdefault, li.xxmsochpdefault, div.xxmsochpdefault
{mso-style-name:x_xmsochpdefault;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.xmsochpdefault, li.xmsochpdefault, div.xmsochpdefault
{mso-style-name:x_msochpdefault;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:10.0pt;
font-family:"Calibri",sans-serif;}
span.xmsohyperlink
{mso-style-name:x_msohyperlink;
color:#0563C1;
text-decoration:underline;}
span.xmsohyperlinkfollowed
{mso-style-name:x_msohyperlinkfollowed;
color:#954F72;
text-decoration:underline;}
span.xxmsohyperlink
{mso-style-name:x_xmsohyperlink;
color:#0563C1;
text-decoration:underline;}
span.xxmsohyperlinkfollowed
{mso-style-name:x_xmsohyperlinkfollowed;
color:#954F72;
text-decoration:underline;}
span.xxemailstyle17
{mso-style-name:x_xemailstyle17;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.xemailstyle25
{mso-style-name:x_emailstyle25;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.xemailstyle26
{mso-style-name:x_emailstyle26;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle32
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1277179324;
mso-list-template-ids:838659410;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hello,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I added the parser to my syslog-ng.conf file and watched the output via syslog-ng -Fedtv and also via MySQL.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">It is parsing. Within the MySQL destination I added location to the column and set the value as “${location} which now adds “Location: (IDUSSDNSRV02) 10.235.1.15->EventChannel” to the record.
<o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal">Is it possible to create custom columns and match existing data to those colums? For example, with Location I’d like to mutate it and call it Hostname and pull only hostnames (i.e. IDUSSDNSRV02), or hostname.ip and set 10.235.1.15 as the
IP? I’m searching through documentation to see how that might be accomplished. I think its possible though we may need to nest parsers, etc.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Also regarding the log format, the ossec.conf file has the plain log format option set.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Allen Olivas</span><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#3A67B8">Info</span></i></b><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#5F5F5F">Defense</span></i></b><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
Office: (972) 848-7910<br>
Email: allen.olivas@infodefense.com<br>
Toll Free: (877) INFODEFENSE<br>
</span><a href="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/" title="http://www.infodefense.com/"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:blue">www.infodefense.com</span></a><span style="font-size:13.5pt;font-family:"Times New Roman",serif;color:black"><br>
<br>
</span><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><o:p> </o:p></b></p>
<p class="MsoNormal"><b><o:p> </o:p></b></p>
<p class="MsoNormal"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> <b>
On Behalf Of </b>Gabor Nagy (gnagy)<br>
<b>Sent:</b> Thursday, August 8, 2019 7:29 AM<br>
<b>To:</b> Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> Re: [syslog-ng] Parse message fields for use as columns in MySQL<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><span style="color:black">Thanks for sharing an example log.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">> 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.<br>
> <a href="https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/logging.html">https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/logging.html</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">But this one has a header + key-value structured fields + JSON payload.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">I've created a complex parser block, it needs to follow source that is parsing the message with BSD format (this is the default):<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black">parser p_wazuh {</span><span style="color:black"><o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> channel {</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> parser {</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> csv-parser( columns(level, rule, location, classification, json_msg)</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> delimiters(chars(";")) );</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> kv-parser( value-separator(":") pair-separator(";") template("${level}; ${rule}; ${location} ${classification};") );</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> json-parser( template("$json_msg") );</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> };</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black"> }; </span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:Consolas;color:black">};</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">It's far from perfect, especially the key-value parsing, where the key or value sometimes is not quoted:<br>
</span><span style="color:#201F1E;background:white">Example 1: Alert Level: 3; </span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#201F1E;background:white">here only "Level" will be used as a key,</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#201F1E">Example 2: Rule: 60137 - Windows User Logoff; </span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#201F1E">Here only the 60137 will be used as a value.</span><span style="color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">Please run syslog-ng with -Fedtv in the foreground and check the set name-value pairs.<br>
I suggest the "Setting value;", "Initial message parsing follows" "message processing begin" debug lines to see how does the parsing process go.<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> syslog-ng <<a href="mailto:syslog-ng-bounces@lists.balabit.hu">syslog-ng-bounces@lists.balabit.hu</a>> on behalf of Allen Olivas <<a href="mailto:allen.olivas@infodefense.com">allen.olivas@infodefense.com</a>><br>
<b>Sent:</b> Wednesday, August 7, 2019 17:18<br>
<b>To:</b> Syslog-ng users' and developers' mailing list <<a href="mailto:syslog-ng@lists.balabit.hu">syslog-ng@lists.balabit.hu</a>><br>
<b>Subject:</b> Re: [syslog-ng] Parse message fields for use as columns in MySQL</span>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div style="border:solid #9C6500 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="MsoNormal" style="line-height:12.0pt;background:#FFEB9C"><b><span style="font-size:10.0pt;color:#9C6500">CAUTION:</span></b><span style="font-size:10.0pt;color:black"> 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.<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="xmsonormal">Hello,<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">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. <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">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"}}}<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Here is the config for syslog-ng. <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">@version: 3.22<o:p></o:p></p>
<p class="xmsonormal">@include "scl.conf"<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"># First, set some global options<o:p></o:p></p>
<p class="xmsonormal"> options {<o:p></o:p></p>
<p class="xmsonormal"> keep-timestamp(yes);<o:p></o:p></p>
<p class="xmsonormal"> keep-hostname(yes);<o:p></o:p></p>
<p class="xmsonormal"> };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"># Sources<o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"># This is the default behavior of syslogd package<o:p></o:p></p>
<p class="xmsonormal"># Logs may come from unix stream, but not from another machine.<o:p></o:p></p>
<p class="xmsonormal">#<o:p></o:p></p>
<p class="xmsonormal">#source s_src { system(); internal(); };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"># If you wish to get logs from remote machine you should uncomment<o:p></o:p></p>
<p class="xmsonormal"># this and comment the above source line.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">#source s_network { syslog ( transport(udp) port(514)); };<o:p></o:p></p>
<p class="xmsonormal">source s_external { network( transport(udp) port(514)); };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">parser p_mysql {<o:p></o:p></p>
<p class="xmsonormal"> csv-parser(<o:p></o:p></p>
<p class="xmsonormal"> columns(host, id, location, facility, rule, priority, tag, datetime, program, msg)<o:p></o:p></p>
<p class="xmsonormal"> delimiters(chars(";")));<o:p></o:p></p>
<p class="xmsonormal"> };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"># Destinations<o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal">#Wazuh Destination<o:p></o:p></p>
<p class="xmsonormal">destination wazuh_syslog { file("/var/log/wazuh_syslog.log"); };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"># MySQL define destination<o:p></o:p></p>
<p class="xmsonormal">destination d_mysql {<o:p></o:p></p>
<p class="xmsonormal">sql(<o:p></o:p></p>
<p class="xmsonormal">type(mysql)<o:p></o:p></p>
<p class="xmsonormal">username("remote")<o:p></o:p></p>
<p class="xmsonormal">password("remote")<o:p></o:p></p>
<p class="xmsonormal">database("syslog")<o:p></o:p></p>
<p class="xmsonormal">host("127.0.0.1")<o:p></o:p></p>
<p class="xmsonormal">table("logs")<o:p></o:p></p>
<p class="xmsonormal">columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")<o:p></o:p></p>
<p class="xmsonormal">values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")<o:p></o:p></p>
<p class="xmsonormal">indexes("datetime", "host")<o:p></o:p></p>
<p class="xmsonormal">);<o:p></o:p></p>
<p class="xmsonormal">};<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"># Filters<o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal">filter f_info { level(info); };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"># Log paths<o:p></o:p></p>
<p class="xmsonormal">########################<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">#Wazuh Log<o:p></o:p></p>
<p class="xmsonormal">log { source(s_external); parser(p_mysql); destination(wazuh_syslog); destination(d_mysql); };<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"># MySQL log to destination<o:p></o:p></p>
<p class="xmsonormal">log {source(s_external); parser(p_mysql); destination(d_mysql);};<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">###<o:p></o:p></p>
<p class="xmsonormal">@include "/etc/syslog-ng/conf.d/*.conf"<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">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;”
<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">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. <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">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. <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Thanks,</span><o:p></o:p></p>
<p class="xmsonormal" style="margin-bottom:12.0pt"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Allen Olivas</span><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#3A67B8">Info</span></i></b><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#5F5F5F">Defense</span></i></b><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
Office: (972) 848-7910<br>
Email: <a href="mailto:allen.olivas@infodefense.com">allen.olivas@infodefense.com</a><br>
Toll Free: (877) INFODEFENSE<br>
</span><a href="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" title="http://www.infodefense.com/"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:blue">www.infodefense.com</span></a><o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="xmsonormal"><b>From:</b> syslog-ng <<a href="mailto:syslog-ng-bounces@lists.balabit.hu">syslog-ng-bounces@lists.balabit.hu</a>>
<b>On Behalf Of </b>Gabor Nagy (gnagy)<br>
<b>Sent:</b> Friday, August 2, 2019 3:49 AM<br>
<b>To:</b> Syslog-ng users' and developers' mailing list <<a href="mailto:syslog-ng@lists.balabit.hu">syslog-ng@lists.balabit.hu</a>><br>
<b>Subject:</b> Re: [syslog-ng] Parse message fields for use as columns in MySQL<o:p></o:p></p>
</div>
</div>
<p class="xmsonormal"> <o:p></o:p></p>
<div>
<p class="xmsonormal"><span style="color:black">Hi Allen,</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">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?</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">We can usually solve custom parsing issues with SCLs, which are built up by common filters/parsers according to the custom message format.</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">Some examples of these </span><o:p></o:p></p>
</div>
<div>
<ul style="margin-top:0in" type="disc">
<li class="xmsonormal" style="color:black;mso-list:l0 level1 lfo1">netskope parser, websense-parser, checkpoint-parser<o:p></o:p></li></ul>
</div>
<div>
<p class="xmsonormal"><span style="color:black">(you can find these in syslog-ng's install directory, in "<install_prefix>/share/syslog-ng/include/scl")</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">Some documentation about creating your on SCL:<br>
<a href="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">https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.22/administration-guide/15#TOPIC-1209122</a></span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">If you can share an example log iof Wazuh, we can help how you should parse it.</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">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.</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black"><a href="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">https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/logging.html</a></span><o:p></o:p></p>
</div>
<p class="xmsonormal"> <o:p></o:p></p>
<div>
<p class="xmsonormal"><span style="color:black"> </span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">Regards,</span><o:p></o:p></p>
</div>
<div>
<p class="xmsonormal"><span style="color:black">Gabor</span><o:p></o:p></p>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="xmsonormal"><b><span style="color:black">From:</span></b><span style="color:black"> syslog-ng <<a href="mailto:syslog-ng-bounces@lists.balabit.hu">syslog-ng-bounces@lists.balabit.hu</a>> on behalf of Allen Olivas <<a href="mailto:allen.olivas@infodefense.com">allen.olivas@infodefense.com</a>><br>
<b>Sent:</b> Thursday, August 1, 2019 23:35<br>
<b>To:</b> Syslog-ng users' and developers' mailing list <<a href="mailto:syslog-ng@lists.balabit.hu">syslog-ng@lists.balabit.hu</a>><br>
<b>Subject:</b> [syslog-ng] Parse message fields for use as columns in MySQL</span>
<o:p></o:p></p>
<div>
<p class="xmsonormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div style="border:solid #9C6500 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="xmsonormal" style="line-height:12.0pt;background:#FFEB9C"><b><span style="font-size:10.0pt;color:#9C6500">CAUTION:</span></b><span style="font-size:10.0pt;color:black"> 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.</span><o:p></o:p></p>
</div>
<p class="xmsonormal"> <o:p></o:p></p>
<div>
<div>
<p class="xxmsonormal">Hello, <o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
<p class="xxmsonormal">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.
<o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
<p class="xxmsonormal">In the declared the destination and list out columns and values.
<o:p></o:p></p>
<p class="xxmsonormal"># MySQL define destination<o:p></o:p></p>
<p class="xxmsonormal">destination d_mysql {<o:p></o:p></p>
<p class="xxmsonormal">sql(<o:p></o:p></p>
<p class="xxmsonormal">type(mysql)<o:p></o:p></p>
<p class="xxmsonormal">username("syslog")<o:p></o:p></p>
<p class="xxmsonormal">password("xxxxxxx")<o:p></o:p></p>
<p class="xxmsonormal">database("syslog")<o:p></o:p></p>
<p class="xxmsonormal">host("127.0.0.1")<o:p></o:p></p>
<p class="xxmsonormal">table("logs")<o:p></o:p></p>
<p class="xxmsonormal">columns("host", "id", "location", "facility", "rule", "priority", "level", "tag", "datetime", "program", "msg")<o:p></o:p></p>
<p class="xxmsonormal">values("$HOST", "$ID", "$LOCATION","$FACILITY", "$RULE", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")<o:p></o:p></p>
<p class="xxmsonormal">indexes("datetime", "host", "id", "location", "rule")<o:p></o:p></p>
<p class="xxmsonormal">);<o:p></o:p></p>
<p class="xxmsonormal">};<o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
<p class="xxmsonormal">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. <o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
<p class="xxmsonormal">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.
<o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
<p class="xxmsonormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">Thanks,</span><o:p></o:p></p>
<p class="xxmsonormal" style="margin-bottom:12.0pt"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Allen Olivas</span><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
</span><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#3A67B8">Info</span></i></b><b><i><span style="font-size:18.0pt;font-family:"Arial",sans-serif;color:#5F5F5F">Defense</span></i></b><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black"><br>
Office: (972) 848-7910<br>
Email: <a href="mailto:allen.olivas@infodefense.com">allen.olivas@infodefense.com</a><br>
Toll Free: (877) INFODEFENSE<br>
</span><a href="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" title="http://www.infodefense.com/"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:blue">www.infodefense.com</span></a><o:p></o:p></p>
<p class="xxmsonormal"> <o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>