[syslog-ng] escapes in SQL statements (was Re: dbtype in SQL logging)

[Zoltán Pallagi]

Try this:
     columns("datetime varchar(16)", "host varchar(32)", "program 
varchar(16)", "pid varchar(4) default null", "message varchar")
       values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
         indexes("datetime", "host", "program", "pid", "message")
   null(""));

null(): If the content of a column matches the string specified in the 
/|null()|/ parameter, the contents of the column will be replaced with 
an SQL NULL value

It worked for me:

Incoming log entry; line='<166>Feb 22 08:38:59 Vpxa: HostChanged Event 
Fired, properties changed []'
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 []\')'

Ofc, you can use integer type for pid, but this is a string and not an 
integer.

On 2011-02-22 10:57, Hendrik Visage wrote:
> Hi there,
>
>  Okay, looks like I was looking/thinking about something else, so lets 
> explain my problem.
>
>  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' :(
>
> 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 ;(
>
> I have the following destination:
> destination d_pgsql {
>   sql(type(pgsql)
>   host("127.0.0.1") username("logwriter") password("logwriter") 
> port("5432")
>   database("syslog")
>   table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}") #or whatever you 
> want, example ${HOST}" for hosts, ${LEVEL}" for levels.. etc
>   columns("datetime varchar(16)", "host varchar(32)", "program 
> varchar(16)", "pid varchar(8) default null", "message varchar")
>   values("$R_DATE", "$HOST", "$PROGRAM", "${PID:-NULL}", "$MSG")
>   indexes("datetime", "host", "program", "pid", "message"));
> };
>
> and the following log lines:
>
> Incoming log entry; line='<166>Feb 22 08:38:59 Vpxa: [2011-02-22 
> 08:38:59.862 1B7A7B90 verbose \'App\'] [VpxaHalServices] HostChanged 
> Event Fired, properties changed []'
> 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 []\')'
>
>
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.campin.net/syslog-ng/faq.html
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.balabit.hu/pipermail/syslog-ng/attachments/20110222/a06f1dab/attachment.htm