snmpd

#!/usr/bin/perl

# NOTE! This script is designed to be executed within Atlas V only!

# Periodically scan hosts.

# Switches: 
#	Check interface status, vlans and MAC addresses. 
#	Backup config every 24 hours. 
#	Detect loops and other disrepancies.

# Routers:
#	Check interface status and routing information.
#	Backup config every 24 hours.
#	Detect possible problems.

# UPS:
#	Check bypass and battery status


#$ snmpwalk -c public -v 1 $IP_ADDRESS $OID
# Cisco	 1.3.6.1.4.1.9 
# https://www.iana.org/assignments/enterprise-numbers/enterprise-numbers


#$ snmpwalk -c public -v 1 10.113.10.30 1.3.6.1.2.1.33 -m /usr/share/snmp/mibs/STDUPSV1.txt
# Eaton 5P
# MGE UPS SYSTEMS GALAXY 5000 60 kVA
#
# input ON, output ON
# host=7, snmp_name=upsBatteryStatus, suffix=1, value=2 (normal)
# host=7, snmp_name=upsOutputSource, suffix=1, value=3 (normal)
# host=7, snmp_name=upsAlarmsPresent, suffix=1, value=0
#
# input ON, output OFF
# host=7, snmp_name=upsBatteryStatus, suffix=1, value=2 (normal)
# host=7, snmp_name=upsOutputSource, suffix=1, value=2 (none)
# host=7, snmp_name=upsAlarmsPresent, suffix=1, value=(greater than 0)
#
# input OFF, output ON
# host=7, snmp_name=upsBatteryStatus, suffix=1, value=2 (normal)
# host=7, snmp_name=upsOutputSource, suffix=1, value=5 (battery)
# host=7, snmp_name=upsAlarmsPresent, suffix=1, value=(greater than 0)
#
# input OFF, output OFF
# no response
#
# Raise alarm if status != 2 or source != 3 or alarms > 0


#$ snmpwalk -c public -v 1 10.132.10.32 1.3.6.1.4.1.318 -m /usr/share/snmp/mibs/powernet417.txt 
# Smart-UPS RT 3000 XL
#
# upsBasicBatteryStatus.0 = INTEGER: batteryNormal(2)
# upsBasicOutputStatus.0 = INTEGER: onLine(2)
# upsAdvStateSystemMessages.0 = "" # 3 digit Decimal code representing the current active message


use strict;
use warnings;
#use Net::SNMP;
use SNMP::Effective;
use Time::HiRes;
use Data::Dumper;
use JSON;
use DBI;
use AtlasV;
use Carp;

my $DEBUG = 0;

my $dbh = DBI->connect(AtlasV::db());
unless ($dbh) {
  confess "Error: ".$DBI::errstr;
}
$dbh->{'mysql_auto_reconnect'} = 1;


my $lifetime = '7 DAY'; # https://www.w3schools.com/sql/func_mysql_date_sub.asp

#my $json = JSON->new( allow_nonref => 1 );
my $json = JSON->new();
warn "$0 $$ running\n";
while (1) {

  # Expire data
  $dbh->do("DELETE FROM macsightings WHERE recorded < DATE_SUB(NOW(), INTERVAL $lifetime)");
  $dbh->do("DELETE FROM arpsightings WHERE recorded < DATE_SUB(NOW(), INTERVAL $lifetime)");

  # Fetch list of hosts to scan
  my @hosts = ();
  {
    my $sth = $dbh->prepare("SELECT id,ip,class FROM hosts WHERE disabled = false AND scanned IS NULL OR scanned < DATE_SUB(NOW(), INTERVAL 1 HOUR)");
    $sth->execute();
    while (my $host = $sth->fetchrow_hashref) {
      push @hosts, $host;
    }
  }
  my %host_ip_by_id = map { $_->{'id'} => $_->{'ip'} } @hosts; 
  my %host_id_by_ip = map { $_->{'ip'} => $_->{'id'} } @hosts;

  # Fetch SNMP OIDs to try
  my @oids = ();
  {
    my $sth = $dbh->prepare("SELECT oid,name,hostclass FROM snmp_oids,hostclass_snmp WHERE hostclass_snmp.snmp_oid=snmp_oids.id");
    $sth->execute();
    while (my $oid = $sth->fetchrow_hashref) {
      push @oids, $oid;
    }
  }
  my %r_oids = map { $_->{'oid'} => $_->{'name'} } @oids;  
  
  my $snmp = SNMP::Effective->new(
    max_sessions   => 32,
    master_timeout => 240,
    #DestHost      => [ values %host_ip_by_id ],
    Arg            => {
      Community    => 'public',
      Version      => 1,
      RemotePort   => 161,
      Timeout      => '1000000', # microseconds
      Retries      => 3,
      RetryNoSuch  => 0,
    },
    #walk          => [ values %oids ],
    callback       => sub { 
      my $host = shift;
      my $error = shift;
      
      if ($error) { 
        $DEBUG && warn "$host: $error\n"; 
        return;
      }
      $DEBUG && warn "$host: got response\n";

#      warn Dumper($host->data);
      
      # Process SNMP data returned by $host
      my $host_id = $host_id_by_ip{$host};
      foreach my $prefix (sort by_dots keys %{$host->data}) {
        my $snmp_name = $r_oids{$prefix};
        $host->data->{$snmp_name} = $host->data->{$prefix};
 
        # DEBUG
        #next unless $DEBUG && $snmp_name =~ 'ups';
        #foreach my $suffix (sort by_dots keys %{$host->data->{$snmp_name}}) {
        #  my $value = $host->data->{$snmp_name}->{$suffix};
        #  warn "UPS: host=$host_id, snmp_name=$snmp_name, suffix=$suffix, value=$value\n";
        #  warn Dumper($host->data);
        #}
      }

      #warn Dumper($host->data);
      
      # Extract UPS information (type 1) - failure states in CAPS
      if ($host->data->{'upsBatteryStatus'} || $host->data->{'upsOutputSource'} || $host->data->{'upsAlarmsPresent'}) {
        $DEBUG && warn "$host has UPS data (type 1)\n";
        my $sth = $dbh->prepare("REPLACE INTO ups_status (host,ups_type,alarms,outputstatus,batterystatus) VALUES (?,1,?,?,?)");
        my $alarms = $host->data->{'upsAlarmsPresent'}->{'1'} || 0;
        my $output = 'UNKNOWN('.$host->data->{'upsOutputSource'}->{'1'}.')';
        if ($host->data->{'upsOutputSource'}->{'1'} == 3) { $output = 'online'; }
        if ($host->data->{'upsOutputSource'}->{'1'} == 4) { $output = 'IN BYPASS'; }
        if ($host->data->{'upsOutputSource'}->{'1'} == 5) { $output = 'ON BATTERY'; }
        my $battery = 'UNKNOWN('.$host->data->{'upsBatteryStatus'}->{'1'}.')'; 
        if ($host->data->{'upsBatteryStatus'}->{'1'} == 2) { $battery = 'normal'; }
        if ($host->data->{'upsBatteryStatus'}->{'1'} == 3) { $battery = 'LOW'; }
        if ($host->data->{'upsBatteryStatus'}->{'1'} == 4) { $battery = 'DEPLETED'; }
        $sth->execute($host_id, $alarms, $output, $battery);
        $sth->finish;
      }

      # Extract UPS information (type 2) - failure states in CAPS
      if ($host->data->{'upsBasicBatteryStatus'} || $host->data->{'upsBasicOutputStatus'} || $host->data->{'upsAdvStateSystemMessages'}) {
        $DEBUG && warn "$host has UPS data (type 2)\n";
        my $sth = $dbh->prepare("REPLACE INTO ups_status (host,ups_type,alarms,outputstatus,batterystatus) VALUES (?,2,?,?,?)");
        my $alarms = $host->data->{'upsAdvStateSystemMessages'}->{'1'} || 0;
        my $output = 'UNKNOWN('.$host->data->{'upsBasicOutputStatus'}->{'1'}.')';
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 2) { $output = 'online'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 3) { $output = 'ON BATTERY'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 4) { $output = 'ON SMART BOOST'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 5) { $output = 'IN TIMED SLEEP'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 6) { $output = 'IN SOFTWARE BYPASS'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 7) { $output = 'OFF'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 8) { $output = 'REBOOTING'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 9) { $output = 'IN SWITCHED BYPASS'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 10) { $output = 'IN FAILURE BYPASS'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 11) { $output = 'WAITING FOR POWER'; }
        if ($host->data->{'upsBasicOutputStatus'}->{'1'} == 12) { $output = 'IN SMART TRIM'; }
        my $battery = 'UNKNOWN('.$host->data->{'upsBasicBatteryStatus'}->{'1'}.')';
        if ($host->data->{'upsBasicBatteryStatus'}->{'1'} == 2) { $battery = 'normal'; }
        if ($host->data->{'upsBasicBatteryStatus'}->{'1'} == 3) { $battery = 'LOW'; }
        $sth->execute($host_id, $alarms, $output, $battery);
        $sth->finish;
      }

      # Extract port information (ifIndex = ifIndex)
      if ($host->data->{'ifIndex'}) {
        $DEBUG && warn "$host has ifIndex (interface table)\n";
        my $vlan_insert_sth = $dbh->prepare('INSERT INTO vlans (id) VALUES (?)');
        my $port_insert_sth = $dbh->prepare('INSERT IGNORE INTO ports (name,host) VALUES (?,?)');
        my $port_select_sth = $dbh->prepare('SELECT id FROM ports WHERE name=? AND host=?');
        my $port_update_sth = $dbh->prepare('UPDATE ports SET `type`=?, `index`=?, up=?, admin=?, speed=?, vlan=?, description=? WHERE id=?');
        foreach my $ifindex (sort by_dots keys %{$host->data->{'ifIndex'}}) {
          my $ifname = $host->data->{'ifName'}->{$ifindex};
          my $iftype = $host->data->{'ifType'}->{$ifindex};
          my $ifvlan = $host->data->{'ifVlan'}->{$ifindex} || 0; # undef = no vlan, e.g trunk
          my $ifspeed = $host->data->{'ifHighSpeed'}->{$ifindex};
          my $ifalias = $host->data->{'ifAlias'}->{$ifindex};
          my $ifadmin = $host->data->{'ifAdminStatus'}->{$ifindex};
          my $ifoper = $host->data->{'ifOperStatus'}->{$ifindex};
          #warn "$host> PORT $ifname type=$iftype speed=$ifspeed vlan=$ifvlan alias=$ifalias admin=$ifadmin oper=$ifoper\n";

          # With SNMP, 1=Up 2=Down and other values may be possible in $ifadmin and $ifoper. 
          # Change any other value than 1 to 0.
          $ifadmin = ($ifadmin == 1 ? 1 : 0);
          $ifoper = ($ifoper == 1 ? 1 : 0);

          # Ensure vlan exists in database
          $vlan_insert_sth->execute($ifvlan);
          
          # Get unique id for this interface port
          $port_insert_sth->execute($ifname, $host_id);
          $port_select_sth->execute($ifname, $host_id);
          my $port_id = $port_select_sth->fetchrow_hashref->{'id'};
          warn "WARNING! $host port id undefined while updating port, should not be possible\n" unless defined $port_id;

          # Update port information
          $port_update_sth->execute($iftype, $ifindex, $ifoper, $ifadmin, $ifspeed, $ifvlan, $ifalias, $port_id);
          warn $dbh->errstr if $dbh->errstr;
        }
        $vlan_insert_sth->finish;
        $port_insert_sth->finish;
        $port_select_sth->finish;
        $port_update_sth->finish;
        
      }

      # Extract ARP table data (ifIndex.a.b.c.d = CCCCCC)
      if ($host->data->{'ipNetToMediaPhysAddress'}) {
        $DEBUG && warn "$host has ipNetToMediaPhysAddress (ARP table)\n";
        my $mac_insert_sth = $dbh->prepare('INSERT IGNORE INTO macs (address) VALUES (?)');
        my $mac_select_sth = $dbh->prepare('SELECT id FROM macs WHERE address=?');
        my $port_insert_sth = $dbh->prepare('INSERT IGNORE INTO ports (name,host) VALUES (?,?)');
        my $port_select_sth = $dbh->prepare('SELECT id FROM ports WHERE name=? AND host=?');
        my $arp_insert_sth = $dbh->prepare('REPLACE INTO arpsightings (mac,ipn,port) VALUES (?,INET_ATON(?),?)');
        foreach my $suffix (sort by_dots keys %{$host->data->{'ipNetToMediaPhysAddress'}}) {
          my ($ifindex, @octets) = split(/\./, $suffix, 5);
          my $ifname = $host->data->{'ifName'}->{$ifindex};
          my $inet = join('.', @octets);
          # Convert the six ASCII characters to MAC address format e.g "01:23:45:67:89:ab"
          my $mac = join(':', map { unpack('h*', $_) } split(//, $host->data->{'ipNetToMediaPhysAddress'}->{$suffix}) );
          #warn "$host> ARP $inet via $ifname at $mac\n";
          
          # Get (or allocate) unique id for this MAC address
          $mac_insert_sth->execute($mac);
          $mac_select_sth->execute($mac);
          my $mac_id = $mac_select_sth->fetchrow_hashref->{'id'};
          warn "WARNING! $host mac id undefined while recording ARP, should not be possible\n" unless defined $mac_id;
          
          # Get unique id for this interface port
          $port_insert_sth->execute($ifname, $host_id);
          $port_select_sth->execute($ifname, $host_id);
          my $port_id = $port_select_sth->fetchrow_hashref->{'id'};
          warn "WARNING! $host port id undefined while recording ARP, should not be possible\n" unless $port_id;
          
          # Record ARP IP address sighting with port
          $arp_insert_sth->execute($mac_id, $inet, $port_id);
          
        }
        $mac_insert_sth->finish;
        $mac_select_sth->finish;
        $port_insert_sth->finish;
        $port_select_sth->finish;
        $arp_insert_sth->finish;
      }
      
      # Extract MAC forwarding data (n.n.n.n.n.n = port)
      if ($host->data->{'dot1dTpFdbPort'}) {
        $DEBUG && warn "$host has dot1dTpFdbPort (MAC table)\n";
        my $vlan_insert_sth = $dbh->prepare('INSERT INTO vlans (id) VALUES (?)');
        my $mac_insert_sth = $dbh->prepare('INSERT IGNORE INTO macs (address) VALUES (?)');
        my $mac_select_sth = $dbh->prepare('SELECT id FROM macs WHERE address=?');
        my $port_insert_sth = $dbh->prepare('INSERT IGNORE INTO ports (name,host) VALUES (?,?)');
        my $port_select_sth = $dbh->prepare('SELECT id FROM ports WHERE name=? AND host=?');
        my $macsighting_insert_sth = $dbh->prepare('REPLACE INTO macsightings (mac,vlan,port) VALUES (?,?,?)');
        foreach my $suffix (sort by_dots keys %{$host->data->{'dot1dTpFdbPort'}}) {
          my $mac = mac_from_dots($suffix);
          my $port = $host->data->{'dot1dTpFdbPort'}->{$suffix};
          my $ifindex = $host->data->{'dot1dBasePortIfIndex'}->{$port};
          next unless $ifindex; # Juniper sometimes returns invalid port (0) for vlan interface
          my $ifname = $host->data->{'ifName'}->{$ifindex};
          my $ifvlan = $host->data->{'ifVlan'}->{$ifindex} || 0; # undef = no vlan, e.g trunk
          #warn "$host> MAC $mac ifname=$ifname ifvlan=$ifvlan\n";
          
          # Get (or allocate) unique id for this MAC address
          $mac_insert_sth->execute($mac);
          $mac_select_sth->execute($mac);
          my $mac_id = $mac_select_sth->fetchrow_hashref->{'id'};
          warn "WARNING! $host mac id undefined while recording MAC sighting, should not be possible\n" unless defined $mac_id;
        
          # Get unique id for this interface port
          $port_insert_sth->execute($ifname, $host_id);
          $port_select_sth->execute($ifname, $host_id);
          my $port_id = $port_select_sth->fetchrow_hashref->{'id'};
          warn "WARNING! $host port id undefined while recording MAC sighting, should not be possible\n" unless defined $port_id;          
          
          # Ensure vlan ID exists in database
          $vlan_insert_sth->execute($ifvlan);

          # Record MAC sighting with vlan/port
          $macsighting_insert_sth->execute($mac_id, $ifvlan, $port_id);
          
        }
        $mac_insert_sth->finish;
        $mac_select_sth->finish;
        $port_insert_sth->finish;
        $port_select_sth->finish;
        $vlan_insert_sth->finish;
        $macsighting_insert_sth->finish;
      }

      $DEBUG && warn "$host finished\n---\n";              
    }
  );

  # Add hosts/oids to scan
  foreach my $host (@hosts) {
    # Make a list of OIDs to scan based on host class
    my @walk = map { $_->{'oid'} } grep { $_->{'hostclass'} == $host->{'class'} } @oids;
  
    $snmp->add(
      dest_host => $host->{ip},
      walk => [ @walk ],
    );
  }

  # Start scan
  $snmp->execute();
  
  

  my $now = time;
  my $interval = 60;
  my $delta = $interval - ($now % $interval);
  $DEBUG && warn "Time: $now -> will sleep $delta seconds\n";
  sleep ($delta);

}


sub query_result {
  my $session = shift;
  my $cols = [];
  my @records = ();
  while (my $line = <$session>) {
    chomp $line;
    last unless $line; # Empty line = end of response
    next if $line =~ /^#/; # Comment/message
    if ($line =~ /^\!/) {
      # Error
      warn "$0 $$ $line\n";
      last;
    }
    #warn "$0 $$ decode line=".$line."\n";
    if ($line =~ /^\@(.+)/) { $cols = $json->decode($1); next; }
    my $row = $json->decode($line);
    my %hash = ();
    @hash{@{$cols}} = @{$row}; 
    push @records, \%hash;
    #warn "$0 $$ hash=".Dumper(\%hash);
  } 
  return @records;
}


# Usage: sort by_dots ARRAY
# ARRAY must contain strings of numbers separated by dots, eg. IP addresses or SNMP OIDs
sub by_dots {

  # Split by dots
  my @a = split(/\./, $a);
  my @b = split(/\./, $b);
  
  # Compare numerically until a difference is found
  while (@a || @b) {
    my $n1 = shift @a || 0;
    my $n2 = shift @b || 0;
    return -1 if ($n1 < $n2);
    return  1 if ($n1 > $n2);  
  }

  # ...or return 0 if no difference
  return 0;
}


# Usage: mac_from_dots('255.255.255.255.255.255') 
# Would return 'ff:ff:ff:ff:ff:ff"
sub mac_from_dots {
  my $dots = shift;
  return join(':', map { sprintf('%02x', $_) } split(/\./, $dots) );
}



__END__

# Below are some sample SQL queries to make use of the information collected by this script


# Search MAC address table for most recent sightings

SELECT 
  m.address, 
  IF(p.vlan = 0, "trunk", p.vlan) AS vlan, 
  h.name, 
  p.name, 
  p.description, 
  ms.recorded
FROM macsightings AS ms
LEFT JOIN macs AS m ON (m.id = ms.mac)
LEFT JOIN ports AS p ON (p.id = ms.port)
LEFT JOIN hosts AS h ON (h.id = p.host)
ORDER BY m.address, ms.recorded
;


# Search ARP table for most recent MAC sightings

SELECT 
  m.address, 
  INET_NTOA(a.ipn) AS ip,  
  IF(p.vlan = 0, "trunk", p.vlan) AS vlan, 
  h.name, 
  p.name, 
  p.description, 
  a.recorded
FROM arpsightings AS a
LEFT JOIN macs AS m ON (m.id = a.mac)
LEFT JOIN ports AS p ON (p.id = a.port)
LEFT JOIN hosts AS h ON (h.id = p.host)
ORDER BY m.address, a.recorded
;


# Search ARP table for the most recent IP sightings

SELECT 
  INET_NTOA(a.ipn) AS ip, 
  m.address, 
  IF(p.vlan = 0, "trunk", p.vlan) AS vlan, 
  h.name, 
  p.name, 
  p.description, 
  a.recorded
FROM arpsightings AS a
LEFT JOIN macs AS m ON (m.id = a.mac)
LEFT JOIN ports AS p ON (p.id = a.port)
LEFT JOIN hosts AS h ON (h.id = p.host)
ORDER BY a.ipn, a.recorded
;


# Select port information for hosts

SELECT
  h.name AS host,
  p.name AS interface,
  t.name AS type,
  IF(p.admin = 1, "up", "down") AS admin,
  IF(p.up = 1, "up", "down") AS oper,
  p.speed,
  p.vlan,
  p.description
FROM ports AS p
LEFT JOIN hosts AS h ON (h.id = p.host)
LEFT JOIN snmp_iftypes AS t ON (t.id = p.type)
ORDER BY h.name, p.index
;