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.