[syslog-ng] Colon embedded in messages (:)

[Evan Rempel]

PostgeSQL can log to syslog using appropriately formated messages.
Configure your postgresql.conf file to include the line

log_destination = 'syslog'

and then you will get lines in syslog like;

2009-09-29T12:42:10-07:00 hostname facility.level postgres[20288]: [32-1] postgresql statement

then all of the syslog-ng parsing, macros and templates will work correctly.

Evan.

James Kelly wrote:
> Thanks.  This is exactly what is happening.  If, using a template, I 
> just use $PROGRAM, I get a bunch of lines with just "insert". 
> 
> If I use something like the following:
> 
> template t_postgres_msgs {
>              template("$ISODATE $HOST $PROGRAM $MSG \n"); };
> 
> I no longer see the colons.  However, if something is matched by the 
> filter, it still prints a blank message (hence, wasting a ton of space 
> and clogging up the logs).  For example, this is what I get instead of 
> the matched sql statement:
> 
> 2009-09-29T10:30:30-04:00 hcdb1-rep2
> 
> I thought the filter would just "trash" or not record the message at 
> all, not print the host and timestamp for each filtered message.
> 
> Thanks a lot...  I appreciate this help a lot.
> James
> 
> On Tue, Sep 29, 2009 at 10:05 AM, Srinivasan Sreenivasan 
> <srinivasan.srinivasan at sabre.com 
> <mailto:srinivasan.srinivasan at sabre.com>> wrote:
> 
>     I think Syslog-ng thinks insert is a program name. Use a template
>     with $PROGRAM in it to see if it prints “insert” to confirm this.
> 
>     Solution:
>     Send a program name before your sql statement using a template.
> 
> 
> 
>     On 9/29/09 8:57 AM, "James Kelly" <james.kelly at hmsinc.com
>     <mailto:james.kelly at hmsinc.com>> wrote:
> 
>         Hello,
> 
>         I am attempting to implement syslog-ng on our servers hosting
>         postgresql databases.  The general idea is to log "too much" at
>         the database level and then, using syslog filters, reduce it to
>         the information we want to actually hold in the syslog and send
>         to the log server.  So far I am extremely pleased with how easy
>         it is to implement and well documented.  However, I do have one
>         problem that is making it difficult to use.
> 
>         The problem is for each message that the filter matches, it does
>         not completely drop the message.. rather, it logs the date /
>         server / and a colon:
> 
>         For example, this is what I get for a message that is matched by
>         a filter:
> 
>         "Sep 29 09:43:29 hcdb1-rep2      :"
> 
>         I notice that even with the unmatched statements, there is a
>         colon.  For example, in the postgresql log, I see:
> 
>         insert into "public"."table"(blah,blah,blah) values
>         (blah,blah,blah);
> 
>         but the same message once captured from syslog shows the
>         following in the log and logserver:
> 
>         Sep 29 09:43:29 hcdb1-rep2      insert: into
>         "public"."table"(blah,blah,blah) values (blah,blah,blah);
> 
>         *Note the colon after "insert".*  I can't seem to figure out
>         where this is coming from or how to avoid it.  It also causes
>         some filtering problems that I won't go into here so as to not
>         confuse the issues, but safe to say it is also related to the colon.
> 
>         I have spent a lot of time trying to figure this out and am at a
>         dead-end.  Due to the amount of messages I need to filter out
>         (below is just one of the many filters I need to put in and have
>         tested with the same result), not being able to completely
>         filter these out is a killer.
> 
>         I am using the 3.0.4 open-source edition on Ubuntu 8.  Here is
>         my config:
> 
>         ******************************************************************************************
>         @version: 3.0
> 
>         options {
>         };
> 
>         ######
>         # sources
>         source s_local {
>         # message generated by Syslog-NG
>         internal();
>         # standard Linux log source (this is the default place for the
>         syslog()
>         # function to send logs to)
>         unix-stream("/dev/log");
>         # messages from the kernel
>         file("/proc/kmsg" program_override("kernel: "));
>         file("/var/log/postgresql/postgresql-8.3-main.log");
>         };
> 
> 
>         ######
>         # destinations
>         destination d_messages { file("/var/log/messages"); };
> 
>         #####
>         # filters
> 
>         filter f_inserts2 {
>                 not match("_health_central" value("MESSAGE"));
>         };
> 
> 
>         destination d_logserver { tcp("internal.host.com
>         <http://internal.host.com> <http://internal.host.com> "); };
> 
> 
> 
>         log {
>         source(s_local);
>         filter(f_inserts2);
>         destination(d_messages);
>         #destination(d_logserver);
>         };
>         ******************************************************************************************
> 
>         Thanks!
>         James Kelly
> 
>         ------------------------------------------------------------------------
>         ______________________________________________________________________________
>         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
> 
> 
> 


-- 
Evan Rempel                               erempel at uvic.ca
Senior Programmer Analyst                    250.721.7691
Unix Services, University Systems, University of Victoria