[syslog-ng] Colon embedded in messages (:)
Evan Rempel
erempel at uvic.ca
Tue Sep 29 23:19:46 CEST 2009
As far as I know, the messages will always be split on multiple lines by postgresql,
and you will need to match on the process number.
In your example, the 10440 is the process number, and the -1, -2, -3 are the line numers of
the messages that the single process creates. A single connection that runs multiple queries can
create many messages, with ever increasing line number counts.
Evan.
James Kelly wrote:
> Thanks a lot. That seems to have worked a lot better but has introduced
> another problem. The messages are split up into too many lines and,
> when there is a match, the first of N lines are removed, still creating
> unwanted messages.
>
> For example, a pretty small insert spans 3 lines... There was a match on
> the 2nd line, so it is not printed. However, the 1st and 3rd were.
>
> 2009-09-29T16:21:16-04:00 blahh-rep2 postgres [10440-1]
> postgres-blahh-10.100.10.34(15779)-4690-2009-09-29 16:21:16 EDT-LOG:
> statement: insert into
> 2009-09-29T16:21:16-04:00 blahh-rep2 postgres [10440-3]
> '1000000000000000');
>
> I played around with log_msg_size thinking I could control how much is
> on each line, but it didn't help. Am I missing something obvious? If a
> "fragment" of the message is matched, I would want the entire message to
> not be logged.
>
> Honestly, I have not spent nearly as much time on this most recent
> problem, so if it is an RTFM situation, just let me know. I did do some
> reading and searching on this though and came up empty handed.
>
> Thanks again for this great help,
> James
>
> On Tue, Sep 29, 2009 at 3:43 PM, Evan Rempel <erempel at uvic.ca
> <mailto:erempel at uvic.ca>> wrote:
>
> 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>
> > <mailto: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>
> > <mailto: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> <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
> <mailto:erempel at uvic.ca>
> Senior Programmer Analyst 250.721.7691
> Unix Services, University Systems, University of Victoria
> ______________________________________________________________________________
> 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
More information about the syslog-ng
mailing list