thanos1983 has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Not reasonable output from SNMP Request
by soonix (Chancellor) on Feb 09, 2014 at 14:51 UTC

      To: soonix

      Thanks a lot, this is explains the unreasonable output. I changed all the table values from int to bigint. All values where I store the measurements. Beginners failures ;)

Re: Not reasonable output from SNMP Request
by Laurent_R (Canon) on Feb 09, 2014 at 18:42 UTC
    2,147,483,647 (= 2^^31 - 1) is the largest signed integer that can be stored on 32 bits.

      To: Laurent_R

      Thanks for the mathematical analysis this helps to understand the limitation of int in numerical values. I was so confused this is a big relief.

Re: Not reasonable output from SNMP Request
by t_rex_joe (Sexton) on Feb 09, 2014 at 23:18 UTC
    Congrats on the progress for developing your own stats table for network devices. The concept is that of higher level programmers looking for the next challenge.

    Here's some tips I use in my "find a device on the network program" including example code.

    -- Dont use timestamps in the db as a way to store insert times... when restoring a backup the timestamp will take the current timestamp and insert not the one in the backup. (unless they fixed this bug) I use the integer type and use epoch time for update/insert time.

    #!/usr/bin/perl -w use strict; use warnings; use Date::Manip; # Convert to EPOCH my $ptime = ParseDate("now"); print "PTIME: \"$ptime\"\n"; my $sepoch = UnixDate($ptime,"%s"); print "SEPOCH: \"$sepoch\"\n"; #$dtime = UnixDate($ptime, "%Y-%m-%d %H:%M:%S"); #print "DTIME: \"$dtime\"\n"; # Convert it back my $date = ParseDateString("epoch $sepoch"); print "DATE: \"$date\"\n"; my $ndtime = UnixDate($date, "%Y-%m-%d %H:%M:%S"); print "NDTIME: \"$ndtime\"\n"; exit;
    Output:
    PTIME: "2014020918:06:03" SEPOCH: "1391987163" DATE: "2014020918:06:03" NDTIME: "2014-02-09 18:06:03"

    -- Use "bind_columns" instead of hashref. It will help with separating the values. Using the "hash_ref" will cause headaches when trying to debug.

    $query = undef; $sth = undef; $ect = 0; @row = (); $macidx = undef +; $query = "SELECT clmacidx FROM tblmac where clmac = ?"; if($debug == 1) { print "QUERY: \"$query\"\n"; } $sth = $dbh->prepare($query); $sth->execute($mac); $sth->bind_columns(undef, \$macidx); while(@row = $sth->fetchrow_array()) { $ect++; } $sth->finish; #### $macidx is now the value of "clmacidx" from select statement if($ect != 1) { print "UNK ECT: \"$ect\" FOR MAC: \"$mac\" L: \"" +. __LINE__ . "\" NEXT\n"; } if(!(defined $macidx) || ($macidx eq "")) { print "MACIDX: \"$macidx\" IS UNDEF NEXT L: \"" . __LINE__ . " +\"\n"; } if($debug == 1) { print "MACIDX: \"$macidx\" L: \"" . __LINE__ . " +\"\n"; }
    -- Check to see if the value exists in a seperate query before inserting.
    $query = undef; $sth = undef; $ect = 0; @row = (); $dbok = 1; $query = "SELECT cllseen, clmacidx FROM tblmac where clmac = ?"; if($debug == 1) { print "QUERY: \"$query\"\n"; } $sth = undef; $sth = $dbh->prepare($query); $sth->execute($mac); my $mts = undef; $macidx = undef; $sth->bind_columns(undef, \$mts, \$macidx); while(@row = $sth->fetchrow_array()) { $ect++; } $sth->finish; if($debug == 1) { print "ECT: \"$ect\"\n"; } if($ect == 0) { if($debug == 1) { print "MAC: \"$mac\" DOES -NOT- EXIST. INSERTI +NG L: \"" . __LINE__ . "\"\n"; } ### DB $query = undef; $sth = undef; $query = "INSERT INTO \`tblmac\` (clits, clts, clmac, clupby, cl +lseen) values (?,?,?,?,?)"; if($debug == 1) { print "QUERY: \"$query\"\n"; } eval { $sth = $dbh->prepare($query); $sth->execute($ineptime,$in +eptime,$mac,$val,$ineptime); 1; } or do { print "$dtime CANT CONNECT TO SERVER L: \"" . __LINE__ +. "\" SLEEPING 30\n"; sleep 30; return($dbh); }; $sth->finish; ### EO DB }
    Next level for this concept. Create a script (cron fired) that only gets the values and places into a log file, then read the file by "File::Tail" and insert based on the timestamp (epoch at time of write).

    This will help if you have to null the table and start over not to mention backups.

    ex. "<epoch time>,<ip_of_device>,<interface num>,<stats>"
        "1391987163,10.1.1.1,4,21234453"

    Joe

      To: t_rex_joe

      Wow, this is an impressive tutorial!!!! ;) Thanks a lot!!!

      To be 100% honest since I am a beginner in coding too, I have difficulties to understand all coding with a quick look but I will try to implement as much as possible of your suggestions and since where I can get ;).

      I wish there was a +10 vote this support help beginners like me a lot! Again thank you for time and effort to assist with my first steps

        thanos1983,

        No problem. The book I learned most from is "MySQL and Perl for the Web, By Paul DuBois".

        A lot of good examples in this book including the snippets I use today in my Code.

        If you have anymore question please create a question and send me a pm. so we all can learn.

        Joe