<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="Osso Notes">
<title></title></head>
<body>
<p>started to do this port but got again distracted.
<br>
<br>hopefully it can go into 3.3 first and then backport it to 3.2.
<br>
<br>----- Original message -----
<br>> On Fri, Mar 11, 2011 at 3:27 PM, Zoltán Pallagi <<a href="mailto:pzolee@balabit.hu">pzolee@balabit.hu</a>>
<br>> wrote:
<br>>
<br>> > Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi
<br>> > should port the fix back to OSE.
<br>> >
<br>>
<br>> 3.2.2+ or will that go into 3.3alpha?
<br>>
<br>>
<br>> >
<br>> >
<br>> >
<br>> > On 2011-03-11 13:35, Hendrik Visage wrote:
<br>> >
<br>> > Okay, now that I have the "" -> Null replacements, now I have another
<br>> > escape issue.
<br>> >
<br>> > Making use of Postgresql's bigserial, it reads:
<br>> > <qoute
<br>> > <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>> > >
<br>> > To insert the next value of the sequence into the serial column,
<br>> > specify that the serial column should be assigned its default value.
<br>> > This can be done either by excluding the column from the list of
<br>> > columns in the INSERTstatement, or through the use of the DEFAULT key
<br>> > word. </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")
<br>> > port("5432")
<br>> > database("syslog") table("SystemEvents")
<br>> > columns("ID bigserial primary key","ReceivedAt timestamptz NULL",
<br>> > "DeviceReportedTime timestamptz NULL",
<br>> > "Facility smallint NULL","Priority smallint NULL","FromHost varchar(60)
<br>> > NULL",
<br>> > "Message text","InfoUnitID int NULL","SysLogTag varchar(60)",
<br>> > "CustomerID bigint","NTSeverity int NULL","Importance int
<br>> > NULL","EventSource varchar(60)","EventUser varchar(60) NULL",
<br>> > "EventCategory int NULL","EventID int NULL","EventBinaryData text
<br>> > NULL","MaxAvailable int NULL","CurrUsage int NULL","MinUsage int NULL",
<br>> > "MaxUsage int NULL","EventLogType varchar(60)","GenericFileName
<br>> > VarChar(60)","SystemID int NULL")
<br>> > values(DEFAULT,"$R_ISODATE",
<br>> > "$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,
<br>> > DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID,
<br>> > SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser,
<br>> > EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage,
<br>> > MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES
<br>> > (\'DEFAULT\', \'2011-03-11T14:14:12+02:00\',
<br>> > \'2011-03-11T11:12:37+02:00\', \'20\', \'6\', \'betvmi01\',
<br>> > \'[2011-03-11 11:12:37.565 11AA6B90 verbose \'\'App\'\'] [VpxaVMAP]
<br>> > CheckThreshold percent changes (0,0,0,0) - threshold 5\', \'1\',
<br>> > \'Vpxa: \', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
<br>> > NULL, NULL, NULL, NULL, NULL, NULL)' Error running SQL query;
<br>> > type='pgsql', host='127.0.0.1', port='5432', user='logwriter',
<br>> > database='syslog', error='*ERROR:  invalid input syntax for integer:
<br>> > "DEFAULT"*\x0aLINE 1: ... EventLogType, GenericFileName, SystemID)
<br>> > VALUES (\'DEFAULT\',...\x0a
<br>> > ^\x0a', query='INSERT INTO SystemEvents (ID, ReceivedAt,
<br>> > DeviceReportedTime, Facility, Priority, FromHost, Message, InfoUnitID,
<br>> > SysLogTag, CustomerID, NTSeverity, Importance, EventSource, EventUser,
<br>> > EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage,
<br>> > MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES
<br>> > (\'DEFAULT\', \'2011-03-11T14:14:12+02:00\',
<br>> > \'2011-03-11T11:12:37+02:00\', \'20\', \'6\', \'betvmi01\',
<br>> > \'[2011-03-11 11:12:37.565 11AA6B90 verbose \'\'App\'\'] [VpxaVMAP]
<br>> > CheckThreshold percent changes (0,0,0,0) - threshold 5\', \'1\',
<br>> > \'Vpxa: \', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
<br>> > 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,
<br>> > Facility, Priority, FromHost, Message, InfoUnitID, SysLogTag,
<br>> > CustomerID, NTSeverity, Importance, EventSource, EventUser,
<br>> > EventCategory, EventID, EventBinaryData, MaxAvailable, CurrUsage,
<br>> > MinUsage, MaxUsage, EventLogType, GenericFileName, SystemID) VALUES
<br>> > (DEFAULT, '2011-03-11T14:10:18+02:00', '2011-03-11T11:08:43+02:00',
<br>> > '20', '6', 'betvmi01', '[2011-03-11 11:08:43.522 11921B90 verbose
<br>> > ''App''] [VpxaVMAP::Invoke] Command returned successfully', '1',
<br>> > 'Vpxa: ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
<br>> > NULL, NULL, NULL, NULL, NULL); INSERT 0 1
<br>> > syslog=#
<br>> >
<br>> > On Tue, Feb 22, 2011 at 12:59 PM, Zoltán Pallagi
<br>> > <<a href="mailto:pzolee@balabit.hu">pzolee@balabit.hu</a>>wrote:
<br>> >
<br>> > > Try this:
<br>> > > columns("datetime varchar(16)", "host varchar(32)", "program
<br>> > > varchar(16)", "pid varchar(4) default null", "message varchar")
<br>> > > values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
<br>> > >
<br>> > > indexes("datetime", "host", "program", "pid", "message")
<br>> > > null(""));
<br>> > >
<br>> > > null(): If the content of a column matches the string specified in
<br>> > > the * null()* parameter, the contents of the column will be replaced
<br>> > > 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
<br>> > > Event Fired, properties changed []'
<br>> > > Running SQL query; query='INSERT INTO t2105010221cdb33661c82e91cb0b
<br>> > > (datetime, host, program, pid, message) VALUES (\'Feb 22 11:50:17\',
<br>> > > \'thor-t410\', \'Vpxa\', NULL, \'HostChanged Event Fired, properties
<br>> > > changed []\')'
<br>> > >
<br>> > > Ofc, you can use integer type for pid, but this is a string and not
<br>> > > an integer.
<br>> > >
<br>> > >
<br>> > > On 2011-02-22 10:57, Hendrik Visage wrote:
<br>> > >
<br>> > > Hi there,
<br>> > >
<br>> > > Okay, looks like I was looking/thinking about something else, so lets
<br>> > > explain my problem.
<br>> > >
<br>> > > I want the pid inseted to be a "proper" number/integer, not a
<br>> > > string/character, and the fun starts with several of my sources not
<br>> > > having a pid entry, thus that field I would like to be filled with
<br>> > > NULL values. However, it still gets escaped, and when it gets
<br>> > > 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
<br>> > > that that column is a integer column, thus should not be escaped as
<br>> > > 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")
<br>> > > port("5432")
<br>> > > database("syslog")
<br>> > > table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}") #or whatever you
<br>> > > want, example ${HOST}" for hosts, ${LEVEL}" for levels.. etc
<br>> > > columns("datetime varchar(16)", "host varchar(32)", "program
<br>> > > 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
<br>> > > 08:38:59.862 1B7A7B90 verbose \'App\'] [VpxaHalServices] HostChanged
<br>> > > Event Fired, properties changed []'
<br>> > > Running SQL query; query='INSERT INTO logs_somedevice_20110222
<br>> > > (datetime, host, program, pid, message) VALUES (\'Feb 22 11:39:27\',
<br>> > > \'somedevice\', \'Vpxa\', \'NULL\', \'[2011-02-22 08:38:59.862
<br>> > > 1B7A7B90 verbose \'\'App\'\'] [VpxaHalServices] HostChanged Event
<br>> > > Fired, properties changed []\')'
<br>> > >
<br>> > >
<br>> > > ______________________________________________________________________________
<br>> > > Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a>
<br>> > >
<br>> > > Documentation:
<br>> > > <a href="http://www.balabit.com/support/documentation/?product=syslog-ng">http://www.balabit.com/support/documentation/?product=syslog-ng</a> FAQ:
<br>> > > <a href="http://www.campin.net/syslog-ng/faq.html">http://www.campin.net/syslog-ng/faq.html</a>
<br>> > >
<br>> > >
<br>> > >
<br>> >
<br>> >
<br><br></p>
</body>
</html>