Here is how I do it:
- Create table clients where nas info is stored:
DROP TABLE IF EXISTS
clients;
CREATE TABLEclients(
cidint(2) NOT NULL auto_increment,
clientvarchar(15) NOT NULL default ‘0.0.0.0’,
secretvarchar(32) NOT NULL default ‘*’,
shortnamevarchar(32) NOT NULL default ‘’,
nastypevarchar(32) NOT NULL default ‘’,
PRIMARY KEY (cid)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
1.1. I’m using customized database of freeradius with different module, but you can suit it to your needs.
In groups table create field Mikrotik-Rate-Limit and Night-Mikroti-Rate-Limit,
Differ your shapes as you want 1:2 or 1:1.5 etc…
Add a script that will execute this at a certain time containing:
#!/bin/sh
/usr/bin/mysql -uradius -ppwd radius -e “alter table “yourgrouptable” change MikroTik-Rate-Limit back_rate_limit varchar(64) NOT NULL DEFAULT ‘0’;”
/usr/bin/mysql -uradius -ppwd radius -e “alter table “yourgrouptable” change Night-Mikroti-Rate-Limit Mikroti-Rate-Limit varchar(64) NOT NULL DEFAULT ‘0’;”
/usr/bin/mysql -uradius -ppwd radius -e “alter table “yourgrouptable” change back_rate_limit Night-Mikroti-Rate-Limit varchar(64) NOT NULL DEFAULT ‘0’;”
- Create php script that gathers MikroTik named NASes/it can be done with 1 script but I prefer 2/ which will be executed right after the table fields change
#!/usr/bin/php -q
<?php mysql_connect("localhost", "radius", "radpwd") or die("Could not connect: " . mysql_error()); mysql_select_db("radius"); $query = "SELECT client from clients where nastype='MikroTik'"; $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $client = $row[0]; system("/usr/bin/php -q /etc/ppp/temp/shape-mt.php $client"); } mysql_free_result($result); ?>
- Create shape-mt.php script that handles the shapings:
mysql_connect(“localhost”, “radius”, “radpwd”) or
die("Could not connect: " . mysql_error());
mysql_select_db(“radius”);$ras = $argv[1];
$query = “SELECT Framed-User, MikroTik-Rate-Limit from “your online table” INNER JOIN “yourgrouptable” ON “your online table”.groupid=“yourgroutable”.groupid where “Online-Info”=‘Online’ and “NAS”=‘$ras’”;
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$user = $row[0];
$rate = $row[1];
system(“/usr/bin/perl -w /usr/bin/send-speed.pl $ras $user $rate”);
}
mysql_free_result($result);
?>
- Executing your perl script for overwriting the dynamic queues:
#!/usr/bin/perl
my $host = $ARGV[0];
my $username = $ARGV[1];
my $rate = $ARGV[2];
my $found=0;use Data::Dumper;
use Net::Telnet ();$t = new Net::Telnet (
Host => “$host”,
Timeout => 10,
Dump_log => “./xyz”,
Prompt => ‘/[.+] > $/’);$t->login(“mikrotik-RW-user”, “mikroti-password”);
$t->cmd(“/queue simple set "<pppoe-$username>" limit-at=$rate max-limit=$rate”);
Do not forget to enable telnet on the MikroTik and you can use pptp-$username if you provide PPTP.
If you have any further questions please feel free to ask here!
