ipv6-dns v2 (fetich via SNMP, new DB schema)

5 files changed, 115 insertions, 116 deletions

diff --git a/clients/ipv6-dns.pl b/clients/ipv6-dns.pl
index 24b8bfb..dcb059d 100755
--- a/clients/ipv6-dns.pl
+++ b/clients/ipv6-dns.pl
@@ -8,10 +8,10 @@ use strict;
 use warnings;
 
 BEGIN {
-        require "../include/config.pm";
-        eval {
-                require "../include/config.local.pm";
-        };
+require "../include/config.pm";
+	eval {
+		require "../include/config.local.pm";
+	};
 }
 
 my $dbh = nms::db_connect() or die "Can't connect to database";
@@ -44,7 +44,12 @@ print "Running automagic IPv6 DNS updates\n";
 while (1) {
 	
 	# Fetch visible IPv6 addresses from the last three minutes
-	my $q = $dbh->prepare("SELECT DISTINCT ON (ipv6.mac) ipv6.address AS v6, ipv6.mac, ipv4.address AS v4, ipv6.time - ipv6.age*'1 second'::interval FROM ipv6 LEFT JOIN ipv4 ON ipv4.mac = ipv6.mac WHERE ipv6.time > NOW()-'3 min'::interval ORDER BY ipv6.mac, ipv6.time DESC, mac")
+	#my $q = $dbh->prepare("SELECT DISTINCT ON (ipv6.mac) ipv6.address AS v6, ipv6.mac, ipv4.address AS v4, ipv6.time - ipv6.age*'1 second'::interval FROM ipv6 LEFT JOIN ipv4 ON ipv4.mac = ipv6.mac WHERE ipv6.time > NOW()-'3 min'::interval ORDER BY ipv6.mac, ipv6.time DESC, mac")
+	my $q = $dbh->prepare(
+"SELECT DISTINCT ON (v6) v6, ipv6.mac, ipv6.seen, v4
+FROM (SELECT DISTINCT ON (address) address AS v6, mac, seen FROM seen_mac WHERE family(address) = 6 AND seen > CURRENT_TIMESTAMP - '3 min'::interval) ipv6
+LEFT JOIN (SELECT DISTINCT ON (address) address AS v4, mac, seen FROM seen_mac WHERE family(address) = 4 AND seen > CURRENT_TIMESTAMP - '3 min'::interval) ipv4 ON ipv4.mac = ipv6.mac
+ORDER BY v6, ipv6.seen DESC, mac")
 		or die "Can't prepare query";
 	$q->execute() or die "Can't execute query";
 	
@@ -104,7 +109,7 @@ while (1) {
 	# try to delete everything. FIXME: Query the zone file and diff against the
 	# database, to avoid running as many NS-updates as tuples in the result set.
 	
-	$q = $dbh->prepare("SELECT DISTINCT address AS v6 FROM ipv6 WHERE time < NOW() - '4 hours'::interval AND time > NOW() - '4 hours 30 minutes'::interval")
+	$q = $dbh->prepare("SELECT DISTINCT address AS v6 FROM seen_mac WHERE seen BETWEEN CURRENT_TIMESTAMP - '4 hours'::interval AND CURRENT_TIMESTAMP - '4 hours 30 minutes'::interval")
 		or die "Can't prepare query";
 	$q->execute() or die "Can't execute query";
 	
diff --git a/clients/ipv6-stats-v2.pl b/clients/ipv6-stats-v2.pl
new file mode 100644
index 0000000..ab76cc9
--- /dev/null
+++ b/clients/ipv6-stats-v2.pl
@@ -0,0 +1,98 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+use lib '../include';
+use nms;
+use Data::Validate::IP qw(is_public_ipv6 is_public_ipv4 is_private_ipv4);
+use Net::MAC qw(mac_is_unicast);
+
+my $dbh = nms::db_connect();
+$dbh->{AutoCommit} = 0;
+
+while (1) {
+	my $coregws = $dbh->prepare("SELECT ip, community, sysname FROM switches WHERE switchtype <> 'dlink3100'")
+		or die "Can't prepare query: $!";
+	$coregws->execute;
+	
+	my %seen = ();
+	my $num_v4 = 0;
+	my $num_v6 = 0;
+	while (my $ref = $coregws->fetchrow_hashref) {
+		print STDERR "Querying ".$ref->{'sysname'}." ...\n";
+		my $snmp;
+		eval {
+			$snmp = nms::snmp_open_session($ref->{'ip'}, $ref->{'community'});
+		};
+		warn $@ if $@;
+		next if not $snmp;
+		
+		# Pull in old media table that does not support ipv6.
+		my $ip_phys_table = fetch($snmp, ('ipNetToMediaNetAddress', 'ipNetToMediaPhysAddress'));
+		for my $entry (values %$ip_phys_table) {
+			my $ip_addr = $entry->{'ipNetToMediaNetAddress'};
+			my $mac = Net::MAC->new(
+				mac => nms::convert_mac($entry->{'ipNetToMediaPhysAddress'}),
+				die => 0,
+			);
+		
+			next if $mac->get_base() != 16 || $mac->get_mac() eq ''; # We only support base 16 addresses
+			next if (!is_public_ipv4($ip_addr) && !is_private_ipv4($ip_addr)); # We consider RFC1918 public
+	
+			$seen{$ip_addr} = $mac->get_mac();
+			$num_v4++;
+		}
+		
+		# Pull in new media table with IPv6 support
+		$ip_phys_table = $snmp->gettable('ipNetToPhysicalTable');
+		for my $entry (values %$ip_phys_table) {
+			my $type = $entry->{'ipNetToPhysicalNetAddressType'};
+			my $ip_addr = undef;
+			my $mac = Net::MAC->new(
+				mac => nms::convert_mac($entry->{'ipNetToPhysicalPhysAddress'}),
+				die => 0,
+			);
+			
+			if ($type != 2) {
+				warn "$ip_addr is of unexpected type $type (should be 2)! Tell Berge.\n";
+				next;
+			}
+		
+			$ip_addr = nms::convert_ipv6($entry->{'ipNetToPhysicalNetAddress'});
+		
+			next if $mac->get_base() != 16 || $mac->get_mac() eq ''; # We only support base 16 addresses
+			next if not is_public_ipv6($ip_addr);
+	
+			$seen{$ip_addr} = $mac->get_mac();
+			$num_v6++;
+		}
+	
+	}
+	
+	# Populate database
+	my $i = 0;
+	foreach my $ip_addr (keys %seen) {
+		$i++;
+		my $q = $dbh->do('INSERT INTO seen_mac (address, mac) VALUES (?, ?)', undef, $ip_addr, $seen{$ip_addr})
+			or die "Can't execute query: $!";
+	}
+	
+	$dbh->commit;
+	print "Collected $num_v6 IPv6 addresses and $num_v4 IPv4 addresses. $i unique addresses.\n";
+	print "Sleeping for 60 seconds ...\n";
+	sleep(60);
+}
+
+
+# Fetch provided fields from a single table returning {iid => {tag => val}}
+sub fetch {
+	my $session = shift;
+	my @vars    = map { new SNMP::Varbind([$_]) } @_;
+	my $data    = {};
+	foreach my $result (@{$session->bulkwalk(0, 8, new SNMP::VarList(@vars))}) {
+		foreach my $entry (@$result) {
+			$data->{$entry->iid}->{$entry->tag} = $entry->val;
+		}
+	}
+	return $data;
+}
+
diff --git a/clients/ipv6-stats.pl b/clients/ipv6-stats.pl
deleted file mode 100755
index 59dacbd..0000000
--- a/clients/ipv6-stats.pl
+++ /dev/null
@@ -1,89 +0,0 @@
-#!/usr/bin/perl
-
-use warnings;
-use strict;
-use lib '../include';
-use nms qw(switch_connect switch_exec switch_disconnect);
-use Net::Telnet::Cisco;
-
-BEGIN {
-        require "../include/config.pm";
-        eval {
-                require "../include/config.local.pm";
-        };
-}
-
-#
-# Fetch list of MAC addresses and IPv6 addresses
-#
-sub query_router {
-	my ($host) = @_;
-
-	my $ios = Net::Telnet::Cisco->new(
-			Host => $host,
-			Errmode => 'return');
-	if (not defined $ios) {
-		warn "Can't connect to $host: $!, skipping";
-		return ();
-	}
-	if (not $ios->login($nms::config::ios_user, $nms::config::ios_pass)) {
-		warn "Can't login to $host. Wrong username or password?";
-		return ();
-	}
-	$ios->autopage(0);
-	$ios->cmd("terminal length 0");
-	my @v6data = $ios->cmd('show ipv6 neighbors')
-		or warn "$host wouldn't let me run show ipv6 neighbors.";
-	my @v4data = $ios->cmd('show ip arp')
-		or warn "$host wouldn't let me run show ip arp.";
-
-	# Remove useless header and footer
-#	shift @v6data; 
-	pop @v6data;
-#	shift @v4data;
-	pop @v4data;
-
-	return { 'v6' => \@v6data, 'v4' => \@v4data };
-}
-
-while (1) {
-	print "Gathering IPv6 and IPv4 stats\n";
-	# Connect to DB
-	my $dbh = nms::db_connect();
-	$dbh->{AutoCommit} = 0;
-
-	my ($v4, $v6) = 0;
-	foreach my $router (@nms::config::distrobox_ips) {
-		my $data = query_router($router);
-		# IPv6
-		foreach my $line (@{$data->{'v6'}}) {
-			my ($address, $age, $mac, undef, undef) = split('\s+', $line);
-			if ($mac =~ /[a-f0-9]{4}\.[a-f0-9]{4}\.[a-f0-9]{4}/ && # Sanity check MAC address
-				 $address !~ /^FE.*/) { # Filter out non-routable addresses
-				my $q = $dbh->prepare('INSERT INTO ipv6 (address, age, mac, time) VALUES (?, ?, ?, timeofday()::timestamp)')
-					or die "Can't prepare query: $!";
-				$q->execute($address, $age, $mac)
-					or die "Can't execute query: $!";
-				$v6++;
-			}
-		}
-		# IPv4
-		foreach my $line (@{$data->{'v4'}}) {
-			my (undef, $address, $age, $mac, undef, undef) = split('\s+', $line);
-			if ($mac =~ /[a-f0-9]{4}\.[a-f0-9]{4}\.[a-f0-9]{4}/) {# Sanity check MAC address
-				$age = 0 if $age eq '-';
-				my $q = $dbh->prepare('INSERT INTO ipv4 (address, age, mac, time) VALUES (?, ?, ?, timeofday()::timestamp)')
-					or die "Can't prepare query: $!";
-				$q->execute($address, $age, $mac)
-					or die "Can't execute query: $!";
-				$v4++;
-			}
-		}
-	}
-	print "Added $v6 IPv6 addresses and $v4 IPv4 addresses.\n";
-	$dbh->commit;
-	$dbh->disconnect;
-	
-	print "Sleeping for two minutes.\n";
-	sleep 120; # Sleep for two minutes
-}
diff --git a/include/nms.pm b/include/nms.pm
index a0f570b..3f89384 100644
--- a/include/nms.pm
+++ b/include/nms.pm
@@ -27,6 +27,8 @@ BEGIN {
 	SNMP::loadModules('ENTITY-MIB');
 	SNMP::loadModules('IF-MIB');
 	SNMP::loadModules('LLDP-MIB');
+	SNMP::loadModules('IP-MIB');
+	SNMP::loadModules('IP-FORWARD-MIB');
 }
 
 sub db_connect {
diff --git a/sql/nms.sql b/sql/nms.sql
index 002ae6b..fe5f4b8 100644
--- a/sql/nms.sql
+++ b/sql/nms.sql
@@ -347,34 +347,17 @@ CREATE TABLE dhcp (
 
 ALTER TABLE public.dhcp OWNER TO nms;
 
+-- Name: seen_mac; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
 --
--- Name: ipv4; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
---
-
-CREATE TABLE ipv4 (
-    mac macaddr NOT NULL,
-    address inet NOT NULL,
-    "time" timestamp without time zone NOT NULL,
-    age integer
-);
 
-
-ALTER TABLE public.ipv4 OWNER TO nms;
-
---
--- Name: ipv6; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
---
-
-CREATE TABLE ipv6 (
+CREATE TABLE seen_mac (
     mac macaddr NOT NULL,
     address inet NOT NULL,
-    "time" timestamp with time zone NOT NULL,
-    age integer,
-    vlan text
+    seen timestamp with time zone DEFAULT now() NOT NULL
 );
 
 
-ALTER TABLE public.ipv6 OWNER TO nms;
+ALTER TABLE public.seen_mac OWNER TO nms;
 
 --
 -- Name: mbd_log; Type: TABLE; Schema: public; Owner: nms; Tablespace: