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