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.<br>
<br>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.<br><br>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<br>
2009-09-29T16:21:16-04:00 blahh-rep2 postgres [10440-3] '1000000000000000');<br><br>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. <br>
<br>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.<br><br>Thanks again for this great help,<br>
James<br><br><div class="gmail_quote">On Tue, Sep 29, 2009 at 3:43 PM, Evan Rempel <span dir="ltr"><<a href="mailto:erempel@uvic.ca">erempel@uvic.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
PostgeSQL can log to syslog using appropriately formated messages.<br>
Configure your postgresql.conf file to include the line<br>
<br>
log_destination = 'syslog'<br>
<br>
and then you will get lines in syslog like;<br>
<br>
2009-09-29T12:42:10-07:00 hostname facility.level postgres[20288]: [32-1] postgresql statement<br>
<br>
then all of the syslog-ng parsing, macros and templates will work correctly.<br>
<br>
Evan.<br>
<div class="im"><br>
James Kelly wrote:<br>
> Thanks. This is exactly what is happening. If, using a template, I<br>
> just use $PROGRAM, I get a bunch of lines with just "insert".<br>
><br>
> If I use something like the following:<br>
><br>
> template t_postgres_msgs {<br>
> template("$ISODATE $HOST $PROGRAM $MSG \n"); };<br>
><br>
> I no longer see the colons. However, if something is matched by the<br>
> filter, it still prints a blank message (hence, wasting a ton of space<br>
> and clogging up the logs). For example, this is what I get instead of<br>
> the matched sql statement:<br>
><br>
> 2009-09-29T10:30:30-04:00 hcdb1-rep2<br>
><br>
> I thought the filter would just "trash" or not record the message at<br>
> all, not print the host and timestamp for each filtered message.<br>
><br>
> Thanks a lot... I appreciate this help a lot.<br>
> James<br>
><br>
> On Tue, Sep 29, 2009 at 10:05 AM, Srinivasan Sreenivasan<br>
> <<a href="mailto:srinivasan.srinivasan@sabre.com">srinivasan.srinivasan@sabre.com</a><br>
</div><div class="im">> <mailto:<a href="mailto:srinivasan.srinivasan@sabre.com">srinivasan.srinivasan@sabre.com</a>>> wrote:<br>
><br>
> I think Syslog-ng thinks insert is a program name. Use a template<br>
> with $PROGRAM in it to see if it prints “insert” to confirm this.<br>
><br>
> Solution:<br>
> Send a program name before your sql statement using a template.<br>
><br>
><br>
><br>
> On 9/29/09 8:57 AM, "James Kelly" <<a href="mailto:james.kelly@hmsinc.com">james.kelly@hmsinc.com</a><br>
</div><div><div></div><div class="h5">> <mailto:<a href="mailto:james.kelly@hmsinc.com">james.kelly@hmsinc.com</a>>> wrote:<br>
><br>
> Hello,<br>
><br>
> I am attempting to implement syslog-ng on our servers hosting<br>
> postgresql databases. The general idea is to log "too much" at<br>
> the database level and then, using syslog filters, reduce it to<br>
> the information we want to actually hold in the syslog and send<br>
> to the log server. So far I am extremely pleased with how easy<br>
> it is to implement and well documented. However, I do have one<br>
> problem that is making it difficult to use.<br>
><br>
> The problem is for each message that the filter matches, it does<br>
> not completely drop the message.. rather, it logs the date /<br>
> server / and a colon:<br>
><br>
> For example, this is what I get for a message that is matched by<br>
> a filter:<br>
><br>
> "Sep 29 09:43:29 hcdb1-rep2 :"<br>
><br>
> I notice that even with the unmatched statements, there is a<br>
> colon. For example, in the postgresql log, I see:<br>
><br>
> insert into "public"."table"(blah,blah,blah) values<br>
> (blah,blah,blah);<br>
><br>
> but the same message once captured from syslog shows the<br>
> following in the log and logserver:<br>
><br>
> Sep 29 09:43:29 hcdb1-rep2 insert: into<br>
> "public"."table"(blah,blah,blah) values (blah,blah,blah);<br>
><br>
> *Note the colon after "insert".* I can't seem to figure out<br>
> where this is coming from or how to avoid it. It also causes<br>
> some filtering problems that I won't go into here so as to not<br>
> confuse the issues, but safe to say it is also related to the colon.<br>
><br>
> I have spent a lot of time trying to figure this out and am at a<br>
> dead-end. Due to the amount of messages I need to filter out<br>
> (below is just one of the many filters I need to put in and have<br>
> tested with the same result), not being able to completely<br>
> filter these out is a killer.<br>
><br>
> I am using the 3.0.4 open-source edition on Ubuntu 8. Here is<br>
> my config:<br>
><br>
> ******************************************************************************************<br>
> @version: 3.0<br>
><br>
> options {<br>
> };<br>
><br>
> ######<br>
> # sources<br>
> source s_local {<br>
> # message generated by Syslog-NG<br>
> internal();<br>
> # standard Linux log source (this is the default place for the<br>
> syslog()<br>
> # function to send logs to)<br>
> unix-stream("/dev/log");<br>
> # messages from the kernel<br>
> file("/proc/kmsg" program_override("kernel: "));<br>
> file("/var/log/postgresql/postgresql-8.3-main.log");<br>
> };<br>
><br>
><br>
> ######<br>
> # destinations<br>
> destination d_messages { file("/var/log/messages"); };<br>
><br>
> #####<br>
> # filters<br>
><br>
> filter f_inserts2 {<br>
> not match("_health_central" value("MESSAGE"));<br>
> };<br>
><br>
><br>
> destination d_logserver { tcp("<a href="http://internal.host.com" target="_blank">internal.host.com</a><br>
</div></div>> <<a href="http://internal.host.com" target="_blank">http://internal.host.com</a>> <<a href="http://internal.host.com" target="_blank">http://internal.host.com</a>> "); };<br>
<div class="im">><br>
><br>
><br>
> log {<br>
> source(s_local);<br>
> filter(f_inserts2);<br>
> destination(d_messages);<br>
> #destination(d_logserver);<br>
> };<br>
> ******************************************************************************************<br>
><br>
> Thanks!<br>
> James Kelly<br>
><br>
> ------------------------------------------------------------------------<br>
> ______________________________________________________________________________<br>
> Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><br>
> Documentation:<br>
> <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a><br>
> FAQ: <a href="http://www.campin.net/syslog-ng/faq.html" target="_blank">http://www.campin.net/syslog-ng/faq.html</a><br>
><br>
><br>
> ______________________________________________________________________________<br>
> Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><br>
> Documentation:<br>
> <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a><br>
> FAQ: <a href="http://www.campin.net/syslog-ng/faq.html" target="_blank">http://www.campin.net/syslog-ng/faq.html</a><br>
><br>
><br>
><br>
<br>
<br>
</div><font color="#888888">--<br>
Evan Rempel <a href="mailto:erempel@uvic.ca">erempel@uvic.ca</a><br>
Senior Programmer Analyst 250.721.7691<br>
Unix Services, University Systems, University of Victoria<br>
</font><div><div></div><div class="h5">______________________________________________________________________________<br>
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><br>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" target="_blank">http://www.balabit.com/support/documentation/?product=syslog-ng</a><br>
FAQ: <a href="http://www.campin.net/syslog-ng/faq.html" target="_blank">http://www.campin.net/syslog-ng/faq.html</a><br>
<br>
</div></div></blockquote></div><br>