--- /dev/null
+icelog
+
+Copyright (c) 2002 Ivan Kohler
+All rights reserved.
+This program is free software; you can redistribute it and/or modify it under
+the same terms as Perl itself.
+
+ivan-icelog@420.am
+
+iceaccessd/iceaccess_server is a client/server program for collecting logging
+data from multiple, possibly remote icecast servers in a central SQL database.
+
+icecounter.cgi is a CGI to query the SQL database and displaytotal elapsed
+minutes (live vs. archived) for a single customer or all customers, total or
+broken down by month.
+
+create-Pg.sql and create-mysql.sql contain the SQL to create the
+required table.
+
+To use:
+
+ - create the database table as defined in create-Pg.sql or create-mysql.sql
+ - copy iceaccessd to /usr/local/bin/iceaccessd on the icecast server(s)
+ - chmod a+rx /usr/local/bin/iceaccessd on the icecast server(s)
+ - set the parameters in icelog.conf and copy it to /etc/icelog.conf
+ - on the central database machine, run:
+ iceaccess_server icecast.machine /path/to/icecast/access.log 0
+ for each remote icecast.machine
+ - on each icecast.machine, after rotating your icecast access.log, HUP the
+ iceaccessd process
+ - if the central database machine goes down, you can restart the parsing from
+ a particular position in the logfile using the command:
+ iceaccess_server icecast.machine /path/to/icecast/access.log position
+ You can ask the database for the position log parsing left off at with
+ a query like:
+ SELECT MAX(logpos) FROM icelog
+ WHERE logdate > <timestamp> AND logmachine = "icecast.machine";
+ where <timestamp> is the UNIX timestamp when you last rotated your logs,
+ and icecast.machine is the machine in question.
+
--- /dev/null
+create table icelog (
+ lognum serial primary key,
+ logdate timestamp,
+ logmachine varchar(255),
+ logpos int,
+ customer varchar(255),
+ liveflag char(1),
+ hostname varchar(255),
+ start int,
+ mountpoint varchar(255),
+ bytes int,
+ useragent varchar(255),
+ seconds int
+);
+
--- /dev/null
+create table icelog (
+ lognum int primary key auto_increment,
+ logdate datetime,
+ logmachine varchar(255),
+ logpos int,
+ customer varchar(255),
+ liveflag char(1),
+ hostname varchar(255),
+ start int,
+ mountpoint varchar(255),
+ bytes int,
+ useragent varchar(255),
+ seconds int
+);
+
--- /dev/null
+#!/usr/bin/perl -w
+#
+# Copyright (c) 2002 Ivan Kohler
+# All rights reserved.
+# This program is free software; you can redistribute it and/or modify it under
+# the same terms as Perl itself.
+#
+# ivan-icelog@420.am
+
+use strict;
+use vars qw( $Debug );
+use DBI;
+use IO::Handle;
+use Net::SSH qw(sshopen2);
+
+$Debug = 0;
+
+my $machine = shift or die &usage;
+my $logfile = shift || '/var/log/icecast/access.log';
+my $pos = shift || 0;
+
+require "/etc/icelog.conf";
+
+my $dbh = DBI->connect($dsn, $username, $password)
+ or die "Can't connect to $dsn: ". $DBI::errstr;
+
+my $iceaccessd = '/usr/local/bin/iceaccessd';
+
+my $me = "[iceaccess_server]";
+
+#my $pos = 0;
+
+while (1) {
+ my($reader, $writer) = (new IO::Handle, new IO::Handle);
+ $writer->autoflush(1);
+ warn "$me Connecting to $machine\n" if $Debug;
+ sshopen2($machine,$reader,$writer,$iceaccessd, $logfile, $pos);
+ warn "$me Entering main loop\n" if $Debug;
+ while (1) {
+ warn "$me Reading (waiting for) data\n" if $Debug;
+ my $line = scalar(<$reader>);
+ die "No response from remote iceaccessd process" unless defined($line);
+ chomp $line;
+ my %hash;
+ ( $pos, %hash ) = split(/\t/, $line);
+ if ( $pos eq 'EOF' ) { ; #re-open iceacceed process on new logfile
+ $pos = 0;
+ last;
+ }
+
+ #write to db
+
+ my $customer = $hash{mountpoint};
+ $hash{mountpoint} =~ /^\/([^\/]*)/
+ or die "weird mountpoint $hash{mountpoint}";
+ $customer = $1;
+
+ #$hash{mountpoint} =~ /\/|^([^\/]+)$/;
+ #my $file = $1;
+
+ my $liveflag;
+ if ( $hash{mountpoint} =~ /\/0+(\.\w+)?$/ ) {
+ $liveflag = 'Y';
+ } else {
+ $liveflag = 'N';
+ }
+
+ #switch to prepare_cached?
+ my $sth = $dbh->prepare(<<END) or die $dbh->errstr;
+ INSERT INTO icelog ( logdate, logmachine, logpos, customer, liveflag,
+ hostname, start, mountpoint, bytes, useragent,
+ seconds
+ ) VALUES ( NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
+END
+
+ $sth->execute(
+ $machine, #logmachine
+ $pos, #logpos
+ $customer, #customer
+ $liveflag, #liveflag
+ $hash{hostname}, #hostname
+ $hash{start}, #start
+ $hash{mountpoint}, #mountpoint
+ $hash{bytes}, #bytes
+ $hash{useragent}, #useragent,
+ $hash{seconds}, #seconds
+ ) or die $sth->errstr;
+ $sth->finish;
+
+ }
+ close $writer;
+ close $reader;
+ warn "connection to $machine lost!\n";
+ sleep 5;
+ warn "reconnecting...\n";
+}
+
+
+
+
+sub usage {
+ die "Usage:\n\n iceaccess_server machine logfile position\n";
+}
--- /dev/null
+#!/usr/bin/perl -w
+#
+# Copyright (c) 2002 Ivan Kohler
+# All rights reserved.
+# This program is free software; you can redistribute it and/or modify it under
+# the same terms as Perl itself.
+#
+# ivan-icelog@420.am
+
+use strict;
+use vars qw($eof);
+#use Date::Parse;
+use Time::Local;
+
+my $c = 0;
+my %mon =
+ map { ( $_ => $c++ ) } qw( jan feb mar apr may jun jul aug sep oct nov dec );
+
+$|=1;
+$eof=0;
+
+my( $file, $pos ) = @ARGV;
+open(FILE,"<$file") or die "Can't open $file: $!";
+seek(FILE,$pos,0) or die "Can't seek: $!";
+
+$SIG{'HUP'} = sub { print "EOF\n"; exit; };
+#$SIG{'HUP'} = sub { $eof=time; }; # set an alarm
+
+while (1) {
+
+ while (<FILE>) {
+ next if /^$/;
+ #rootwood.haze.st - - [24/Dec/2001:15:33:50 -0800] "GET / HTTP/1.0" 200 1388544 "-" "xmms/1.2.5" 89
+ /^
+ ([\w\-\.]+)\ -\ -\ #hostname
+ \[(\d{2})\/(\w{3})\/(\d{4}):(\d{2}):(\d{2}):(\d{2})\ -(\d{4})\]\ #date
+ "GET\ ([\/\w\-\.]+)\ HTTP\/\d\.\d"\ # request string
+ (\d{3})\ #staus resonse
+ (\d+)\ #bytes
+ "([^"]*)"\ #referer
+ "([^"]*)"\ #user agent
+ (\d+) #seconds connected
+ $/xo or do {
+ die "unparsable line: $_";
+ next;
+ };
+
+ #warn "ok: $_";
+
+ my $hostname = $1;
+ my( $mday, $mon, $year, $hours, $min, $sec, $zone ) =
+ ( $2, $3, $4, $5, $6, $7, $8 );
+
+ my $mountpoint = $9;
+ my $status = $10;
+ my $bytes = $11;
+ my $referer = $12;
+ my $useragent = $13;
+ my $seconds = $14;
+
+ $SIG{HUP} = sub { $eof=1 };
+ print join("\t",
+ tell FILE,
+ hostname => $hostname,
+ start => timelocal($sec, $min, $hours, $mday, $mon{lc($mon)}, $year),
+ mountpoint => $mountpoint,
+ bytes => $bytes,
+ useragent => $useragent,
+ seconds => $seconds,
+ ), "\n";
+ $SIG{'HUP'} = sub { print "EOF\n"; exit; };
+ if ( $eof ) {
+ print "EOF\n";
+ exit;
+ }
+
+ }
+
+ sleep 1;
+ seek(FILE,0,1);
+}
+
--- /dev/null
+#!/usr/bin/perl
+
+use vars qw($dsn $username $password $dbh $query);
+use vars qw($my_mon, $my_year);
+use vars qw($title);
+use vars qw($min_time $max_time $min_mon $max_mon $min_year, $max_year );
+use CGI;
+use CGI::Carp qw(fatalsToBrowser);
+use DBI;
+use Time::Local;
+
+require "/etc/icelog.conf";
+
+my $cgi = new CGI;
+($query) = $cgi->keywords;
+
+$dbh = DBI->connect($dsn, $username, $password)
+ or die "Can't connect to $dsn: ". $DBI::errstr;
+
+if ( $cgi->param('customer') ) {
+ @customers = ( $cgi->param('customer') );
+} else { #everybody
+ my $sth = $dbh->prepare('select distinct customer from icelog')
+ or die $dbh->errstr;
+ $sth->execute or die $sth->errstr;
+ @customers = map { $_->[0] } @{$sth->fetchall_arrayref};
+ $sth->finish;
+}
+($my_mon,$my_year) = ($cgi->param('mon'), $cgi->param('year'));
+
+my $sth = $dbh->prepare('select min(start), max(start) from icelog')
+ or die $dbh->errstr;
+$sth->execute or die $sth->errstr;
+( $min_time, $max_time ) = @{$sth->fetchrow_arrayref};
+$sth->finish;
+( $min_mon, $min_year ) = (localtime($min_time))[4,5];
+$min_mon++; $min_year+=1900;
+( $max_mon, $max_year ) = (localtime($max_time))[4,5];
+$max_mon++; $max_year+=1900;
+
+my $title = 'icecast log';
+if ( $my_mon && $my_year ) {
+ $title .= " $my_mon/$my_year";
+} else {
+ $title .= ' (all)';
+}
+
+print $cgi->header, <<END;
+<html>
+ <head>
+ <title>icecast log</title>
+ </head>
+ <body bgcolor="#e8e8e8">
+ <h2>$title</h2>
+END
+
+for ( my($mon,$year) = ($min_mon, $min_year);
+ $year < $max_year || ( $year == $max_year && $mon <= $max_mon);
+ do { $mon++; if ( $mon == 13 ) { $year++; $mon-=12; } }
+ ) {
+ $cgi->param('year', $year);
+ $cgi->param('mon', $mon);
+ print '<a href="'. $cgi->self_url. qq(">$mon/$year</a> | );
+}
+$cgi->param('year', '');
+$cgi->param('mon', '');
+print '<a href="'. $cgi->self_url. qq(">all</a>);
+$cgi->param('year', $my_year);
+$cgi->param('mon', $my_mon);
+
+print <<END;
+ <br>
+ <table border>
+ <tr><th>Cust#</th><th>Minutes (live)</th><th>Minutes (archived)</th><th>Minutes (total)</th></tr>
+END
+
+foreach my $customer ( @customers ) {
+
+ my $liveminutes = &getminutes($customer, 'Y', $my_mon, $my_year);
+ my $archminutes = &getminutes($customer, 'N', $my_mon, $my_year);
+ my $totminutes = $liveminutes + $archminutes;
+
+ $cgi->param('customer', $customer);
+ my $self_url = $cgi->self_url;
+
+ print qq(<tr><td><a href="$self_url">$customer</a></td><td>$liveminutes</td><td>$archminutes</td><td>$totminutes</td></tr>);
+}
+
+print <<END;
+ </table>
+</html>
+END
+
+sub getminutes {
+ my($customer, $liveflag, $mon, $year) = @_;
+ my $statement =
+ 'select sum(seconds) from icelog where customer = ? and liveflag = ?';
+ if ( $mon && $year ) {
+ my $start = timelocal(0,0,0,1,$mon-1,$year-1900);
+ $mon++; if ( $mon == 13 ) { $year++; $mon-=12; }
+ my $end = timelocal(0,0,0,1,$mon-1,$year-1900) - 1;
+ $statement .= " and start >= $start and start <= $end";
+ }
+ #warn $statement;
+ my $sth = $dbh->prepare($statement) or die $dbh->errstr;
+ $sth->execute($customer, $liveflag) or die $sth->errstr;
+ my $seconds = $sth->fetchrow_arrayref->[0];
+ $sth->finish;
+ sprintf("%.3f", $seconds / 60);
+}
--- /dev/null
+# DBI datasource, username and password
+($dsn, $username, $password) = ('DBI:mysql:icelog', 'user', 'password');