<div dir="ltr"><div><div><div><div>Hi<br></div> Evan<br></div> Your suggestion works flawlessly. The syslog table in OSQUERY gets filled up with logs 😀 The missing part is the rewrite rule r_csv_message. Many many thanks to you.<br><br></div>Regards<br></div>Dwijadas Dey<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Apr 19, 2017 at 12:06 AM, Evan Rempel <span dir="ltr"><<a href="mailto:erempel@uvic.ca" target="_blank">erempel@uvic.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<div class="m_1005913446201928953moz-cite-prefix">The fact that your error "Received more
fields than expected" went away implies that the number of fields
is correct.<br>
Without any errors or any data in the table your trouble shooting
options are limited.<br>
<br>
I would make another file based destination for syslog-ng<br>
<br>
destination d_osquery_copy {<br>
file("/var/osquery/syslog" template(t_csv));<br>
};<br>
<br>
And add this destination to your log statement.<br>
<br>
log {<br>
source(s_osquery);<br>
destination(d_osquery);<br>
destination(d_osquery_copy);<br>
};<br>
<br>
<br>
Then you will have a copy of the data that is being sent to
osquery and you should be able to get help from the osquery
community.<br>
<br>
<br>
One other thing to note is that I did not provide you with the
correct CSV of the MESSAGE portion. If the $MESSAGE contains
double quotes<br>
then this will not be a correctly formatted CSV field.<br>
<br>
you can make a rewrite rule for the message<br>
<br>
rewrite r_csv_message {<br>
set("$MESSAGE", value("CSVMESSAGE") );<br>
subst("\"","\\\"", value("CSVMESSAGE"), flags(global) );<br>
};<br>
<br>
then you need to invoke this rewrite rule in your log statement.<br>
<br>
log {<br>
source(s_osquery);<br>
rewrite(r_csv_message);<br>
destination(d_osquery);<br>
destination(d_osquery_copy);<br>
};<br>
<br>
And finally your template needs to use the CSVMESSAGE rather than
the MESSAGE<br>
<br>
template t_csv { template("\"${ISODATE}\", \"${HOST}\",
\"${LEVEL_NUM}\", \"${FACILITY}\", \"${PROGRAM}\",
\"${CSVMESSAGE}\"\n"); template_escape(no); };<br>
<br>
<br>
I hope that helps too.<br>
<br>
Evan.<div><div class="h5"><br>
<br>
On 04/18/2017 10:22 AM, Dwijadas Dey wrote:<br>
</div></div></div><div><div class="h5">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>Hi<br>
</div>
Evan <br>
</div>
Thanks you for a quick reply. After changing the
template as suggested by you, the error goes away but the
syslog table in OSQUERY does not get filled up. May be the
OSQUERY expects 7 entry for the syslog table while the
template has six fields.<br>
<br>
<pre>> osquery> .schema syslog
> CREATE TABLE syslog_events(`time` BIGINT, `datetime` TEXT, `host` TEXT,
> `severity` INTEGER, `facility` TEXT, `tag` TEXT, `message` TEXT);</pre>
No verbose error as well.<br>
<br>
</div>
Regards<br>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Apr 18, 2017 at 9:45 PM, Evan
Rempel <span dir="ltr"><<a href="mailto:erempel@uvic.ca" target="_blank">erempel@uvic.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<div class="m_1005913446201928953m_8640115338039768182moz-cite-prefix">The
documentation from OSQuery is for rsyslog and shows that
a csv set of values is needed.<br>
<br>
string="%timestamp:::date-rfc3<wbr>339,csv%,%hostname:::csv%,%<wbr>syslogseverity:::csv%,%syslogf<wbr>acility-text:::csv%,%syslogtag<wbr>:::csv%,%msg:::csv%\n"<br>
<br>
In syslog-ng this format becomes<br>
<br>
template t_csv { template("\"${ISODATE}\",
\"${HOST}\", \"${LEVEL_NUM}\", \"${FACILITY}\",
\"${PROGRAM}\", \"${MESSAGE}\"\n"); template_escape(no);
};<br>
<br>
Give that a try and see how things go.
<div>
<div class="m_1005913446201928953h5"><br>
<br>
<br>
On 04/18/2017 08:57 AM, Dwijadas Dey wrote:<br>
</div>
</div>
</div>
<div>
<div class="m_1005913446201928953h5">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>Hi<br>
</div>
Peter<br>
</div>
I am trying to send
syslogs to a named pipe and on the
other end OSQUERY will consume the
syslogs from the named pipe. Once
OSQUERY consumes syslogs, it will
sends the logs to RocksDB that
comes along with OSQUERY. I have
been able to send the syslogs to
named pipe ( verified with cat
command ) but on the other hand
OSQUERY did consume the logs but
could not send these logs to the
table due to format error.<br>
<br>
</div>
The schema of syslog table in
OSQUERY<br>
------------------------------<wbr>------------------------------<br>
osquery> .schema syslog<br>
CREATE TABLE syslog_events(`time`
BIGINT, `datetime` TEXT, `host`
TEXT, `severity` INTEGER, `facility`
TEXT, `tag` TEXT, `message` TEXT);<br>
<br>
Conf file in syslog-ng
(/etc/syslog-ng/conf.d/osquery<wbr>.conf)<br>
------------------------------<wbr>------------------------------<wbr>----------------------<br>
source s_osquery {<br>
system(); <br>
};<br>
<br>
template t_csv {<br>
template("'${HOUR}${MIN}${SEC}<wbr>',\t'${ISODATE}',\t'${HOST}',\<wbr>t'${TAG}',\t'${LEVEL}',\t'${FA<wbr>CILITY}',\t'${MSG}'\n");<br>
#
template("$timestamp\t${ISODAT<wbr>E}\t{$HOST}\t$syslogseverity\<wbr>t$syslogfacility\t$syslogtag\<wbr>t$msg\n");<br>
template_escape(no);<br>
};<br>
<br>
destination d_osquery {<br>
pipe("/var/osquery/syslog_pipe<wbr>"
template(t_csv));<br>
};<br>
<br>
log {<br>
source(s_osquery);<br>
destination(d_osquery);<br>
};<br>
<br>
</div>
I am trying to match the above
template to rsyslog format for OSQUERY<br>
<br>
<a href="https://osquery.readthedocs.io/en/stable/deployment/syslog/#rsyslog-versions-7_1" target="_blank">https://osquery.readthedocs.io<wbr>/en/stable/deployment/syslog/#<wbr>rsyslog-versions-7_1</a><br>
<br>
</div>
If i cat the pipe, i can see the
syslogs.<br>
<br>
# cat /var/osquery/syslog_pipe<br>
<br>
'155349',
'2017-04-18T15:53:49+00:00', <wbr>
'ubuntu', '26', 'info', 'auth',
'Disconnected from 61.177.172.51 port
20876 [preauth]'<br>
'155349',
'2017-04-18T15:53:49+00:00', <wbr>
'ubuntu', '55', 'notice',
'authpriv', 'PAM 2 more
authentication failures; logname= uid=0
euid=0 tty=ssh ruser=
rhost=61.177.172.51 user=root'<br>
<br>
<br>
</div>
The above logs contains exactly 7 fields
as required by OSQUERY syslog table as
described above.<br>
<br>
<br>
</div>
The error that i am getting at the moment -<br>
------------------------------<wbr>------------------------------<br>
E0418 15:50:39.131995 4229 syslog.cpp:173]
Received more fields than expected in line:
''154852',
'2017-04-18T15:48:52+00:00', <wbr>
'ubuntu', '9b', 'err', 'local3',
'severity=2 location=syslog.cpp:173
message=Received more fields than expected
in line: ''154852',
'2017-04-18T15:48:52+00:00', 'ubuntu',
'9d', 'notice', 'local3',
'severity=0 location=file_events.cpp:68
message=Added file event listener to:
/root/.ssh/**<br>
E0418 15:50:39.132355 4229 syslog.cpp:173]
Received more fields than expected in line:
''154852',
'2017-04-18T15:48:52+00:00', <wbr>
'ubuntu', '9b', 'err', 'local3',
'severity=2 location=syslog.cpp:173
message=Received more fields than expected
in line: ''154852',
'2017-04-18T15:48:52+00:00', 'ubuntu',
'9d', 'notice', 'local3',
'severity=0 location=file_events.cpp:68
message=Added file event listener to:
/home/*/.ssh/**<br>
E0418 15:50:39.132758 4229 syslog.cpp:173]
Received more fields than expected in line:
''154852',
'2017-04-18T15:48:52+00:00', <wbr>
'ubuntu', '9b', 'err', 'local3',
'severity=2 location=syslog.cpp:173
message=Received more fields than expected
in line: ''154852',
'2017-04-18T15:48:52+00:00', 'ubuntu',
'9d', 'notice', 'local3',
'severity=0 location=file_events.cpp:68
message=Added file event listener to:
/tmp/**<br>
I0418 15:50:39.133230 4229 events.cpp:767]
Event publisher syslog run loop terminated
for reason: Too many errors in syslog
parsing.<br>
<br>
</div>
I think the issue is with the template
definition which needs to match with the
template with rsyslog as described in the
above link.<br>
<br>
</div>
I will appreciate if someone can point out the
issues in template and how it should be in
syslog-ng.<br>
<br>
<br>
</div>
Regards<br>
<div>
<div>
<div>
<div><br>
<div>
<div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Apr 18, 2017 at
7:12 PM, Czanik, Péter <span dir="ltr"><<a href="mailto:peter.czanik@balabit.com" target="_blank">peter.czanik@balabit.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div>
<div>
<div>Hi,<br>
</div>
<br>
What do you try to achieve? Sending
syslog messages to OSquery or collecting
OSquery logs by syslog-ng?<br>
<br>
</div>
/me now has a test environment installed<br>
<br>
</div>
Bye,<br>
</div>
<div class="gmail_extra"><br clear="all">
<div>
<div class="m_1005913446201928953m_8640115338039768182m_1327877505004078358gmail_signature" data-smartmail="gmail_signature">
<div dir="ltr">
<div>Peter Czanik (CzP) <<a href="mailto:peter.czanik@balabit.com" target="_blank">peter.czanik@balabit.com</a>><br>
Balabit / syslog-ng upstream<br>
<a href="https://www.balabit.com/blog/author/peterczanik/" target="_blank">https://www.balabit.com/blog/a<wbr>uthor/peterczanik/</a><br>
<a href="https://twitter.com/PCzanik" target="_blank">https://twitter.com/PCzanik</a></div>
</div>
</div>
</div>
<div>
<div class="m_1005913446201928953m_8640115338039768182h5"> <br>
<div class="gmail_quote">On Mon, Apr 17,
2017 at 4:32 PM, Dwijadas Dey <span dir="ltr"><<a href="mailto:dwijad@gmail.com" target="_blank">dwijad@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>Hi<br>
</div>
Robert<br>
</div>
You are
right, i am trying the
same with a named pipe so
that OSQUERY consume
syslogs as pointed by
Evan. There are plenty of
documents showing the same
with rsyslog but not with
syslog-ng.<br>
<br>
</div>
This is what my syslog
configuration for osquery:-<br>
<br>
/etc/syslog-ng/conf.d/osquery.<wbr>conf<br>
<br>
source s_osquery {<br>
# system();<br>
pipe("/var/osquery/syslog_pipe<wbr>");<br>
#
unix-stream("/dev/log");<br>
};<br>
#filter osqueryd {<br>
#
program("^osqueryd.*");<br>
#};<br>
destination d_osquery {<br>
file("/var/log/osquery/osquery<wbr>d.results.log"
template("$(format-json
--scope selected_macros
--scope nv_pairs)\n"));<br>
};<br>
log {<br>
source(s_osquery);<br>
# filter(osqueryd);<br>
destination(d_osquery);<br>
};<br>
<br>
</div>
But this does not produce any
logs for OSQUERY. I have
checked , the name piped has
been created.<br>
<br>
# ls -l
/var/osquery/syslog_pipe<br>
pr--rw---- 1 root adm 0 Apr 14
15:41 /var/osquery/syslog_pipe<br>
<br>
But when i try to check what
logs are passing through the
pipe using following command,
no message shows up.<br>
# cat /var/osquery/syslog_pipe<br>
<br>
</div>
<div>I have correct options set
in OSQUERY configuration file
in /etc/osquery/osquery.conf.<br>
<br>
..................<br>
..................<br>
"logger_plugin": "syslog",<br>
"enable_syslog": "true",<br>
"syslog_pipe_path":
"/var/osquery/syslog_pipe",<br>
..................<br>
..................<br>
</div>
I think Evan can point me the
right configuration for
syslog-ng ( version 3.5.6 in
ubuntu 16 )<br>
<br>
</div>
Regards
<div>
<div class="m_1005913446201928953m_8640115338039768182m_1327877505004078358h5"><br>
<div><br>
<br>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On
Mon, Apr 17, 2017 at 6:24
PM, Fekete, Róbert <span dir="ltr"><<a href="mailto:robert.fekete@balabit.com" target="_blank">robert.fekete@balabit.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div>
<div>
<div>
<div>Hi, <br>
<br>
</div>
It seems that by
default, osquery
logs JSON
messages into a
file. ( <a href="https://osquery.readthedocs.io/en/latest/deployment/logging/" target="_blank">https://osquery.readthedocs.io<wbr>/en/latest/deployment/logging/</a>
)<br>
</div>
You can use this
file in a
syslog-ng source,
and parse the JSON
messages with the
json parser (note
that you need a
recent syslog-ng
OSE for this), see
<a href="https://www.balabit.com/documents/syslog-ng-ose-latest-guides/en/syslog-ng-ose-guide-admin/html/json-parser.html" target="_blank">https://www.balabit.com/docume<wbr>nts/syslog-ng-ose-latest-guide<wbr>s/en/syslog-ng-ose-guide-admin<wbr>/html/json-parser.html</a>
.<br>
<br>
<br>
</div>
The above Osquery
page mentions that
it can send log
messages directly to
syslog (instead of a
file), but I
haven't found how
you can actually
configure it.<br>
<br>
</div>
<div>Regards, <br>
<br>
</div>
<div>Robert<br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">
<div>
<div class="m_1005913446201928953m_8640115338039768182m_1327877505004078358m_-3763707499530419432h5">On
Fri, Apr 14,
2017 at 9:46 PM,
Dwijadas Dey <span dir="ltr"><<a href="mailto:dwijad@gmail.com" target="_blank">dwijad@gmail.com</a>></span>
wrote:<br>
</div>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div class="m_1005913446201928953m_8640115338039768182m_1327877505004078358m_-3763707499530419432h5">
<div dir="ltr">
<div>
<div>
<div>Hi<br>
</div>
List users<br>
</div>
Is it possible to send OSQUERY logs to syslog-ng 3.5
In the <a href="https://osquery.readthedocs.io/en/latest/deployment/syslog/" target="_blank">OSQUERY
docs</a>
rsyslog is
configured to
write logs to
syslog. Does
the same
method applies
to syslog-ng
3.5 ?<br>
<br>
</div>
Thanks and
regards<br>
</div>
<br>
</div>
</div>
<br>
</blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
<br>
</div>
</div>
</div>
<br>
______________________________<wbr>______________________________<wbr>__________________<br>
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" rel="noreferrer" target="_blank">https://lists.balabit.hu/mailm<wbr>an/listinfo/syslog-ng</a><br>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" rel="noreferrer" target="_blank">http://www.balabit.com/support<wbr>/documentation/?product=<wbr>syslog-ng</a><br>
FAQ: <a href="http://www.balabit.com/wiki/syslog-ng-faq" rel="noreferrer" target="_blank">http://www.balabit.com/wiki/sy<wbr>slog-ng-faq</a><br>
<br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="m_1005913446201928953mimeAttachmentHeader"></fieldset>
<br>
<pre>______________________________<wbr>______________________________<wbr>__________________
Member info: <a class="m_1005913446201928953moz-txt-link-freetext" href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/<wbr>mailman/listinfo/syslog-ng</a>
Documentation: <a class="m_1005913446201928953moz-txt-link-freetext" href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/<wbr>support/documentation/?<wbr>product=syslog-ng</a>
FAQ: <a class="m_1005913446201928953moz-txt-link-freetext" href="http://www.balabit.com/wiki/syslog-ng-faq" target="_blank">http://www.balabit.com/wiki/<wbr>syslog-ng-faq</a>
</pre>
</blockquote>
<br>
<p><br>
</p>
</div></div><span class="HOEnZb"><font color="#888888"><pre class="m_1005913446201928953moz-signature" cols="500">--
Evan Rempel <a class="m_1005913446201928953moz-txt-link-abbreviated" href="mailto:erempel@uvic.ca" target="_blank">erempel@uvic.ca</a>
Senior Systems Administrator 250.721.7691
Data Centre Services, University Systems, University of Victoria
</pre>
</font></span></div>
<br>______________________________<wbr>______________________________<wbr>__________________<br>
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" rel="noreferrer" target="_blank">https://lists.balabit.hu/<wbr>mailman/listinfo/syslog-ng</a><br>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" rel="noreferrer" target="_blank">http://www.balabit.com/<wbr>support/documentation/?<wbr>product=syslog-ng</a><br>
FAQ: <a href="http://www.balabit.com/wiki/syslog-ng-faq" rel="noreferrer" target="_blank">http://www.balabit.com/wiki/<wbr>syslog-ng-faq</a><br>
<br>
<br></blockquote></div><br></div>