==== XML ====





	Отчет по трафику детальный
	Kayfolom
	ekorepov@ukhta-inform.ru
	authorUrl
	2009
	c
	license
	1.0
	Detail traffic report
	
		traffic_report_detail.php

==== PHP код плагина ====


registerEvent( 'onPrepareContent', 'plgTrafficReportDetail' );

function plgTrafficReportDetail(&$row, &$params, $page=0){
    $pos = strpos($row->text, '{user_report_detail}');
    if ($pos!==false) {
        $row->text = preg_replace('/{user_report_detail}/', '', $row->text);

	function mysql_list_db_tables($database) {  
		$tables = Array();  
		$results = mysql_query('SHOW TABLES');  
		while($row = @mysql_fetch_assoc($results)) { $tables[] = $row["Tables_in_UTM5_detail_stat"]; }  
		return $tables;  
	} 
	$config["mysql_host"]	= "xxx.xxx.xxx.xxx";
	$config["mysql_db"]	= "UTM5_detail_stat";
	$config["mysql_user"]	= "asdfgsdrgsdg";
	$config["mysql_pass"]	= "asdrgedrgdgsdrg";
	$config["t_class_arr"]	= array(10, 14, 20, 24); // traffic class for a store

	$con = mysql_connect($config["mysql_host"], $config["mysql_user"], $config["mysql_pass"]);
	if ($con == false) {
		print_r("ERROR! MySql-->Could not connect: ".mysql_error());
		over();
	}

	$db = mysql_select_db($config["mysql_db"], $con);
	if ($db == false) {
		print_r("ERROR! MySql-->Could not select database: ".mysql_error());
		over();
	}

	$array_dates = mysql_list_db_tables($db);
	rsort($array_dates);
//*******************************  Форма начало
	$form_title = '';
	if (isset($uri->_vars['date_select'])) {
		unset($uri->_vars['date_select']);
	}
	if (isset($uri->_vars['t_class'])) {
		unset($uri->_vars['t_class']);
	}
	$form_footer = '';
	$form_body = '';
	$form_body = $form_body.'Выберите дату : ";

//	$form_body = $form_body.'       Выберите адрес :        ";

	$form_body = $form_body.'       Тип трафика :        ";

	$form_body = $form_body.'       Сортировка :        ";


	$form_body = $form_body."";
	$date_select_form = $form_title.$form_body.$form_footer;
//*******************************  Форма конец
//*******************************  Формирование отчета начало
	$http_report_message = '';
	$report_table = '';
	$date_select = JRequest::getVar('date_select');
	$t_class = JRequest::getVar('t_class');
	$t_sort = JRequest::getVar('t_sort');
	if (isset($date_select) & isset($t_class)) { 
		$array_date = sscanf($date_select, "%4s_%2s_%2s");
		$query_report = "Select CONCAT(' ', FROM_UNIXTIME(`timestamp`),' ',__t_class.t_class_name,'',";
		$query_report = $query_report."'',packets,'',bytes,'',inet_ntoa(4294967295 & src_ip),' ',src_port,'',";
		$query_report = $query_report."'',inet_ntoa(4294967295 & dst_ip),' ',dst_port,' ',__proto.name,'') as res_str ";
		$query_report = $query_report."From $date_select Inner Join __proto ON proto = __proto.id ";
		$query_report = $query_report."Inner Join __t_class ON t_class = __t_class.id Where account_id = ".$_SESSION['URFA']['basic_account'];
		if ($t_class>-1) {
			$query_report = $query_report." AND t_class = ".$t_class;		
		}
		$t_sort = str_replace('-', ' ', $t_sort);
//	print $t_sort;
		$query_report = $query_report." ORDER BY $t_sort";
//		print_r($query_report);
		$result_report = mysql_query($query_report);
		if (mysql_num_rows($result_report)>0) {
			$http_report_message = "Детальный отчет по трафику за ".$array_date[2].".".$array_date[1].".".$array_date[0].' Количество записей : '.mysql_num_rows($result_report);
		        $report_table = '';
			$report_table = $report_table.'';
			$report_table = $report_table.'';
			while($row_report = mysql_fetch_array($result_report)) {
				$report_table = $report_table.$row_report[0];
			}
			$report_table = $report_table.'Дата-время Трафик Пакеты Байты IP источника Порт источника IP назначения Порт назначения Протокол';
		} else {
			$http_report_message = "Данные за период ".$array_date[2].".".$array_date[1].".".$array_date[0].'г. по IP-адресу '.$IP_select." отсутствуют";
		}

        }

//*******************************  Формирование отчета конец
        $row->text = preg_replace('/{detail_report_message}/', $http_report_message, $row->text);
        $row->text = preg_replace('/{report_table}/', $report_table, $row->text);
        $row->text = preg_replace('/{date_select_form}/', $date_select_form, $row->text);
	}
}

==== PHP код скрипта экспорта детальной статистики в mysql ==== Вот пример работы, вырезка из логов:


02.12.09 13:21:34	Export script started
02.12.09 13:21:35	There are 1 files to export
02.12.09 13:21:35	"/netup/utm5/db/iptraffic_raw_1259748201.utm" contain 131579 records
02.12.09 13:23:08	From "/netup/utm5/db/iptraffic_raw_1259748201.utm" exported 107984 records (1159 records per second)
02.12.09 13:23:08		Skipped 23595 records (system: 0; null: 0; timestamp: 0; t_class: 23595)
02.12.09 13:23:08	Script execution time is 93.17 seconds
02.12.09 13:23:08	Total exported 107984 records
02.12.09 13:23:08	Total skipped 0 records (system: 0; null: 0; timestamp: 0)
02.12.09 13:23:08	Maximum speed	: 1159	records per second
02.12.09 13:23:08	Minimum speed	: 1159	records per second
02.12.09 13:23:08	Medium speed	: 1159	records per second
02.12.09 13:23:08	Global speed	: 1159	records per second
02.12.09 13:23:08	Export script finished


 MySql                     *****/
/***** Developed by DRiN a/k/a DJ Neo D'Matrix      *****/
/***** Ukraine, Kiev 2006                           *****/
/********************************************************/
/***** update by Kayfolom 2009                      *****/
/***** Usage: read HOWTO                            *****/
/*
В качестве аргумента передаем имя файла с детальной статистикой *.utm
Запускам через raw_fd_script
При запуске без аргументов будет загружать все *.utm файлы не найденные в mysql-таблице __log
*/
/*********************** CONFIG **********************/
	@ini_set("display_errors", "1");
	error_reporting(E_ALL);

	$config["mysql_host"]	= "10.0.16.105";
//	$config["mysql_host"]	= "10.0.0.9";
	$config["mysql_db"]	= "UTM5_detail_stat";
	$config["mysql_user"]	= "sdfgsdgsdfhdfh";
	$config["mysql_pass"]	= "asdfgdfgdfg";
	$config["path2dbfiles"]	= "/netup/utm5/db";
	$config["table_stat"]	= "stat";

	$config["exec_limit"]	= 60*60;
	$config["ignoresystem"]	= false; // If true script will ignore system trafic (id == 0)
	$config["log2screen"]	= false;
	$config["log2file"]	= "/netup/utm5/log/export_detail_stat.log";
	$config["delete_utm"]	= false;
	$config["t_class_arr"]	= array(10, 14, 20, 24); // traffic class for a store
//	$config["create_table"]	= "CREATE TABLE IF NOT EXISTS `{TABLE_NAME}` (`id` int(11) NOT NULL auto_increment,`timestamp` int(4) unsigned,`account_id` int(2) unsigned,`t_class` int(2) unsigned, `packets` int(4) unsigned, `bytes` int(4) unsigned, `src_ip` int(4) unsigned, `src_port` smallint(2) unsigned, `dst_ip` int(4) unsigned, `dst_port` smallint(2) unsigned, `proto` TINYINT(1) unsigned, `tos`  TINYINT(1) unsigned, PRIMARY KEY  (`id`)) ENGINE=MyISAM;";
	$config["create_table"]	= "CREATE TABLE IF NOT EXISTS `{TABLE_NAME}` (`timestamp` int(4) unsigned,`account_id` int(2) unsigned,`t_class` int(2) unsigned, `packets` int(4) unsigned, `bytes` int(4) unsigned, `src_ip` int(4) unsigned, `src_port` smallint(2) unsigned, `dst_ip` int(4) unsigned, `dst_port` smallint(2) unsigned, `proto` TINYINT(1) unsigned, `tos`  TINYINT(1) unsigned) ENGINE=MyISAM;";
/*********************** FUNCTIONS **********************/
function getmicrotime() { 
    list($usec, $sec) = explode(" ", microtime()); 
    return ((float)$usec + (float)$sec); 
}

function mylog($log_str) {
	global $config, $flog;

	$now = date("d.m.y H:i:s");
	$log_str = "$now	$log_str\r\n";

	if ($config["log2screen"]) {
		echo $log_str;
	}
	fwrite($flog, $log_str);
}

function export_record($line) {
	global $config, $skip_system, $skip_null, $skip_timestamp, $skip_t_class;

	$_timestamp = $line['timestamp'];
	$_account_id = $line['account_id'];
	$_t_class = $line['t_class'];
	$_packets = $line['packets'];
	$_bytes = $line['bytes'];
	$_src_ip = $line['src_ip']; 
	$_src_port = $line['src_port'];
	$_dst_ip = $line['dst_ip']; 
	$_dst_port = $line['dst_port'];
	$_proto = $line['proto'];
	$_tos = $line['tos'];

	if (($_account_id == 0) and $config["ignoresystem"]) {
		$skip_system++;	
		return(2);
	} /** ignore system **/

	if (($_packets == 0) or ($_bytes == 0)) {
		$skip_null++;
		return;
	} /** no data transfer **/

	if ($line['timestamp'] == 0) {
		$skip_timestamp++;
		return;
	} /** no timestamp **/

	if(!in_array($_t_class, $config["t_class_arr"])) {
		$skip_t_class++;
		return;
	} /** no timestamp **/

	$query = $config["create_table"];
	$date_tmp = date("Y_m_d", $line['timestamp']);
	$query = preg_replace('/{TABLE_NAME}/', $date_tmp, $query);
	$result = mysql_query($query);
	$columns = "`timestamp`, `account_id`, `t_class`, `packets`, `bytes`, `src_ip`, `src_port`, `dst_ip`, `dst_port`, `proto`, `tos`";
	$values = "'$_timestamp', '$_account_id', '$_t_class', '$_packets', '$_bytes', '$_src_ip', '$_src_port', '$_dst_ip', '$_dst_port','$_proto','$_tos'";
	$query = "INSERT DELAYED INTO `{$date_tmp}` ($columns) values ($values)";
	$result = mysql_query($query);
	return($result);
}

function export($exportfile) {
	global $config, $rps;

	if(!is_file($exportfile)) {
		mylog("File \"$exportfile\" not exists");
		return;
	}

	if (filesize($exportfile)<10000000)  {
		mylog("File \"$exportfile\" size less 10000000 bytes - not complete?");
		return;
	}

	$count_records = floor(filesize($exportfile)/76);
	if($count_records == 0) {
		mylog("File \"$exportfile\" not have records to export");
		return;
	}

	mylog("\"$exportfile\" contain $count_records records");

	if(!($f = fopen($exportfile, "r"))) {
		mylog("Can't open \"$exportfile\" for reading");
		return;
	}

	$exportfile_basename = basename($exportfile);
	$my_query = "SELECT file_name FROM __log WHERE file_name = '$exportfile_basename'";
//	print $my_query."\r\n";
	$my_result = mysql_query($my_query);
	$num_rows = mysql_num_rows($my_result);
//	print $num_rows."\r\n";
	if ($num_rows!=0) {
		mylog("File \"$exportfile\" already processed");
		return;
	}

	$my_query = "INSERT INTO __log (file_name,`date`) VALUES ('$exportfile_basename',NOW())";
//	print $my_query."\r\n";
//	mylog($my_query);
	$my_result = mysql_query($my_query);

	$i = 0;
	$rps = 0;
	$start_time = getmicrotime();

	for ($n = 1; $n <= $count_records; $n++) {
		$record = sfread($f);
		$result = export_record($record);
		if ($result) {
			if ($result !== 2) $i++;
		} 
//		else {
//			mylog("Export failed (record #$n)");
//		}
	}
	fclose($f);
	$end_time = getmicrotime();
	$ecex_time = $end_time - $start_time;
	$rps = $i/$ecex_time;
	return $i;
}

function over($halt=true) {
	global $flog;

	if ($halt) {
		mylog("Export script halted by error");
	} else {
		mylog("Export script finished");
	}

	mylog("\r\n");

	fclose($flog);
	exit();
}
function sfread($f) {
	$int_str = fread($f, 76);
	$decode_mask = "VWTF1/Csrc_ip1/Csrc_ip2/Csrc_ip3/Csrc_ip4/Cdst_ip1/Cdst_ip2/Cdst_ip3/Cdst_ip4/Vnexthop/vin_iface/vout_iface";
	$decode_mask = $decode_mask."/Vpackets/Vbytes/Vfirst/Vlast/vsrc_port/vdst_port/vtcp_flags/Cproto/Ctos/vWTF3";
	$decode_mask = $decode_mask."/vsrc_as/vdst_as/vWTF4/Vslink_id/Vaccount_id/Vsourceaddr1/Vt_class/Vtimestamp/Vnfgen_addr";
	$result = unpack($decode_mask,$int_str);
	$result['src_ip']  = $result['src_ip1'] + $result['src_ip2']*256 + $result['src_ip3']*256*256 + $result['src_ip4']*256*256*256; 
	$result['dst_ip'] = $result['dst_ip1'] + $result['dst_ip2']*256 + $result['dst_ip3']*256*256 + $result['dst_ip4']*256*256*256; 
	return ($result);
}

/*********************** MAIN **********************/

$flog = fopen($config["log2file"], "a");

mylog("Export script started");

$con = mysql_connect($config["mysql_host"], $config["mysql_user"], $config["mysql_pass"]);
if ($con == false) {
	mylog("ERROR! MySql-->Could not connect: ".mysql_error());
	over();
}

$db = mysql_select_db($config["mysql_db"], $con);
if ($db == false) {
	mylog("ERROR! MySql-->Could not select database: ".mysql_error());
	over();
}

//$result = mysql_query("LOCK TABLES a WRITE;");

set_time_limit($config["exec_limit"]);
error_reporting(0);

$start_time = getmicrotime();


$total_records = 0;
$rps = 0;
$max_rps = 0;
$min_rps = 2147483647;
$total_rps = 0;
$total_rps_times = 0;

if ($_SERVER["argv"][1]) {
	$utmfiles[] = $_SERVER["argv"][1];
} else {
//    exit;
	$d = dir($config["path2dbfiles"]);
	while (false !== ($entry = $d->read())) {
		$query_utm_file = "SELECT file_name FROM __log WHERE file_name = '".$entry."'";
		$result__utm_file = mysql_query($query_utm_file);
		$num_rows = mysql_num_rows($result__utm_file); 
//		print $entry." - ".$num_rows."\r\n";
                if ($num_rows > 0) continue;
		$fullpath = "{$config[path2dbfiles]}/$entry";
		if (!is_file($fullpath)) continue;
		if (!is_readable($fullpath)) continue;
		$entry_parts=split("[.]", $entry);
		$ext = $entry_parts[count($entry_parts)-1];
		if ($ext != 'utm') continue;

		$utmfiles[] = $fullpath;
	}
	$d->close();
//	print_r($utmfiles);	
//	exit;
}

$count_files = count($utmfiles);
if(isset($utmfiles) and ($count_files > 0)){
	mylog("There are $count_files files to export");
} else {
	mylog("There are no files to export");
	over();
}

$total_skip_system = 0;
$total_skip_null = 0;
$total_skip_timestamp = 0;
$total_skip_t_class = 0;

foreach($utmfiles as $n=>$utmfile) {
//	if(is_file($utmfile)) {
//	    rename($utmfile, $utmfile.".run");
//	} else {
//	    mylog("File \"$utmfile\" not exists (may be already exported)");
//	}

	$skip_system = 0;
	$skip_null = 0;
	$skip_timestamp = 0;
	$skip_t_class = 0;

//	$exported_records = export($utmfile.".run");
	$exported_records = export($utmfile);
	if ($exported_records) {
		$myrps = round($rps*10)/10;
		$skiped = $skip_system + $skip_null + $skip_timestamp + $skip_t_class;
		mylog("From \"$utmfile\" exported $exported_records records ($myrps records per second)");
		mylog("\tSkipped $skiped records (system: $skip_system; null: $skip_null; timestamp: $skip_timestamp; t_class: $skip_t_class)");
		$total_records += $exported_records;
		$total_skip_system += $skip_system;
		$total_skip_null += $skip_null;
		$total_skip_timestamp += $skip_timestamp;
		$total_skip_t_class += $skip_t_class;

		if ($rps > $max_rps) $max_rps = $rps;
		if ($rps < $min_rps) $min_rps = $rps;
		$total_rps += $rps;
		$total_rps_times++;
//		if($config["delete_utm"]) {
//			unlink($utmfile.".run");
//		} else {
//			rename($utmfile.".run", $utmfile.".exported");
//		}
	} else {
		mylog("File \"$utmfile\" export failed");
//		rename($utmfile.".run", $utmfile.".filed");
	}
}

//$result = mysql_query("UNLOCK TABLES;");

$end_time = getmicrotime();
$ecex_time = $end_time - $start_time;
$med_rps = $total_rps/$total_rps_times;
$glob_rps = $total_records/$ecex_time;

$max_rps = round($max_rps*10)/10;
$min_rps = round($min_rps*10)/10;
$med_rps = round($med_rps*10)/10;
$glob_rps = round($glob_rps*10)/10;
$ecex_time = round($ecex_time*100)/100;

$total_skiped = $total_skip_system + $total_skip_null + $total_skip_timestamp;

mylog("Script execution time is $ecex_time seconds");
mylog("Total exported $total_records records");
mylog("Total skipped $total_skiped records (system: $total_skip_system; null: $total_skip_null; timestamp: $total_skip_timestamp)");
mylog("Maximum speed	: $max_rps	records per second");
mylog("Minimum speed	: $min_rps	records per second");
mylog("Medium speed	: $med_rps	records per second");
mylog("Global speed	: $glob_rps	records per second");

over(false);
?>

==== SQL ====


use UTM5_detail_stat;
CREATE TABLE `__log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6615 DEFAULT CHARSET=latin1;

CREATE TABLE `__proto` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert  into __proto values 
(0, 'ip'), 
(1, 'icmp'), 
(3, 'ggp'), 
(6, 'TCP'), 
(8, 'EGP'), 
(12, 'PUP'), 
(17, 'UDP'), 
(20, 'HMP'), 
(22, 'XNS-IDP'), 
(27, 'RDP'), 
(41, 'IPv6'), 
(43, 'IPv6-Route'), 
(44, 'IPv6-Frag'), 
(50, 'ESP'), 
(51, 'AH'), 
(58, 'IPv6-ICMP'), 
(59, 'IPv6-NoNxt'), 
(60, 'IPv6-Opts'), 
(66, 'RVD'), 
(2, 'IGMP');

CREATE TABLE `__t_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_class_name` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1;

Классы трафика естественно поменяйте на свои
insert  into __t_class values 
(10, 'Входящий'), 
(14, 'Входящий ночной'), 
(16, 'WebCams'), 
(18, 'Service'), 
(20, 'Исходящий'), 
(24, 'Исходящий ночной'), 
(50, 'Городской входящий'), 
(60, 'Городской исходящий'), 
(1000, 'Локальный');

==== HTML ====

Детальный отчет по трафику

Внимание! Время формирования отчета может достигать 10 минут, дождитесь результата.

{user_report_detail}

{date_select_form}

{detail_report_message}

{report_table}

==== Пример ====

Детальный отчет по трафику

Внимание! Время формирования отчета может достигать 10 минут, дождитесь результата.

Выберите дату : Тип трафика : Сортировка :