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