Dear Monks,
Thanks is advance for reading and assisting us the beginers in every perl aspect that we might encounter.
I creating a SNMP request on a server (connection information stored in MySQL) and based on his reply I either insert the data or update them in MySQL table again.
I want to collect the inOctets, outOctets and the SysUptime. The program seems to be operating correctly apart from the part of the outOctets. I am using Dumper to observe the reply based on the oids and I also print before and after the Insert process and also the update process.
Terminal output.
$VAR1 = { '1.3.6.1.2.1.2.2.1.16.3' => 3640701351, '1.3.6.1.2.1.2.2.1.10.5' => 142854533, '1.3.6.1.2.1.2.2.1.16.5' => 2402806828, '1.3.6.1.2.1.2.2.1.10.3' => 1910386961, '1.3.6.1.2.1.1.3.0' => 41646422 }; Before insert new_out_oid_loop: 3640701351 and interface: 3! After insert new_out_oid_loop: 3640701351 and interface: 3!
So based on this output theoretically I should have outOctets: 3640701351 but in MySQL I can see: 2147483647!!!
It dose not make sense, initially I thought that might this is an error of MySQL or my computer so I used another computer to run the program and I get the same output.
Then I changed the input value from outOctets to inOctets and the number is stored normally.
So the only option left is that somewhere somehow I am altering the number. I have run out of options and possible ideas is there anyone with a fresh pair of eyes to take a look. I have spend 2 days now and still can not find the error.
My code is provided under so if anyone can not see the error straight can executed and possible implement another printf() or something that will assist him to find the problem.
#! /usr/local/bin/perl use strict; use warnings; use DBI; use MyConfig; use DBD::mysql; use Data::Dumper; use POSIX qw (strftime); use Net::SNMP qw (:snmp); use Time::HiRes qw ( time usleep gettimeofday tv_interval ); $|=1; #flush every time the program $\="\n"; #Debug process $|=1; my ($community,$id,$public,$interfaces,$dsn,$port,$ip,$num_interfaces, +$j,$oid,$in_av_bitrate,$out_av_bitrate,$oid_time_new,$dsn_db,$checkEx +ist,$query,$result,$update_time); my %config = getconfig(); my $DATABASE = 'test'; my $HOST = 'localhost'; my $MYSQLPORT = '3306'; my $USERNAME = 'root'; my $PASSWD = ''; my $DEVICE_TABLE = 'Interfaces'; my $DEVICES = 'Devices'; my $in_community = 'public'; my $in_port = '161'; my $in_IP = ''; my $in_interfaces = '3 5'; my $dbh = DBI->connect("dbi:mysql::$HOST:$MYSQLPORT", $USERNAME, $PASSWD, { 'PrintError' => 1, 'RaiseError' => 1 } ) or die "Could not connect to ". $HOST .": ". $DBI::errstr ."\n"; my $databases = $dbh->do("SHOW DATABASES LIKE '".$DATABASE."'") or die + "Error: " .dbh->errstr. "\n"; if ($databases eq 1) { printf "Database: ". $DATABASE ." exists not creating: ". $DATABAS +E ."\n"; } else { printf "Database: ". $DATABASE ." does not exist creating: ". $DAT +ABASE ."\n"; $checkExist = $dbh->prepare("CREATE DATABASE IF NOT EXISTS `".$DAT +ABASE."`") or die "Could not create the: ". $DATABASE ." error: ". $d +bh->errstr ."\n"; if (!$checkExist->execute) { die "Error: ". $checkExist->errstr ."\n"; } } $dbh->disconnect(); $dbh = DBI->connect("dbi:mysql:$DATABASE:$HOST:$MYSQLPORT", $USERNAME, $PASSWD, { 'PrintError' => 1, 'RaiseError' => 1 } ) or die "Could not connect to ". $HOST .": ". $DBI::errstr ."\n"; my $tables = $dbh->do("SHOW TABLES FROM `".$DATABASE."` WHERE Tables_i +n_".$DATABASE." LIKE '".$DEVICE_TABLE."'") or die "Error: " .dbh->err +str. "\n"; if ($tables eq 1) { printf "Table: ". $DEVICE_TABLE ." exists not creating: ". $DEVICE +_TABLE ."\n"; } else { printf "Table: ". $DEVICE_TABLE ." does not exist creating: ". $DE +VICE_TABLE ."\n"; # timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES +TAMP $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `".$DEVICE +_TABLE."` ( `id` int(11) NOT NULL AUTO_INCREMENT, `interface_id` int(11) NOT NULL DEFAU +LT '0', `IP` varchar(15) COLLATE utf8mb4_unic +ode_ci NOT NULL, `in_bitRate` int(11) NOT NULL DEFAULT + '0', `bitrate_av_in` int(11) NOT NULL DEFA +ULT '0', `out_bitRate` int(11) NOT NULL DEFAUL +T '0', `bitrate_av_out` int(11) NOT NULL DEF +AULT '0', `avg_in_bitRate` int(11) NOT NULL DEF +AULT '0', `avg_out_bitRate` int(11) NOT NULL DE +FAULT '0', `first_update_time` timestamp NOT NUL +L DEFAULT '0000-00-00 00:00:00', `last_update_time` timestamp NOT NULL + DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sysUpTime` int(11) NOT NULL DEFAULT +'0', `last_in_octets` int(11) NOT NULL DEF +AULT '0', `last_out_octets` int(11) NOT NULL DE +FAULT '0', `cummulative_in` int(11) NOT NULL DEF +AULT '0', `cummulative_out` int(11) NOT NULL DE +FAULT '0', `counter` int(11) NOT NULL DEFAULT '0 +', PRIMARY KEY (`id`), UNIQUE KEY `interface_id` (`interface +_id`,`IP`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 + COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1 ;"); if (!$checkExist->execute) { die "Error: ". $checkExist->errstr ."\n"; } $checkExist->finish(); } $tables = $dbh->do("SHOW TABLES FROM `".$DATABASE."` WHERE Tables_in_" +.$DATABASE." LIKE '".$DEVICES."'") or die "Error: " .dbh->errstr. "\n +"; if ($tables eq 1) { printf "Table: ". $DEVICES ." exists not creating: ". $DEVICES ."\ +n"; } else { printf "Table: ". $DEVICES ." does not exist creating: ". $DEVICES + ."\n"; $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `". $DEVIC +ES ."` ( `id` int(11) NOT NULL AUTO_INCREMENT, `IP` varchar(15) CHARACTER SET utf8mb4 + COLLATE utf8mb4_unicode_ci NOT NULL, `PORT` int(11) NOT NULL, `COMMUNITY` tinytext COLLATE latin1_bi +n NOT NULL, `INTERFACES` text COLLATE latin1_bin N +OT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 + COLLATE=latin1_bin AUTO_INCREMENT=1 ;"); if (!$checkExist->execute) { die "Error: ". $checkExist->errstr ."\n"; } $checkExist->finish(); $checkExist = $dbh->prepare("INSERT IGNORE INTO `". $DEVICES ."` ( +`IP`,`PORT`,`COMMUNITY`,`INTERFACES`) VALUES ('".$in_IP."','".$in_por +t."','".$in_community."','ALL') "); if (!$checkExist->execute) { die "Error: ". $checkExist->errstr ."\n"; } $checkExist->finish(); $checkExist = $dbh->prepare("INSERT IGNORE INTO `". $DEVICES ."` ( +`IP`,`PORT`,`COMMUNITY`,`INTERFACES`) VALUES ('".$in_IP."','".$in_por +t."','".$in_community."','".$in_interfaces."') "); if (!$checkExist->execute) { die "Error: ". $checkExist->errstr ."\n"; } $checkExist->finish(); } $oid= $oid_time_new = $in_av_bitrate = $out_av_bitrate = $num_interfac +es = 0; my $counter = 1; my $number = 0; # Select and Extract/retrieve data from table. my $sth = $dbh->prepare("SELECT * FROM `".$DEVICES."`") || die "SQL Er +ror: $dbh::errstr\n"; if (!$sth->execute) { die "Error: ". $sth->errstr ."\n"; } # Check if there are data inside the rows of the table if ($sth->rows > 0) { while (my $ref = $sth->fetchrow_hashref()) { $id = $ref->{'id'}; $ip = $ref->{'IP'}; $port = $ref->{'PORT'}; $community = $ref->{'COMMUNITY'}; $interfaces = $ref->{'INTERFACES'}; #print("This is the IP: ".$ip."!\n"); if ($interfaces eq 'ALL') { printf "The ID: %s has interfaces set to ALL proceeding to nex +t ID.\n", $id; next; } program (); } # End of While loop } # End of if loop else { # If rows return empty exit printf "The table: ".$DEVICES." is empty, user need to enter data. + Exit!!!\n"; exit 1; } # End of else loop sub program { my $if_inOctets = '1.3.6.1.2.1.2.2.1.10'; my $if_outOctets = '1.3.6.1.2.1.2.2.1.16'; my $sysUpTime = '1.3.6.1.2.1.1.3.0'; # Initiate snmp session my ($session, $error)= Net::SNMP->session( -hostname => $ip || 'localhost', -community => $community || 'public', -nonblocking => 0, -port => $port, -version => 'snmpv1', -translate => 0, ); if (!defined ($session)) { printf "ERROR: Failed to queue get request for host '%s': %s!\n", +$ip, $error; exit 1; } my (@bitrate_in,@bitrate_out,@memory,@in_oid,@out_oid,@request) = +(); @memory = split (' ',$interfaces); for ($j = 1 ; $j <= scalar (@memory) ; $j++) { $result = $j - 1; $in_oid[$j-1] = ($if_inOctets.".".$memory[$j-1]); $out_oid[$j-1] = ($if_outOctets.".".$memory[$j-1]); } #printf Dumper(\@memory); push (@request, (@in_oid,@out_oid,$sysUpTime)); #printf Dumper(\@request); $num_interfaces = scalar (@memory); #printf "This is the number of Interfaces: ".$num_interfaces."\n"; my (@cummulative_in) = (0) x $num_interfaces; my (@cummulative_out) = (0) x $num_interfaces; my (@new_in_oid_loop) = (0) x $num_interfaces; my (@new_out_oid_loop) = (0) x $num_interfaces; my (@bitrate_av_in) = (0) x $num_interfaces; my (@bitrate_av_out) = (0) x $num_interfaces; my (@old_in_oid_loop) = (0) x $num_interfaces; my (@old_out_oid_loop) = (0) x $num_interfaces; my (@in_av_bitrate) = (0) x $num_interfaces; my (@out_av_bitrate) = (0) x $num_interfaces; my $oid_time_old = 0; #$oid = $session->get_request( #-varbindlist => [$sysUpTime], #); $oid = $session->get_request( -varbindlist => \@request , ); # In case of an error print the error if (!defined ($oid)) { printf "Problem: with session get request: %s.\n", $session->error +(); $session->close(); exit 1; } #printf "The sysUpTime for host '%s' is %s.\n", #$session->hostname(), $oid->{$sysUpTime}; #print("This is Dumper before Loop\n"); #print Dumper($oid); #exit(); for ($j=1; $j <= $num_interfaces; $j++) { print Dumper($oid); $new_in_oid_loop[$j-1] = $oid->{$in_oid[$j-1]}; #printf("This is the in new_in_oid_loop: %s and interface: %s!\n", +$new_in_oid_loop[$j-1],$memory[$j-1]); $new_out_oid_loop[$j-1] = $oid->{$out_oid[$j-1]}; #printf("This is the in new_out_oid_loop: %s and interface: %s!\n" +,$new_out_oid_loop[$j-1],$memory[$j-1]); $oid_time_new = $oid->{$sysUpTime}; #printf("This is the sysuptime: %s!\n",$oid_time_new); my $database_hash = $dbh->prepare("SELECT * FROM `".$DEVICE_TABLE. +"` WHERE `interface_id` = $memory[$j-1]") || die "SQL Error: $dbh::er +rstr\n"; $database_hash->execute() || die "SQL Error: $dbh::errstr\n"; if ($database_hash->fetch()) { my $database_hash = $dbh->prepare("SELECT * FROM `".$DEVICE_TA +BLE."` WHERE `interface_id` = $memory[$j-1]") || die "SQL Error: $dbh +::errstr\n"; $database_hash->execute() || die "SQL Error: $dbh::errstr\n"; while (my $database_ref = $database_hash->fetchrow_hashref()) +{ $old_in_oid_loop[$j-1] = $database_ref->{'last_in_octets'}; $old_out_oid_loop[$j-1] = $database_ref->{'last_out_octets'}; $bitrate_av_in[$j-1] = $database_ref->{'bitrate_av_in'}; $bitrate_av_out[$j-1] = $database_ref->{'bitrate_av_out'}; $oid_time_old = $database_ref->{'sysUpTime'}; $counter = $database_ref->{'counter'}; $counter++; if ($new_in_oid_loop[$j-1] >= $old_in_oid_loop[$j-1]) { $bitrate_in[$j-1] = 800*($new_in_oid_loop[$j-1]-$old_in_oi +d_loop[$j-1])/($oid_time_new - $oid_time_old); $bitrate_av_in[$j-1] = $bitrate_av_in[$j-1] + $bitrate_in[ +$j-1]; $bitrate_out[$j-1] = 800*($new_out_oid_loop[$j-1]-$old_out +_oid_loop[$j-1])/($oid_time_new - $oid_time_old); $bitrate_av_out[$j-1] = $bitrate_av_out[$j-1] + $bitrate_o +ut[$j-1]; $in_av_bitrate[$j-1] = $bitrate_av_in[$j-1]/$counter; $out_av_bitrate[$j-1] = $bitrate_av_out[$j-1]/$counter; } #end of if loop else { #$bitrate_in[$j-1] = 800*(((2**32)-1-$old_in_oid_loop[$j-1 +])+$new_in_oid_loop[$j-1])/($oid_time_new - $oid_time_old); #$bitrate_av_in[$j-1] = $bitrate_av_in[$j-1] + $bitrate_in +[$j-1]; #$bitrate_out[$j-1] = 800*(((2**32)-1-$old_out_oid_loop[$j +-1])+$new_out_oid_loop[$j-1])/($oid_time_new - $oid_time_old); #$bitrate_av_out[$j-1] = $bitrate_av_out[$j-1] + $bitrate_ +out[$j-1]; #$in_av_bitrate[$j-1] = $bitrate_av_in[$j-1]/$counter; #$out_av_bitrate[$j-1] = $bitrate_av_out[$j-1]/$counter; } #end of else loop #printf("This is the update part!\n"); printf("This is in: %s!\n",$new_in_oid_loop[$j-1]); printf("This is out: %s!\n",$new_out_oid_loop[$j-1]); my $update_statement = $dbh->prepare("UPDATE `".$DEVICE_TABLE. +"` SET `IP`='".$ip."',`in_bitRate`='".$bitrate_in[$j-1]."',`bitrate_a +v_in`='".$bitrate_av_in[$j-1]."',`out_bitRate`='".$bitrate_out[$j-1]. +"',`bitrate_av_out`='".$bitrate_av_out[$j-1]."',`avg_in_bitRate`='".$ +in_av_bitrate[$j-1]."',`avg_out_bitRate`='".$out_av_bitrate."',`last_ +update_time`= FROM_UNIXTIME(NOW()),`sysUpTime`='".$oid_time_new."',`l +ast_in_octets`='".$new_in_oid_loop[$j-1]."',`last_out_octets`='".$new +_out_oid_loop[$j-1]."',`cummulative_in`= `cummulative_in` + '".$new_i +n_oid_loop[$j-1]."',`cummulative_out`= `cummulative_out` + '".$new_in +_oid_loop[$j-1]."',`counter`='".$counter."' WHERE `IP` = '$ip' AND `i +nterface_id` = '$memory[$j-1]'") || die "SQL Error: ". $dbh::errstr . +"\n"; $update_statement->execute() || die "SQL Error: ". $dbh::errst +r ."\n"; }# End of while condition } #end of if condition else { #printf("This is the Insert part!\n"); #printf("Before insert new_out_oid_loop: %s and interface: %s! +\n",$oid->{$out_oid[$j-1]},$memory[$j-1]); my $insert_statement = $dbh->prepare("INSERT IGNORE INTO `".$D +EVICE_TABLE."` (`interface_id`,`IP`,`first_update_time`,`last_update_ +time`,`sysUpTime`,`last_in_octets`,`last_out_octets`,`cummulative_in` +,`cummulative_out`,`counter`) VALUES ('".$memory[$j-1]."','".$ip."',F +ROM_UNIXTIME(NOW()),FROM_UNIXTIME(NOW()),'".$oid_time_new."','".$new_ +in_oid_loop[$j-1]."','".$new_out_oid_loop[$j-1]."','".$new_in_oid_loo +p[$j-1]."','".$new_out_oid_loop[$j-1]."','".$counter."')") || die "SQ +L Error: ". $dbh::errstr ."\n"; $insert_statement->execute() || die "SQL Error: ". $dbh::errst +r ."\n"; } # end of else condition (INSERT) } #end of for loop $session->close(); } # end of subprogram $sth->finish(); # Disconnect $dbh->disconnect();
Again thank you for your support and effort to assist me.
In reply to Not reasonable output from SNMP Request by thanos1983
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |