#!/usr/local/bin/php -q
<?
/*
 Network Interface Statistics Collection Agent
 by Brett Baugh
 */

// Delete or comment out the "echo" line below to enable this script
// after you've read the comments.  :)
echo "You need to edit this script and read the comments at its\nbeginning before running it. Important stuff in there...\n\n"; die;

/*
 This update will take *quite* a while if you have a lot of data
 in your database. On my 1.2GHz with a 45-meg database containing
 roughly 300,000 entries, it took about three minutes (and cut 20
 meg off the stats table's size :)

 IT IS RECOMMENDED that you run this script with its output piped
 into a file, which you can "tail -f" so you can watch the update's
 progress. This is just in case the update fails, so you know what
 to tell me should you need help. This script will not destroy any
 existing data unless the stats table update goes *perfectly*. It
 works by first adding and filling in the new "if_id" column, then
 checking to make sure *every one* of the rows in "stats" got a
 non-zero value put into its if_id column. If that test passes,
 *then* it deletes three unneeded columns, both indices, and the
 primary key. It then adds a new primary key and goes on its way.

 Oh, did I mention that once this script has run, you can't run
 it again? :)  "mysqldump -a stats > backup.sql" is all I can say.
 I've only seen it fail *once* so far, and that was on a suspicious
 database, so it may never fail on anyone out there. But just in
 case.........

 Updating could possibly use up a lot of memory, too, so try to have
 at least 30 meg free (that's RAM and swap space, not disk space).
 If you don't run this script as root, you may run into the maximum
 allowed memory usage limit, set in your "php.ini" file (it defaults
 to 8 meg; pretty tiny for our needs). Run it as root. :)

 This script updates a version 2.0 installation of NISCA to
 version 2.1, and ONLY version 2.0.  It alters your database,
 it modifies your configuration, it does a lot... so be sure
 to back up your database before running it, usually by running
 "mysqldump stats > backup.sql". To restore this dump, you would
 do something like:

 echo y|mysqladmin drop DATABASE_NAME
 mysqladmin create DATABASE_NAME
 mysql DATABASE_NAME < backup.sql

 ...and be sure to redo the privileges the user NISCA connects as
 has to DATABASE_NAME if necessary.

 Okay, here endeth the comments; update away.....

 */

?>

NOTICE:  You're going to see a MySQL error below here caused by
functions.php trying to load data from a table that doesn't exist
yet. This is perfectly ok! Just ignore it and all will be well. :)

<?
require_once("/etc/nisca/nisca.conf");
require_once($location["functions"]);

if ($version != "2.0") {
  echo "\n\nSorry; you need to upgrade (or downgrade) from\n" .
       "v$version to v2.0 before running this... Dying.\n\n";
  die;
}
?>

Okay. Any errors you see beyond this point are abnormal
and should have some attention payed to them...

<?
$version="2.1";
set_time_limit(0);

// First, update the version number in the database.
$sq="update config set version='$version'";
$res=dosql($sq);

// Now add the new stats_ifs table...
$sq="create table stats_ifs(
        id mediumint not null auto_increment primary key,
        hostname varchar(64) not null,
        community varchar(64) not null,
        if_name varchar(64) not null,
        index idx1 (hostname, if_name),
        index idx2 (hostname, community, if_name)
)";
$res=dosql($sq);

// Let's show the user an estimate of how long they'll have to wait for this....
$sq="select count(stamp) as cnt from stats";
$res=mysql_query($sq);
$numrows=mysql_result($res,0,"cnt");
echo "There are " . number_format($numrows) . " database entries to be waded through and\n" .
  "converted. My 1.2-GHz machine does about 1,958 rows per second with an\n" .
  "optimized Zend Optimizer v1.1.0 (1,651 rows per second without it);\n";

if ($fp=file("/proc/cpuinfo")) {

/*
     rate = <number of rows> / <number of seconds>; thus,
     <number of seconds> = <number of rows> / rate
 My 1,202.765 MHz machine does about 1,958 rows per second.
 So that means roughly 1.628 rows per second per megahertz.
 So we multiply THIS machine's cpu speed (in MHz) by 1.628,
 and that's its rate; then we divide the number of rows
 by this machine's rate and we'll have the number of seconds
 it'll take the update to run. Roughly. Whew.
 */

  foreach($fp as $line) {
    if (preg_match('/^\s*cpu MHz\s*:\s*([\d\.]+)/i', $line, $regs)) {
      $cpu=$regs[1];
      break;
    }
  }
  $eta=sprintf("%0.1f", $numrows/($cpu * 1.628));
  echo "your machine should take about $eta seconds. Please wait.\n\n";
} else {
  echo "adjust similarly to figure out how long converting your database will take.\nPlease wait.\n\n";
}
$start_time=getmicrotime();

// Now we get to have lots of fun splitting the stats table into
// stats and stats_ifs... like it should have been all along.

echo "\n---------------------------------------------------------------------------\n" .
     "Creating \"stats_ifs\" table... "; flush();
$sq="select distinct hostname, community, if_name from stats order by hostname, community, if_name";
$res=dosql($sq);
$if_count=mysql_num_rows($res);
if ($if_count > 0) {
// Add the "if_id" column to the stats table.
  $sq2="alter table stats add column if_id mediumint default 0 not null first";
  $res2=dosql($sq2);

// Now, populate the if_id column of each row of "stats" and add to the "stats_ifs" table;
// this is the part that takes the longest. Go have lunch.
  while ($row=mysql_fetch_array($res)) {
    $h=ss($row["hostname"]);
    $c=ss($row["community"]);
    $i=ss($row["if_name"]);
    $sq2="insert into stats_ifs values(0, '$h', '$c', '$i')";
    $res2=dosql($sq2);
    $id=mysql_insert_id();
    $sq2="update stats set if_id=$id where (hostname='$h' AND community='$c' AND if_name='$i')";
    $res2=dosql($sq2);
  }
  $sq="select hostname, community, if_name from stats where (if_id is null OR if_id=0)";
  $res=dosql($sq);
  if (mysql_num_rows($res) > 0) {
    echo "oh dear.\nSome of the entries in the \"stats\" table didn't get converted;\n" .
      "specifically, the ones for these interfaces:\n";
    while ($row=mysql_fetch_array($res)) {
      echo 
      "===============================================================\n" .
      "  Host:\t\t" . ss($row["hostname"]) . "\n" .
      "  Community:\t" . ss($row["community"]) . "\n" .
      "  Interface:\t" . ss($row["if_name"]) . "\n";
    }
    echo "===============================================================\n\n" .
      "This shouldn't have happened.  Email Brett a letter telling him it happened to you\n" .
      "and he'll try to help you. phee@isthisthingon.org. Fnord.\n\n" .
      "$if_count rows were converted (not all successfully).\n" .
      "No existing data in your stats table will be destroyed, but you may\n" .
      "notice your database has gotten a bit larger.\n Processing continues...\n" .
      "---------------------------------------------------------------------------\n";
    flush();
  } else {
    echo "done.\nNow converting stats table; this WILL take awhile.\n";

// Now totally screw up the stats table. I hope you all made backups... :)
// This is the part that makes your database about 44% smaller.

    echo "Destroying primary key... "; flush();
    $sq="alter table stats drop primary key";
    $res=dosql($sq);

    echo "done.\nDestroying \"idx1\" index... "; flush();
    $sq="alter table stats drop index idx1";
    $res=dosql($sq);

    echo "done.\nDestroying \"idx2\" index... "; flush();
    $sq="alter table stats drop index idx2";
    $res=dosql($sq);

    echo "done.\nDestroying \"hostname\" column... "; flush();
    $sq="alter table stats drop column hostname";
    $res=dosql($sq);

    echo "done.\nDestroying \"community\" column... "; flush();
    $sq="alter table stats drop column community";
    $res=dosql($sq);

    echo "done.\nDestroying \"if_name\" column... "; flush();
    $sq="alter table stats drop column if_name";
    $res=dosql($sq);

    echo "done.\nAdding new primary key... "; flush();
    $sq="alter table stats add primary key (stamp,if_id)";
    $res=dosql($sq);

    echo "done.\nStats table conversion was successful.\n" .
      "$if_count interfaces were added to the \"stats_ifs\" table.\n" .
      "---------------------------------------------------------------------------\n";
    flush();
  }
} else {
  echo "Your stats table was empty. Converting to new format the quick and easy way... ";
  $sq="drop table if exists stats";
  $res=dosql($sq);
  $sq="create table stats(
        if_id mediumint not null,
        r_bytes bigint,
        r_packets bigint,
        r_errors bigint, 
        r_dropped bigint,
        t_bytes bigint,  
        t_packets bigint,
        t_errors bigint, 
        t_dropped bigint,
        stamp int not null,
	primary key (stamp,if_id)
  )";
  $res=dosql($sq);
  echo "done.\n" .
       "---------------------------------------------------------------------------\n";
}

// config.graph_fmt and reports.g_fmt hold the JPG/PNG toggle.
echo "Adding \"graph_fmt\" field to \"config\" table... ";
$sq="alter table config add column graph_fmt varchar(1) default 'p'";
$res=dosql($sq);
echo "done.\nAdding \"g_fmt\" field to \"reports\" table... ";
$sq="alter table reports add column g_fmt varchar(1) default 'p'";
$res=dosql($sq);

$elapsed=sprintf("%0.2f", getmicrotime() - $start_time);
echo "done.\n" .
       "---------------------------------------------------------------------------\n\n" .
       "Update to version 2.1 complete! It took $elapsed seconds.\n\n";
?>