Fish

Fish

About

This program parses syslog events from a Barracuda Spam firewall and puts the results into a MySQL database. It requires PHP CLI.

The table structure looks like this:

    • msg_id
    • timestamp
    • host
    • process
    • resolved
    • client
    • start
    • end
    • service
    • encrypted
    • sender
    • recipient
    • action
    • reason
    • reasonx
    • score
    • size
    • subject
    • queue_id

You can grab it here: Fish.zip

Examples

mysql> SELECT COUNT(*) AS count, r_code.meaning FROM event, r_code WHERE FROM_UNIXTIME(timestamp)BETWEEN '2010-04-01 00:00:00' AND '2010-05-01 00:00:00' AND (event.reason = r_code.id) AND action = '2' GROUP BY event.reason ORDER BY count DESC;

+---------+-----------------------------+

| count | meaning |

+---------+-----------------------------+

| 3766392 | Barracuda Blocklist |

| 247963 | RBL Match |

| 69529 | Spam Fingerprint Found |

| 63371 | Intention Analysis Match |

| 21916 | Score |

| 11731 | No Such User |

| 7238 | No Such Domain |

| 7018 | Realtime Intent |

| 5645 | Need Fully Qualified Sender |

| 5429 | Virus |

| 4336 | Multi-Level Intent |

| 3688 | Timeout Exceeded |

| 10 | Client IP |

| 7 | Message Size Bypass |

| 3 | Barracuda Whitelist |

+---------+-----------------------------+

mysql> SELECT COUNT(*) AS count, INET_NTOA(client) FROM event WHERE from_unixtime(timestamp) BETWEEN '2010-05-01 00:00:00' AND '2010-05-02 00:00:00' AND action = '2' GROUP BY client ORDER BY count DESC limit 10;

+-------+-------------------+

| count | INET_NTOA(client) |

+-------+-------------------+

| 276 | 62.111.138.130 |

| 226 | 200.74.249.27 |

| 143 | 112.153.203.62 |

| 142 | 72.55.141.20 |

| 132 | 64.22.65.181 |

| 129 | 64.22.65.182 |

| 126 | 64.22.65.183 |

| 109 | 93.32.186.103 |

| 105 | 202.156.81.220 |

| 103 | 89.171.120.66 |

+-------+-------------------+

Setup

1) Configure your Barracuda to export syslogs.

2) On your collector edit your syslog.conf and add something like this (I have 2 devices):

+192.168.1.10

*.* /var/log/spam.log

+192.168.1.11

*.* /var/log/spam.log

3) Create a database called "spam"

~$ mysql -N -B --user=root --password=toor -e "CREATE DATABASE spam;"

4) Create the reason code mappings:

~$ cat r_code.sql | mysql -uroot -ptoor -D spam

5)Create a writer for the spam database:

~$ mysql -N -B --user=root --password=toor -e "GRANT ALL PRIVILEGES ON spam.* TO 'spamwriter'@'localhost' IDENTIFIED BY 'apassword';"

6) Open fish.php and modify the database settings to suit.

7) To start the program just do something like this:

~$ ./fish.php /var/log/spam.log&

and it will happily chug away as long as the file exists.

If you are dealing with a lot of data create something like this:

#!/usr/local/bin/bash

# Delete old spam log and restart syslogd and fish

# Check the commands of course, this is for FreeBSD

log="/var/log/spam.log"

if [ -e "$log" ]; then

rm -f $log

sleep 2

fi;

touch $log

/etc/rc.d/syslogd restart

sudo -u alittleuser fish.php $log&

# End

and put this in root's crontab:

0 0 * * * fish.sh

Changelog

2010-06-08: Explode delimiter on subject line was changed from ":" to "SUBJ:". This still may fail but I don't have a better solution just yet.