<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">I should have read the RFC on CSV prior
to drafting the format.<br>
<br>
<a class="moz-txt-link-freetext" href="https://tools.ietf.org/html/rfc4180">https://tools.ietf.org/html/rfc4180</a><br>
<br>
Turns out Peter and I both got it wrong. The rewrite should be<br>
<br>
rewrite r_csv_message {<br>
set("$MESSAGE", value("CSVMESSAGE") );<br>
subst("\"","\"\"", value("CSVMESSAGE"), flags(global) );<br>
};<br>
<br>
Of course, there are many "interpretations" of CSV so your
consumer may not conform to the official standard.<br>
<br>
On 04/20/2017 03:36 AM, Czanik, Péter wrote:<br>
</div>
<blockquote
cite="mid:CANcUavtwX0RDY4=LbsdFhE5z5VQsVrEbcjw+zgyW1JLVq7wZ8g@mail.gmail.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<div dir="ltr">
<div>
<div>
<div>
<div>Hi,<br>
</div>
<br>
Just a heads up: I also got it working after a bit of
debugging. The problem was, that in the above
configuration sample there are spaces in the template.
After removing those, it worked. Here is my config:<br>
<br>
[root@localhost conf.d]# cat oq.conf<br>
rewrite r_csv_message {<br>
set("$MESSAGE", value("CSVMESSAGE") );<br>
subst("\"","\\\"", value("CSVMESSAGE"),
flags(global) );<br>
};<br>
<br>
template t_csv {<br>
template("\"${ISODATE}\",\"${HOST}\",\"${LEVEL_NUM}\",\"${FACILITY}\",\"${PROGRAM}\",\"${CSVMESSAGE}\"\n");<br>
template_escape(no);<br>
};<br>
<br>
destination d_osquery_copy {<br>
file("/var/log/csv_osquery" template(t_csv));<br>
};<br>
<br>
destination d_osquery {<br>
pipe("/var/osquery/syslog_pipe" template(t_csv));<br>
};<br>
<br>
log {<br>
source(s_sys);<br>
rewrite(r_csv_message);<br>
destination(d_osquery);<br>
destination(d_osquery_copy);<br>
};<br>
[root@localhost conf.d]# <br>
<br>
</div>
I figured it out by installing rsyslog and looking at the
differences in the output.<br>
<br>
</div>
I plan to summarize my experiences in a blog in a week or two.<br>
<br>
</div>
Bye,<br>
</div>
<div class="gmail_extra"><br clear="all">
<div>
<div class="gmail_signature" data-smartmail="gmail_signature">
<div dir="ltr">
<div>Peter Czanik (CzP) <<a moz-do-not-send="true"
href="mailto:peter.czanik@balabit.com" target="_blank">peter.czanik@balabit.com</a>><br>
Balabit / syslog-ng upstream<br>
<a moz-do-not-send="true"
href="https://www.balabit.com/blog/author/peterczanik/"
target="_blank">https://www.balabit.com/blog/author/peterczanik/</a><br>
<a moz-do-not-send="true"
href="https://twitter.com/PCzanik" target="_blank">https://twitter.com/PCzanik</a></div>
</div>
</div>
</div>
<br>
<div class="gmail_quote">On Tue, Apr 18, 2017 at 8:58 PM,
Dwijadas Dey <span dir="ltr"><<a moz-do-not-send="true"
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>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<span class="HOEnZb"><font color="#888888"><br>
</font></span></div>
<span class="HOEnZb"><font color="#888888">Dwijadas Dey</font></span>
<div>
<div class="h5"><br>
<div><br>
<div class="gmail_extra"><br>
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0
0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">
<div class="m_6199536138515407586HOEnZb">
<div class="m_6199536138515407586h5">
<div class="gmail_extra">
<div class="gmail_quote">On Wed, Apr 19,
2017 at 12:06 AM, Evan Rempel <span
dir="ltr"><<a
moz-do-not-send="true"
href="mailto:erempel@uvic.ca"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:erempel@uvic.ca">erempel@uvic.ca</a></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_6199536138515407586m_-3202658078092580564m_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="m_6199536138515407586m_-3202658078092580564h5"><br>
<br>
On 04/18/2017 10:22 AM,
Dwijadas Dey wrote:<br>
</div>
</div>
</div>
<div>
<div
class="m_6199536138515407586m_-3202658078092580564h5">
<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
moz-do-not-send="true"
href="mailto:erempel@uvic.ca" target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:erempel@uvic.ca">erempel@uvic.ca</a></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_6199536138515407586m_-3202658078092580564m_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%,%sys<wbr>logseverity:::csv%,%syslogfaci<wbr>lity-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_6199536138515407586m_-3202658078092580564m_1005913446201928953h5"><br>
<br>
<br>
On 04/18/2017
08:57 AM,
Dwijadas Dey
wrote:<br>
</div>
</div>
</div>
<div>
<div
class="m_6199536138515407586m_-3202658078092580564m_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\t<wbr>$syslogfacility\t$syslogtag\t$<wbr>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
moz-do-not-send="true"
href="https://osquery.readthedocs.io/en/stable/deployment/syslog/#rsyslog-versions-7_1"
target="_blank"><a class="moz-txt-link-freetext" href="https://osquery.readthedocs.io">https://osquery.readthedocs.io</a><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
moz-do-not-send="true" href="mailto:peter.czanik@balabit.com"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:peter.czanik@balabit.com">peter.czanik@balabit.com</a></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_6199536138515407586m_-3202658078092580564m_1005913446201928953m_8640115338039768182m_1327877505004078358gmail_signature"
data-smartmail="gmail_signature">
<div dir="ltr">
<div>Peter
Czanik (CzP)
<<a
moz-do-not-send="true"
href="mailto:peter.czanik@balabit.com" target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:peter.czanik@balabit.com">peter.czanik@balabit.com</a></a>><br>
Balabit /
syslog-ng
upstream<br>
<a
moz-do-not-send="true"
href="https://www.balabit.com/blog/author/peterczanik/" target="_blank"><a class="moz-txt-link-freetext" href="https://www.balabit.com/blog/a">https://www.balabit.com/blog/a</a><wbr>uthor/peterczanik/</a><br>
<a
moz-do-not-send="true"
href="https://twitter.com/PCzanik" target="_blank"><a class="moz-txt-link-freetext" href="https://twitter.com/PCzanik">https://twitter.com/PCzanik</a></a></div>
</div>
</div>
</div>
<div>
<div
class="m_6199536138515407586m_-3202658078092580564m_1005913446201928953m_8640115338039768182h5">
<br>
<div
class="gmail_quote">On
Mon, Apr 17,
2017 at 4:32
PM, Dwijadas
Dey <span
dir="ltr"><<a
moz-do-not-send="true" href="mailto:dwijad@gmail.com" target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:dwijad@gmail.com">dwijad@gmail.com</a></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_6199536138515407586m_-3202658078092580564m_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
moz-do-not-send="true" href="mailto:robert.fekete@balabit.com"
target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:robert.fekete@balabit.com">robert.fekete@balabit.com</a></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
moz-do-not-send="true"
href="https://osquery.readthedocs.io/en/latest/deployment/logging/"
target="_blank"><a class="moz-txt-link-freetext" href="https://osquery.readthedocs.io">https://osquery.readthedocs.io</a><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
moz-do-not-send="true"
href="https://www.balabit.com/documents/syslog-ng-ose-latest-guides/en/syslog-ng-ose-guide-admin/html/json-parser.html"
target="_blank"><a class="moz-txt-link-freetext" href="https://www.balabit.com/docume">https://www.balabit.com/docume</a><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_6199536138515407586m_-3202658078092580564m_1005913446201928953m_8640115338039768182m_1327877505004078358m_-3763707499530419432h5">On
Fri, Apr 14,
2017 at 9:46
PM, Dwijadas
Dey <span
dir="ltr"><<a
moz-do-not-send="true" href="mailto:dwijad@gmail.com" target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:dwijad@gmail.com">dwijad@gmail.com</a></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_6199536138515407586m_-3202658078092580564m_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
moz-do-not-send="true"
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>
</blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
<br>
</body>
</html>