Okay, now that I have the "" -> Null replacements, now I have another escape issue.<br><br>Making use of Postgresql's bigserial, it reads:<br><qoute <a 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>><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></quote><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("127.0.0.1") username("logwriter") password("logwriter") port("5432")<br> 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: invalid input syntax for integer: "DEFAULT"</b>\x0aLINE 1: ... EventLogType, GenericFileName, SystemID) VALUES (\'DEFAULT\',...\x0a ^\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án Pallagi <span dir="ltr"><<a href="mailto:pzolee@balabit.hu">pzolee@balabit.hu</a>></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("datetime varchar(16)", "host varchar(32)", "program
varchar(16)", "pid varchar(4) default null", "message varchar")<br>
values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")<div class="im"><br>
indexes("datetime", "host", "program", "pid", "message")<br></div>
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='<166>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></div><div class="h5"><br>
<br>
On 2011-02-22 10:57, Hendrik Visage wrote:
</div></div><blockquote type="cite"><div><div></div><div class="h5">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 "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>
sql(type(pgsql)<br>
host("127.0.0.1") username("logwriter") password("logwriter")
port("5432")<br>
database("syslog")<br>
table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}") #or whatever
you want, example ${HOST}" for hosts, ${LEVEL}" for levels.. etc<br>
columns("datetime varchar(16)", "host varchar(32)", "program
varchar(16)", "pid varchar(8) default null", "message varchar")<br>
values("$R_DATE", "$HOST", "$PROGRAM", "${PID:-NULL}", "$MSG")<br>
indexes("datetime", "host", "program", "pid", "message"));<br>
};<br>
<br>
and the following log lines:<br>
<br>
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 []'<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 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 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>