Average_Calculate

#!/usr/bin/perl -w

#==================================================================================
# Auteur / Author   : kisslo
# Date (little endian)  : 25/05/2011
# Fichier / file name   : average_db_data.pl
# Description       : Calculate average latency per IP (incoming and outgoing)
#==================================================================================

use strict;
use DBI;
use Term::UI;
use Term::ReadLine;

my ($sth0,$sth00,$sth1,$sth2,$sth3);        # Used to launch the mysql request (contains the statement)
my ($ip_in,@ip_in,$latency_in,@latency_in); # Scalar and array contening IP / latency
my ($time_in,$average_time_in);         # $time_in -> the latency / $average_time_in -> average of all latency
my (%average_ip_in,%average_ip_out);        # Hash list contains average latency and ip
my ($know_ip,@know_ip);             # Contains the existant IP in average latency table
my $i;                      # Used to count the number of MySQL request during test
my $j;                      # Used to defined data position in an array
my $k = 1;                  # Used to count the number of loop;
my $stat;                   # Boolean, identical ip = 1 . Comparison between ip in average table and ip of an other table
my $fetch_data;                 # Temporary variable to storage after execution handle
my ($connected_ip,@connected_ip);       # Liste of ip connected
my ($valid_ip,@valid_ip);           # Ip with "time to live" valid
my ($date_more,$date_less);         # The intervale to keep the


# ==================== Data for connection to the database ==================== #
my $dbname = 'vision_db';   # Name of the database
my $host = 'localhost';     # Host name / IP
my $login = 'root';     # Login used for connection at the database
my $pass = 'yourpass';      # Password for the database

# ==== Connection sequence ==== #
my $dbh = DBI->connect("dbi:mysql:dbname=$dbname;host=$host;",$login,$pass)
or die "Fail to connect at the database $dbname ! ".DBI::errstr;;
# ============================================================================= #

# ======================= That value(s) can be modified ======================= #
my $numb_timer = 10;    # Number of latency used for latency average
# ============================================================================= #


while (1)
{
    sleep 10;
    print "\nLoop number : $k\n";       # The number of code looping
    $k++;                   # Increment the numb of loop

    @connected_ip = `/home/vision/alex/connected_ip.pl`;    # Recover the connected ip
    chop @connected_ip;

    ##################
    ## PREPARATION  ##
    ##################

    # ================================================== #
    # ========== You can change this value(s) ========== #
    # ================================================== #
        my $numb_time = 10; # The numb of max values to create an average
        my $date_gap = 20;  # Indicate the time duration of conservation of the ip
    # ================================================== #

    $date_more = `date +%s` + $date_gap/2;
    $date_less = `date +%s` - $date_gap/2;

    # === Preparation of the average table : delete all values except the values which are less old than indicate in "$date_gap" === #
    # ============================================================================================================================== #
    $sth0 = $dbh->prepare('DELETE FROM `IpLatencyAverage` WHERE id NOT IN (SELECT * FROM (SELECT `id` FROM `IpLatencyAverage` WHERE unix_time < '.$date_more.' AND unix_time > '.$date_less.') AS tmp0)')
        or die 'Impossible to prepare the request::'.$dbh->errstr();
    $sth0->execute()
        or die 'Error during the execution :: '.$dbh->errstr();

    # === Check ip remaining in the table... === #
    # ========================================== #
    $sth00 = $dbh->prepare('SELECT DISTINCT ip_in FROM IpLatencyAverage')
        or die 'Impossible to prepare the request::'.$dbh->errstr();
    $sth00->execute()
        or die 'Error during the execution :: '.$dbh->errstr();

    # === ...and put them in an array === #
    # =================================== #
    undef @valid_ip;
    while ($valid_ip = $sth00->fetchrow_array())
    {
        push (@valid_ip,$valid_ip);
    }

    #############################
    ## EXECUTION : outgoing ip ##
    #############################

    # === Take all different ip and make an average latency with the last X times indicate in "$numb_time"  === #
    # ========================================================================================================= #
    $sth1 = $dbh->prepare('SELECT DISTINCT IpIn FROM IpVisionOut ORDER BY time DESC LIMIT '.$numb_time)
        or die 'Impossible to prepare the request :: '.$dbh->errstr();
    $sth1->execute()
        or die 'Error during the execution :: '.$dbh->errstr();

    while (@ip_in = $sth1->fetchrow_array())
    {
        $time_in = 0;
        foreach $ip_in (@ip_in)
        {
            $sth2 = $dbh->prepare('SELECT TempsLatenceIp FROM IpVisionOut WHERE IpIn="'.$ip_in.'" ORDER BY id DESC LIMIT '.$numb_timer)
                or die 'Impossible to prepare the request::'.$dbh->errstr();
            $sth2->execute()
                or die 'Error during the execution :: '.$dbh->errstr();

            while (@latency_in = $sth2->fetchrow_array())
            {
                foreach $latency_in (@latency_in)
                {
                    $time_in += $latency_in;
                }
            }
            $average_time_in = $time_in/$numb_timer;
            $average_ip_in{$ip_in} = $average_time_in;
        }
    }

    # === Write the data into the database === #
    # ======================================== #
    foreach $ip_in (sort(keys(%average_ip_in)))
    {
        if ($average_ip_in{$ip_in} != 0)
        {
            if (grep { $_ eq $ip_in } @connected_ip )
            {
                if ( $average_ip_in{$ip_in} >= 2.000000000 )
                {
                    $sth3 = $dbh->prepare('INSERT INTO IpHighAverage (ip_in,latency_average) VALUES ("'.$ip_in.'","'.$average_ip_in{$ip_in}.'")')
                        or die 'Impossible to prepare the request::'.$dbh->errstr();
                    $sth3->execute()
                        or die 'Error during the execution :: '.$dbh->errstr();
                    print "INSERT HIGH -- IP in = $ip_in \t latency = $average_ip_in{$ip_in}\n";    #
                        $sth3->finish();
                }
                if (grep { $_ eq $ip_in } @valid_ip )
                {
                    $sth3 = $dbh->prepare('UPDATE IpLatencyAverage SET latency_average = "'.$average_ip_in{$ip_in}.'",unix_time = "'.`date +%s`.'" WHERE ip_in = "'.$ip_in.'"')
                        or die 'Impossible to prepare the request::'.$dbh->errstr();
                    $sth3->execute()
                        or die 'Error during the execution :: '.$dbh->errstr();
                    `\/home/vision/alex/analysis.pl '$ip_in' '$average_ip_in{$ip_in}'`;
                    print "UPDATE -- IP in = $ip_in \t\t latency = $average_ip_in{$ip_in}\n";   #
                        $sth3->finish();
                }
                else
                {
                    $sth3 = $dbh->prepare('INSERT INTO IpLatencyAverage (ip_in,latency_average,unix_time) VALUES ("'.$ip_in.'","'.$average_ip_in{$ip_in}.'","'.`date +%s`.'")')
                        or die 'Impossible to prepare the request::'.$dbh->errstr();
                    $sth3->execute()
                        or die 'Error during the execution :: '.$dbh->errstr();
                    `\/home/vision/alex/analysis.pl '$ip_in' '$average_ip_in{$ip_in}'`;
                    print "INSERT -- IP in = $ip_in \t\t latency = $average_ip_in{$ip_in}\n";   #
                        $sth3->finish();
                }
            }
        }
    }

    $sth0->finish();
    $sth00->finish();
    $sth1->finish();
    $sth2->finish();
}
$dbh->disconnect();