Welcome, Guest. Please login or register.

Author Topic: mySQL trigger for modifying syslog entries  (Read 1639 times)

dobst

  • Full Member
  • ***
  • Posts: 144
    • View Profile
mySQL trigger for modifying syslog entries
« on: September 04, 2013, 02:11:56 PM »
Our switch and dhcp logs are sent to our syslog server where also nedi runs. We are using syslog.pl to put the messages into the db. Usually they  look like this:

Code: [Select]
id level time source info class device
31836054 10 1378295641 127.0.0.1 Sep  4 13:54:01 switch 134653: Sep  4 13:54:00: LINEPROTO-5-UPDOWN: Line protocol on Interface FastEthernet3/0/22, changed state to up -
31836053 10 1378295641 139.18.x.x Sep  4 13:54:01 dhcp dhcpd: DHCPDISCOVER from 00:50:56:xx:xx:xx via eth0: network 139.18.x.x/x: no free leases -

To remove overhead and unnecessary stuff like date and logging process in the info field and to get a clear view I set the following mysql trigger before insert on table events:

Code: [Select]
BEGIN
IF NEW.info LIKE "% % %:%:% dhcp dhcpd: %" THEN
SET NEW.info = TRIM(REPLACE(NEW.info, SUBSTRING_INDEX(NEW.info, ' ', 6), ''));
#SET NEW.class = 'dhcp';
END IF;

IF NEW.source = '127.0.0.1' THEN
SET NEW.source = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.info, ' ', 5), ' ', -1 );
#delete everything before '%: %: '
SET NEW.info = TRIM(RIGHT(RIGHT(NEW.info, CHAR_LENGTH(NEW.info)-LOCATE(': ', NEW.info)), CHAR_LENGTH(RIGHT(NEW.info, CHAR_LENGTH(NEW.info)-LOCATE(': ', NEW.info)))-LOCATE(': ', RIGHT(NEW.info, CHAR_LENGTH(NEW.info)-LOCATE(': ', NEW.info)))));
END IF;
END

So we get it nice  :) :

Code: [Select]
id level time source info class device
31841065 10 1378295917 139.18.x.x DHCPREQUEST for 139.18.x.x from 5c:b5:24:xx:xx:xx (android-3b91bb277fxxxxxx) via 139.18.x.x -
31841064 10 1378295917 switch SW_DAI-4-DHCP_SNOOPING_DENY: 1 Invalid ARPs (Res) on Gi2/6, vlan 60.([14da.e9xx.xxxx/139.18.x.x/000b.5fxx.xxxx/139.18.x.x/13:58:35 MEZ Wed Sep 4 2013]) -

It's performant enough to handle syslog messages of all our 600 devices and about 10k dhcp clients. DB grows about 300 MB per day.
« Last Edit: September 04, 2013, 05:20:44 PM by dobst »