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.