#!/usr/bin/perl -T
#
# Resolve addresses to hostnames in SQL logs.
#
# This program implements three levels of cache to minimize the 
# impact on the DNS.
#
# 1- an internal cache in %cache
# 2- it tries to get resolved addresses (i.e. hostnames) from 
#    previously processed hits in the database.
# 3- it uses the cache of your local DNS server, which should
#    cache negative answers too (starting with bind 8).
#
# Renaud Waldura August 2000
#

use strict;
use vars qw( %ENV );

use constant LOCAL_DEBUG => 9;
use constant UNRES_ADDR => '_X';	# unresolvable address

use Carp;
use Socket;
use DBI;


# debugging?
my $DEBUG = $ENV{GLOBAL_DEBUG} + LOCAL_DEBUG;

my $db;				# database handler
my %cache = ();			# internal address -> hostname cache

my $days = shift @ARGV;
die "Not a number of days: $days" unless ($days =~ /^\d+$/);


eval
{
   # connect to database
   $db = DBI->connect( undef,	# use $ENV{DBI_DSN}
		       undef,	# use $ENV{DBI_USER}
		       undef,	# use $ENV{DBI_PASS}
		       { RaiseError => 1 },
		     )
      or die $DBI::errstr;
   
   # prepare SQL statements
   my ( $st_main,
	$st_gethostname,
	$st_update,
      ) = prepare_statements($db);
   
   # issue main query
   $st_main->execute($days);
	
   while ($_ = $st_main->fetch())
   {
      my($hostname, $address) = @$_;
      
      if ($hostname)		# address was already resolved
      {
	 unless (exists $cache{$address})
	 {
	    $cache{$address} = $hostname;
	    debug(2, "Caching $address => $hostname");
	 }
	 
	 next;
      }
      
      # look up address in internal cache
      if ($hostname = $cache{$address})
      {
	 debug(1, "Resolved $address to $hostname with cache");
      }
      else
      {
	 # look up in external caches
	 $hostname = resolve_address($address, $st_gethostname);
	 
	 # and store result
	 $cache{$address} = $hostname;
	 debug(2, "Caching $address => $hostname");
      }
      
      # unable to resolve
      next if ($hostname eq UNRES_ADDR);
      
      # store resolved name back to database
      $st_update->execute($hostname, $address);
      
      debug(1, "Updated $address to \"$hostname\"");
   }
   
   $st_main->finish();
};
if ($@)
{
   warn $@;
}

$db->disconnect() if ($db);	# disconnect from database

%cache = ();			# flush cache :)


#####################################################################
sub debug
{
   my($level, $mesg) = @_;
   warn "$mesg\n" if ($DEBUG >= $level);
}

#####################################################################
sub resolve_address
{
   my($address, $st) = @_;
   my $hostname = '';
   
   # look up address in database
   $st->execute($address);
   my $tmp = $st->fetch();	# only keep most recent hit
   $st->finish();

   # hostname found in database
   if ($tmp && (($hostname) = @$tmp) && $hostname)
   {
      debug(1, "Resolved $address to \"$hostname\" with database");
      return $hostname;
   }
   
   # finally look up in DNS
   $hostname = gethostbyaddr( gethostbyname($address), AF_INET );
   
   debug(1, "Resolved $address to \"$hostname\" with DNS");

   return $hostname || UNRES_ADDR;
}

#####################################################################
sub prepare_statements
{
   my($db) = @_;
   my @tmp;
   
   push(@tmp, $db->prepare(<<_SQL_));
SELECT DISTINCT
	remoteHost,
	remoteIP
FROM
	http_requests
WHERE
	timeServed > DATE_SUB(NOW(), INTERVAL ? DAY)
ORDER BY
	remoteHost DESC
_SQL_

   push(@tmp, $db->prepare(<<_SQL_));
SELECT DISTINCT
	remoteHost
FROM
	http_requests
WHERE
	remoteHost != ''
AND	remoteIP = ?
ORDER BY
	timeServed DESC
_SQL_

   push(@tmp, $db->prepare(<<_SQL_));
UPDATE
	http_requests
SET
	remoteHost = ?
WHERE
	remoteHost = ''
AND	remoteIP = ?
_SQL_

   return @tmp;
}