Thinking a bit more at statistics I started looking at the query-log, if you have managed a DNS server you know that the query-log tends to build up quickly and it can become pretty unmanageable to troubleshoot unless you've some decent programming skills. Due to this I figured, hell, lets dump the data in a MySQL database so we can run some nice queries to see trending etc.
The code below is what I ended up with. What it does is creating a named pipe (FIFO) that allows BIND to write all query-log data directly a database after being parsed by the script.
Please notice that I'm still working on the code and will post the updates to this post, if you have any proposals or issues with it please email me.
Disclaimer: This code is very much untested and it's expensive to run from a CPU perspective, use caution!
...
logging {
channel query_log {
file "/var/log/querypipe";
time-stamp yes;
}
...
Once started all query-log data is sent to the script and gets parsed, once the writecache has reached it's top the data is being written to the database. The reason for maintaining a writecache is because MySQL with MyISAM has a lot better performance inserting several rows at once rather than one at the time, the backside to this is that you're data wont be real-time which may not be ideal depending on your reporting needs.
CREATE TABLE `queries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(25) DEFAULT NULL,
`time` varchar(25) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`port` int(11) DEFAULT NULL,
`query` varchar(255) DEFAULT NULL,
`rr` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
To run the code edit the variables to match your system settings, to demonize it run it with -D, once started, run rndc reload or restart your name-server to release locking of the query-log.
#!/usr/bin/perl
use Getopt::Long;
use DBI;
use POSIX qw(setsid);
my $daemon = 0;
Getopt::Long::GetOptions('daemon|D' => \$daemon);
if ($daemon) {
defined(my $pid = fork) or die "Can't fork: $!";
exit if $pid;
setsid or die "Can't start a new session: $!";
}
$SIG{INT} = \&terminate;
$SIG{HUP} = \&terminate;
sub terminate {
unlink $FIFO;
exit 0;
}
while (1) {
my $LOG_PATH = '/var/named/chroot/var/log/';
my $FIFO = $LOG_PATH.'querypipe';
my $RNDC = '/usr/sbin/rndc';
my $user = 'named';
my($login,$pass,$uid,$gid) = getpwnam($user) or die "$user not in passwd file";
my $db = DBI->connect("dbi:mysql:database=dns;host=localhost:3306;user=dns;password=dns") or die "Failed to connecto DB\n";
while (1) {
unless (-p $FIFO) {
unlink $FIFO;
system('mknod', $FIFO, 'p')
&& die "can't mknod $FIFO: $!";
}
chown $uid, $gid, $FIFO;
system("$RNDC reload");
open (FIFO, "< $FIFO") || die "can't read $FIFO: $!";
while () {
my($date, $time, $source, $port, $query, $rr) = /([0-9]{1,2}-[A-Za-z]{3}-[0-9]{4}) ([0-9]{2}:[0-9]{2}:[0-9]{2}).[0-9]{1,5} client (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})#([0-9]{1,5}): query: (.*) IN (.*) +/;
my $statement = $db->prepare("INSERT INTO `queries` values (NULL, '$date', '$time', '$source', '$port', '$query', '$rr')");
$statement->execute() or die "Failed to write to DB!";
}
close FIFO;
}
}
Planned additions:
-
-
- Auto adjust write frequency based on load
- Clean-up
- Fix MySQL table to use appropriate fields
