<br><br><div class="gmail_quote">On Fri, Mar 11, 2011 at 3:27 PM, Zoltán Pallagi <span dir="ltr">&lt;<a 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">
    Hm... it&#39;s a bug in syslog-ng. We already found it in PE before,
    Bazsi should port the fix back to OSE.</div></blockquote><div><br>3.2.2+ or will that go into 3.3alpha?<br> </div><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"><div><div></div><div class="h5"><br>
    <br>
    <br>
    On 2011-03-11 13:35, Hendrik Visage wrote:
    <blockquote type="cite">Okay, now that I have the &quot;&quot; -&gt; Null replacements,
      now I have another escape issue.<br>
      <br>
      Making use of Postgresql&#39;s bigserial, it reads:<br>
      &lt;qoute <a href="http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL" target="_blank">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>serial</tt> column, specify that the <tt>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>INSERT</tt> statement,
      or through the use of the <tt>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>
        sql(type(pgsql)<br>
        host(&quot;127.0.0.1&quot;) username(&quot;logwriter&quot;) password(&quot;logwriter&quot;)
      port(&quot;5432&quot;)<br>
        database(&quot;syslog&quot;) table(&quot;SystemEvents&quot;)<br>
      columns(&quot;ID bigserial primary key&quot;,&quot;ReceivedAt timestamptz NULL&quot;,
      &quot;DeviceReportedTime timestamptz NULL&quot;,<br>
      &quot;Facility smallint NULL&quot;,&quot;Priority smallint NULL&quot;,&quot;FromHost
      varchar(60) NULL&quot;,<br>
      &quot;Message text&quot;,&quot;InfoUnitID int NULL&quot;,&quot;SysLogTag varchar(60)&quot;,<br>
      &quot;CustomerID bigint&quot;,&quot;NTSeverity int NULL&quot;,&quot;Importance int
      NULL&quot;,&quot;EventSource varchar(60)&quot;,&quot;EventUser varchar(60) NULL&quot;,<br>
      &quot;EventCategory int NULL&quot;,&quot;EventID int NULL&quot;,&quot;EventBinaryData text
      NULL&quot;,&quot;MaxAvailable int NULL&quot;,&quot;CurrUsage int NULL&quot;,&quot;MinUsage int
      NULL&quot;,<br>
      &quot;MaxUsage int NULL&quot;,&quot;EventLogType varchar(60)&quot;,&quot;GenericFileName
      VarChar(60)&quot;,&quot;SystemID int NULL&quot;)<br>
      values(DEFAULT,&quot;$R_ISODATE&quot;,
      &quot;$S_ISODATE&quot;,&quot;$FACILITY_NUM&quot;,&quot;$LEVEL_NUM&quot;,&quot;$HOST&quot;,<br>
&quot;$MSGONLY&quot;,&quot;1&quot;,&quot;$MSGHDR&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;)<br>

indexes(&quot;ID&quot;,&quot;ReceivedAt&quot;,&quot;Facility&quot;,&quot;Priority&quot;,&quot;FromHost&quot;,&quot;SysLogTag&quot;,)<br>
      null(&quot;&quot;));<br>
      };<br>
      <br>
      <br>
      <br>
      Running SQL query; query=&#39;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 (\&#39;DEFAULT\&#39;,
      \&#39;2011-03-11T14:14:12+02:00\&#39;, \&#39;2011-03-11T11:12:37+02:00\&#39;,
      \&#39;20\&#39;, \&#39;6\&#39;, \&#39;betvmi01\&#39;, \&#39;[2011-03-11 11:12:37.565 11AA6B90
      verbose \&#39;\&#39;App\&#39;\&#39;] [VpxaVMAP] CheckThreshold percent changes
      (0,0,0,0) - threshold 5\&#39;, \&#39;1\&#39;, \&#39;Vpxa: \&#39;, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL)&#39;<br>
      Error running SQL query; type=&#39;pgsql&#39;, host=&#39;127.0.0.1&#39;,
      port=&#39;5432&#39;, user=&#39;logwriter&#39;, database=&#39;syslog&#39;, error=&#39;<b>ERROR: 
        invalid input syntax for integer: &quot;DEFAULT&quot;</b>\x0aLINE 1: ...
      EventLogType, GenericFileName, SystemID) VALUES
      (\&#39;DEFAULT\&#39;,...\x0a                                                            
      ^\x0a&#39;, query=&#39;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 (\&#39;DEFAULT\&#39;,
      \&#39;2011-03-11T14:14:12+02:00\&#39;, \&#39;2011-03-11T11:12:37+02:00\&#39;,
      \&#39;20\&#39;, \&#39;6\&#39;, \&#39;betvmi01\&#39;, \&#39;[2011-03-11 11:12:37.565 11AA6B90
      verbose \&#39;\&#39;App\&#39;\&#39;] [VpxaVMAP] CheckThreshold percent changes
      (0,0,0,0) - threshold 5\&#39;, \&#39;1\&#39;, \&#39;Vpxa: \&#39;, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL)&#39;<br>
      <br>
      AS long as there is now escapes for the DEFAULT, then it &quot;works&quot;:<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,
      &#39;2011-03-11T14:10:18+02:00&#39;, &#39;2011-03-11T11:08:43+02:00&#39;, &#39;20&#39;,
      &#39;6&#39;, &#39;betvmi01&#39;, &#39;[2011-03-11 11:08:<a href="tel:43.522%2011921" target="_blank">43.522 11921</a>B90 verbose
      &#39;&#39;App&#39;&#39;] [VpxaVMAP::Invoke] Command returned successfully&#39;, &#39;1&#39;,
      &#39;Vpxa: &#39;, 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án
        Pallagi <span dir="ltr">&lt;<a href="mailto:pzolee@balabit.hu" target="_blank">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>
                columns(&quot;datetime varchar(16)&quot;, &quot;host varchar(32)&quot;,
            &quot;program varchar(16)&quot;, &quot;pid varchar(4) default null&quot;,
            &quot;message varchar&quot;)<br>
                  values(&quot;$R_DATE&quot;, &quot;$HOST&quot;, &quot;$PROGRAM&quot;, &quot;$PID&quot;, &quot;$MSG&quot;)
            <div><br>
                      indexes(&quot;datetime&quot;, &quot;host&quot;, &quot;program&quot;, &quot;pid&quot;,
              &quot;message&quot;)<br>
            </div>
              null(&quot;&quot;));<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=&#39;&lt;166&gt;Feb 22 08:38:59 Vpxa:
            HostChanged Event Fired, properties changed []&#39;<br>
            Running SQL query; query=&#39;INSERT INTO
            t2105010221cdb33661c82e91cb0b (datetime, host, program, pid,
            message) VALUES (\&#39;Feb 22 11:50:17\&#39;, \&#39;thor-t410\&#39;,
            \&#39;Vpxa\&#39;, NULL, \&#39;HostChanged Event Fired, properties
            changed []\&#39;)&#39;<br>
            <br>
            Ofc, you can use integer type for pid, but this is a string
            and not an integer.
            <div>
              <div><br>
                <br>
                On 2011-02-22 10:57, Hendrik Visage wrote: </div>
            </div>
            <blockquote type="cite">
              <div>
                <div>Hi there,<br>
                  <br>
                   Okay, looks like I was looking/thinking about
                  something else, so lets explain my problem.<br>
                  <br>
                   I want the pid inseted to be a &quot;proper&quot;
                  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 &#39;NULL&#39; :(<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>
                    sql(type(pgsql)<br>
                    host(&quot;127.0.0.1&quot;) username(&quot;logwriter&quot;)
                  password(&quot;logwriter&quot;) port(&quot;5432&quot;)<br>
                    database(&quot;syslog&quot;)<br>
                    table(&quot;logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}&quot;)
                  #or whatever you want, example ${HOST}&quot; for hosts,
                  ${LEVEL}&quot; for levels.. etc<br>
                    columns(&quot;datetime varchar(16)&quot;, &quot;host varchar(32)&quot;,
                  &quot;program varchar(16)&quot;, &quot;pid varchar(8) default null&quot;,
                  &quot;message varchar&quot;)<br>
                    values(&quot;$R_DATE&quot;, &quot;$HOST&quot;, &quot;$PROGRAM&quot;,
                  &quot;${PID:-NULL}&quot;, &quot;$MSG&quot;)<br>
                    indexes(&quot;datetime&quot;, &quot;host&quot;, &quot;program&quot;, &quot;pid&quot;,
                  &quot;message&quot;));<br>
                  };<br>
                  <br>
                  and the following log lines:<br>
                  <br>
                  Incoming log entry; line=&#39;&lt;166&gt;Feb 22 08:38:59
                  Vpxa: [2011-02-22 08:38:59.862 1B7A7B90 verbose
                  \&#39;App\&#39;] [VpxaHalServices] HostChanged Event Fired,
                  properties changed []&#39;<br>
                  Running SQL query; query=&#39;INSERT INTO
                  logs_somedevice_20110222 (datetime, host, program,
                  pid, message) VALUES (\&#39;Feb 22 11:39:27\&#39;,
                  \&#39;somedevice\&#39;, \&#39;Vpxa\&#39;, \&#39;NULL\&#39;, \&#39;[2011-02-22
                  08:38:59.862 1B7A7B90 verbose \&#39;\&#39;App\&#39;\&#39;]
                  [VpxaHalServices] HostChanged Event Fired, properties
                  changed []\&#39;)&#39;<br>
                  <br>
                </div>
              </div>
              <pre><fieldset></fieldset>
______________________________________________________________________________
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><div>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a>
FAQ: <a 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>
  </div></div></div>

</blockquote></div><br>