#!/usr/bin/perl -w

# Display the contents of a radacct database.
# Loosely modelled on Miquel van Smoorenburg's last.c

# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version
# 2 of the License, or (at your option) any later version.

# Frank D. Cringle <fdc@ciwe.ping.de>, 1999-09-10

# v0.02, 1999-09-12, take account of AcctDelayTime,
#                    fix duration formatting,
#                    show sessions with stop but no start record,
#                    correct 'radacct begins' message.

# v0.03, 1999-09-14, only show old sessions if a time range
#                    was requested,
#                    fix typo in calculation of duration > 1day.

use strict;
use DBI;
use Socket;

################################################################
# Customize these values

my $dbname = 'radacct';
my $dbuser = 'nobody';
my $dbpass = '';

# where to find nas short names
my $naslist = '/etc/raddb/naslist';

# remove initial capital letters (such as C, D, P) from username
my $strip_prefix = 1;

################################################################

my $Version = '0.02';

my $have_dates = 1;
eval { require Time::ParseDate; import Time::ParseDate 'parsedate' };
$have_dates = 0 if $@;

sub usage {
    print <<EOT;
$0 Version $Version

Usage:
$0 [ -d ] [ -r ] [ -num | -n num ] [ username | line | ip-adr ... ]

-d means lookup host names in dns,
-r gives raw unformatted output (no limit on field widths),
num limits output to (approximately) the 'num' most recent logins,
line is nas-name/line-number.  line-number missing means all lines,
username may contain SQL wild-cards ( _ % )

Examples:
$0          -- show all logins
$0 harry    -- show user harry\'s logins
$0 h%       -- show all logins by users whose name begins with h
$0 -10 pm/5 -- show the last 10 logins on portmaster line 5
$0 pm/      -- show all portmaster logins
EOT
    exit 0 unless $have_dates;
print <<EOT;


Oh, good.  Time::ParseDate is installed on this machine, so these
extra goodies are available:

You can limit the output to a day or a specified time-span.

-o {date} this day only
-f {date} start of time period
-t {date} end of time period

Dates are specified as described in "perldoc Time::ParseDate".

Examples:
$0 -o yesterday
$0 -o "last tuesday"
$0 -f "thursday midnight" -t "10am yesterday"
$0 -f "1999-09-09 10:00" -t "1999-09-09 14:00"

If only one of -f/-t is specified, the other defaults to the
oldest/most recent entry in the database.
EOT
    exit 0;
}

my %nas;
if ($naslist and open IN, $naslist) {
    while (<IN>) {
	if (/^([\w.]+)\s+([\w.-]+)/) {
	    my $ip = sprintf("%d.%d.%d.%d",
			     unpack('C4', gethostbyname($1)));
	    $nas{$ip} = "$2/";
	}
    }
    close IN;
}

my $where = '';
my $where_user = '';
my $where_line = '';
my $where_ip = '';
my $opts = 1;
my($opt_d,$opt_f,$opt_r,$opt_t);
my $limit = '';

# There is a certain amount of "do what I mean" about this.
# Usernames, lines and IP numbers are or'ed togther within each
# class and then the classes are and'ed.
while (my $arg = shift) {
    if ($opts and $have_dates and $arg =~ /^-([fot])(.*)/) {
	my($x,$v) = ($1,$2);
	$v = shift unless $v;
	$v .= " midnight" if $x eq 'o';
	my $t = parsedate($v, PREFER_PAST=>1);
	die "Can't parse $v\n" if $t == 0;
	$x eq 'f' && ( $opt_f = $t, next );
	$x eq 't' && ( $opt_t = $t, next );
	$opt_f = $t;
	$opt_t = $t+86400;
	next;
    }
    if ($opts and $arg =~ /^-([dr])(.*)$/) {
	unshift @ARGV, "-$2" if $2;
	$1 eq 'd' && $opt_d++;
	$1 eq 'r' && $opt_r++;
	next;
    }
    if ($opts and $arg =~ /^-(n|\d+)(\d*)$/) {
	$limit = $1;
	$limit = ($2 || shift) if $1 eq 'n';
	$limit = ($limit+1)*2;
	next;
    }
    usage($arg) if $opts and $arg =~ /^-/;
    $opts = 0;	
    if ($arg =~ /^\d+\.\d+\.\d+\.\d+$/) {
	$where_ip .= "FramedIPAddress='$arg' or ";
	next;
    }
    if ($arg =~ m{(.+)/(.*)}) {
	my $nasip;
	while (my($k,$v) = each %nas) {
	    if ($v eq "$1/") {
		$nasip = $k;
		last;
	    }
	}
	unless ($nasip) {
	    warn "unknown access server: $arg\n";
	    next;
	}
	if ($2 ne '') {
	    $where_line .= "(NASIPAddress='$nasip' and NASPortId='$2') or ";
	}
	else {
	    $where_line .= "NASIPAddress='$nasip' or ";
	}
	next;
    }
    if ($arg =~ /[%_]/) {
	$where_user .= "UserName like '$arg' or ";
    }
    else {
	$where_user .= "UserName='$arg' or ";
    }
}

$where .= "Tstamp >= $opt_f and " if $opt_f;
$where .= "Tstamp <= $opt_t and " if $opt_t;

for ($where_ip, $where_line, $where_user) {
    next unless $_;
    s/ or $//;
    $where .= "($_) and ";
}
if ($where) {
    $where =~ s/ and $//;
    $where = "where $where";
}
if ($limit) {
    $limit = "limit $limit";
}

my $lastdate = 0;
$| = 1;

sub showdate {
    my $d = shift || $lastdate;
    substr scalar localtime($d), 0, 16;
}

my($db,$sth);
$SIG{INT} = $SIG{QUIT} = sub {
    $sth->finish if $sth;
    $db->disconnect if $db;
    print "\nInterrupted ", showdate(), "\n";
    exit 1;
};

$db = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,
		   { PrintError => 0, AutoCommit => 1 } )
  or die $DBI::errstr, "\n";

$sth = $db->prepare(<<EOT);
select AcctSessionId, UserName, NASIPAddress, NASPortId,
       AcctStatusType, AcctSessionTime, FramedIPAddress,
       AcctDelayTime, Tstamp
       from radacct $where
       order by RadacctID desc $limit
EOT

$sth and $sth->execute or die $db->errstr;

my %Session;

while (my $r = $sth->fetchrow_hashref) {
    $lastdate = $r->{Tstamp} - ($r->{AcctDelayTime}||0);
    if ($r->{AcctStatusType} eq 'Start') {
	showline($r, $Session{$r->{NASIPAddress}.$r->{AcctSessionId}});
	delete $Session{$r->{NASIPAddress}.$r->{AcctSessionId}};
	next;
    }
    elsif ($r->{AcctStatusType} eq 'Stop') {
	$Session{$r->{NASIPAddress}.$r->{AcctSessionId}} = $r;
	next;
    }
    else {
	warn "Don't know how to handle AcctStatusType ",
	$r->{AcctStatusType}, "\n";
    }
}

my @oldies = values %Session;
if (@oldies and ($opt_f or $opt_t)) {
    $sth = $db->prepare(<<EOT);
select AcctSessionId, UserName, NASIPAddress, NASPortId,
       AcctStatusType, AcctSessionTime, FramedIPAddress,
       AcctDelayTime, Tstamp
       from radacct where UserName=? and AcctStatusType='Start' and
       AcctSessionId=? and NASIPAddress=?
EOT
    print "\nSessions that ended in, but started before, the selected period:\n";
    foreach my $r (sort { $a->{Tstamp} <=> $b->{Tstamp} } @oldies) {
	$sth->execute($r->{UserName}, $r->{AcctSessionId}, $r->{NASIPAddress});
	if ($sth->rows) {
	    my $start = $sth->fetchrow_hashref;
	    showline($start, $r);
	    next;
	}
	showline(undef, $r);
    }
}

$sth = $db->prepare('select Tstamp from radacct order by RadacctID limit 1')
  and $sth->execute or die $db->errstr;

$lastdate = ($sth->fetchrow_array)[0];
print "\n$dbname begins ", scalar localtime($lastdate), "\n";
$sth->finish;
$db->disconnect;
exit 0;

sub showline {
    my($start,$stop) = @_;
    my $logout = 'still logged in ';
    if ($stop) {
	$logout = showdate($stop->{Tstamp} - ($stop->{AcctDelayTime}||0));
	my $duration = $stop->{AcctSessionTime};
	if ($opt_r) {
	    $logout .= " ($duration)";
	}
	elsif ($duration > 86400) {
	    $logout .= sprintf(" (%d+%02d:%02d)",
			       $duration/86400,
			       ($duration/3600)%24,
			       ($duration/60)%60);
	}
	else {
	    $logout .= sprintf(" (%02d:%02d)",
			       ($duration/3600)%24,
			       ($duration/60)%60);
	}
    }
    my $login = '???? ';
    if ($start) {
	$login = showdate($start->{Tstamp} - ($start->{AcctDelayTime}||0));
    }
    if (substr($login, 0, 11) eq substr($logout, 0, 11)) {
	$logout = substr($logout, 11);
    }
    my $r = $stop ? $stop : $start;
    my $line = '?';
    $line = $nas{$r->{NASIPAddress}} if exists $nas{$r->{NASIPAddress}};
    $line .= $r->{NASPortId};
    my $name = $r->{UserName};
    $name =~ s/^[A-Z]// if $strip_prefix;
    my $host = $r->{FramedIPAddress};
    if ($opt_d and $host) {
	my $h = gethostbyaddr(inet_aton($host), AF_INET);
	$host = $h if $h;
    }
    my $fmt = "%-16.16s %-12.12s %-16.16s %s - %s\n";
    $fmt = "%s %s %s %s - %s\n" if $opt_r;
    printf($fmt, $name, $line, $host, $login, $logout);
}