$txtable = "bd_tx_total_log"; } else { $rxtable = "bd_rx_log"; $txtable = "bd_tx_log"; } if ($dbtype == DB_PGSQL) { $sql_ip = "and ip <<= '{$ip}'"; } else { if ($dbtype == DB_MYSQL) { $p = parse_addr($ip); $net = $p["ip"] & $p["mask"]; $sql_ip = "and (ip & " . sprintf("%u", $p["mask"]) . ") = " . sprintf("%u", $net); } } $pg_sql = "select rx.scale as rxscale, tx.scale as txscale, tx.total+rx.total as total, tx.total as sent,\nrx.total as received, tx.tcp+rx.tcp as tcp, tx.udp+rx.udp as udp,\ntx.icmp+rx.icmp as icmp, tx.http+rx.http as http,\ntx.p2p+rx.p2p as p2p, tx.ftp+rx.ftp as ftp\nfrom\n \n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, {$txtable}\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = " . $txtable . ".sensor_id\n{$sql_ip}\ngroup by ip) as tx,\n \n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, {$rxtable}\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = " . $rxtable . ".sensor_id\n{$sql_ip}\ngroup by ip) as rx\n \nwhere tx.ip = rx.ip;"; $my_sql = "select rx.scale as rxscale, tx.scale as txscale, tx.total+rx.total as total, tx.total as sent,\nrx.total as received, tx.tcp+rx.tcp as tcp, tx.udp+rx.udp as udp,\ntx.icmp+rx.icmp as icmp, tx.http+rx.http as http,\ntx.p2p+rx.p2p as p2p, tx.ftp+rx.ftp as ftp\nfrom\n \n(SELECT inet_ntoa(ip) as ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, {$txtable}\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = " . $txtable . ".sensor_id\n{$sql_ip}\ngroup by ip) as tx,\n \n(SELECT inet_ntoa(ip) as ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, {$rxtable}\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = " . $rxtable . ".sensor_id\n{$sql_ip}\ngroup by ip) as rx\n \nwhere tx.ip = rx.ip;"; //echo "</center><pre>$my_sql</pre><center>";exit(0); if ($dbtype == DB_PGSQL) { $result = pg_query($pg_sql); } else { if ($dbtype == DB_MYSQL) { $result = mysql_query($my_sql); } } echo "<table width=100% border=1 cellspacing=0><tr><td>Ip<td>Name<td>Total<td>Sent<td>Received<td>tcp<td>udp<td>icmp<td>http<td>p2p<td>ftp"; if ($dbtype == DB_PGSQL) { $r = pg_fetch_array($result); } else { if ($dbtype == DB_MYSQL) { $r = mysql_fetch_array($result); }
if ($dbtype == DB_PGSQL) { $sql_ip = "and ip <<= '{$ip}'"; } else { if ($dbtype == DB_MYSQL) { $p = parse_addr($ip); $net = $p["ip"] & $p["mask"]; $sql_ip = "and (ip & " . sprintf("%u", $p["mask"]) . ") = " . sprintf("%u", $net); } } $pg_sql = "select *, extract(epoch from timestamp) as ts from sensors, {$table} where sensors.sensor_id = " . $table . ".sensor_id {$sql_ip} and sensor_name = '" . bd_escape_string($sensor_name) . "' and timestamp > {$timestamp}::abstime and timestamp < " . ($timestamp + $interval) . "::abstime order by ip;"; //original query //$my_sql = "select *, unix_timestamp(timestamp) as ts from sensors, $table where sensors.sensor_id = ".$table.".sensor_id $sql_ip and sensor_name = '".bd_escape_string($sensor_name)."' and unix_timestamp(timestamp) > $timestamp and unix_timestamp(timestamp) < ".($timestamp+$interval)." order by ip"; //optimized query $minTime = ''; $maxTime = ''; $my_sql = "select *, unix_timestamp(timestamp) as ts from sensors, {$table} where sensors.sensor_id = " . $table . ".sensor_id {$sql_ip} and sensor_name = '" . bd_escape_string($sensor_name) . "' and unix_timestamp(timestamp) > {$timestamp} and unix_timestamp(timestamp) < " . ($timestamp + $interval) . " order by ip"; if (DEBUG) { file_put_contents('debug_q.log', $my_sql); } //echo $my_sql."<br>"; exit(1); if ($dbtype == DB_PGSQL) { $result = pg_query($pg_sql); } else { if ($dbtype == DB_MYSQL) { $result = mysql_query($my_sql); } } // The SQL statement pulls the data out of the database ordered by IP address, that way we can average each // datapoint for each IP address to provide smoothing and then toss the smoothed value into the accumulator // to provide accurate total traffic rate. $SentPeak = "";
} // Sqlize the incomming variables if (isset($subnet)) { if ($dbtype == DB_PGSQL) { $sql_subnet = "and ip <<= '{$subnet}'"; } else { if ($dbtype == DB_MYSQL) { $p = parse_addr($subnet); $net = $p["ip"] & $p["mask"]; $sql_subnet = "and (ip & " . sprintf("%u", $p["mask"]) . ") = " . sprintf("%u", $net); } } } // Sql Statement $pg_sql = "select tx.ip, rx.scale as rxscale, tx.scale as txscale, tx.total+rx.total as total, tx.total as sent, \nrx.total as received, tx.tcp+rx.tcp as tcp, tx.udp+rx.udp as udp,\ntx.icmp+rx.icmp as icmp, tx.http+rx.http as http,\ntx.p2p+rx.p2p as p2p, tx.ftp+rx.ftp as ftp\nfrom\n\n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, bd_tx_log\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = bd_tx_log.sensor_id\n{$sql_subnet}\nand timestamp > {$timestamp}::abstime and timestamp < " . ($timestamp + $interval) . "::abstime\ngroup by ip) as tx,\n\n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, bd_rx_log\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = bd_rx_log.sensor_id\n{$sql_subnet}\nand timestamp > {$timestamp}::abstime and timestamp < " . ($timestamp + $interval) . "::abstime\ngroup by ip) as rx\n\nwhere tx.ip = rx.ip\norder by total desc;"; $my_sql = "select inet_ntoa(tx.ip) as ip, rx.scale as rxscale, tx.scale as txscale, tx.total+rx.total as total, tx.total as sent, \nrx.total as received, tx.tcp+rx.tcp as tcp, tx.udp+rx.udp as udp,\ntx.icmp+rx.icmp as icmp, tx.http+rx.http as http,\ntx.p2p+rx.p2p as p2p, tx.ftp+rx.ftp as ftp\nfrom\n\n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, bd_tx_log\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = bd_tx_log.sensor_id\n{$sql_subnet}\nand unix_timestamp(timestamp) > {$timestamp} and unix_timestamp(timestamp) < " . ($timestamp + $interval) . "\ngroup by ip) as tx,\n\n(SELECT ip, max(total/sample_duration)*8 as scale, sum(total) as total, sum(tcp) as tcp, sum(udp) as udp, sum(icmp) as icmp,\nsum(http) as http, sum(p2p) as p2p, sum(ftp) as ftp\nfrom sensors, bd_rx_log\nwhere sensor_name = '" . bd_escape_string($sensor_name) . "'\nand sensors.sensor_id = bd_rx_log.sensor_id\n{$sql_subnet}\nand unix_timestamp(timestamp) > {$timestamp} and unix_timestamp(timestamp) < " . ($timestamp + $interval) . "\ngroup by ip) as rx\n\nwhere tx.ip = rx.ip\norder by total desc"; //echo "</center><pre>$my_sql</pre><center>"; exit(0); if ($dbtype == DB_PGSQL) { pg_query("SET sort_mem TO 30000;"); $result = pg_query($pg_sql); } else { if ($dbtype == DB_MYSQL) { $result = mysql_query($my_sql); } } if ($dbtype == DB_PGSQL) { pg_query("set sort_mem to default;"); } if ($limit == "all") { if ($dbtype == DB_PGSQL) { $limit = pg_num_rows($result);