Tuesday, March 12, 2013

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.

3 comments:

V said...

man arpwatch says
The -f flag is used to set the ethernet/ip address database filename. The default is arp.dat.

How do you compare this with your statement "Unfortunately, this isn't configurable, i.e. arpwatch, out-of-the-box, doesn't support any other way of logging."

Stjepan Groš (sgros) said...

You should've read my statement more carefully, i.e. I've said that it doesn't support ANY OTHER WAY of logging (BESIDES into plain file). Besides, the whole point of this post was to describe how I did logging into SQL database.

V said...

Aggreed!

About Me

scientist, consultant, security specialist, networking guy, system administrator, philosopher ;)

Blog Archive