Listing 1 Traffic data graph

=1=    #!/usr/bin/perl -w
=2=    use strict;
=3=    $|++;
=4=    
=5=    ## start config
=6=    
=7=    my $HEIGHT = 500;
=8=    my $WIDTH = 1000;
=9=    my $TIMEFRAME = 86400 * 3;
=10=    
=11=    my $DATABASE = "/var/log/pfstats.sqlite";
=12=    my $OUTPUT = "/web/htdocs/web-blue-stats2.png";
=13=    
=14=    my $COUNT = int($WIDTH/10);     # controls desired barsize
=15=    my $LABELS = int($WIDTH/20);    # function of fontsize
=16=    my @PLOT = qw(OTHER
=17=                  stonehenge-inbound
=18=                  stonehenge-outbound
=19=                  webstonehenge
=20=                  geekcruises
=21=                 );
=22=    sub MAPPING {
=23=      local $_ = shift;
=24=      s/^(webstonehenge|geekcruises)(-.*)?/$1/
=25=        or s/^((stonehenge)-(in|out)bound)(-.*)?/$1/
=26=          or s/.*/OTHER/;
=27=      return $_;
=28=    };
=29=    my @COLORS = qw(pink
=30=                    lorange
=31=                    orange
=32=                    lgreen
=33=                    lblue
=34=                   );
=35=    
=36=    ## end config
=37=    
=38=    require DBI;
=39=    
=40=    my $dbh = DBI->connect("dbi:SQLite:dbname=$DATABASE","","",
=41=                           {RaiseError => 1, PrintError => 0})
=42=      or die $DBI::error;
=43=    
=44=    $dbh->func("mapping", 1, \&MAPPING, "create_function");
=45=    
=46=    my $high = time;
=47=    my $low = $high - $TIMEFRAME;
=48=    my $gap = ($high-$low)/$COUNT;
=49=    
=50=    my @graphdata = [map time_to_label($low + $_ * $gap), 0..($COUNT-1)];
=51=    my %graph_col_map = map {$PLOT[$_] => $_ + 1} 0..$#PLOT;
=52=    
=53=    my $sth = $dbh->prepare
=54=      (q{SELECT round((stamp - ?) / ? - 0.5), mapping(type), avg(bytes)
=55=         FROM stats
=56=         WHERE stamp BETWEEN ? AND ?
=57=         GROUP BY 1, 2}
=58=      );
=59=    $sth->execute($low, $gap, $low, $high);
=60=    while (my ($row, $type, $bytes) = $sth->fetchrow_array) {
=61=      my $graph_col_map = $graph_col_map{$type};
=62=      $graph_col_map = 10 unless defined $graph_col_map;
=63=      ## (bytes) / (300 seconds per sample) = Bytes per Second
=64=      ## so multiply that by seconds per month divided by 1G
=65=      $graphdata[$graph_col_map][$row] = ($bytes / 300) * (86400 * 31 / 1e9);
=66=    }
=67=    
=68=    sub time_to_label {
=69=      my $time = shift;
=70=      my @values = localtime($time);
=71=      sprintf "%02d/%02d-%02d", $values[4]+1, $values[3], $values[2];
=72=    }
=73=    
=74=    require GD::Graph::bars;
=75=    my $g = GD::Graph::bars->new($WIDTH, $HEIGHT) or die;
=76=    $g->set(
=77=            x_label => 'month/day-hour (pacific time)',
=78=            x_label_skip => $COUNT/$LABELS,
=79=            x_labels_vertical => 1,
=80=            y_label => 'average gigabytes per month',
=81=            y_long_ticks => 1,
=82=            title => 'blue.stonehenge.com traffic by customer',
=83=            cumulate => 1,
=84=            dclrs => \@COLORS,
=85=           );
=86=    $g->set_legend(@PLOT);
=87=    my $gd = $g->plot(\@graphdata) or die;
=88=    
=89=    open OUT, ">$OUTPUT.tmp" or die "$OUTPUT.tmp: $!";
=90=    print OUT $gd->png;
=91=    close OUT;
=92=    
=93=    rename "$OUTPUT.tmp", $OUTPUT or die "rename: $!";
=94=    exit 0;