From 383add202012146f37009832ad19d27945e555f1 Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Mon, 21 Jan 2002 11:27:27 +0000
Subject: [PATCH 1/1] initial import

---
 README           |  40 ++++++++++++++++++++
 create-Pg.sql    |  15 ++++++++
 create-mysql.sql |  15 ++++++++
 iceaccess_server | 103 +++++++++++++++++++++++++++++++++++++++++++++++++++
 iceaccessd       |  82 +++++++++++++++++++++++++++++++++++++++++
 icecounter.cgi   | 110 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 icelog.conf      |   2 +
 7 files changed, 367 insertions(+)
 create mode 100644 README
 create mode 100644 create-Pg.sql
 create mode 100644 create-mysql.sql
 create mode 100755 iceaccess_server
 create mode 100644 iceaccessd
 create mode 100755 icecounter.cgi
 create mode 100644 icelog.conf

diff --git a/README b/README
new file mode 100644
index 0000000..5a4f75e
--- /dev/null
+++ b/README
@@ -0,0 +1,40 @@
+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.
+
diff --git a/create-Pg.sql b/create-Pg.sql
new file mode 100644
index 0000000..c5592a1
--- /dev/null
+++ b/create-Pg.sql
@@ -0,0 +1,15 @@
+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
+);
+
diff --git a/create-mysql.sql b/create-mysql.sql
new file mode 100644
index 0000000..c7b24e1
--- /dev/null
+++ b/create-mysql.sql
@@ -0,0 +1,15 @@
+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
+);
+
diff --git a/iceaccess_server b/iceaccess_server
new file mode 100755
index 0000000..bc98560
--- /dev/null
+++ b/iceaccess_server
@@ -0,0 +1,103 @@
+#!/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";
+}
diff --git a/iceaccessd b/iceaccessd
new file mode 100644
index 0000000..b47e3db
--- /dev/null
+++ b/iceaccessd
@@ -0,0 +1,82 @@
+#!/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);
+}
+
diff --git a/icecounter.cgi b/icecounter.cgi
new file mode 100755
index 0000000..604f17b
--- /dev/null
+++ b/icecounter.cgi
@@ -0,0 +1,110 @@
+#!/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);
+}
diff --git a/icelog.conf b/icelog.conf
new file mode 100644
index 0000000..058c8fc
--- /dev/null
+++ b/icelog.conf
@@ -0,0 +1,2 @@
+# DBI datasource, username and password
+($dsn, $username, $password) = ('DBI:mysql:icelog', 'user', 'password');
-- 
2.11.0