[syslog-ng] Store syslog occurrence frequency instead of adding all of them to the DB

[Marcos Tang]

Hi Martin & Florian,
 
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? 
 
What is the corresponding syslog-ng.conf file which I should modify to fit for your table?
 
Once again, thanks for your suggestions for me.
 
mysql> desc logs;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| host     | varchar(32)      | YES  | MUL | NULL    |                |
| facility | varchar(10)      | YES  | MUL | NULL    |                |
| priority | varchar(10)      | YES  | MUL | NULL    |                |
| level    | varchar(10)      | YES  |     | NULL    |                |
| tag      | varchar(10)      | YES  |     | NULL    |                |
| date     | date             | YES  | MUL | NULL    |                |
| time     | time             | YES  | MUL | NULL    |                |
| program  | varchar(15)      | YES  | MUL | NULL    |                |
| msg      | text             | YES  |     | NULL    |                |
| seq      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+----------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)


Regards,
Marcos

--- On Fri, 8/19/11, Martin Holste <mcholste at gmail.com> wrote:


From: Martin Holste <mcholste at gmail.com>
Subject: Re: [syslog-ng] Store syslog occurrence frequency instead of adding all of them to the DB
To: "Syslog-ng users' and developers' mailing list" <syslog-ng at lists.balabit.hu>
Cc: "Marcos Tang" <marcostang2002 at yahoo.com>
Date: Friday, August 19, 2011, 4:10 AM


I'd add on to this by using a crc function to hash the message and
store that in a column to make the unique check very fast:
CREATE TABLE mylogs (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  timestamp TIMESTAMP NOT NULL,
  crc INT UNSIGNED NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 0,
  msg TEXT,
  UNIQUE KEY (crc)
);
INSERT INTO mylogs (timestamp, crc, msg) values($timestamp, CRC32($msg),
$msg) ON DUPLICATE KEY UPDATE count=count+1, timestamp=$timestamp;

On Thu, Aug 18, 2011 at 1:33 PM, system at ra-schaal.de
<system at ra-schaal.de> wrote:
> Am 18.08.2011 19:52, schrieb Marcos Tang:
>
>> Can those records being "processed" some how and when I search the MySQL
>> DB, I only see ONE record list the following only?
>>
>> Total occurrenceMessage content
>> =====================
>> 14,400File system is full
>>
>
> can´t you use something like
>
> INSERT INTO tables VALUES (date,logstring) ON duplicate KEY UPDATE date;
>
> ?
>
> florian
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.balabit.hu/pipermail/syslog-ng/attachments/20110819/b2b90c76/attachment-0001.htm