Feedback to Google

So since it's rumored that Google does check on the Internet for feedback in recent changes I wanted to give my $.02 on some things:

- Spacing in google search on the left side: Why would you do this? Unfortunately we live in a world were screens are getting smaller and the resolution stays the same with this netbook-mania, removing any valuable space is moronic.

- Apply of labels in Gmail: I'm a "Enterprise"-customer of Gmail and quite a heavy user. I've became quite abusive with my labels but I like to have things organized causing me to tag multiple emails with multiple labels, the extra click of accepting that I've chosen a label when searching for one is a PITA, if you did choose the wrong one it's just one click to remove the wrongly selected anyway, why make it redundant?

Office 2.0

Dinner ready!

Making some food

Potatoes, beef and mushrooms.

DNS query parser

The last days I've been working on our internal DNS servers looking at the new BIND statistics I enabled 2 weeks back. I sure found some interesting behaviors of some clients that shouldn't be there thanks to it.

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:
- Help-messages
- Function to log
- Auto adjust write frequency based on load
- Clean-up
- Fix MySQL table to use appropriate fields