FreeRADIUS 雖然可以使用 MySQL 及帳號管理來記錄每個連線帳號的資料，
不過我沒去啟用那個功能(也不會用~~") 所以就另外寫了排程來處理這個工作
※開啟 FreeRADIUS 的 authlog 功能
/etc/radius/etc/raddb/sites-available/default 94行
auth_log 取消註解
/etc/radius/etc/radiusd.conf 443行
auth = yes
重新啟動 radiusd 服務
/sbin/service radiusd restart
Crontab -e
*/3 * * * * /usr/bin/php -q /home/shell/radius_log.php > /dev/null 2>&1
建立 Tables
CREATE TABLE IF NOT EXISTS `logs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `acc` varchar(50) DEFAULT NULL, `remote_ip` varchar(15) DEFAULT NULL, `dhcp_ip` varchar(15) DEFAULT NULL, `addtime` datetime DEFAULT NULL, `uptime` datetime DEFAULT NULL, `endtime` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='logs' AUTO_INCREMENT=1 ;
radius_log.php 的內容
<?php
//取得登入資料
exec("cat /var/log/radius/radius.log|grep 'Auth: Login OK:'",$arr);
// EX: Tue Apr 29 15:13:36 2014 : Auth: Login OK: [username] (from client localhost port 7 cli 102.11.1.14)
foreach($arr as $val)
{
$flag = ""; //flag
$_val = explode(" : ",$val);
//取得連線時間
$dd = date("Y-m-d H:i:s",strtotime(trim($_val[0])));
//取得帳號名稱
preg_match_all('/\[+.+\]/', trim($_val[1]), $_name);
$name = str_replace(array("[","]"),"",trim($_name[0][0]));
if(isset($_name[0][0]) && $name!="")
{
$acc = $name; //帳號
}
//取得 ip
preg_match_all('/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/', trim($_val[1]), $_ip);
if(isset($_ip[0][0]) && isset($acc))
{
$ip = $_ip[0][0];
//echo $dd."-".$acc."-".$ip."<br/>";
//檢查資料庫裡是否有相同帳號、ip及時間，若有則跳過、更新時間
$sql = "SELECT * FROM ".$table_name." WHERE `remote_ip` = '".$ip."' AND `addtime` = '".$dd."' AND `acc` = '".$acc."' ORDER BY `id` DESC LIMIT 1;";
//echo $sql."<br/>";
$rs = mysql_query($sql,$db_link);
//假如有資料
if(mysql_num_rows($rs))
{
$row = mysql_fetch_array($rs);
$id = $row['id'];
//假如有其它ip則直接寫入end
$sql2 = "SELECT * FROM ".$table_name." WHERE `remote_ip` = '".$ip."' AND `acc` = '".$acc."' AND `id` != '".$id."' AND `endtime` is NULL ORDER BY `addtime` DESC;";
$rs2 = mysql_query($sql2,$db_link);
//假如有其它資料就寫入end
if(mysql_num_rows($rs2)>0)
{
//將此筆寫入endtime
$update_sql = "UPDATE ".$table_name." SET `endtime` = '".date("Y-m-d H:i:s")."' WHERE `acc` = '".$acc."' AND `addtime` = '".$dd."' AND `remote_ip` = '".$ip."' AND `id` = '".$id."';";
//echo $update_sql."<br/>\n";
mysql_query($update_sql,$db_link);
$flag = "end";
}
else
{
if(trim($row['dhcp_ip'])!="")
{
$org_dhcp_ip = trim($row['dhcp_ip']);
//找找有沒同ip同dhcp且未為end的資料，若有則更新結束時間
$sql2 = "SELECT * FROM ".$table_name." WHERE `remote_ip` = '".$ip."' AND `id` != '".$id."' AND `endtime` is NULL ORDER BY `addtime` DESC;";
$rs2 = mysql_query($sql2,$db_link);
//假如有其它資料就寫入end
if(mysql_num_rows($rs2)>0)
{
//將此筆寫入endtime
$update_sql = "UPDATE ".$table_name." SET `endtime` = '".date("Y-m-d H:i:s")."' WHERE `addtime` = '".$dd."' AND `remote_ip` = '".$ip."' AND `id` = '".$id."';";
//echo $update_sql."<br/>\n";
mysql_query($update_sql,$db_link);
$flag = "end";
}
}
if(trim($row['endtime'])=="")
{
//結束時間不為空才繼續跑
$flag = "update";
}
else
{
$flag = "end";
}
}
}
else
{
$insert_sql = "INSERT INTO ".$table_name." (`acc`,`remote_ip`,`addtime`) VALUES ('".$acc."','".$ip."','".$dd."')";
mysql_query($insert_sql);
$flag = "new";
}
//echo $id;
if($flag!="end")
{
//從ps裡取得分配的ip資料
exec("ps -ef|grep 'ipparam ".$ip."'",$arr1);
// ex: root 11496 11495 0 17:04 ? 00:00:00 /usr/sbin/pppd local file /etc/ppp/options.pptpd 115200 192.168.10.1:192.168.10.101 ipparam 102.11.1.14",
$have_flag = false; //若為true則寫入uptime代表連線中，若為false則寫入endtime代表已斷線
foreach($arr1 as $val)
{
if(strpos($val,"/usr/sbin/pppd local file /etc/ppp/options.pptpd 115200"))
{
//取得ip
preg_match_all('/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/', trim($val), $_ip);
//arrayPrint($_ip);
if(isset($_ip[0][2]) && $_ip[0][2]!="" && $_ip[0][2]==$ip)
{
$have_flag = true;
$remote_ip = $_ip[0][2]; //來源ip
$dhcp_ip = $_ip[0][1]; //分配的ip
//不存在則新增檔案
if(!isset($org_dhcp_ip) || $flag=="new")
{
$update_sql = "UPDATE ".$table_name." SET `uptime` = '".date("Y-m-d H:i:s")."',`dhcp_ip` = '".$dhcp_ip."' WHERE `acc` = '".$acc."' AND `addtime` = '".$dd."' AND `remote_ip` = '".$ip."';";
//echo $update_sql."<br/>\n";
mysql_query($update_sql,$db_link);
}
//如果找到的dhcp_ip與原本資料庫的不同則建立新資料，原資料寫入endtime (原資料標準是時間+遠端ip+endtime為空)
elseif(isset($org_dhcp_ip) && $org_dhcp_ip!=$dhcp_ip && $remote_ip==$ip)
{
//原先資料刪除
$delete_sql = "DELETE FROM ".$table_name." WHERE `acc` = '".$acc."' AND `addtime` = '".$dd."' AND `remote_ip` = '".$ip."';";
mysql_query($delete_sql,$db_link);
//新增一筆
$insert_sql = "INSERT INTO ".$table_name." (`acc`,`remote_ip`,`addtime`,`dhcp_ip`,`uptime`) VALUES ('".$acc."','".$ip."','".$dd."','".$dhcp_ip."','".date("Y-m-d H:i:s")."');";
mysql_query($insert_sql);
}
elseif($remote_ip==$ip)
{
//將dhcp_ip資料回寫
$update_sql = "UPDATE ".$table_name." SET `dhcp_ip` = '".$dhcp_ip."',`uptime` = '".date("Y-m-d H:i:s")."' WHERE `acc` = '".$acc."' AND `addtime` = '".$dd."' AND `remote_ip` = '".$remote_ip."';";
mysql_query($update_sql,$db_link);
}
}
}
}
if($have_flag==false) //查無資料就結束
{
//結束
$update_sql = "UPDATE ".$table_name." SET `endtime` = '".date("Y-m-d H:i:s")."' WHERE `acc` = '".$acc."' AND `addtime` = '".$dd."' AND `remote_ip` = '".$ip."';";
mysql_query($update_sql,$db_link);
}
}
}
}
?>
取得各帳號使用的流量
<?php
echo "==目前連線流量資料==<br/>";
//處理檢查每一個ip流量
$sql = "SELECT * FROM ".$table_name." WHERE `endtime` is NULL ORDER BY `addtime` ASC";
$rs = mysql_query($sql,$db_link);
exec("ifconfig",$arr1);
if(mysql_num_rows($rs))
{
$ipArr = array();
$flag = false;
$j = 0;
while($row = mysql_fetch_array($rs))
{
for($i=0;$i<count($arr1);$i++)
{
if(strpos($arr1[$i],"P-t-P:".$row['dhcp_ip']))
{
echo $row['acc']." / ".$row['dhcp_ip']." / ";
$i = $i+5;
$flag = true;
}
if(strpos($arr1[$i],"RX bytes:") && $flag==true)
{
echo $arr1[$i]."<Br/>";
$flag = false;
$j++;
}
}
}
}
?>
