BIND DNS query log shipping into a MySQL database

BIND DNS query log shipping into a MySQL database

Yay!, I’ve been wanting to do this for a while! Here it goes:-

Documented herein is a method for shipping BIND DNS query logs into a MySQL database and then reporting upon them!

Note: SSH keys are used for all password-less log-ons to avoid prompt issues

BIND logging configuration

BIND named.conf query logging directive should be set to simple logging:-

logging{

  # Your other log directives here

  channel query_log {
    file "/var/log/query.log";
    severity info;
    print-time yes;
    print-severity yes;
    print-category yes;
  };

  category queries {
    query_log;
  };
};

The reason why a simple log is needed is because the built-in BIND log rotation only allows rotation granularity of 1 day if based on time, hence an external log rotation method is required for granularity of under 24 hours.

BIND query log rotation

My external BIND log rotation script is scheduled from within cron and it looks like this:-

#!/bin/bash
QLOG=/var/named/chroot/var/log/query.log
LOCK_FILE=/var/run/${0##*/}.lock

if [ -e $LOCK_FILE ]; then
  OLD_PID=`cat $LOCK_FILE`
  if [ ` ps -p $OLD_PID > /dev/null 2>&1 ` ]; then
    exit 0
  fi
fi
echo $$ > $LOCK_FILE

cat $QLOG > $QLOG.`date '+%Y%m%d%H%M%S'`
if [ $? -eq 0 ]; then
  > $QLOG
fi
service named reload

rm -f $LOCK_FILE

Place this in the crontab, working at between one and six hours, ensure it is not run on the hour or at the same time as other instances of this job on associated servers

make sure /var/named/chroot/var/log/old exists for file rotation, used in the data pump script later on.

From here, I create a MySQL table, called dnslogs with the following structure:-

create table dnslog (
  q_server   VARCHAR(255),
  q_date     VARCHAR(11),
  q_time     VARCHAR(8),
  q_client   VARCHAR(15),
  q_view     VARCHAR(64),
  q_text     VARCHAR(255),
  q_class    VARCHAR(8),
  q_type     VARCHAR(8),
  q_modifier VARCHAR(8)
);

You can either define a database user with a password and configure it such in the scripts, or you can configure a database user which can only connect and insert into the dnslogs table.

Then I use the following shell script to pump the rotated log data into the MySQL database:-

#!/bin/bash
PATH=/path/to/specific/mysql/bin:$PATH export PATH
DB_NAME=your_db
DB_USER=db_user
DB_PASS=i_know_it_is_a_bad_idea_storing_the_pass_here
DB_SOCK=/var/lib/mysql/mysql.sock
SSH_USER=someone
LOG_DIR=/var/named/chroot/var/log
LOG_REGEX=query.log.\*
NAME_SERVERS="your name server list here"

LOCK_FILE=/var/run/${0##*/}.lock

if [ -e $LOCK_FILE ]; then
  OLD_PID=`cat $LOCK_FILE`
  if [ ` ps -p $OLD_PID > /dev/null 2>&1 ` ]; then
    exit 0
  fi
fi
echo $$ > $LOCK_FILE
for host in $NAME_SERVERS; do
  REMOTE_LOGS="`ssh -l $SSH_USER $host find $LOG_DIR -maxdepth 1 -name $LOG_REGEX | sort -n`"
  test -n "$REMOTE_LOGS" && for f in $REMOTE_LOGS ; do
    ssh -C -l $SSH_USER $host "cat $f" | \
      sed 's/\./ /; s/#[0-9]*://; s/: / /g; s/\///g; s/'\''//g;' | \
        awk -v h=$host '{ printf("insert into '$DEST_TABLE' values ( 
'\''%s'\'', 
STR_TO_DATE('\''%s %s.%06s'\'','\''%s'\''), 
'\''%s'\'', 
'\''%s'\'', 
'\''%s'\'', 
'\''%s'\'', 
'\''%s'\'', 
'\''%s'\''
);\n",
h, 
$1, 
$2, 
$3 * 1000, 
"%d-%b-%Y %H:%i:%S.%f", 
$7, 
$9, 
$11, 
$12, 
$13, 
$14
); }' | mysql -A -S $DB_SOCK -u $DB_USER --password=$DB_PASS $DB_NAME 2> $ERROR_LOG
    RETVAL=$?
    if [ $RETVAL -ne 0 ]; then
      echo "Import of $f returned non-zero return code $RETVAL"
      test -s $ERROR_LOG && cat $ERROR_LOG
      continue
    fi
    ssh -l $SSH_USER $host mv $f ${f%/*}/old/
  done
done
rm -f $LOCK_FILE $ERROR_LOG

Put this script into a file and schedule from within crontab, running some time after the rotate job suffice to allow it to complete, but before the next rotate job.

Note that the last operation of the script is to move the processed log file into $LOG_DIR/old/.

This will take each file in /var/named/chroot/var/log/query.\* and ship it into the dnslogs table as frequently as is defined in the crontab.

From here, it is possible to report from the db with a simple query method such as:-

#!/bin/bash
PATH=/path/to/specific/mysql/bin:$PATH export PATH
DB_NAME=your_db
DB_USER=db_user
DB_PASS=i_know_it_is_a_bad_idea_storing_the_pass_here
DB_SOCK=/var/lib/mysql/mysql.sock
SSH_USER=someone
SQL_REGEX='%your-search-term-here%'

LOCK_FILE=/var/run/${0##*/}.lock

if [ -e $LOCK_FILE ]; then
  OLD_PID=`cat $LOCK_FILE`
  if [ ` ps -p $OLD_PID > /dev/null 2>&1 ` ]; then
    exit 0
  fi
fi
echo $$ > $LOCK_FILE

echo "select * from dnslogs where q_text like '$SQL_REGEX';" | \
  mysql -A -S $DB_SOCK -u $DB_USER --password=$DB_PASS $DB_NAME

rm -f $LOCK_FILE

And there it is! SQL reporting from DNS query logs! You can turn this into whatever report you like.

From there, you may wish to script solutions to partition the database and age the data.

Database partitioning should be done upon the q_timestamp value, dividing the table into periods which align with the expectation of the depth for which reporting is expected. On a minimal basis, I would recommend keeping at least 4 days of data in partitions of between 24 hours and 1 hour, depending upon the reporting expectations. If reports are upon the previous day’s data only, then 1 partition per day will do, while reports which are only interested in the past hour or so will benefit from having partitions of an hour. in MySQL, sub-partitions are not worthwhile because they give you nothing more than partitions but adds a layer of complexity on what is otherwise a linear data set.
Once partitioning is established, it should be possible to fulfill reports by querying only the relevant partitions to cover the time span of interest.
Partitioning also has another benefit, which is data aging. Instead of deleting old records, it is possible to drop entire partitions which cover select periods of time without having to create a huge temporary table to hold the difference as would be required by a delete operation. This becomes an extremely useful feature if you have a disk with a table size which is greater than the amount of free space available.

Script updates for add and drop partition to follow….

Advertisements

Why I love admin.com’s MX record!

It’s pretty fair to say that admin.com is probably one of the most abused domains in the world.

I take my hat off to them in their attempt to combat spam.

They tool the simple eloquent solution of setting their MX record to localhost.

This dear reader is pure genius.

It is genius because it means that any DNS-aware mail server carrying mail for admin.com will burn up on repeated local delivery attempts my this MX record to localhost forcing the mail server into attempting delivery to itself.

The added bonus of this method is that mail never hits admin.com’s servers thus ensuring that their servers do not serve a dross of spam.

While it is obvious that this method does not allow delivery of mail if you actually want to receive mail, it is only suitable in this uncommon situation, and hmm, maybe some other situations.

It may possibly be a suitable remedy to eliciting noticeable decommissioning of domains such that the receiving SMTP servers catch no load and the sending SMTP servers get to see all the errors.

This may also be a useful spoofing technique for DNS views within your control if you want to suppress mail to certain domains within a subscribed client-base.

Or maybe suppressing mail from a machine which it is not possible to disable applications from mailing out.

A quick ‘hack’ to test this on any given machine is to alias the given domain to localhost in the /etc/hosts or c:\windows\system32\drivers\etc\hosts file in order to elicit the same outcome.

Caution is recommended – don’t lock-out access to key hosts like yourself or the device’s default router by aliasing critical network nodes. Your mileage may vary – don’t alias the name in which your machine has (if known to the device) for which you are using to administer the given device.