view release on metacpan or  search on metacpan

bin/webstat_mail.pl  view on Meta::CPAN

#!/usr/bin/perl -w
use strict;
use DBI;
use Carp;
use vars qw(%opts %conf);
use Getopt::Std;
use Data::Dumper;
use Date::Format;
use Net::SMTP;

getopts("dtc:MTh", \%opts);
&usage if ($opts{h}); 
&readconfigfile;

my $dbh	= DBI->connect("DBI:$WebStat::Config::database{driver}:$WebStat::Config::database{database}:$WebStat::Config::database{host}", "$WebStat::Config::database{user}", "$WebStat::Config::database{password}" );

die "Cannot connect to database: $DBI::errstr ($!)" unless $dbh;

$dbh->do("set SQL_BIG_TABLES=1");

# for each server: do something..
while( my ($server, $serverconfig) = each %WebStat::Config::server) {
	next if ($server eq "default");
	print "$server\n" if $opts{d};
	#print Data::Dumper->Dump([\$serverconfig], [qw($serverconfig)]) if $opts{d};

	# setup a few useful dates
	my %dates = (
				 today     => time2str("%Y-%m-%d", time), 
				 yesterday => time2str("%Y-%m-%d", time-(60*60*24)),
				 weekago   => time2str("%Y-%m-%d", time-(60*60*24*7)),
				 monthago  => time2str("%Y-%m-%d", time-(60*60*24*30))
				 );

	my %stats;

	my $serverquery;
	unless ($serverconfig->{alias}) {
	  $serverquery=qq[server="$server"];
	} else {
	 # $serverquery=qq[(server="]. join ('" or server="', $server,@{$serverconfig->{alias}}) .qq[")];
	  $serverquery= qq[server in ("]. join ('","', $server,@{$serverconfig->{alias}}) . qq[")]; 
	}

	if ($serverconfig->{urlpath}) {
	  $serverquery .= qq[ and urlpath regexp "$serverconfig->{urlpath}"];
	}

  # check things

	if ($opts{t}) {
	  $stats{today}   = CountThings($dates{today}, $serverquery);
	} else {
	  $stats{daily}   = CountThings($dates{yesterday}, $serverquery);
	  $stats{weekly}  = CountThings($dates{weekago}, $serverquery);
	  $stats{monthly} = CountThings($dates{monthago}, $serverquery);
	}


  # print or mail it ...
	my $data;
	$data .= "Statistics for $server";
    $data .= " [$serverconfig->{urlpath}]" if $serverconfig->{urlpath};
    $data .= " ($serverconfig->{description})" if $serverconfig->{description};
	$data .= "\n";

	# for the output below..
	$dates{daily} = $dates{yesterday};
	$dates{weekly} = $dates{weekago};
	$dates{monthly} = $dates{monthago};

	for my $time ($opts{t} ? "today" : qw(daily weekly monthly)) { 
	  $data .= "\n$time (from $dates{$time})\n";
	  $data .= sprintf("   % 3u users, % 5u hits % 8.0fKB\n", 
					   $stats{$time}{usercount}, $stats{$time}{hits}, $stats{$time}{traffic});
	  #$data .= "     $stats{$time}{pagehits} pageviews ($stats{$time}{pagetraffic}KB)\n";
	}

    $data .= "\n";
	
	my $timequery;
	unless ($opts{t}) {
	  $timequery = qq[(timeserved >= "$dates{yesterday}" and timeserved < "$dates{today}")];
	} else {
	  $timequery = qq[(timeserved >= "$dates{today}")];
	}

	
	$data .= "\nTop 20 domains visiting the site:\n";
	$data .= "   Hits  Domain\n";
	my $sqlcommand = qq[select substring(remotehost,locate(".",remotehost)+1,100) as subdims,\
						remoteip,count(remotehost) as c from $WebStat::Config::database{"table"} \