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

 This script updates a version 1.3 installation of NISCA to
 version 2.0, and ONLY version 1.3.  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.

 There's one oddity about this script: it will use the community
 "public" for reports involving the localhost-only non-SNMP interfaces.
 Just edit your localhost reports manually after upgrading and change
 the interfaces to what they should be. Sorry; nothing I could do about it :(
 */

// Delete the line below to enable the functioning of this script.
echo "\nYou need to read the INSTALL file before upgrading to v2.0. Sorry :)\n\n"; die; 

// Change the default administrator password below if you wish.
// The "admin" user will be created with this password.
$admin_pw="change";

require_once("functions.bak");

if ($version != "1.3") {
  echo "\n\nSorry; you need to upgrade from v $version to v 1.3 before running this...\n\n";
  die;
}

$version="2.0";
set_time_limit(0);
snmp_set_quick_print(1);

// First, we convert all the reports to the new format.
echo "Altering \"reports\" table; please wait... "; flush();
$sq="alter table reports add column in_out varchar(4) default 'io' after reportctl";
$res=dosql($sq);

$sq="select id, host_if from reports order by id";
$res=dosql($sq);
if (mysql_num_rows($res)<1) {
  echo "\nNo reports found in database. Structure altered.\n----------------\n";
} else {
  $count=0;
  while ($row=mysql_fetch_array($res)) {
    if (! $arr=preg_split('/\*/', $row["host_if"])) {
      continue;
    }
    $id=$row["id"];
    $new="";
    foreach($arr as $hostif) {
      $tmp=strpos($hostif, ":");
      if ($tmp>0 && ! ereg("!", $hostif)) {
        $host=substr($hostif, 0, $tmp);
        $if=substr($hostif, $tmp+1);
      } else {
        continue 2;
      }
      $new.="$host!$if*";
    }
    $new=substr($new, 0, strlen($new)-1);
    $sq2="update reports set host_if='$new' where id=$id";
    $res2=dosql($sq2);
    $count++;
  }
  echo "complete.\n$count reports were converted.\n----------------\n";
}

// Now convert the stats table so if_name is varchar(64), not (32).
echo "Converting \"stats.if_name\" column; please wait... "; flush();
$sq="alter table stats modify column if_name varchar(64) not null";
$res=dosql($sq);
echo "complete.\n";

// Now add community to the stats table.
echo "Adding \"stats.community\" column; please wait... "; flush();
$sq="alter table stats add column community varchar(64) not null default '' after stamp";
$res=dosql($sq);
echo "complete.\n";

echo "Creating data for \"stats.community\" column; please wait... "; flush();
foreach ($hostname as $h) {
  echo "$h... ";
  $c=$snmp_community[$h];
  if ($c == "" && $snmp_community["*all*"] == "") {
    $c="public";
  } elseif ($c == "") {
    $c=$snmp_community["*all*"];
  }
  $sq="update stats set community='$c' where hostname='$h'";
  $res=dosql($sq);
}
echo "complete.\n";

// Now change the primary key on the stats table to include communities.
echo "Converting primary key on \"stats\" table; please wait... "; flush();
$sq="alter table stats drop primary key";
$res=dosql($sq);
$sq="alter table stats add primary key (hostname, community, if_name, stamp)";
$res=dosql($sq);
echo "complete.\n";

echo "Adding hostname, if_name index to \"stats\" table; please wait... "; flush();
$sq="alter table stats add index idx1 (hostname, if_name)";
$res=dosql($sq);
echo "complete.\n";
echo "Adding stamp index to \"stats\" table; please wait... "; flush();
$sq="alter table stats add index idx2 (stamp)";
$res=dosql($sq);
echo "complete.\n----------------\n";

// Now, we create the new tables to store the config information.
$sq="create table config(
        version varchar(10),
        delay int,
        have_gd varchar(1),
        ttf_font varchar(128),
        x smallint,
        y smallint,  
        graph_background varchar(6),
        graph_text varchar(6),
        graph_border_light varchar(6),
        graph_border_dark varchar(6),
        graph_peak_highlight varchar(6),
        graph_incoming_data varchar(6),
        graph_outgoing_data varchar(6),
        statsfile varchar(128),
        js_list varchar(1),
        dateformat varchar(30),
        dformat varchar(15),
        tformat varchar(15),
        bitbyte varchar(1),
        mailto varchar(255),
        collect_cmd varchar(255),
        snmp_cmd varchar(255),
        ps_cmd varchar(255))";
$res=dosql($sq);
echo "\"config\" table created.\n";

$sq="create table interfaces(
        hostname varchar(64) not null,
        if_name varchar(64) not null,
        community varchar(64) not null,
        dynamic varchar(1),
        snmpv2 varchar(1),
	alias varchar(255),
	speed bigint,
        primary key host_if (hostname, if_name, community))";
$res=dosql($sq);
echo "\"interfaces\" table created.\n";

$sq="create table localhost(
        hostname varchar(64) not null,
        if_name varchar(64) not null,
	alias varchar(255),
        primary key host_if (hostname, if_name))";
$res=dosql($sq);
echo "\"localhost\" table created.\n";
        
$sq="create table users(
        user varchar(50) not null primary key,
        pass varchar(50))";
$res=dosql($sq);
$sq="insert into users values('admin', encrypt('$admin_pw'))";
$res=dosql($sq);
echo "\"users\" table created and initialized.\n";
  
$sq="create table sessions(
        user varchar(50) not null primary key,
        session_id varchar(128))";
$res=dosql($sq);
echo "\"sessions\" table created.\n----------------\n";

// Then, we stuff the current config into the "config" table.
if ($have_gd != "") { $have_gd='y'; }
if ($X=="") { $X=700; }
if ($Y=="") { $Y=350; }
$sq="insert into config values('$version', $delay, '$have_gd', " .
    "'$ttf_font', $X, $Y, '$graph_background', '$graph_text', " .
    "'$graph_border_light', '$graph_border_dark', '$graph_peak_highlight', " .
    "'$graph_incoming_data', '$graph_outgoing_data', '$statsfile', 'y', " .
    "'m/d/Y, H:i:s', 'm/d/y', 'H:i:s', 'B', 'root@localhost', " .
    "'nohup /usr/local/sbin/collect.php > /dev/null &', " .
    "'nohup /usr/local/sbin/snmp_collect.php > /dev/null &', " .
    "'ps axw')";
$res=dosql($sq);
echo "\"config\" table populated.\n";

// Now, populate the "interfaces" table.
$count=0;
if (is_array($hostname) && count($hostname > 0)) {
  foreach ($hostname as $h) {
    $d=$dynamic[$h];
    if ($d == "" && $dynamic["*all*"] != "") {
      $d=$dynamic["*all*"];
    }
    if ($d != "") { $d="y"; }
    $c=$snmp_community[$h];
    if ($c=="" && $snmp_community["*all*"] == "") {
      $c="public";
    } elseif ($c == "") {
      $c=$snmp_community["*all*"];
    }

// Get the name-to-number map for this host.
    $nums=array();
    if ($a=snmpwalkoid($h, $c, ".1.3.6.1.2.1.2.2.1.2")) {
      for(reset($a); $k=key($a); next($a)) {
        $if_num=preg_replace('/^.+\.(\d+)$/', '\\1', $k);
        $name=ereg_replace('"', "", $a[$k]);
        $nums[$name]=$if_num;
      }
    }

// Get the ifSpeed for this interface.
    foreach ($interfaces[$h] as $i) {
      if ($a=snmpget($h, $c, ".1.3.6.1.2.1.2.2.1.5." . $nums[$i])) {
        $speed=$a;
      } else {
        $speed=0;
      }

// We'll default the SNMPv2 toggle to "no" and alias to ''.
      $sq="insert into interfaces values('$h', '$i', '$c', '$d', '', '', $speed)";
      $res=dosql($sq);
      $count++;
    }
  }
  echo "\"interfaces\" table populated with $count entries.\n";
} else {
  echo "\"interfaces\" table left empty. No SNMP hosts were defined.\n";
}

// Now the "localhost" table.
$count=0;
if ($local_hostname != "") {
  $tmp=preg_match('/\((.*)\)/', $local_interfaces, $regs);
  $tmp=array();
  if (preg_match('/\|/', $regs[1])) {
    $tmp=preg_split('/\|/', $regs[1]);
  } else {
    $tmp[0]=$regs[1];
  }
  $local_interfaces=$tmp;
  if (count($local_interfaces > 0)) {
    foreach($local_interfaces as $i) {
      $sq="insert into localhost values('$local_hostname', '$i', '')";
      $res=dosql($sq);
      $count++;
    }
    echo "\"localhost\" table populated with $count entries.\n----------------\n";
  } else {
    echo "\"localhost\" table left empty. No localhost interfaces were defined.\n----------------\n";
  }
}

echo "Update to version 2.0 complete!\n\n";
?>