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;

[download]
Output: 
PTIME: "2014020918:06:03"
SEPOCH: "1391987163"
DATE: "2014020918:06:03"
NDTIME: "2014-02-09 18:06:03"

[download]

-- 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"; }

[download]
-- 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

      }

[download]
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

In reply to Re: Not reasonable output from SNMP Request by t_rex_joe
in thread Not reasonable output from SNMP Request by thanos1983

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.