dialup_admin/bin/log_badlogins

   1 #!/usr/bin/perl
   2 #
   3 # Log failed logins in the sql database
   4 # Works with mysql, postgresql and Oracle
   5 # It will read the sql parameters from the admin.conf file
   6 #
   7 # Usage:
   8 # log_badlogins <radius.log> [<admin.conf>] [all]
   9 #
  10 # Defaults:
  11 # radius.log: none
  12 # admin.conf: /usr/local/dialup_admin/conf/admin.conf
  13 # all:        no. Go to the end of the file. Don't read it all.
  14
  15 use Date::Manip qw(ParseDate UnixDate);
  16 use Digest::MD5;
  17 use File::Temp;
  18 $|=1;
  19
  20 $file=shift||'none';
  21 $conf=shift||'/usr/local/dialup_admin/conf/admin.conf';
  22 $all_file=shift||'no';
  23 #
  24 # Uncomment to force inserts even if there are sql errors. That can
  25 # help in case there is one sql query which stops the whole failed
  26 # logins logging system from working
  27 #$force=1;
  28 #
  29 #
  30 # CHANGE THESE TO MATCH YOUR SETUP
  31 #
  32 #$regexp = 'from client localhost port 135|from client blabla ';
  33 $tmpdir=tempdir( CLEANUP => 1 );
  34 $tmpfile="$tmpdir/sql.input";
  35 #
  36 $verbose = 0;
  37 #
  38
  39 open CONF, "<$conf"
  40         or die "Could not open configuration file\n";
  41 while(<CONF>){
  42         chomp;
  43         ($key,$val)=(split /:\s*/,$_);
  44         # Fixme : recursivly solve %{.*} replacement for $val
  45         # Fixme: Conf should be put in an associative array
  46         $sql_type = $val if ($key eq 'sql_type');
  47         $sql_server = $val if ($key eq 'sql_server');
  48         $sql_username = $val if ($key eq 'sql_username');
  49         $sql_password = $val if ($key eq 'sql_password');
  50         $sql_database = $val if ($key eq 'sql_database');
  51         $sql_accounting_table = $val if ($key eq 'sql_accounting_table');
  52         $realm_strip = $val if ($key eq 'general_strip_realms');
  53         $realm_del = $val if ($key eq 'general_realm_delimiter');
  54         $realm_for = $val if ($key eq 'general_realm_format');
  55         $domain = $val if ($key eq 'general_domain');
  56         $sql_timeout = $val if ($key eq 'sql_connect_timeout');
  57         $sql_extra = $val if ($key eq 'sql_extra_servers');
  58         $sqlcmd = $val if ($key eq 'sql_command');
  59         $clients= $val if ($key eq 'general_clients_conf');
  60 }
  61 close CONF;
  62
  63 open CLIENTS, "<$clients"
  64         or die "Could not open $clients file\n";
  65 while(<CLIENTS>){
  66         chomp;
  67         s/^\s*//g;
  68         s/\s*#.*//g;
  69         if (!/^\s*$/ && /=/) {
  70                 ($key,$val)=(split /\s*=\s*/,$_);
  71                 $client_short = $val if ($key eq 'shortname');
  72         } else {
  73                 if (/\{/) {
  74                         s/.*client\s+([^\s]*)\s+\{.*$/\1/;
  75                         if (/^\d+\.\d+\.\d+\.\d+/) {
  76                                 $client = $_;
  77                         } else {
  78                                 if (/\./ || /localhost/) {
  79                                         $name = $_ ;
  80                                 } else {
  81                                         $name = $_.".".$domain;
  82                                 }
  83                                 $addr = gethostbyname $name;
  84                                 ($a,$b,$c,$d)=unpack('C4',$addr);
  85                                 $client = "$a.$b.$c.$d";
  86 #DEBUG#                         print $name." = ".$client."\n";
  87                         }
  88                 } else {
  89                         if (/\}/) {
  90                                 $client_array{$client_short} .= $client;
  91                         }
  92                 }
  93         }
  94 }
  95 close CLIENTS;
  96
  97 $realm_del = '@' if ($realm_del eq '');
  98 $realm_for = 'suffix' if ($realm_for eq '');
  99 if ($sql_type eq 'mysql'){
 100         $pass = (!$sql_password) ? '' : "-p$sql_password";
 101 }
 102 else{
 103         $pass = $sql_password;
 104 }
 105 $pass =~ s/(\W)/\\$1/g;
 106 die "SQL server not defined\n" if ($sql_server eq '');
 107
 108 die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
 109 die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd);
 110
 111 $opt = "";
 112 $opt = "-O connect_timeout=$sql_timeout" if ($sql_timeout);
 113 $opt .= " -f" if ($force);
 114 @servers = (split /\s+/,$sql_extra) if ($sql_extra ne '');
 115 unshift @servers, $sql_server;
 116
 117 open LOG, "<$file"
 118         or die "Could not open file $file\n";
 119 if ($verbose > 1) { print STDOUT "DEBUG: Opened $file\n" }
 120
 121 seek LOG, 0, 2 if ($all_file eq 'no');
 122 for(;;){
 123         while(<LOG>){
 124                 if ($verbose > 1) { print STDOUT "DEBUG: Reading $file\n" }
 125                 $do=0;
 126                 chomp;
 127                 next if ($regexp ne '' && !/$regexp/);
 128                 if ($_ ne ''){
 129                         $user = $nas = $port = $caller = '-';
 130                         if (/Login incorrect/){
 131                                 if (/Login incorrect \((.+?)\):/){
 132                                         $cause = "Login-Incorrect ($1)";
 133                                         if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect ($1)\n" }
 134                                 }else{
 135                                         $cause='Login-Incorrect';
 136                                         if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect\n" }
 137                                 }
 138                                 $do=1;
 139                         }
 140                         elsif (/Invalid user/){
 141                                 if (/Invalid user \((.+?)\):/){
 142                                         $cause = "Invalid-User ($1)";
 143                                 }else{
 144                                         $cause='Invalid-User';
 145                                 }
 146                                 $do=1;
 147                         }
 148                         elsif (/Multiple logins/){
 149                                 if (/MPP attempt/){
 150                                         $cause='Multiple-Logins (MPP Attempt)';
 151                                 }else{
 152                                         $cause='Multiple-Logins';
 153                                 }
 154                                 $do=1;
 155                         }
 156                         elsif (/(Outside allowed timespan \(.+?\)):/){
 157                                 $cause = "$1";
 158                                 $do=1;
 159                         }
 160                         if ($do){
 161                                 $date = (split / : /,$_)[0];
 162                                 $date2 = ParseDate($date);
 163                                 if ($date2){
 164                                         ($year,$mon,$mday,$hour,$min,$sec)=UnixDate($date2,'%Y','%m','%d','%H','%M','%S');
 165                                 }
 166                                 $time = "$year-$mon-$mday $hour:$min:$sec";
 167                                 if (/\[([\w\-\.\!\@\s]+?)\]\s+\(from (.+?)\)/){
 168                                         $user = $1;
 169                                         ($nas,$port) = (split /\s+/,$2)[1,3];
 170                                         if ($2 =~ /cli (.+?)$/){
 171                                                 $caller = $1;
 172                                         }
 173                                 }
 174                                 elsif (/\[([\w\-\.\!\@\s]+?)\/.+?\]\s+\(from (.+?)\)/){
 175                                         $user = $1;
 176                                         ($nas,$port) = (split /\s+/,$2)[1,3];
 177                                         if ($2 =~ /cli (.+?)$/){
 178                                                 $caller = $1;
 179                                         }
 180                                 }
 181                                 $caller='' if (!defined($caller));
 182                                 $user =~s/[^\w\-\.\d\!\@\s]//g;
 183                                 $nas =~s/[^\w\.\-]//g;
 184                                 $port =~s/[^\d]//g;
 185                                 $addr = $client_array{$nas};
 186                                 if ($user ne '' && $realm_strip eq 'yes'){
 187                                         ($one,$two) = (split /$realm_del/, $user)[0,1];
 188                                         if ($two ne ''){
 189                                                 $user = ($realm_for eq 'suffix') ? $one : $two;
 190                                         }
 191                                 }
 192                                 foreach $server (@servers){
 193                                         unlink "$tmpfile.$server" if ($delete{$server});
 194                                         open TMP, ">>$tmpfile.$server"
 195                                                 or die "Could not open temporary file\n";
 196                                         $ctx = Digest::MD5->new;
 197                                         $ctx->add($user);
 198                                         $ctx->add($addr);
 199                                         $ctx->add($port);
 200                                         $ctx->add($time);
 201                                         $ctx->add('badlogin');
 202                                         $uniqueid = $ctx->hexdigest;
 203                                         print TMP "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle');
 204 #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";
 205                                         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";
 206                                         close TMP;
 207                                         $command = "$sqlcmd -h$server $opt -u$sql_username $pass $sql_database <$tmpfile.$server" if ($sql_type eq 'mysql');
 208                                         $command = "$sqlcmd  -U $sql_username -f $tmpfile.$server $sql_database" if ($sql_type eq 'pg');
 209                                         $command = "$sqlcmd  $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle');
 210                                         $command = "$sqlcmd '$server' '$sql_port' '' '$sql_username' '$sql_pass' <$tmpfile.$server" if ($sql_type eq 'sqlrelay');
 211                                         if ($verbose > 1) { print STDOUT "DEBUG: Sending datafile $tmpfile.$server to \"$sql_type\" database\n" }
 212                                         `$command`;
 213                                         if ($verbose > 1) { print STDOUT "DEBUG: Sent data to \"$sql_type\" database\n" }
 214
 215                                         $exit = $? >> 8;
 216                                         $delete{$server} = ($exit == 0) ? 1 : 0;
 217                                         print STDERR "ERROR: SQL query failed for host $server\n" if ($exit != 0);
 218                                 }
 219                         }
 220                 }
 221         }
 222         if ($all_file ne 'once') {
 223                 sleep 2;
 224                 seek LOG,0,1;
 225         } else {
 226                 exit(0);
 227         }
 228 }