Archive old/unused things

3 files changed, 112 insertions, 0 deletions

diff --git a/examples/historical/munin/backfill_total_network_traffic.pl b/examples/historical/munin/backfill_total_network_traffic.pl
new file mode 100755
index 0000000..000b0d5
--- /dev/null
+++ b/examples/historical/munin/backfill_total_network_traffic.pl
@@ -0,0 +1,55 @@
+#! /usr/bin/perl -I/root/tgmanage/include
+use strict;
+use warnings;
+use lib 'include';
+use nms;
+use Data::Dumper::Simple;
+
+use Date::Parse;
+
+my $dbh = nms::db_connect();
+$dbh->{AutoCommit} = 0;
+
+# This has a slightly modded version of get_current_datarate inlined. It's probably outdated by the time you read this.
+my $total_traffic = $dbh->prepare("select sum(bytes_in) * 8 / 1048576.0 / 1024.0 as traffic_out, sum(bytes_out) * 8 / 1048576.0 / 1024.0 as traffic_in from (SELECT switch,port,
+      (bytes_out[1] - bytes_out[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS bytes_out,
+      (bytes_in[1] - bytes_in[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS bytes_in,
+      time[1] AS last_poll_time
+      FROM (
+        SELECT switch,port,
+        ARRAY_AGG(time) AS time,
+        ARRAY_AGG(bytes_in) AS bytes_in,
+        ARRAY_AGG(bytes_out) AS bytes_out
+        FROM (
+           SELECT *,rank() OVER (PARTITION BY switch,port ORDER BY time DESC) AS poll_num
+           FROM polls WHERE time BETWEEN (to_timestamp(?) - interval '5 minutes') AND to_timestamp(?)
+           AND official_port
+        ) t1
+        WHERE poll_num <= 2
+        GROUP BY switch,port
+      ) t2
+      WHERE
+        time[2] IS NOT NULL
+        AND bytes_in[1] >= 0 AND bytes_out[1] >= 0
+        AND bytes_in[2] >= 0 AND bytes_out[2] >= 0
+        AND bytes_out[1] >= bytes_out[2]
+        AND bytes_in[1] >= bytes_in[2]) as datarate natural join switches where switchtype like 'dlink3100%' and port < 45")
+	or die "Can't prepare query: $!";
+
+my $inout = shift @ARGV;
+while (<>) {
+	if (m,<!-- [^/]* CEST / (\d+) --> <row><v>[^<]*</v></row>, && $1 > 1397458800) {
+		my $time = $1;
+		if ($time > 1397458800) {
+			$total_traffic->execute($time, $time);
+			my $ref = $total_traffic->fetchrow_hashref;
+			my $value = $ref->{'traffic_' . $inout};
+			$value = (!defined $value || $value == 0 || $value > 400) ? "NaN" : sprintf "%e", $value;
+			s,<v>[^<]*</v>,<v>$value</v>,;		
+		}
+	}
+	print;
+}
+$total_traffic->finish;
+$dbh->disconnect();
+exit 0
diff --git a/examples/historical/munin/clients_connected.pl b/examples/historical/munin/clients_connected.pl
new file mode 100755
index 0000000..5301c63
--- /dev/null
+++ b/examples/historical/munin/clients_connected.pl
@@ -0,0 +1,26 @@
+#! /usr/bin/perl -I/root/tgmanage/include
+use strict;
+use warnings;
+use lib 'include';
+use nms;
+use Data::Dumper::Simple;
+
+my $dbh = nms::db_connect();
+$dbh->{AutoCommit} = 0;
+
+my $active_clients = $dbh->prepare("select family(address), count(distinct(mac)) from seen_mac where family(address) in (6,4) and  seen >= now() - INTERVAL '1 hour' group by family(address);")
+	or die "Can't prepare query: $!";
+
+$active_clients->execute;
+print <<EOF;
+graph_title Clients seen the last hour
+graph_vlabel count
+graph_scale no
+EOF
+while (my $ref = $active_clients->fetchrow_hashref) {
+  print "clients_".$ref->{'family'}.".label v".$ref->{'family'}." clients\n";
+  print "clients_".$ref->{'family'}.".value ".$ref->{'count'}."\n";
+}
+$active_clients->finish;
+$dbh->disconnect();
+exit 0
diff --git a/examples/historical/munin/total_network_traffic.pl b/examples/historical/munin/total_network_traffic.pl
new file mode 100755
index 0000000..2c0799b
--- /dev/null
+++ b/examples/historical/munin/total_network_traffic.pl
@@ -0,0 +1,31 @@
+#! /usr/bin/perl -I/root/tgmanage/include
+use strict;
+use warnings;
+use lib 'include';
+use nms;
+use Data::Dumper::Simple;
+
+# By the looks of this code, the in/out values are from the perspective of the
+# switch. However, something gets flipped somewhere which makes it from the
+# perspective of the client. I have no idea why. Have fun!
+
+my $dbh = nms::db_connect();
+$dbh->{AutoCommit} = 0;
+
+my $total_traffic = $dbh->prepare("select sum(bytes_in) * 8 / 1048576.0 / 1024.0 as traffic_in, sum(bytes_out) * 8 / 1048576.0 / 1024.0 as traffic_out from get_current_datarate() natural join switches where switchtype like 'dlink3100%' and port < 45")
+	or die "Can't prepare query: $!";
+
+$total_traffic->execute;
+print <<EOF;
+graph_title Total network traffic
+graph_vlabel Gb/s
+graph_scale no
+EOF
+my $ref = $total_traffic->fetchrow_hashref;
+print "total_network_traffic_in.label Total incoming traffic\n";
+print "total_network_traffic_in.value ". $ref->{'traffic_in'}."\n";
+print "total_network_traffic_out.label Total outgoing traffic\n";
+print "total_network_traffic_out.value ". $ref->{'traffic_out'}."\n";
+$total_traffic->finish;
+$dbh->disconnect();
+exit 0;