#!/usr/bin/perl -w # # This is a script used to query the Polycom MGC for IPs of connected endpoints # and update a Quagga db for engaging QoS measures. # It assumes that a Quagga DB maintains info and state of currently connected endpoints # and that a custom database (rts-web) of registered user endpoints can verify IPs # of the locally managed endpoints (as third-party endpoints are excluded). # use strict; use DBI; use Getopt::Std; use Getopt::Std; use Net::SNMP; #Definitions of variables my ($session, $error, $result, $ip, $index, $dbh, $query, $sth, $sth2); my ($addr, $state, $insert, $update, $dbh2, $sth3); my %ips; #Start SNMP session to get currently connected endpoints on the MCU ($session, $error) = Net::SNMP->session( -hostname => '195.251.25.55', -community => 'g3netr+s', -port => 161 ); #This is the Polycom MGC oid for retrieving the IPs of connected endpoints my $oid = '.0.0.8.341.1.1.4.3.2.1.4'; #Put the SNMP results into our table $result = $session->get_table( -baseoid => $oid); #Connect to quagga db $dbh = DBI->connect('DBI:mysql:quagga:localhost','quagga','ctiru6') || die "Couldn't connect to database: " . $DBI::errstr; #Connect to custom (rts-web) db $dbh2 = DBI->connect('DBI:mysql:rts-web:localhost','quagga','ctiru6') || die "Couldn't connect to database: " . $DBI::errstr; #For each element of table (connected endpoint) do the following: foreach $index (keys(%$result)) { $ip=$result->{$index}; #Maintain a hash table of IPs that were retrieved from the MCU, #for use later in the script $ips{$ip} = 1; print "Processing $ip\n"; #Check IP if it's already in quagga db (thus has been seen before) $query = "SELECT addr,metric,state,ip_ver FROM qos_rts WHERE addr='$ip'"; $sth = $dbh->prepare($query) || die "Prepare Error:". $dbh->errstr; $sth->execute() || die "Execute Error:".$sth->errstr; #Check IP if it's in rts-web to see if it is managed locally in our network $query = "SELECT * FROM USER WHERE IPAddress='$ip'"; $sth2 = $dbh2->prepare($query) || die "Prepare Error:". $dbh2->errstr; $sth2->execute() || die "Execute Error:".$sth2->errstr; #If not locally managed, don't take any QoS measures for it, ignore it. if ($sth2->rows == 0) {print "$ip not in the USER table, skipping...\n";next;} #If locally managed and not in quagga db, insert it in quagga db # marking it with state=new, so that the Quagga daemon can take QoS measures if ($sth->rows == 0) { print "Inserting $ip into the database\n"; $insert = "INSERT INTO qos_rts (addr,metric,state,ip_ver) VALUES('$ip',0,'new','4');"; print $insert."\n"; $sth2 = $dbh->prepare($insert); $sth2->execute() || die "Execute Error:".$sth2->errstr; # print $sth2->errstr."\n"; } #If locally managed and already in quagga db, do nothing else { print "$ip already in the database\n"; } } # Having processed all IPs from the SNMP query, # check to see which IPs exist in the Quagga db # but were not encountered on the MCU # Get all IPs and their state from the Quagga db $query = "SELECT addr,state FROM qos_rts;"; $sth = $dbh->prepare($query); $sth->execute || die "Execute Error:".$sth->errstr; while (($addr,$state)=($sth->fetchrow_array)) { # #If IP is not connected to the MCU and Quagga state is not already marked as "deleted" # set state=deleted # if (!$ips{$addr} && ($state ne 'deleted')) { print "Marking $addr as deleted in the database\n"; $update = "UPDATE qos_rts SET state='deleted' WHERE addr='$addr';"; $sth2 = $dbh->prepare($update); $sth2->execute || die "Execute Error:".$sth2->errstr; } }