<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    Hm... it's a bug in syslog-ng. We already found it in PE before,
    Bazsi should port the fix back to OSE.<br>
    <br>
    <br>
    On 2011-03-11 13:35, Hendrik Visage wrote:
    <blockquote
      cite="mid:AANLkTintmeLbGacuNMuT4JtavRAF_sEPbXejm-xZTYeg@mail.gmail.com"
      type="cite">Okay, now that I have the "" -&gt; Null replacements,
      now I have another escape issue.<br>
      <br>
      Making use of Postgresql's bigserial, it reads:<br>
      &lt;qoute <a moz-do-not-send="true"
href="http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL">http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL</a>&gt;<br>
      To insert the next value of the sequence into the <tt
        class="TYPE">serial</tt> column, specify that the <tt
        class="TYPE">serial</tt> column should be assigned its default
      value. This can be done either by excluding the column from the
      list of columns in the <tt class="COMMAND">INSERT</tt> statement,
      or through the use of the <tt class="LITERAL">DEFAULT</tt> key
      word. <br>
      &lt;/quote&gt;<br>
      <br>
      Which means I have to have *no* quotes, which the syslog-ng
      enforces :(<br>
      <br>
      destination d_pgsql {<br>
      &nbsp; sql(type(pgsql)<br>
      &nbsp; host("127.0.0.1") username("logwriter") password("logwriter")
      port("5432")<br>
      &nbsp; database("syslog") table("SystemEvents")<br>
      columns("ID bigserial primary key","ReceivedAt timestamptz NULL",
      "DeviceReportedTime timestamptz NULL",<br>
      "Facility smallint NULL","Priority smallint NULL","FromHost
      varchar(60) NULL",<br>
      "Message text","InfoUnitID int NULL","SysLogTag varchar(60)",<br>
      "CustomerID bigint","NTSeverity int NULL","Importance int
      NULL","EventSource varchar(60)","EventUser varchar(60) NULL",<br>
      "EventCategory int NULL","EventID int NULL","EventBinaryData text
      NULL","MaxAvailable int NULL","CurrUsage int NULL","MinUsage int
      NULL",<br>
      "MaxUsage int NULL","EventLogType varchar(60)","GenericFileName
      VarChar(60)","SystemID int NULL")<br>
      values(DEFAULT,"$R_ISODATE",
      "$S_ISODATE","$FACILITY_NUM","$LEVEL_NUM","$HOST",<br>
"$MSGONLY","1","$MSGHDR","","","","","","","","","","","","","","","")<br>
indexes("ID","ReceivedAt","Facility","Priority","FromHost","SysLogTag",)<br>
      null(""));<br>
      };<br>
      <br>
      <br>
      <br>
      Running SQL query; query='INSERT INTO SystemEvents (ID,
      ReceivedAt, DeviceReportedTime, Facility, Priority, FromHost,
      Message, InfoUnitID, SysLogTag, CustomerID, NTSeverity,
      Importance, EventSource, EventUser, EventCategory, EventID,
      EventBinaryData, MaxAvailable, CurrUsage, MinUsage, MaxUsage,
      EventLogType, GenericFileName, SystemID) VALUES (\'DEFAULT\',
      \'2011-03-11T14:14:12+02:00\', \'2011-03-11T11:12:37+02:00\',
      \'20\', \'6\', \'betvmi01\', \'[2011-03-11 11:12:37.565 11AA6B90
      verbose \'\'App\'\'] [VpxaVMAP] CheckThreshold percent changes
      (0,0,0,0) - threshold 5\', \'1\', \'Vpxa: \', NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL)'<br>
      Error running SQL query; type='pgsql', host='127.0.0.1',
      port='5432', user='logwriter', database='syslog', error='<b>ERROR:&nbsp;
        invalid input syntax for integer: "DEFAULT"</b>\x0aLINE 1: ...
      EventLogType, GenericFileName, SystemID) VALUES
      (\'DEFAULT\',...\x0a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      ^\x0a', query='INSERT INTO SystemEvents (ID, ReceivedAt,
      DeviceReportedTime, Facility, Priority, FromHost, Message,
      InfoUnitID, SysLogTag, CustomerID, NTSeverity, Importance,
      EventSource, EventUser, EventCategory, EventID, EventBinaryData,
      MaxAvailable, CurrUsage, MinUsage, MaxUsage, EventLogType,
      GenericFileName, SystemID) VALUES (\'DEFAULT\',
      \'2011-03-11T14:14:12+02:00\', \'2011-03-11T11:12:37+02:00\',
      \'20\', \'6\', \'betvmi01\', \'[2011-03-11 11:12:37.565 11AA6B90
      verbose \'\'App\'\'] [VpxaVMAP] CheckThreshold percent changes
      (0,0,0,0) - threshold 5\', \'1\', \'Vpxa: \', NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL)'<br>
      <br>
      AS long as there is now escapes for the DEFAULT, then it "works":<br>
      <br>
      syslog=# INSERT INTO SystemEvents (ID, ReceivedAt,
      DeviceReportedTime, Facility, Priority, FromHost, Message,
      InfoUnitID, SysLogTag, CustomerID, NTSeverity, Importance,
      EventSource, EventUser, EventCategory, EventID, EventBinaryData,
      MaxAvailable, CurrUsage, MinUsage, MaxUsage, EventLogType,
      GenericFileName, SystemID) VALUES (DEFAULT,
      '2011-03-11T14:10:18+02:00', '2011-03-11T11:08:43+02:00', '20',
      '6', 'betvmi01', '[2011-03-11 11:08:43.522 11921B90 verbose
      ''App''] [VpxaVMAP::Invoke] Command returned successfully', '1',
      'Vpxa: ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL);<br>
      INSERT 0 1<br>
      syslog=# <br>
      <br>
      <div class="gmail_quote">On Tue, Feb 22, 2011 at 12:59 PM, Zolt&aacute;n
        Pallagi <span dir="ltr">&lt;<a moz-do-not-send="true"
            href="mailto:pzolee@balabit.hu">pzolee@balabit.hu</a>&gt;</span>
        wrote:<br>
        <blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
          0.8ex; border-left: 1px solid rgb(204, 204, 204);
          padding-left: 1ex;">
          <div bgcolor="#ffffff" text="#000000"> Try this:<br>
            &nbsp;&nbsp;&nbsp; columns("datetime varchar(16)", "host varchar(32)",
            "program varchar(16)", "pid varchar(4) default null",
            "message varchar")<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
            <div class="im"><br>
              &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; indexes("datetime", "host", "program", "pid",
              "message")<br>
            </div>
            &nbsp; null(""));<br>
            <br>
            null(): If the content of a column matches the string
            specified in the <i><code>null()</code></i> parameter, the
            contents of the column will be replaced with an SQL NULL
            value<br>
            <br>
            It worked for me:<br>
            <br>
            Incoming log entry; line='&lt;166&gt;Feb 22 08:38:59 Vpxa:
            HostChanged Event Fired, properties changed []'<br>
            Running SQL query; query='INSERT INTO
            t2105010221cdb33661c82e91cb0b (datetime, host, program, pid,
            message) VALUES (\'Feb 22 11:50:17\', \'thor-t410\',
            \'Vpxa\', NULL, \'HostChanged Event Fired, properties
            changed []\')'<br>
            <br>
            Ofc, you can use integer type for pid, but this is a string
            and not an integer.
            <div>
              <div class="h5"><br>
                <br>
                On 2011-02-22 10:57, Hendrik Visage wrote: </div>
            </div>
            <blockquote type="cite">
              <div>
                <div class="h5">Hi there,<br>
                  <br>
                  &nbsp;Okay, looks like I was looking/thinking about
                  something else, so lets explain my problem.<br>
                  <br>
                  &nbsp;I want the pid inseted to be a "proper"
                  number/integer, not a string/character, and the fun
                  starts with several of my sources not having a pid
                  entry, thus that field I would like to be filled with
                  NULL values. However, it still gets escaped, and when
                  it gets escaped, the NULL is not null anymore, but the
                  string 'NULL' :(<br>
                  <br>
                  I was hoping that in some why I could coerce syslog-ng
                  to understand that that column is a integer column,
                  thus should not be escaped as it is currently done ;(<br>
                  <br>
                  I have the following destination:<br>
                  destination d_pgsql {<br>
                  &nbsp; sql(type(pgsql)<br>
                  &nbsp; host("127.0.0.1") username("logwriter")
                  password("logwriter") port("5432")<br>
                  &nbsp; database("syslog")<br>
                  &nbsp; table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
                  #or whatever you want, example ${HOST}" for hosts,
                  ${LEVEL}" for levels.. etc<br>
                  &nbsp; columns("datetime varchar(16)", "host varchar(32)",
                  "program varchar(16)", "pid varchar(8) default null",
                  "message varchar")<br>
                  &nbsp; values("$R_DATE", "$HOST", "$PROGRAM",
                  "${PID:-NULL}", "$MSG")<br>
                  &nbsp; indexes("datetime", "host", "program", "pid",
                  "message"));<br>
                  };<br>
                  <br>
                  and the following log lines:<br>
                  <br>
                  Incoming log entry; line='&lt;166&gt;Feb 22 08:38:59
                  Vpxa: [2011-02-22 08:38:59.862 1B7A7B90 verbose
                  \'App\'] [VpxaHalServices] HostChanged Event Fired,
                  properties changed []'<br>
                  Running SQL query; query='INSERT INTO
                  logs_somedevice_20110222 (datetime, host, program,
                  pid, message) VALUES (\'Feb 22 11:39:27\',
                  \'somedevice\', \'Vpxa\', \'NULL\', \'[2011-02-22
                  08:38:59.862 1B7A7B90 verbose \'\'App\'\']
                  [VpxaHalServices] HostChanged Event Fired, properties
                  changed []\')'<br>
                  <br>
                </div>
              </div>
              <pre><fieldset></fieldset>
______________________________________________________________________________
Member info: <a moz-do-not-send="true" href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><div class="im">
Documentation: <a moz-do-not-send="true" href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a>
FAQ: <a moz-do-not-send="true" href="http://www.campin.net/syslog-ng/faq.html" target="_blank">http://www.campin.net/syslog-ng/faq.html</a>

</div></pre>
            </blockquote>
            <br>
          </div>
        </blockquote>
      </div>
      <br>
    </blockquote>
    <br>
  </body>
</html>