$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);
