Помогите составить запрос

MySQL/PostgreSQL/SQLite/Oracle/M$SQL/....

Модератор: terminus

Правила форума
Убедительная просьба юзать теги [code] при оформлении листингов.
Сообщения не оформленные должным образом имеют все шансы быть незамеченными.
Ответить
Аватара пользователя
alex3
лейтенант
Сообщения: 872
Зарегистрирован: 2006-11-20 16:47:56
Откуда: Переславль
Контактная информация:
Контактная информация пользователя alex3

Помогите составить запрос

Непрочитанное сообщение alex3 » 2008-01-14 14:41:19

Сделал trafd по статье Лиса. Надо посчитать общий трафик за вычетом некоторых сетей провайдера. У меня загруз. Тупо перебирать номера не хочется, как это сделать элегантно... Сеть, например такая ххх.ххх.128.0-ххх.ххх.159.255....ломал голову, весь mysql.ru перечитал в части регекспов - так ни до чего не допер. предложение сделать что-то вроде "regexp 'ххх.ххх.1[2-5][0-9].[0-9]*'" не катит, поскольку сеть ххх.ххх.120.0 тоже удовлетворяет данному регекспу, но не является сеткой прова.
Если ipfw можно считать речью обычного человека, то pf - речь политика. За каждой ошибкой -ядерный песец.
Вернуться к началу
Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/
Вернуться к началу
zg
полковник
Сообщения: 5845
Зарегистрирован: 2007-12-07 13:51:33
Откуда: Верх-Нейвинск

Re: Помогите составить запрос

Непрочитанное сообщение zg » 2008-01-14 14:48:45

alex3 писал(а):Сделал trafd по статье Лиса. Надо посчитать общий трафик за вычетом некоторых сетей провайдера. У меня загруз.
я это делал с помощью временных таблиц и inet_aton, просто и красиво ;) правда в итоге при разборе гиговых логов пришлось писать скрипт на C, уж больно много мускуль жрал ресурсов
Вернуться к началу
Аватара пользователя
dikens3
подполковник
Сообщения: 4856
Зарегистрирован: 2006-09-06 16:24:08
Откуда: Нижний Новгород
Контактная информация:
Контактная информация пользователя dikens3

Re: Помогите составить запрос

Непрочитанное сообщение dikens3 » 2008-01-14 14:55:36

#!/bin/sh

# Спам от провайдера NIS
echo "use exim; SELECT log.size,log.src_ip,log.time,log.hostname,log.helo,log.src_email,log.dst_email,textlog.text FROM log JOIN textlog ON log.action = textlog.id WHERE (src_ip BETWEEN '212.67.0.0' AND '212.67.31.255') AND (size IS NULL) ORDER BY log.time;" | \
mysql -u root -p$password > spamnis
Может поможет выделенное?
Лучше установить FreeBSD, чем потратить 30 лет на Linux'ы и выяснить какой из них хуже.
Вернуться к началу
zg
полковник
Сообщения: 5845
Зарегистрирован: 2007-12-07 13:51:33
Откуда: Верх-Нейвинск

Re: Помогите составить запрос

Непрочитанное сообщение zg » 2008-01-14 15:04:00

Код: Выделить всё

#!/usr/local/bin/php
<?

$argc = $argc;
$argv = $argv;

$errors   = array();
$log_name = '';
$stderr   = fopen('php://stderr', 'w');

$output_log = '';
$output_sum = '';

$date = date('Y-m-d');

if ( !_get_params() ) _exit_becouse_error( 'Illegal parameters' );
if ( $show_help || !$log_name ) exit(_show_help());

$log_name = realpath( $log_name );

// --------------
// Part two
// --------------

define('DB_CONNECT', true);

include('db.tables.inc.php');
include('db.config.inc.php');

$nets = prepare_nets();
//$nets = optimize_layers( $nets );

if ( !$nets ) _exit_becouse_error( 'No nets for ips' );
if ( !preg_match('/^\d{4}-\d\d-\d\d$/', $date) ) _exit_becouse_error( 'Illegal date' );

// Временная таблица масок сетей
// , PRIMARY KEY  (`first_ip`,`last_ip`,`net_id`)
$sql = "CREATE TEMPORARY TABLE `t1` ( `first_ip` int(1) unsigned NOT NULL default '0', `last_ip` int(1) unsigned NOT NULL default '0', `net_id` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`first_ip`, `last_ip`) ) ENGINE = HEAP";
mysql_query( $sql );

$vals = array();

while ( list( , $net ) = each( & $nets ) )
{
	$vals[] = '('. $net[ TBL_NMS_FIRST_IP ] .','. $net[ TBL_NMS_LAST_IP ] .','. $net[ TBL_NMS_NET_ID ] .')';
}

$sql = 'INSERT INTO t1 VALUES '. join(',',$vals);
if ( !mysql_query( $sql ) ) echo "error: can't insert nets\n" ;

unset($nets);
unset($vals);


// Временная таблица лога
$sql = "CREATE TEMPORARY TABLE `t` (`ip_sour` INT( 1 ) UNSIGNED NOT NULL ,`ip_dest` INT( 1 ) UNSIGNED NOT NULL ,`bytes` BIGINT( 1 ) UNSIGNED NOT NULL ,`net_id` SMALLINT( 1 ) UNSIGNED NOT NULL , `dogovor_id` SMALLINT( 1 ) UNSIGNED NOT NULL ) ENGINE=HEAP";
my_query( $sql );

timer();

$sql = "LOAD DATA INFILE '". realpath( $log_name ) ."' INTO TABLE t (ip_sour, ip_dest, bytes)";
if ( !mysql_query( $sql ) )
{
	// Увы.. памяти не хватило... :(((
	$fp = fopen('/usr/system/bin/MEMORY_LACK', "a+");
	fputs($fp, date('Y-m-d H:i:s  '). "log $log_name, size ". filesize( $log_name ). "\n");
	fclose($fp);
	
	// Придётся создавать временную таблицу в файле
	my_query( 'DROP TABLE t' );
	my_query( "CREATE TEMPORARY TABLE `t` (`ip_sour` INT( 1 ) UNSIGNED NOT NULL ,`ip_dest` INT( 1 ) UNSIGNED NOT NULL ,`bytes` BIGINT( 1 ) UNSIGNED NOT NULL ,`net_id` SMALLINT( 1 ) UNSIGNED NOT NULL , `dogovor_id` SMALLINT( 1 ) UNSIGNED NOT NULL )" );
	my_query( $sql );
}

fputs($stderr, sprintf( 'Load time %.3f'."\n", timer() ));


timer();

// Привязка типа трафика к ip
$sql = 'update t, t1  set t.net_id = t1.net_id where t.ip_sour between t1.first_ip and t1.last_ip';
my_query( $sql );

fputs($stderr, sprintf( 'Update time %.3f'."\n", timer() ));


timer();
// Временная таблица IP
$sql = 'CREATE TEMPORARY TABLE `ips` ( `'. TBL_UIP_IP .'` INT( 1 ) UNSIGNED NOT NULL , `'. TBL_UIP_DOGOVOR_ID .'` SMALLINT( 1 ) UNSIGNED NOT NULL, PRIMARY KEY ( `'. TBL_UIP_IP .'` ) ) ENGINE=HEAP ;';
my_query( $sql );

// Заполняем IP
$sql = 'INSERT IGNORE INTO `ips` SELECT '. TBL_UIP_IP .', '. TBL_UIP_DOGOVOR_ID .' FROM '. TBL_N_USERS_IPS .' WHERE '. TBL_UIP_UDATE .' <= "'. $date .'" ORDER BY '. TBL_UIP_UDATE .' DESC';
my_query( $sql );

// Привязка ip к договорам
$sql = 'UPDATE t, ips SET t.dogovor_id=ips.'. TBL_UIP_DOGOVOR_ID .' WHERE t.ip_dest=ips.'. TBL_UIP_IP .'';
my_query($sql);

fputs($stderr, sprintf( 'IP\'s time %.3f'."\n", timer() ));

$tmp_log = tempnam('/tmp', 'aspdo');
@unlink( $tmp_log );

timer();

$sql = 'select ip_sour, ip_dest, bytes, net_id, if(dogovor_name is null, "NULL", dogovor_name) dogovor_name INTO OUTFILE "'. $tmp_log .'" from t left join billing_dogovors using(dogovor_id)';
$res = my_query( $sql );


if ( $output_log )
{
	if ( is_file( $output_log ) ) @unlink( $output_log );
	rename( $tmp_log, $output_log );
}
else 
{
	readfile( $tmp_log );
}

if ( file_exists( $tmp_log ) ) unlink( $tmp_log );

if ( $output_sum )
{
	$sql = 'select if(dogovor_name is null, "NULL", dogovor_name), net_id, sum(bytes) INTO OUTFILE "'. $tmp_log .'" from t left join billing_dogovors using(dogovor_id) group by t.dogovor_id, net_id';
	$res = my_query( $sql );
	
	if ( file_exists( $output_sum ) ) @unlink( $output_sum );
	rename( $tmp_log, $output_sum );
}

if ( file_exists( $tmp_log ) ) unlink( $tmp_log );

fputs($stderr, sprintf( 'Output time %.3f'."\n", timer() ));

mysql_close();

exit(0);


// ======================================

function _show_help ()
{
?>
Log parser, bind net_id to traffic
    USAGE <?=basename(__FILE__)?> LOG_NAME [-o file] [-O file] [-d date]
        -o       Write data into file 
        -O       Write summary data in file
        -d       Date like 2006-11-07
        --help   Show this message
<?
}

function _get_params ()
{
	for ( $i = 1; $i < $GLOBALS['argc']; $i++ )
	{
		switch ( $GLOBALS['argv'][$i] )
		{
			case '--help':
				$GLOBALS['show_help'] = true;
				break;
				
			case '-o':
				$GLOBALS['output_log'] = @$GLOBALS['argv'][++$i];
				break;
				
			case '-O':
				$GLOBALS['output_sum'] = @$GLOBALS['argv'][++$i];
				break;
				
			case '-d':
				$GLOBALS['date'] = @$GLOBALS['argv'][++$i];
				break;
				
			default:
				if ( !$GLOBALS['log_name'] )
				{
					$GLOBALS['log_name'] = $GLOBALS['argv'][$i];
				}
				else 
				{
					return false;
				}
		}
	}
	
	return true;
}

function _exit_becouse_error ( $error )
{
	echo 'Error: ', $error, "\n";
	exit(-1);
}

function parse_hours ( $hours )
{
	$parts  = preg_split('/,/', $hours);
	$result = array();
	
	if ( $parts )
	{
		foreach ( $parts as $part )
		{
			if ( preg_match('/^(\d+)-(\d+)/', $part, $arr) )
			{
				// iterate HH-HH
				
				$hour1 = $arr[1];
				$hour2 = $arr[2];
				
				if ( preg_match('/^(0+)/', $hour1, $arr) )
				{
					$padding = $arr[1];
					$pad_len = strlen( $hour1 );
				}
				else 
				{
					$padding = false;
					$pad_len = 0;
				}
				
				
				$hour1 = (int) $hour1;
				$hour2 = (int) $hour2;
				
				for ( $i = $hour1; $i <= $hour2; $i += 1 )
				{
					if ( $pad_len )
					{
						$result[] = str_pad( $i, $pad_len, $padding, STR_PAD_LEFT );
					}
					else 
					{
						$result[] = $i;
					}
				}
			}
			else 
			{
				$result[] = $part;
			}
		}
	}
	
	$result = array_unique( $result );
	sort( $result );
	
	return $result;
}

function my_ip2long ( $ip )
{
	if ( preg_match('/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/', $ip) ) return sprintf('%u', ip2long( $ip ));
	
	return $ip;
}


function get_nets ()
{
	$sql = '
		SELECT
			nms.'. TBL_NMS_NET_ID   .',
			nms.'. TBL_NMS_Z_ORDER  .',
			nms.'. TBL_NMS_FIRST_IP .',
			nms.'. TBL_NMS_LAST_IP  .'
		
		FROM
			'. TBL_N_NETS_MASKS .' nms
			
		ORDER BY
			'. TBL_NMS_Z_ORDER .' ASC
	';
	
	$res    = mysql_query( $sql );
	$result = array();
	
	if ( !$res ) return false;
	
	while ( $row = mysql_fetch_assoc( $res ) )
	{
		$result[] = $row;
	}
	
	return $result;
}

function prepare_nets ( )
{
	if ( ! ($nets = get_nets()) ) return false;
	
	$net_layers = array();
	
	foreach ( $nets as $net )
	{
		$net_layers[ $net[ TBL_NMS_Z_ORDER ] ][] = $net;
	}
	
	ksort( $net_layers );
	
	// Пока есть хотя бы два слоя... будем крутиться
	while ( count( $net_layers ) > 1 )
	{
		$layer1 = array_shift( $net_layers );
		$layer2 = array_shift( $net_layers );
		
		array_unshift( $net_layers, merge_layers($layer1, $layer2) );
	}
	
	return array_pop( $net_layers );
}


function merge_layers ( $layer1, $layer2 )
{
	while ( list(, $net1) = each( & $layer1 ) )
	{
		reset( $layer2 );
		
		while ( list($net2_id, $net2) = each( & $layer2 ) )
		{
			// Ситуация ?1 - первая маска левее второй
			// mmmmm
			//       xxxxxx
			// ========================================
			if ( $net1[ TBL_NMS_LAST_IP ] < $net2[ TBL_NMS_FIRST_IP ] )
			{
				// Решение - первая маска должна просто добавится, но сначала надо проверить все сегменты
				continue 1;
			}
			
			// Ситуация ?2 - первая маска частично залазит на вторую слева
			// mmmmm
			//    xxxxxx
			// ========================================
			if (
				$net1[ TBL_NMS_FIRST_IP ] <= $net2[ TBL_NMS_FIRST_IP ] 
					and 
				$net1[ TBL_NMS_LAST_IP ] >= $net2[ TBL_NMS_FIRST_IP ]
					and 
				$net1[ TBL_NMS_LAST_IP ] < $net2[ TBL_NMS_LAST_IP  ]
			) {
				// Решение - вторая маска урезается по краю второй
				$layer2[ $net2_id ][ TBL_NMS_FIRST_IP ] = $net1[ TBL_NMS_LAST_IP ] + 1;
				continue 1;
			}
			
			// Ситуация ?3 - первая маска поностью покрывает вторую
			// mmmmmmmmmmmmmm
			//   xxxxxxxxx
			// ========================================
			if (
				$net1[ TBL_NMS_FIRST_IP ] <= $net2[ TBL_NMS_FIRST_IP ]
					and 
				$net1[ TBL_NMS_LAST_IP ] >= $net2[ TBL_NMS_LAST_IP ]
			) {
				// Решение - вторая маска анулируется
				unset( $layer2[ $net2_id ] );
				continue 1;
			}
			
			// Ситуация ?4 - первая маска делит вторую на две
			//    mmmmm
			//  xxxxxxxxxx
			// ========================================
			if (
				$net1[ TBL_NMS_FIRST_IP ] > $net2[ TBL_NMS_FIRST_IP ]
					and
				$net1[ TBL_NMS_LAST_IP ] < $net2 [ TBL_NMS_LAST_IP ]
			)
			{
				// Решение обрезать вторую маску по левой границе первой и добавить новый сегмент
				$layer2[ $net2_id ][ TBL_NMS_LAST_IP ] = $net1[ TBL_NMS_FIRST_IP ] - 1;
				$new_segment = $net2;
				$new_segment[ TBL_NMS_FIRST_IP ] = $net1[ TBL_NMS_LAST_IP ] + 1;
				$layer2[] = $new_segment;
				continue 1;
			}
			
			// Ситуация ?5 - первая маска частично перекрывает вторую справа
			//      mmmmmmmmmmm
			//   xxxxxxx
			// ========================================
			if (
				$net1[ TBL_NMS_FIRST_IP ] > $net2[ TBL_NMS_FIRST_IP ]
					and 
				$net1[ TBL_NMS_FIRST_IP ] <= $net2[ TBL_NMS_LAST_IP ]
					and
				$net1[ TBL_NMS_LAST_IP ] >= $net2[ TBL_NMS_LAST_IP ]
			) {
				// Решение - урезать вторую маску справа по границе первой
				$layer2[ $net2_id ][ TBL_NMS_LAST_IP ] = $net1[ TBL_NMS_FIRST_IP ] - 1;
				continue 1;
			}
			
			// Ситуация ?6 - первая маска правее второй
			//            mmmmmmmmmmm
			// xxxxxxxx
			// ========================================
			if ( $net1[ TBL_NMS_FIRST_IP ] > $net2[ TBL_NMS_LAST_IP ] ) 
			{
				// Решение - просто продолжать смотреть сегменты
				continue 1;
			}
		}
		
		$layer2[] = $net1;
	}
	
	return $layer2;
}


function my_query ( $sql )
{
	$res = mysql_query( $sql );
	
	if ( !$res )
	{
		$str  = "MYSQL ERROR: #". mysql_errno(). ' '. mysql_error(). "\n";
		$str .= $sql. "\n";
		if ( $GLOBALS['stderr'] ) fputs( $GLOBALS['stderr'], $str ); else $str;
		exit(-1);
	}
	return $res;
}


function timer ( $name = 'default' , $unset_timer = true )
{
	static $timers = array();
	
	if ( !isset( $timers[ $name ] ) )
	{
		$timers[ $name ] = microtime();
		return true;
	}
	
	list($s_sec, $s_mic) = split(' ', $timers[ $name ]);
	list($e_sec, $e_mic) = split(' ', microtime());
	
	$elapsed = $e_sec - $s_sec + ( $e_mic - $s_mic );
	
	if ( $unset_timer ) unset( $timers[ $name ] );
	
	return $elapsed;
}

function & optimize_layers ( & $nets )
{
	$opti = $nets;
	$chng = array();
	
	reset( $nets );
	
	while ( list( $i, $net ) = each( & $nets ) )
	{
		reset( $opti );
		while ( list($i2, $met) = each( & $opti ) )
		{
			if ( $net[ TBL_NMS_NET_ID ] === $met[ TBL_NMS_NET_ID ] )
			{
				$result1 = $net[ TBL_NMS_LAST_IP ] - $met[ TBL_NMS_FIRST_IP ];
				$result2 = $net[ TBL_NMS_FIRST_IP ] - $met[ TBL_NMS_LAST_IP ];
				
				// Ситуация #1 - первая маска бок о бок стоит слева от второй
				// nnnn mmmm
				if ( $result1 == -1 )
				{
					// Решение - сместить правую границу $net и анулировать $met
					$chng[] = array( $net, $met );
					$opti[ $i ][ TBL_NMS_LAST_IP ] = $opti[ $i2 ][ TBL_NMS_LAST_IP ];
					$opti[ $i2 ][ TBL_NMS_NET_ID ] = 0;
				}
				// Ситуация #2 - левая граница маски граничит с такой же сетью
				// mmmm nnnn
				elseif ( $result2 == 1 )
				{
					// Решение - сместить левую границу маски и анулировать $met
					$chng[] = array( $net, $met );
					$opti[ $i ][ TBL_NMS_FIRST_IP ] = $opti[ $i2 ][ TBL_NMS_FIRST_IP ];
					$opti[ $i2 ][ TBL_NMS_NET_ID ] = 0;
				}
			}
		}
	}
	
	reset( $opti );
	
	while ( list( $i, $net ) = each( & $opti ) )
	{
		if ( $net[ TBL_NMS_NET_ID ] == 0 ) unset( $opti[ $i ] );
	}
	
	reset( $opti );
	
	return $opti;
}


?>
вооо какая кака :D в итоге я от него отказался, так как не стало хватать памяти... но на мелких логах ничё работал
Вернуться к началу
zg
полковник
Сообщения: 5845
Зарегистрирован: 2007-12-07 13:51:33
Откуда: Верх-Нейвинск

Re: Помогите составить запрос

Непрочитанное сообщение zg » 2008-01-14 15:20:59

Короче прога, которая из логов вида

Код: Выделить всё

IP-Источник       IP-получатель      Кол-во
IP-Источник       IP-получатель      Кол-во
IP-Источник       IP-получатель      Кол-во
...
может делать следующие логи

Код: Выделить всё

IP-источник     IP-получатель      Кол-во      Тип трафика      Договор
IP-источник     IP-получатель      Кол-во      Тип трафика      Договор
IP-источник     IP-получатель      Кол-во      Тип трафика      Договор
.............
Формат вызова

Код: Выделить всё

logh -n nets.txt -g dogovory.txt logfile.txt
, где

Код: Выделить всё

nets.txt
---------------
# Net1
* 1
1.2.3.4/32
1.2.3.4/32
1.2.3.4/32

# Net2
* 2
2.3.4.5/32
2.3.4.5/32
2.3.4.5/32

# Net3
* 3
2.3.4.5/32
2.3.4.5/32
2.3.4.5/32
.....

Код: Выделить всё

dogovory.txt
----------------
1.2.3.4        Dogovor1
1.2.3.5        Dogovor2
....
юзай :)
Вложения
logh.gz
Скомпилирована на freebsd6.1
(5.51 КБ) 23 скачивания
Вернуться к началу
Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35466
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:
Контактная информация пользователя Alex Keda

Re: Помогите составить запрос

Непрочитанное сообщение Alex Keda » 2008-01-14 23:12:54

муся умеет работать с IP
Убей их всех! Бог потом рассортирует...
Вернуться к началу
Аватара пользователя
alex3
лейтенант
Сообщения: 872
Зарегистрирован: 2006-11-20 16:47:56
Откуда: Переславль
Контактная информация:
Контактная информация пользователя alex3

Re: Помогите составить запрос

Непрочитанное сообщение alex3 » 2008-01-15 10:51:43

Лис, ты как всегда краток :)
Если ipfw можно считать речью обычного человека, то pf - речь политика. За каждой ошибкой -ядерный песец.
Вернуться к началу
Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35466
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:
Контактная информация пользователя Alex Keda

Re: Помогите составить запрос

Непрочитанное сообщение Alex Keda » 2008-01-15 16:29:10

ну, я помню что умеет - читал в книжке, а подробностей не помню.
И потом, я всегда оставляю простор для полёта творческой фантазии =)
Убей их всех! Бог потом рассортирует...
Вернуться к началу