====== Управление сетью на базе Extreame Summit 24i/48i с помошью telnet ======
 
<note important>свичи выступают в роли NAS</note>

===== Логика работы системы =====

1. В зависимости от статуса абонента применить действие на порту
|Статус абонента|действие|
|Интернет отключен|отключить порт|
|Интернет включен|включить порт|

2. Задать шейпер
Если интернет включен то задать шейпер на порту вх/исх

Эти вещи делает скрипт автоматически по событиям использую протокол telnet и perl

===== Пути к файлам =====

Скачав дистрибутива MikBiLL начиная с версии 2.0.5
вы найдете скрипты в папке 
<code>/DISTR/primer/extreame</code>

Рекомендуем скопировать файлы в папку скриптов админки
путь linux
<code>/var/www/mikbill/admin/sys/scripts</code>
путь bsd
<code>/usr/local/www/mikbill/admin/sys/scripts</code>

===== описание файлов =====
|switches_summit_one.pl|скрипт выполняет действие на кокретном абоненте|
|switches_summit.pl|скрипт выполняет действие на всей сети|



===== Настройка =====

Установите модули на перл или средствами вашей OS или через CPAN
выполнить под root
<code>perl -MCPAN -e 'install DBI DBD::mysql Net::Telnet Data::Dumper'</code>
овтетить на все вопросы и установить модули.

В обоих файлах для начала задайте даныне для доступа к вашей БД с MIkBiLL
Строки 23,24,25,26
в обоих файлах 
switches_summit_one.pl
switches_summit.pl

<code>my $sql_db='mikbill';
my $sql_login='mikbill';
my $sql_pass='password';
my $sql_host='localhost';</code>


Для скрипта switches_summit_one.pl
В строке 183 укажите 
ID типа устройства на котором вы будете запускать данный функционал
Админка-Настройки-Справочники-Типы устройств

Пример для типа устройств с ID=7
<code>if ($swtypeid_input=="7") {</code>

по необходимости замените на Ваш номер


Для скрипта switches_summit.pl
В строке 178 укажите 
ID типа устройства на котором вы будете запускать данный функционал
Админка-Настройки-Справочники-Типы устройств

Пример для типа устройств с ID=7
<code>my $sql="SELECT nameswitch, ip, login, pass , swtypeid , swid  FROM $switches WHERE swtypeid = '7'";</code>

по необходимости замените на Ваш номер

===== Ввод в эксплуатацию =====

После настройки осталось запустить это в работу.

1. Запускаем скрипт switches_summit.pl в cron после снятия абонплат 00:30 будет вполне нормальным временем
2. заносим во все скрипты событий следующие строки (  в конец файла)
для linux
<code>cd /var/www/mikbill/admin/sys/scripts
./switches_summit_one.pl $3 $4 $7 $1 &>/dev/null
</code>
для bsd
<code>cd /usr/local/www/mikbill/admin/sys/scripts
./switches_summit_one.pl $3 $4 $7 $1 &>/dev/null
</code>
===== Исходник switches_summit_one.pl =====
<code>#!/usr/bin/perl

use Net::Telnet;
use DBI;
use strict;
use locale;
use Data::Dumper;
use POSIX;

setlocale(LC_ALL, 'C');


# sw_ip = $ARGV[0];
# sw_port = $ARGV[1];
my $swtypeid_input = $ARGV[2];

my $debug=1;
my $print_switch=1;
my $do_nothing=0;
my $mb=1024;

my $dbh;
my $sql_db='mikbill';
my $sql_login='mikbill';
my $sql_pass='password';
my $sql_host='localhost';
my $acct_table1 = "radacct";
my $acct_table2 = "radacct";
my $packets = "packets";
my $users = "users";
my $switches = "switches";
my $switche_type = "switche_type";
my $switch_uplink = "switch_uplink";


sub sql {
    my $dsn = 'DBI:mysql:'.$sql_db.':'.$sql_host;
    my $dbh=DBI->connect($dsn, $sql_login, $sql_pass) or die "Couldn't connect to database: " . DBI->errstr;
    return $dbh;
}

$dbh=&sql;

sub get_switch_type {

    my $sql="SELECT swtypeid,numports FROM $switche_type WHERE 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my %switchtype_ports;

    while (my @row = $sth->fetchrow_array)
    {
        $switchtype_ports{$row[0]}=$row[1];
    }
    $sth->finish;
    return %switchtype_ports;
};

my %switchtype_ports=&get_switch_type;

my $check_switch_uplink_by_swid_port = sub {
    my ($dbh,$swid,$port) = @_;
    my $sql="SELECT port FROM $switch_uplink WHERE swid=$swid and port=$port";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};

my $check_tarif_by_gid = sub {
    my ($dbh,$gid) = @_;
    my $sql="SELECT gid,speed_rate,speed_burst FROM $packets WHERE gid=$gid";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};

my $check_user_by_swid_port = sub {
    my ($dbh,$swid,$port) = @_;
    my $sql="SELECT uid,deposit,credit,blocked,gid,speed_rate,speed_burst FROM $users WHERE swid=$swid and switchport=port";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};


my $set_port_sw_200_down = sub {
    my ($t,$port) = @_;

    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('disable ports '.$port);
    }
    if ($debug==1){
        print $port." user not found\n";
    }
};

my $do_del_rate_up = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('delete rate-limit net_up_'.$port);
    }
};

my $do_del_rate_down = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('delete rate-limit net_down_'.$port);
    }
};


my $set_port_sw_200_rate_up = sub {
    my ($t,$port,$res_speed_up) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('create rate-limit net_up_'.$port.' access-mask net_up port '.$port.' permit limit '.$res_speed_up.'exceed-action drop');
    }
};

my $set_port_sw_200_rate_down = sub {
    my ($t,$port,$res_speed_down) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('create rate-limit net_down_'.$port.' access-mask net_down egress-port '.$port.' port 25 permit limi '.$res_speed_down.' exceed-action drop');
    }
};

my $set_port_sw_200_up = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('enable ports '.$port);
    }
    if ($debug==1){
        print $port." user found\n";
    }
};


my $create_mask_sw_200 = sub {
    my ($t) = @_;
    if ($do_nothing==0) {

    $t->waitfor('/#:/i');
    $t->print('delete access-mask net_up');
    $t->waitfor('/#:/i');
    $t->print('delete access-mask net_down ');

    $t->waitfor('/#:/i');
    $t->print('create access-mask net_up port');
    $t->waitfor('/#:/i');
    $t->print('create access-mask net_down egress-port port');

    }
};


my $get_switch_uplink_by_swid = sub {
    my ($dbh,$swid) = @_;
    my $sql="SELECT port FROM $switch_uplink WHERE swid=$swid";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my %switchtype_ports_uplink;
    my $i=0;
    while (my @row = $sth->fetchrow_array)
    {
        $switchtype_ports_uplink{$i}=$row[0];
        $i=$i+1;
    }
    $sth->finish;
    return %switchtype_ports_uplink;
};

if ($swtypeid_input=="7") {

my $sql="SELECT nameswitch, ip, login, pass , swtypeid , swid  FROM $switches WHERE ip ='".$ARGV[0]."'";
my $sth = $dbh->prepare($sql);
$sth->execute;
my @row = $sth->fetchrow_array;

    my $swtypeid = $row[4] ;
    my $swid = $row[5];
    my $sw_ip = $row[1];
    my $swtypeid = $row[4] ;
    my $max_port_switch=$switchtype_ports{$swtypeid};

    if ($print_switch==1){
    print "ip: $sw_ip\tnameswitch: $row[0]\tswid:$swid\tports:$max_port_switch\t";
    }



    #Telnet to dev
    my $file = "/home/log/$row[1].log";
    my $t = new Net::Telnet(Timeout => 1, Telnetmode => 0, Errmode => "return", Input_log => $file);
    $t->open(Host => $sw_ip);
    $t->waitfor('/login:/i');
    $t->print($row[2]);
    $t->waitfor('/password:/i');
    $t->print($row[3]);

    if ($print_switch==1){
        print "OK\n";
    }

    my $port_current=$ARGV[1];

        my $check_port=$check_switch_uplink_by_swid_port->($dbh,$swid,$port_current);
        if ($check_port==0) {
            my @check_user=$check_user_by_swid_port->($dbh,$swid,$port_current);
            if (@check_user==0) {
                $set_port_sw_200_down->($t,$port_current);
            }else{
                my $uid=$check_user[0];
                my $deposit=$check_user[1];
                my $credit=$check_user[2];
                my $blocked=$check_user[3];
                my $gid=$check_user[4];
                my $user_speed_up=$check_user[5]/$mb;
                my $user_speed_down=$check_user[6]/$mb;
                my @check_tarif_by_gid=$check_tarif_by_gid->($dbh,$gid);
                my $tarif_speed_up=$check_tarif_by_gid[1]/$mb;
                my $tarif_speed_down=$check_tarif_by_gid[2]/$mb;

                $user_speed_up=sprintf("%.0f",$user_speed_up);
                $user_speed_down=sprintf("%.0f",$user_speed_down);
                $tarif_speed_up=sprintf("%.0f",$tarif_speed_up);
                $tarif_speed_down=sprintf("%.0f",$tarif_speed_down);

                my $res_speed_up=0;
                my $res_speed_down=0;

                if ($user_speed_up!=0) {
                    $res_speed_up=$user_speed_up;
                }else{
                    $res_speed_up=$tarif_speed_up;
                }

                if ($user_speed_down!=0) {
                    $res_speed_down=$user_speed_down;
                }else{
                    $res_speed_down=$tarif_speed_down;
                }
#               print "$res_speed_up \n";
#               print "$res_speed_down \n";

                if ($blocked==1) {
                    $set_port_sw_200_down->($t,$port_current);
                }else{
                    if (($deposit+$credit)<0) {
                    $set_port_sw_200_down->($t,$port_current);
                    }else{
                     $set_port_sw_200_up->($t,$port_current);
                     $do_del_rate_up->($t,$port_current);
                     $do_del_rate_down->($t,$port_current);
                     if ($res_speed_up!=0) {
                     $set_port_sw_200_rate_up->($t,$port_current,$res_speed_up);
                     }
                     if ($res_speed_down!=0) {
                     $set_port_sw_200_rate_down->($t,$port_current,$res_speed_down);
                     }


                    }
                }
            }
        }

$sth->finish;

}

$dbh->disconnect;
</code>

===== Исходник switches_summit.pl =====

<code>#!/usr/bin/perl

use Net::Telnet;
use DBI;
use strict;
use locale;
use Data::Dumper;
use POSIX;

setlocale(LC_ALL, 'C');

my $debug=0;
my $print_switch=1;
my $do_nothing=0;
my $mb=1024;

my $dbh;
my $sql_db='mikbill';
my $sql_login='mikbill';
my $sql_pass='password';
my $sql_host='localhost';
my $acct_table1 = "radacct";
my $acct_table2 = "radacct";
my $packets = "packets";
my $users = "users";
my $switches = "switches";
my $switche_type = "switche_type";
my $switch_uplink = "switch_uplink";


sub sql {
    my $dsn = 'DBI:mysql:'.$sql_db.':'.$sql_host;
    my $dbh=DBI->connect($dsn, $sql_login, $sql_pass) or die "Couldn't connect to database: " . DBI->errstr;
    return $dbh;
}

$dbh=&sql;

sub get_switch_type {

    my $sql="SELECT swtypeid,numports FROM $switche_type WHERE 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my %switchtype_ports;

    while (my @row = $sth->fetchrow_array)
    {
        $switchtype_ports{$row[0]}=$row[1];
    }
    $sth->finish;
    return %switchtype_ports;
};

my %switchtype_ports=&get_switch_type;

my $check_switch_uplink_by_swid_port = sub {
    my ($dbh,$swid,$port) = @_;
    my $sql="SELECT port FROM $switch_uplink WHERE swid=$swid and port=$port";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};

my $check_tarif_by_gid = sub {
    my ($dbh,$gid) = @_;
    my $sql="SELECT gid,speed_rate,speed_burst FROM $packets WHERE gid=$gid";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};

my $check_user_by_swid_port = sub {
    my ($dbh,$swid,$port) = @_;
    my $sql="SELECT uid,deposit,credit,blocked,gid,speed_rate,speed_burst FROM $users WHERE swid=$swid and switchport=$port";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @row = $sth->fetchrow_array;
    $sth->finish;
    return @row;
};


my $set_port_sw_200_down = sub {
    my ($t,$port) = @_;

    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('disable ports '.$port);
    }
    if ($debug==1){
        print $port." user not found\n";
    }
};

my $do_del_rate_up = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('delete rate-limit net_up_'.$port);
    }
};

my $do_del_rate_down = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('delete rate-limit net_down_'.$port);
    }
};


my $set_port_sw_200_rate_up = sub {
    my ($t,$port,$res_speed_up) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('create rate-limit net_up_'.$port.' access-mask net_up ethertype 0x800 dest-ip 0.0.0.0 / 0 port '.$port.' permit limit '.$res_speed_up.' exceed-action drop');
    }
};

my $set_port_sw_200_rate_down = sub {
    my ($t,$port,$res_speed_down) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('create rate-limit net_down_'.$port.' access-mask net_down egress-port '.$port.' port 25 permit limit '.$res_speed_down.' exceed-action drop');
    }
};

my $set_port_sw_200_up = sub {
    my ($t,$port) = @_;
    if ($do_nothing==0) {
        $t->waitfor('/#:/i');
        $t->print('enable ports '.$port);
    }
    if ($debug==1){
        print $port." user found\n";
    }
};


my $create_mask_sw_200 = sub {
    my ($t) = @_;
    if ($do_nothing==0) {

    $t->waitfor('/#:/i');
    $t->print('delete access-mask net_up');
    $t->waitfor('/#:/i');
    $t->print('delete access-mask net_down ');

    $t->waitfor('/#:/i');
    $t->print('create access-mask net_up ethertype dest-ip / 0 precedence 100');
    $t->waitfor('/#:/i');
    $t->print('create access-mask net_down egress-port precedence 100');

    }
};


my $get_switch_uplink_by_swid = sub {
    my ($dbh,$swid) = @_;
    my $sql="SELECT port FROM $switch_uplink WHERE swid=$swid";
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my %switchtype_ports_uplink;
    my $i=0;
    while (my @row = $sth->fetchrow_array)
    {
        $switchtype_ports_uplink{$i}=$row[0];
        $i=$i+1;
    }
    $sth->finish;
    return %switchtype_ports_uplink;
};

my $sql="SELECT nameswitch, ip, login, pass , swtypeid , swid  FROM $switches WHERE swtypeid = '7'";
my $sth = $dbh->prepare($sql);
$sth->execute;

while (my @row = $sth->fetchrow_array)
{

    my $swtypeid = $row[4] ;
    my $swid = $row[5];
    my $sw_ip = $row[1];
    my $swtypeid = $row[4] ;
    my $max_port_switch=$switchtype_ports{$swtypeid};

    if ($print_switch==1){
    print "ip: $sw_ip\tnameswitch: $row[0]\tswid:$swid\tports:$max_port_switch\t";
    }

    #Telnet to dev
    my $file = "/home/log/$row[1].log";
    my $t = new Net::Telnet(Timeout => 1, Telnetmode => 0, Errmode => "return", Input_log => $file);
    $t->open(Host => $sw_ip);
    $t->waitfor('/login:/i');
    $t->print($row[2]);
    $t->waitfor('/password:/i');
    $t->print($row[3]);

    if ($print_switch==1){
        print "OK\n";
    }
#    print "test1\n";
#    my %switchtype_ports_uplink=$get_switch_uplink_by_swid->($dbh,$swid);
    $create_mask_sw_200->($t);

    my $port_current=1;
    while ( $port_current <= $max_port_switch)
    {

        my $check_port=$check_switch_uplink_by_swid_port->($dbh,$swid,$port_current);
        if ($check_port==0) {
            my @check_user=$check_user_by_swid_port->($dbh,$swid,$port_current);
            if (@check_user==0) {
                $set_port_sw_200_down->($t,$port_current);
            }else{
                my $uid=$check_user[0];
                my $deposit=$check_user[1];
                my $credit=$check_user[2];
                my $blocked=$check_user[3];
                my $gid=$check_user[4];
                my $user_speed_up=$check_user[5]/$mb;
                my $user_speed_down=$check_user[6]/$mb;
                my @check_tarif_by_gid=$check_tarif_by_gid->($dbh,$gid);
                my $tarif_speed_up=$check_tarif_by_gid[1]/$mb;
                my $tarif_speed_down=$check_tarif_by_gid[2]/$mb;

                $user_speed_up=sprintf("%.0f",$user_speed_up);
                $user_speed_down=sprintf("%.0f",$user_speed_down);
                $tarif_speed_up=sprintf("%.0f",$tarif_speed_up);
                $tarif_speed_down=sprintf("%.0f",$tarif_speed_down);

                my $res_speed_up=0;
                my $res_speed_down=0;

                if ($user_speed_up!=0) {
                    $res_speed_up=$user_speed_up;
                }else{
                    $res_speed_up=$tarif_speed_up;
                }

                if ($user_speed_down!=0) {
                    $res_speed_down=$user_speed_down;
                }else{
                    $res_speed_down=$tarif_speed_down;
                }
#               print "$res_speed_up \n";
#               print "$res_speed_down \n";

                if ($blocked==1) {
                    $set_port_sw_200_down->($t,$port_current);
                }else{
                    if (($deposit+$credit)<0) {
                    $set_port_sw_200_down->($t,$port_current);
                    }else{
                     $set_port_sw_200_up->($t,$port_current);
                     $do_del_rate_up->($t,$port_current);
                     $do_del_rate_down->($t,$port_current);
                     if ($res_speed_up!=0) {
                     $set_port_sw_200_rate_up->($t,$port_current,$res_speed_up);
                     }
                     if ($res_speed_down!=0) {
                     $set_port_sw_200_rate_down->($t,$port_current,$res_speed_down);
                     }


                    }
                }
            }
        }
        $port_current=$port_current+1;
    }

#    my $result=$t->getline;
#    print $result;
}

$sth->finish;


$dbh->disconnect;
</code>

===== На последок =====
Никто не мешает поправить скрипт под ваше оборудование и использовать в своих целях

Обсуждаем на [[http://www.mikbill.ru/forums/forum/9-nas-network-access-server/|форуме]]