#!/usr/bin/perl
#
# Log failed logins in the sql database
# Works with mysql and postgresql
# It will read the sql parameters from the admin.conf file
#
# Usage:
# log_badlogins <radius.log> [<admin.conf>] [all]
#
# Defaults:
# radius.log: none
# admin.conf: /usr/local/dialup_admin/conf/admin.conf
# all: no. Go to the end of the file. Don't read it all.
use Date::
Manip qw(ParseDate UnixDate
);
use Digest::MD5;
$|=1;
$file=shift||'none';
#$conf=shift||'/usr/local/etc/dialupadmin/conf/admin.conf';
$conf=shift||'/usr/local/www/apache22/data/dialupadmin/conf/admin.conf';
$all_file=shift||'no';
#
# Uncomment to force inserts even if there are sql errors. That can
# help in case there is one sql query which stops the whole failed
# logins logging system from working
#$force=1;
#
#
# CHANGE THESE TO MATCH YOUR SETUP
#
#$regexp = 'from client localhost port 135|from client blabla ';
$tmpfile='/var/tmp/sql.input';
#
#
or die "Could not open configuration file\n";
while(<CONF>){
($key,
$val)=
(split /:\
s*/,
$_);
# Fixme : recursivly solve %{.*} replacement for $val
# Fixme: Conf should be put in an associative array
$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');
}
open CLIENTS,
"<$clients" or die "Could not open $clients file\n";
while(<CLIENTS>){
$client_short = $val if ($key eq 'shortname');
} else {
if (/\{/) {
s/.*client\
s+
(&
#91;^\s]*)\s+\{.*$/\1/; if (/^\d+\.\d+\.\d+\.\d+/) {
$client = $_;
} else {
if (/\./ || /localhost/) {
$name = $_ ;
} else {
$name = $_.".".$domain;
}
($a,
$b,
$c,
$d)=
unpack('C4',
$addr);
$client = "$a.$b.$c.$d";
#DEBUG# print $name." = ".$client."\n";
}
} else {
if (/\}/) {
$client_array{$client_short} .= $client;
}
}
}
}
$realm_del = '@' if ($realm_del eq '');
$realm_for = 'suffix' if ($realm_for eq '');
$pass = (!$sql_password) ? '' : "-p$sql_password";
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 "Could not find sql binary. Please make sure that the \$sqlcmd variable points to the right location\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 '');
or die "Could not open file $file\n";
seek LOG,
0,
2 if ($all_file eq
'no');
for(;;){
while(<LOG>){
$do=0;
next if ($regexp ne '' && !/$regexp/);
if ($_ ne ''){
$user = $nas = $port = $caller = '-';
if (/Login incorrect/){
if (/Login incorrect \((.+?)\):/){
$cause = "Login-Incorrect ($1)";
}else{
$cause='Login-Incorrect';
}
$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 / : /,
$_)&
#91;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)&
#91;1,3]; if ($2 =~ /cli (.+?)$/){
$caller = $1;
}
}
elsif (/\[([\w\-\.\!\@\s]+?)\/.+?\]\s+\(from (.+?)\)/){
$user = $1;
($nas,
$port) =
(split /\
s+/,$
2)&
#91;1,3]; if ($2 =~ /cli (.+?)$/){
$caller = $1;
}
}
$user =~
s/&
#91;^\w\-\.\d\!\@\s]//g; $nas =~
s/&
#91;^\w\.\-]//g; $addr = $client_array{$nas};
if ($user ne '' && $realm_strip eq 'yes'){
($one,
$two) =
(split /
$realm_del/,
$user)&
#91;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;
#DEBUG# print "INSERT INTO $sql_accounting_table (UserName,AcctUniqueId,NASIPAddress,NASPortId,AcctStartTime,AcctStopTime,AcctSessionTime,AcctInputOctets,AcctOutputOctets,CallingStationId,AcctTerminateCause) VALUES ('$user','$uniqueid','$addr','$port','$time','$time','0','0','0','$caller','$cause');\n";
print TMP
"INSERT INTO $sql_accounting_table (UserName,AcctUniqueId,NASIPAddress,NASPortId,AcctStartTime,AcctStopTime,AcctSessionTime,AcctInputOctets,AcctOutputOctets,CallingStationId,AcctTerminateCause) VALUES ('$user','$uniqueid','$addr','$port','0','0','0','$caller','$cause');\n";
$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`;
$exit = $? >> 8;
$delete{$server} = ($exit == 0) ? 1 : 0;
print "ERROR: SQL query failed for host $server\n" if ($exit !=
0);
}
}
}
}
if ($all_file ne 'once') {
} else {
}
}
Linear Mode
