escapes in SQL statements (was Re: dbtype in SQL logging)
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 []\')'
My recommendation is that you treat the PID as a string, because that is what it really is. You are never going to do arithmetic on a PID. Is there some circumstance preventing you from making the PID field a string in the database? On Tuesday 22 February 2011 11:57:41 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 []\')' -- Alan McKinnon Systems Engineer^W Technician Infrastructure Services Internet Solutions
+27 11 575 7585 Please note: This email and its content are subject to the disclaimer as displayed at the following link http://www.is.co.za/legal/E-mail+Confidentiality+Notice+and+Disclaimer.htm. Should you not have Web access, send a mail to disclaimers@is.co.za and a copy will be emailed to you.
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
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#DATATYP...
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 INSERTstatement, 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")
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
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 <mailto: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")
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
On Fri, Mar 11, 2011 at 3:27 PM, Zoltán Pallagi <pzolee@balabit.hu> wrote:
Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi should port the fix back to OSE.
3.2.2+ or will that go into 3.3alpha?
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#DATATYP...
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 INSERTstatement, 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")
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
started to do this port but got again distracted. hopefully it can go into 3.3 first and then backport it to 3.2. ----- Original message -----
On Fri, Mar 11, 2011 at 3:27 PM, Zoltán Pallagi <pzolee@balabit.hu> wrote:
Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi should port the fix back to OSE.
3.2.2+ or will that go into 3.3alpha?
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#DATATYP...
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 INSERTstatement, 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")
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
On Fri, 2011-03-11 at 14:27 +0100, Zoltán Pallagi wrote:
Hm... it's a bug in syslog-ng. We already found it in PE before, Bazsi should port the fix back to OSE.
I bit the bullet and tried to extract all SQL related fixes from the PE source tree. It is right now on top of syslog-ng-3.3.git, probably backport is not impossible, but I didn't do that, as I'm going to sleep now. Also, I find it important to mention, that the PE team of syslog-ng has started to migrate the PE product to the OSE core, as outlined some time ago, and their current state is published on git.balabit.hu: http://git.balabit.hu/?p=syslog-ng-team/syslog-ng-core-4.1.git;a=summary The complete PE product has not yet been migrated, this is work-in-progress, but you can watch that git tree. They tend to rebase it from time-to-time, so don't fork your personal repository from there, or be prepared to rebase when they do. I've ported this patch: http://git.balabit.hu/?p=syslog-ng-team/syslog-ng-core-4.1.git;a=commit;h=32... And these are the results (in the OSE repo): 377745f93575bb6e2a2b807599de8a503e10ec4d afsql: don't let the user configure the sql() destination without columns/values/indexes optio c868c74696bbe4de03bfe8a7fa28d74a2fbd388f afsql: use an md5 hashed name for the index name for Oracle e87333100f35bd3b982c5398efe8aaeebd8f47b1 afsql: use database specific BEGIN TRANSACTION queries 4aaf55bbbb3413f6680f1c00bb6d5c3f7c9727a1 afsql: add support for SQL-schema defined default values in columns b05d39c4af90dd9fc8b16827e0dc6248779710dd afsql: make the number of attempts to insert a message runtime configurable Please let me know if had any success. Thanks.
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")
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
______________________________________________________________________________ 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
-- Bazsi
On Fri, 11 Mar 2011 14:35:34 +0200 Hendrik Visage <hvjunk@gmail.com> wrote:
Which means I have to have *no* quotes, which the syslog-ng enforces :(
Why not just exclude it from the list of columns in your syslog-ng.conf since it'll be updated automatically in the table? John
participants (5)
-
Alan McKinnon
-
Balazs Scheidler
-
Hendrik Visage
-
John Kristoff
-
Zoltán Pallagi