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