#!/usr/local/bin/perl -w # mvs-report-db.pl use DBI; use strict; use vars qw(%ENV $VERSION); $VERSION = "0.06"; my $DEBUG = 0; my $mvs_dir = "$ENV{HOME}/mvs_reports"; my $db_dir = "${mvs_dir}/mvs_db"; my $table = "mvslog"; my $db_file = "${db_dir}/${table}.dbf"; my $repdate = localtime; my ($host, $ip, $number, $type, $date); # data point fields my @dpfields = qw( LOGDATE HOST IP NUMBER TYPE ); my @hostfields = qw( HOST IP ); # the current working data point # contains keys: logdate, host, ip, number, type #my $dpoint = {}; # array of data points from the database my $dbdata = []; if (not -d $mvs_dir) { die "Missing base directory ${mvs_dir}, exiting."; } if (not -d $db_dir) { die "Missing database directory ${db_dir}, exiting."; } if (not -f $db_file) { die "Missing database file ${db_file}, exiting."; } my $dbh = DBI->connect("dbi:XBase:${db_dir}"); my $rv; $rv = load_dbdata($dbh, $table, $dbdata); dprint("rv=", $rv); my $filtered = filter($dbdata); dprint("filtered=", scalar @{$filtered}); $rv = make_report($filtered); dprint("rv=", $rv); # end sub load_dbdata { my $dbh = shift; my $table = shift; my $dbdata = shift; my $sql = "select logdate, host, ip, number, type " . "from ${table}"; dprint("load_dbdata sql=", $sql); my $sth = $dbh->prepare($sql); my $rv = $sth->execute; dprint("load_dbdata rv=", $rv); my $row_ref; while ($row_ref = $sth->fetchrow_hashref) { dprint("keys row_ref=", keys %{$row_ref}); dprint("values row_ref=", values %{$row_ref}); push @{$dbdata}, $row_ref; } return scalar @{$dbdata}; } # check for duplicates sub filter { my $dbdata = shift; my %unique; my ($tdp, $dpf); my $duplicate = 0; # find highest data def number for $tdp (@{$dbdata}) { #my $key = $tdp->{HOST} . ":" . $tdp->{IP}; my $key = $tdp->{HOST}; if (not exists $unique{$key}) { $unique{$key} = $tdp; } if ($unique{$key}->{NUMBER} < $tdp->{NUMBER}) { $unique{$key} = $tdp; } } # create list of filtered values my @filtered = sort { $b->{NUMBER} <=> $a->{NUMBER} || sortip($a->{IP}, $b->{IP}) } values %unique; return \@filtered; } # make report from database sub make_report { my $dbdata = shift; my ($tdp, $dpf); $~ = 'TOP'; write; dprint("dbdata=", $dbdata); for $tdp (@{$dbdata}) { dprint("keys tdp=", keys %{$tdp}); dprint("values tdp=", values %{$tdp}); $host = $tdp->{HOST}; $ip = $tdp->{IP}; $number = $tdp->{NUMBER}; $type = $tdp->{TYPE}; $date = $tdp->{LOGDATE}; $~ = 'BODY'; write; } print "\n"; } # ip address comparer sub sortip { my $ip1 = shift; my $ip2 = shift; # return non-ip address strings as equal if ($ip1 !~ m/^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$/) { return 0; } my @ipp1 = ($1, $2, $3, $4); if ($ip2 !~ m/^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$/) { return 0; } my @ipp2 = ($1, $2, $3, $4); return (($ipp1[0] <=> $ipp2[0]) || ($ipp1[1] <=> $ipp2[1]) || ($ipp1[2] <=> $ipp2[2]) || ($ipp1[3] <=> $ipp2[3])); } # print debug info if $DEBUG is on sub dprint { if (not $DEBUG) { return; } my ($pkg, $file, $line, $sub, @others) = caller(1); print "${sub} ${line} : "; print join(' ', map { $_ || 'undef' } @_), "\n"; } format TOP = McAfee VirusScan Update Report ============================== Report Date: @<<<<<<<<<<<<<<<<<<<<<<<< $repdate Host IP VDN Type Date --------------- -------------------- ---- ---- ------------------------ . format BODY = @<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<< @<<< @<<<<<<<<<<<<<<<<<<<<<<< $host, $ip, $number, $type, $date .