Update all maps to use the poll2 table

6 files changed, 28 insertions, 13 deletions

diff --git a/web/nms.gathering.org/nettkart-json.pl b/web/nms.gathering.org/nettkart-json.pl
index 7ffd05b..36685a4 100755
--- a/web/nms.gathering.org/nettkart-json.pl
+++ b/web/nms.gathering.org/nettkart-json.pl
@@ -22,7 +22,7 @@ if (defined($secret2)) {
 my $dbh = nms::db_connect();
 
 my %json = ();
-my $q = $dbh->prepare("select * from ( SELECT switch,sysname,sum(bytes_in) AS bytes_in,sum(bytes_out) AS bytes_out from switches natural left join get_current_datarate() where ip <> inet '127.0.0.1' group by switch,sysname) t1 natural join placements order by zorder;");
+my $q = $dbh->prepare("select * from ( SELECT switch,sysname,sum(ifinoctets) AS ifinoctets,sum(ifoutoctets) AS ifoutoctets from switches natural left join get_current_datarate() where ip <> inet '127.0.0.1' group by switch,sysname) t1 natural join placements order by zorder;");
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 
@@ -34,9 +34,9 @@ while (my $ref = $q->fetchrow_hashref()) {
 	
 	my $clr;
 
-	if (defined($ref->{'bytes_in'})) {
+	if (defined($ref->{'ifinoctets'})) {
 		my $intensity = 0.0;
-		my $traffic = 4.0 * ($ref->{'bytes_in'} + $ref->{'bytes_out'});  # average and convert to bits (should be about the same in practice)
+		my $traffic = 4.0 * ($ref->{'ifinoctets'} + $ref->{'ifoutoctets'});  # average and convert to bits (should be about the same in practice)
 
 		my $max = 100_000_000_000.0;   # 100Gbit
 		my $min =      10_000_000.0;   # 10Mbit
diff --git a/web/nms.gathering.org/nettkart.pl b/web/nms.gathering.org/nettkart.pl
index 3e999a9..2fc0e8b 100755
--- a/web/nms.gathering.org/nettkart.pl
+++ b/web/nms.gathering.org/nettkart.pl
@@ -58,7 +58,7 @@ $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf",
 $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 40, 47 + (236-42)*4.0/4.0, "10 Mbit/sec");
 $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 1600, 1000, "NMS (C) 2005-2012 Tech:Server");
 
-my $q = $dbh->prepare("select * from ( SELECT switch,sysname,sum(bytes_in) AS bytes_in,sum(bytes_out) AS bytes_out from switches natural left join get_current_datarate() where ip <> inet '127.0.0.1' group by switch,sysname) t1 natural join placements order by zorder;");
+my $q = $dbh->prepare("select * from ( SELECT switch,sysname,sum(ifinoctets) AS ifinoctets,sum(ifoutoctets) AS ifoutoctets from switches natural left join get_current_datarate() where ip <> inet '127.0.0.1' group by switch,sysname) t1 natural join placements order by zorder;");
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 
@@ -70,9 +70,9 @@ while (my $ref = $q->fetchrow_hashref()) {
 	
 	my $clr;
 
-	if (defined($ref->{'bytes_in'})) {
+	if (defined($ref->{'ifinoctets'})) {
 		my $intensity = 0.0;
-		my $traffic = 4.0 * ($ref->{'bytes_in'} + $ref->{'bytes_out'});  # average and convert to bits (should be about the same in practice)
+		my $traffic = 4.0 * ($ref->{'ifinoctets'} + $ref->{'ifoutoctets'});  # average and convert to bits (should be about the same in practice)
 
 		my $max = 100_000_000_000.0;   # 100Gbit
 		my $min =      10_000_000.0;   # 10Mbit
diff --git a/web/nms.gathering.org/portkart.pl b/web/nms.gathering.org/portkart.pl
index 0f1f83e..8bcff0b 100755
--- a/web/nms.gathering.org/portkart.pl
+++ b/web/nms.gathering.org/portkart.pl
@@ -28,7 +28,16 @@ $img->stringFT($blk, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0,
 $img->stringFT($blk, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 40, 47 + (236-42)*3.0/3.0, "1 Mbit/sec");
 $img->stringFT($blk, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 1600, 1000, "NMS (C) 2005-2007 Tech:Server");
 
-my $q = $dbh->prepare('select switch,port,bytes_in,bytes_out,placement,switchtype from switches natural join placements natural join get_datarate() where switchtype like \'%3100%\'');
+sub portnum($) {
+	my ($port) = @_;
+	if ($port =~ /(\d+)$/) {
+		return $1;
+	}
+	warn "Unrecognized port name: $port";
+	return undef;
+}
+
+my $q = $dbh->prepare('select switch,ifdescr,ifinoctets,ifoutoctets,placement,switchtype from switches natural join placements natural join get_datarate() where switchtype like \'%2200%\' and sysname like \'e%-%\'');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 
@@ -38,9 +47,9 @@ while (my $ref = $q->fetchrow_hashref()) {
 	
 	my $clr;
 
-	if (defined($ref->{'bytes_in'})) {
+	if (defined($ref->{'ifinoctets'})) {
 		my $intensity = 0.0;
-		my $traffic = 4.0 * ($ref->{'bytes_in'} + $ref->{'bytes_out'});  # average and convert to bits (should be about the same in practice)
+		my $traffic = 4.0 * ($ref->{'ifinoctets'} + $ref->{'ifoutoctets'});  # average and convert to bits (should be about the same in practice)
 
 		my $max = 100_000_000_000.0;   # 1Gbit
 		my $min =       1_000_000.0;   # 1Mbit
@@ -55,8 +64,8 @@ while (my $ref = $q->fetchrow_hashref()) {
 	
 	$ref->{'placement'} =~ /\((\d+),(\d+)\),\((\d+),(\d+)\)/;
 	my $npo = 48;
-	my $f = ($ref->{'port'} - 1) % 2;
-	my $po = ($ref->{'port'} - 1 - $f)/2;
+	my $f = portnum($ref->{'ifdescr'}) % 2;
+	my $po = (portnum($ref->{'ifdescr'}) - $f)/2;
 	my $h = 2*($2-$4)/$npo;
 	my $w = ($1-$3)/2;
 	
diff --git a/web/nms.gathering.org/stromkart.pl b/web/nms.gathering.org/stromkart.pl
index df92906..c0e4a3d 100755
--- a/web/nms.gathering.org/stromkart.pl
+++ b/web/nms.gathering.org/stromkart.pl
@@ -31,7 +31,7 @@ my $wht = $img->colorResolve(255, 255, 255);
 my $gry = $img->colorResolve(127, 127, 127);
 
 #my $q = $dbh->prepare('select switch,sysname,(select placement from placements where placements.switch=switches.switch) as placement,count((bytes_in > 0 and bytes_out > 0) or null) as active_ports,(max(last_poll_time) >= current_timestamp - interval \'2 minutes\') as fresh from switches natural left join get_current_datarate() natural join placements where switchtype like \'dlink3100%\' group by switch,sysname');
-my $q = $dbh->prepare(' select switch,sysname,(select placement from placements where placements.switch = switches.switch) as placement,count((operstatus = 1) or null) as active_ports from switches natural left join get_operstatus() natural join placements where ifdescr like \'ge-0/0/%\'  group by switch,sysname');
+my $q = $dbh->prepare(' select switch,sysname,(select placement from placements where placements.switch = switches.switch) as placement,count((ifoperstatus = 1) or null) as active_ports from switches natural left join get_operstatus() natural join placements where ifdescr like \'ge-0/0/%\'  group by switch,sysname');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 	my $ports = $ref->{'active_ports'};
diff --git a/web/nms.gathering.org/switches-json.pl b/web/nms.gathering.org/switches-json.pl
index 0930d14..5635a02 100755
--- a/web/nms.gathering.org/switches-json.pl
+++ b/web/nms.gathering.org/switches-json.pl
@@ -11,6 +11,7 @@ my $dbh = nms::db_connect();
 my %json = ();
 
 my $q = $dbh->prepare('select switch,sysname,placement,zorder from switches natural join placements');
+my $q2 = $dbh->prepare('select distinct on (switch) switch,temp,time,sysname from switch_temp natural join switches order by switch,time desc');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 	$ref->{'placement'} =~ /\((-?\d+),(-?\d+)\),\((-?\d+),(-?\d+)\)/;
@@ -26,6 +27,11 @@ while (my $ref = $q->fetchrow_hashref()) {
 	};
 }
 
+$q2->execute();
+while (my $ref = $q2->fetchrow_hashref()) {
+	$json{'switches'}{$ref->{'switch'}}{'temp'} = $ref->{'temp'};
+	$json{'switches'}{$ref->{'switch'}}{'time'} = $ref->{'time'};
+}
 my $q = $dbh->prepare('select linknet,switch1,switch2 from linknets');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
diff --git a/web/nms.gathering.org/uplinkkart.pl b/web/nms.gathering.org/uplinkkart.pl
index a72edcc..48eefff 100755
--- a/web/nms.gathering.org/uplinkkart.pl
+++ b/web/nms.gathering.org/uplinkkart.pl
@@ -40,7 +40,7 @@ for my $ports (0..4) {
 	$img->stringFT($blk, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 40, $y + 10, $ports);
 }
 
-my $q = $dbh->prepare(' select switch,sysname,(select placement from placements where placements.switch = switches.switch) as placement,count((operstatus = 1) or null) as active_ports from switches natural left join get_operstatus() natural join placements where ifdescr = \'ge-0/0/44\' or ifdescr = \'ge-0/0/45\' or ifdescr = \'ge-0/0/46\' or ifdescr = \'ge-0/0/47\' group by switch,sysname');
+my $q = $dbh->prepare(' select switch,sysname,(select placement from placements where placements.switch = switches.switch) as placement,count((ifoperstatus = 1) or null) as active_ports from switches natural left join get_operstatus() natural join placements where ifdescr = \'ge-0/0/44\' or ifdescr = \'ge-0/0/45\' or ifdescr = \'ge-0/0/46\' or ifdescr = \'ge-0/0/47\' group by switch,sysname');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {
 	my $ports = $ref->{'active_ports'};