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