Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi should port the fix back to OSE.
On 2011-03-11 13:35, Hendrik Visage wrote:Okay, now that I have the "" -> Null replacements, now I have another escape issue.
Making use of Postgresql's bigserial, it reads:
<qoute http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL>
To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word.
</quote>
Which means I have to have *no* quotes, which the syslog-ng enforces :(
destination d_pgsql {
sql(type(pgsql)
host("127.0.0.1") username("logwriter") password("logwriter") port("5432")
database("syslog") table("SystemEvents")
columns("ID bigserial primary key","ReceivedAt timestamptz NULL", "DeviceReportedTime timestamptz NULL",
"Facility smallint NULL","Priority smallint NULL","FromHost varchar(60) NULL",
"Message text","InfoUnitID int NULL","SysLogTag varchar(60)",
"CustomerID bigint","NTSeverity int NULL","Importance int NULL","EventSource varchar(60)","EventUser varchar(60) NULL",
"EventCategory int NULL","EventID int NULL","EventBinaryData text NULL","MaxAvailable int NULL","CurrUsage int NULL","MinUsage int NULL",
"MaxUsage int NULL","EventLogType varchar(60)","GenericFileName VarChar(60)","SystemID int NULL")
values(DEFAULT,"$R_ISODATE", "$S_ISODATE","$FACILITY_NUM","$LEVEL_NUM","$HOST",
"$MSGONLY","1","$MSGHDR","","","","","","","","","","","","","","","")
indexes("ID","ReceivedAt","Facility","Priority","FromHost","SysLogTag",)
null(""));
};
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)'
Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='logwriter', database='syslog', error='ERROR: invalid input syntax for integer: "DEFAULT"\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)'
AS long as there is now escapes for the DEFAULT, then it "works":
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);
INSERT 0 1
syslog=#
On Tue, Feb 22, 2011 at 12:59 PM, Zoltán Pallagi <pzolee@balabit.hu> wrote:
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")null(""));
indexes("datetime", "host", "program", "pid", "message")
null(): If the content of a column matches the string specified in thenull()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:______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ngHi 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 []\')'