<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#0050d0">
The idea that was mentioned is not without its flaws.<br>
Firstly say you receive a message on the 1st, then you receive the
same message on the 10th, you would lose all record that the message
was received on the 1st as date on it would be modified to the most
recent occurance of the message. The ideal example would be to only
merge multiple occurrences of the message when they appear
sequentially with no other messages between them. <br>
Secondly using a 32-bit checksum of the message text to determine
uniqueness is risky. It would be farily easy to end up with 2
different messages that have the same checksum. A md5 checksum would
be much better, but I dont believe syslog-ng has a function to
compute md5 sums.<br>
Lastly the table key needs to be modified so the host name is part
of it. As it is with the way martin mentioned, if 2 hosts get the
same message, the latter one will update the timestamp of the first
host's message making it look like the second host never had such a
message at all, and that the first host got it twice.<br>
<br>
The idea holds merit, and if these issues are fine with you, then it
should work. But syslog-ng already merges sequential duplicate
messages when logging to files, so it'd probably be fairly trivial
to port this functionality over to the sql driver. I'd make a
feature request on the bug tracker.<br>
<br>
-Patrick<br>
<br>
Sent: Fri Aug 19 2011 02:11:45 GMT-0600 (MST)<br>
From: Marcos Tang <a class="moz-txt-link-rfc2396E" href="mailto:marcostang2002@yahoo.com"><marcostang2002@yahoo.com></a><br>
To: Syslog-ng users' and developers' mailing list
<a class="moz-txt-link-rfc2396E" href="mailto:syslog-ng@lists.balabit.hu"><syslog-ng@lists.balabit.hu></a>, Martin Holste
<a class="moz-txt-link-rfc2396E" href="mailto:mcholste@gmail.com"><mcholste@gmail.com></a> <br>
Subject: Re: [syslog-ng] Store syslog occurrence frequency instead
of adding all of them to the DB
<blockquote
cite="mid:1313741505.66935.YahooMailClassic@web112111.mail.gq1.yahoo.com"
type="cite">
<table border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="font: inherit;" valign="top">
<div>Hi Martin & Florian,</div>
<div> </div>
<div>I am not good at SQL and I have the following table
right now. Do you mean my current table also needs to
merge with your table to form one single table? </div>
<div> </div>
<div>What is the corresponding syslog-ng.conf file which I
should modify to fit for your table?</div>
<div> </div>
<div>Once again, thanks for your suggestions for me.</div>
<div> </div>
<div>mysql> desc logs;<br>
+----------+------------------+------+-----+---------+----------------+<br>
| Field | Type | Null | Key | Default |
Extra |<br>
+----------+------------------+------+-----+---------+----------------+<br>
| host | varchar(32) | YES | MUL | NULL
| |<br>
| facility | varchar(10) | YES | MUL | NULL
| |<br>
| priority | varchar(10) | YES | MUL | NULL
| |<br>
| level | varchar(10) | YES | | NULL
| |<br>
| tag | varchar(10) | YES | | NULL
| |<br>
| date | date | YES | MUL | NULL
| |<br>
| time | time | YES | MUL | NULL
| |<br>
| program | varchar(15) | YES | MUL | NULL
| |<br>
| msg | text | YES | | NULL
| |<br>
| seq | int(10) unsigned | NO | PRI | NULL |
auto_increment |<br>
+----------+------------------+------+-----+---------+----------------+<br>
10 rows in set (0.00 sec)<br>
<br>
<br>
Regards,<br>
Marcos<br>
<br>
--- On <b>Fri, 8/19/11, Martin Holste <i><a class="moz-txt-link-rfc2396E" href="mailto:mcholste@gmail.com"><mcholste@gmail.com></a></i></b>
wrote:<br>
</div>
<blockquote style="padding-left: 5px; margin-left: 5px;
border-left: 2px solid rgb(16, 16, 255);"><br>
From: Martin Holste <a class="moz-txt-link-rfc2396E" href="mailto:mcholste@gmail.com"><mcholste@gmail.com></a><br>
Subject: Re: [syslog-ng] Store syslog occurrence
frequency instead of adding all of them to the DB<br>
To: "Syslog-ng users' and developers' mailing list"
<a class="moz-txt-link-rfc2396E" href="mailto:syslog-ng@lists.balabit.hu"><syslog-ng@lists.balabit.hu></a><br>
Cc: "Marcos Tang" <a class="moz-txt-link-rfc2396E" href="mailto:marcostang2002@yahoo.com"><marcostang2002@yahoo.com></a><br>
Date: Friday, August 19, 2011, 4:10 AM<br>
<br>
<div class="plainMail">I'd add on to this by using a crc
function to hash the message and<br>
store that in a column to make the unique check very
fast:<br>
CREATE TABLE mylogs (<br>
id BIGINT UNSIGNED NOT NULL PRIMARY KEY
AUTO_INCREMENT,<br>
timestamp TIMESTAMP NOT NULL,<br>
crc INT UNSIGNED NOT NULL,<br>
count INT UNSIGNED NOT NULL DEFAULT 0,<br>
msg TEXT,<br>
UNIQUE KEY (crc)<br>
);<br>
INSERT INTO mylogs (timestamp, crc, msg)
values($timestamp, CRC32($msg),<br>
$msg) ON DUPLICATE KEY UPDATE count=count+1,
timestamp=$timestamp;<br>
<br>
On Thu, Aug 18, 2011 at 1:33 PM, <a
moz-do-not-send="true"
href="http://us.mc1121.mail.yahoo.com/mc/compose?to=system@ra-schaal.de"
ymailto="mailto:system@ra-schaal.de">system@ra-schaal.de</a><br>
<<a moz-do-not-send="true"
href="http://us.mc1121.mail.yahoo.com/mc/compose?to=system@ra-schaal.de"
ymailto="mailto:system@ra-schaal.de">system@ra-schaal.de</a>>
wrote:<br>
> Am 18.08.2011 19:52, schrieb Marcos Tang:<br>
><br>
>> Can those records being "processed" some how
and when I search the MySQL<br>
>> DB, I only see ONE record list the following
only?<br>
>><br>
>> Total occurrenceMessage content<br>
>> =====================<br>
>> 14,400File system is full<br>
>><br>
><br>
> can´t you use something like<br>
><br>
> INSERT INTO tables VALUES (date,logstring) ON
duplicate KEY UPDATE date;<br>
><br>
> ?<br>
><br>
> florian<br>
>
______________________________________________________________________________<br>
> Member info: <a moz-do-not-send="true"
href="https://lists.balabit.hu/mailman/listinfo/syslog-ng"
target="_blank">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><br>
> Documentation: <a moz-do-not-send="true"
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 moz-do-not-send="true"
href="http://www.balabit.com/wiki/syslog-ng-faq"
target="_blank">http://www.balabit.com/wiki/syslog-ng-faq</a><br>
><br>
><br>
</div>
</blockquote>
</td>
</tr>
</tbody>
</table>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
______________________________________________________________________________
Member info: <a class="moz-txt-link-freetext" href="https://lists.balabit.hu/mailman/listinfo/syslog-ng">https://lists.balabit.hu/mailman/listinfo/syslog-ng</a>
Documentation: <a class="moz-txt-link-freetext" href="http://www.balabit.com/support/documentation/?product=syslog-ng">http://www.balabit.com/support/documentation/?product=syslog-ng</a>
FAQ: <a class="moz-txt-link-freetext" href="http://www.balabit.com/wiki/syslog-ng-faq">http://www.balabit.com/wiki/syslog-ng-faq</a>
</pre>
</blockquote>
</body>
</html>