#!/usr/bin/env perl
#Juan J. Fernandez
##########################
#Insert Mikrotik log to DB
##########################

use DBI;

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

my @abbr = qw( Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec );
if ($hour > 11) {
  $ampm = "PM";
} else {$ampm = "AM";}

$sep = "_"; #separar fecha del file

#year en dos digitos
$year += 1900;
$year = sprintf("%02d", $year % 100);

#today
my $fecha = "$mday" . "/" . "$abbr[$mon]" . "/" . "$year";
my $file = "$mday" . $sep . "$abbr[$mon]" . $sep . "$hour" . "$ampm" . ".txt";

#ahora van rutinas de expreciones regulares para insertar
#la informacion en las tablas
my $driver = 'DBI:mysql';
my $database = "mikrotik_db";
my $user = 'root';
my $password = '2011@Sagrado';
my $host = 'localhost';

my $dbh = DBI->connect("$driver:$database:$host","$user","$password")
            or die "Can't connect: " . DBI->errstr;

open(my $FH, "<", "$file")
  or die "Could not open filehandle: $!\n";

my $NASip;
my $AccessRequest;
my @MacAddress;
my @ConnectionType;
my @user;
my $count = 0;
my $hora;
my $FromIP;

while(<$FH>) {
##########################################################################
  if (/LOG Received at (...) (..?) (\d\d[:]\d\d[:]\d\d).*Access Request/) {
     $AccessRequest = 1;
     $hora = $3;
  }
    if (/Computer.*([0-9A-F]{2}[:][0-9A-F]{2}[:][0-9A-F]{2}[:][0-9A-F]{2}[:][0-9A-F]{2}[:][0-9A-F]{2})$/) {
      $MacAddress[$count] = $1 if $AccessRequest;
    }
    if (/Username --------------> (\w*)/) {
      $user[$count] = $1 if $AccessRequest;
    }
    if (/From IP.*(\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)/){
      $FromIP = $1 if $AccessRequest;
    }
    if (/NAS IP Address.*(\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)/) { #ultimo sub-segmento del segmento Access Request
      $NASip = $1 if $AccessRequest;
      $count++;
      $AccessRequest = "";
    }

    #Agregar a base de datos
    if($MacAddress[$count] and $user[$count] and $FromIP and $hora){
    $request = 'Sending RADIUS authentication request / Access Request';
    $query = "INSERT INTO event_log(username,macaddress,fecha,hora,ip,evento) VALUES(?,?,?,?,?,?)";
    my $sth=$dbh->prepare($query)
      or die "unable to make the request to the db\n" . DBI->errstr;
    $sth->execute($user[$count],$MacAddress[$count],$fecha,$hora,$FromIP,$request);
    $FromIP="";

     }
##########################################################################
if(@MacAddress){
  if (/LOG Received at ... ..? (\d\d[:]\d\d[:]\d\d)\s(\w+).*(\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b) logged out\s(\w+ \w+)/) {
    my $evento = "logged out $4";print "done\n";
    my $currentCount = $count - 1;
    $query = "INSERT INTO event_log(username,macaddress,fecha,hora,ip,evento) VALUES(?,?,?,?,?,?)";
    my $sth=$dbh->prepare($query)
      or die "unable to make the request to the db\n" . DBI->errstr;
    $sth->execute($2,$MacAddress[$currentCount],$fecha,$1,$3,$evento);
  }}
if(@MacAddress){
  if (/LOG Received at ... ..? (\d\d[:]\d\d[:]\d\d)\s(\w+) failed to login with MT Host IP (\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)/)
  {
      my $evento = "FAILED LOGGING ATTEMPT";print "failed attempt done\n";
      my $currentCount = $count - 1;
      $query = "INSERT INTO event_log(username,macaddress,fecha,hora,ip,evento) VALUES(?,?,?,?,?,?)";
      my $sth=$dbh->prepare($query) or die "unable to make the request to the db\n" . DBI->errstr;
      $sth->execute($2,$MacAddress[$currentCount],$fecha,$1,$3,$evento);
  }}
if(@MacAddress){
  if(/LOG Received at ... ..? (\d\d[:]\d\d[:]\d\d)\s(\w+) is trying to log in to the LAN through the Web interface from (\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)/)
  {
     my $evento = "trying yo log in to the LAN from the Web interface";
     my $currentCount = $count - 1;
     $query = "INSERT INTO event_log(username,macaddress,fecha,hora,ip,evento) VALUES(?,?,?,?,?,?)";
     my $sth=$dbh->prepare($query) or die "unable to make the request to the db\n" . DBI->errstr;
     $sth->execute($2,$MacAddress[$currentCount],$fecha,$1,$3,$evento);
  }}
}

$dbh->disconnect();