Storing arpwatch output into database

arpwatch is very useful tool which logs its output via syslog and also sends mail alerts. Unfortunately, this isn't configurable, i.e. arpwatch, out-of-the-box, doesn't support any other way of logging.  One approach is to modify arpwatch to be able to log into some SQL database, but this isn't straightforward way, i.e. not an easy one. Namely, arpwatch is written in C, and besides, it's hard to know if this would be accepted by upstream (who ever that migh be).

So, I decided to go with a different approach. I configured arpwatch to log its output into log file and wrote a Python script that executes via cron and transfers all the data into the database. Here is how I did it along with all the scripts.

Configuring logging

The first step is to configure arpwatch to log its output into a separate file. This isn't possible to do in arpwatch itself, but it is possible to achieve it by configuring syslog, or rsyslog to be more precise. On CentOS 6 rsyslog is used that allows just that. All you have to do is to place a file named (for example) arpwatch.conf in directory /etc/rsyslog.d with the following content:

if $programname == 'arpwatch' then /var/log/arpwatch.log
&~

Don't forget to restart rsyslog after that. This will write anything logged by arpwatch binary into /var/log/arpwatch.log file. All the different log lines that can appear are documented in arpwatch's manual page so I won't replicate them here.

Configuring database

In my case I created a single table using the following SQL statement:

CREATE TABLE arpwatch (
  macaddr char(17) NOT NULL,
  ip_addr int(10) unsigned NOT NULL,
  state varchar(8) NOT NULL,
  timestamp datetime NOT NULL,
  oldmac char(17) DEFAULT NULL
)

I think it's pretty obvious what goes where. Only thing that might be strange is that I'm using INT(10) for IP address. But that is because SNORT also stores IP addresses in such a way so in order to be compatible with it I used it also. Also, what is missing is primary key, but for the time being I'm not using it.

Script

Here is the script that should be started from the cron. For example, store it in /usr/local/sbin directory and to start it every 20 minutes add the following line (as root user) to cron using 'crontab -e' command:

*/20 * * * * /usr/local/sbin/arpwatchlog2sql.py

Note that the script expects configuration file. Here is a sample configuration file you'll have to modify. The script expects configuration file to be in its current directory, but you can place it into /usr/local/etc and modify the line CONFIGFILE in script accordingly.

Log rotation

Finally, you should be certain that logs are properly handled, i.e. rotated along with other logs. Since arpwatch is logging via syslog, that means that you have to modify rsyslog's log configuration file, i.e. /etc/logrotate.d/syslog. In there you'll see that logfiles maintained by rsyslog are enumerated, one per line. Just add arpwatch.log to that list and that should be it.

Zimbra log cluttering...

When you run Zimbra, logs generated by it are duplicated in system log files (i.e. /var/log/messages, or /var/log/secure) but also in Zimbra specific log files (i.e. /var/log/zimbra.log). The problem with this is that it clutters system logfiles, i.e. takes unnecessary space and makes them hard to analyze. So, it would be good to make Zimbra log only in its own specific log files.

Googling for solution I found this post but with no satisfactory solution. Since there was no ready solution, I turned to Googling on how to configure rsyslog to do that. Namely, CentOS (on which I'm running Zimbra) uses rsyslog as a replacement for a more traditional syslog. It turns out it is possible to filter according to application doing logging.

So, two offending applications are zimbramon and zmmailboxdmgr. In order to prevent them from logging into /var/log/messages add the following lines before section RULES (that resemble classical syslog rules):

if $programname == 'zimbramon' then /var/log/zimbra-stats.log
& ~

if $programname == 'zmmailboxdmgr' then /var/log/zimbra-stats.log
& ~

The first two lines will redirect zimbramon messages, while the second two will do the same for zmmailboxdmgr.