use Date::Manip qw(ParseDate UnixDate);
use Digest::MD5;
use File::Temp;
$|=1;
$file=shift||'none';
$conf=shift||'/usr/local/dialup_admin/conf/admin.conf';
$all_file=shift||'no';
$tmpdir=tempdir( CLEANUP => 1 );
$tmpfile="$tmpdir/sql.input";
$verbose = 0;
open CONF, "<$conf"
or die "Could not open configuration file\n";
while(<CONF>){
chomp;
($key,$val)=(split /:\s*/,$_);
$sql_type = $val if ($key eq 'sql_type');
$sql_server = $val if ($key eq 'sql_server');
$sql_username = $val if ($key eq 'sql_username');
$sql_password = $val if ($key eq 'sql_password');
$sql_database = $val if ($key eq 'sql_database');
$sql_accounting_table = $val if ($key eq 'sql_accounting_table');
$realm_strip = $val if ($key eq 'general_strip_realms');
$realm_del = $val if ($key eq 'general_realm_delimiter');
$realm_for = $val if ($key eq 'general_realm_format');
$domain = $val if ($key eq 'general_domain');
$sql_timeout = $val if ($key eq 'sql_connect_timeout');
$sql_extra = $val if ($key eq 'sql_extra_servers');
$sqlcmd = $val if ($key eq 'sql_command');
$clients= $val if ($key eq 'general_clients_conf');
}
close CONF;
open CLIENTS, "<$clients"
or die "Could not open $clients file\n";
while(<CLIENTS>){
chomp;
s/^\s*//g;
s/\s* if (!/^\s*$/ && /=/) {
($key,$val)=(split /\s*=\s*/,$_);
$client_short = $val if ($key eq 'shortname');
} else {
if (/\{/) {
s/.*client\s+([^\s]*)\s+\{.*$/\1/;
if (/^\d+\.\d+\.\d+\.\d+/) {
$client = $_;
} else {
if (/\./ || /localhost/) {
$name = $_ ;
} else {
$name = $_.".".$domain;
}
$addr = gethostbyname $name;
($a,$b,$c,$d)=unpack('C4',$addr);
$client = "$a.$b.$c.$d";
}
} else {
if (/\}/) {
$client_array{$client_short} .= $client;
}
}
}
}
close CLIENTS;
$realm_del = '@' if ($realm_del eq '');
$realm_for = 'suffix' if ($realm_for eq '');
if ($sql_type eq 'mysql'){
$pass = (!$sql_password) ? '' : "-p$sql_password";
}
else{
$pass = $sql_password;
}
$pass =~ s/(\W)/\\$1/g;
die "SQL server not defined\n" if ($sql_server eq '');
die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd);
$opt = "";
$opt = "-O connect_timeout=$sql_timeout" if ($sql_timeout);
$opt .= " -f" if ($force);
@servers = (split /\s+/,$sql_extra) if ($sql_extra ne '');
unshift @servers, $sql_server;
open LOG, "<$file"
or die "Could not open file $file\n";
if ($verbose > 1) { print STDOUT "DEBUG: Opened $file\n" }
seek LOG, 0, 2 if ($all_file eq 'no');
for(;;){
while(<LOG>){
if ($verbose > 1) { print STDOUT "DEBUG: Reading $file\n" }
$do=0;
chomp;
next if ($regexp ne '' && !/$regexp/);
if ($_ ne ''){
$user = $nas = $port = $caller = '-';
if (/Login incorrect/){
if (/Login incorrect \((.+?)\):/){
$cause = "Login-Incorrect ($1)";
if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect ($1)\n" }
}else{
$cause='Login-Incorrect';
if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect\n" }
}
$do=1;
}
elsif (/Invalid user/){
if (/Invalid user \((.+?)\):/){
$cause = "Invalid-User ($1)";
}else{
$cause='Invalid-User';
}
$do=1;
}
elsif (/Multiple logins/){
if (/MPP attempt/){
$cause='Multiple-Logins (MPP Attempt)';
}else{
$cause='Multiple-Logins';
}
$do=1;
}
elsif (/(Outside allowed timespan \(.+?\)):/){
$cause = "$1";
$do=1;
}
if ($do){
$date = (split / : /,$_)[0];
$date2 = ParseDate($date);
if ($date2){
($year,$mon,$mday,$hour,$min,$sec)=UnixDate($date2,'%Y','%m','%d','%H','%M','%S');
}
$time = "$year-$mon-$mday $hour:$min:$sec";
if (/\[([\w\-\.\!\@\s]+?)\]\s+\(from (.+?)\)/){
$user = $1;
($nas,$port) = (split /\s+/,$2)[1,3];
if ($2 =~ /cli (.+?)$/){
$caller = $1;
}
}
elsif (/\[([\w\-\.\!\@\s]+?)\/.+?\]\s+\(from (.+?)\)/){
$user = $1;
($nas,$port) = (split /\s+/,$2)[1,3];
if ($2 =~ /cli (.+?)$/){
$caller = $1;
}
}
$caller='' if (!defined($caller));
$user =~s/[^\w\-\.\d\!\@\s]//g;
$nas =~s/[^\w\.\-]//g;
$port =~s/[^\d]//g;
$addr = $client_array{$nas};
if ($user ne '' && $realm_strip eq 'yes'){
($one,$two) = (split /$realm_del/, $user)[0,1];
if ($two ne ''){
$user = ($realm_for eq 'suffix') ? $one : $two;
}
}
foreach $server (@servers){
unlink "$tmpfile.$server" if ($delete{$server});
open TMP, ">>$tmpfile.$server"
or die "Could not open temporary file\n";
$ctx = Digest::MD5->new;
$ctx->add($user);
$ctx->add($addr);
$ctx->add($port);
$ctx->add($time);
$ctx->add('badlogin');
$uniqueid = $ctx->hexdigest;
print TMP "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle');
print TMP "INSERT INTO $sql_accounting_table (UserName,AcctSessionId,AcctUniqueId,NASIPAddress,NASPortId,AcctStartTime,AcctStopTime,AcctSessionTime,AcctInputOctets,AcctOutputOctets,CallingStationId,AcctTerminateCause) VALUES ('$user','$uniqueid','$uniqueid','$addr','$port','$time','$time','0','0','0','$caller','$cause');\n";
close TMP;
$command = "$sqlcmd -h$server $opt -u$sql_username $pass $sql_database <$tmpfile.$server" if ($sql_type eq 'mysql');
$command = "$sqlcmd -U $sql_username -f $tmpfile.$server $sql_database" if ($sql_type eq 'pg');
$command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle');
$command = "$sqlcmd '$server' '$sql_port' '' '$sql_username' '$sql_pass' <$tmpfile.$server" if ($sql_type eq 'sqlrelay');
if ($verbose > 1) { print STDOUT "DEBUG: Sending datafile $tmpfile.$server to \"$sql_type\" database\n" }
`$command`;
if ($verbose > 1) { print STDOUT "DEBUG: Sent data to \"$sql_type\" database\n" }
$exit = $? >> 8;
$delete{$server} = ($exit == 0) ? 1 : 0;
print STDERR "ERROR: SQL query failed for host $server\n" if ($exit != 0);
}
}
}
}
if ($all_file ne 'once') {
sleep 2;
seek LOG,0,1;
} else {
exit(0);
}
}