IEPM Database Table Structures
Wed Jul 9 03:18:12 PDT 2008

ALERTS CREATE TABLE `ALERTS` (
`commentsID` int(11) NOT NULL auto_increment,
`srcNodeID` int(11) NOT NULL default '0',
`destNodeID` int(11) NOT NULL default '0',
`epoch` int(10) unsigned NOT NULL default '0',
`commentType` varchar(255) default NULL,
`comment` text,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`active` int(11) default NULL,
PRIMARY KEY (`commentsID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ASN CREATE TABLE `ASN` (
`asnID` int(11) NOT NULL auto_increment,
`ipAddr` varchar(255) default NULL,
`asNumber` int(11) NOT NULL default '0',
`ipName` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`asnID`),
KEY `ipAddr` (`ipAddr`),
KEY `asNumber` (`asNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ASNINFO CREATE TABLE `ASNINFO` (
`asnInfoID` int(11) NOT NULL auto_increment,
`asNumber` int(11) NOT NULL default '0',
`description` varchar(255) default NULL,
`comment` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`asNumber`),
KEY `asnInfoID` (`asnInfoID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

BWDATA CREATE TABLE `BWDATA` (
`bwdataID` int(11) NOT NULL auto_increment,
`epoch` int(10) unsigned NOT NULL default '0',
`toolName` varchar(10) NOT NULL default '',
`throughput` double NOT NULL default '0',
`stream` int(10) unsigned default NULL,
`window_size` double default NULL,
`testtype` varchar(255) default NULL,
`results` text,
`scheduleID` int(11) NOT NULL default '0',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`thrumin` double default NULL,
`thrumax` double default NULL,
`thruavg` double default NULL,
`thrustd` double default NULL,
`rttmin` double default NULL,
`rttavg` double default NULL,
`rttmax` double default NULL,
`rttstd` double default NULL,
PRIMARY KEY (`bwdataID`),
KEY `epoch` (`epoch`,`toolName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

COMMENTS CREATE TABLE `COMMENTS` (
`commentsID` int(11) NOT NULL auto_increment,
`srcNodeID` int(11) NOT NULL default '0',
`destNodeID` int(11) NOT NULL default '0',
`epoch` int(10) unsigned NOT NULL default '0',
`commentType` varchar(255) default NULL,
`comment` text,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`active` int(11) default NULL,
PRIMARY KEY (`commentsID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

DIAG_RESULT CREATE TABLE `DIAG_RESULT` (
`resultID` int(11) NOT NULL auto_increment,
`hostID` int(11) NOT NULL default '0',
`targetID` int(11) NOT NULL default '0',
`epoch` int(10) unsigned NOT NULL default '0',
`triggerStart` varchar(255) default NULL,
`triggerDetected` varchar(255) default NULL,
`toolName` varchar(255) default NULL,
`htmlpath` varchar(255) default NULL,
`routeChange` int(11) default NULL,
`targetHost` int(11) default NULL,
`monHost` int(11) default NULL,
`networkProblem` int(11) default NULL,
`hostalias` varchar(255) default NULL,
PRIMARY KEY (`resultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

GRAPHS CREATE TABLE `GRAPHS` (
`graphid` int(11) NOT NULL auto_increment,
`graphic` blob,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`graphtitle` varchar(255) default NULL,
PRIMARY KEY (`graphid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

GROUPS CREATE TABLE `GROUPS` (
`groupID` int(11) NOT NULL auto_increment,
`groupName` varchar(255) default NULL,
`comment` varchar(255) default NULL,
`active` int(11) default '1',
PRIMARY KEY (`groupID`),
UNIQUE KEY `groupName` (`groupName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MONHOST CREATE TABLE `MONHOST` (
`monhostID` int(11) NOT NULL auto_increment,
`nodeID` int(11) NOT NULL default '0',
`srcDir` varchar(255) NOT NULL default '',
`privateDir` varchar(255) default NULL,
`reportsDir` varchar(255) NOT NULL default '',
`testDataDir` varchar(255) default NULL,
`remoteosDir` varchar(255) default NULL,
`security` varchar(255) default NULL,
`webPath` varchar(255) default NULL,
`siteName` varchar(255) default NULL,
`daysToAnalyze` int(11) default NULL,
`dataToAnalyze` text,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`cgiDir` varchar(255) default NULL,
`cgiPath` varchar(255) default NULL,
PRIMARY KEY (`monhostID`),
UNIQUE KEY `nodeID` (`nodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

NEWALERTS CREATE TABLE `NEWALERTS` (
`alertID` int(11) NOT NULL auto_increment,
`srcNodeId` int(11) NOT NULL default '0',
`destNodeId` int(11) NOT NULL default '0',
`epoch` int(11) NOT NULL default '0',
`triggerStart` varchar(255) NOT NULL default '',
`triggerDetected` varchar(255) NOT NULL default '',
`graphid1` int(11) default NULL,
`graphid2` int(11) default NULL,
`toolName` varchar(255) NOT NULL default '',
`percentDrop` float NOT NULL default '0',
`historyMean` float NOT NULL default '0',
`historyStdDev` float NOT NULL default '0',
`triggerMean` float NOT NULL default '0',
`triggerStdDev` float NOT NULL default '0',
`data1` varchar(255) default NULL,
`data2` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`alertID`),
UNIQUE KEY `alinfo` (`epoch`,`srcNodeId`,`destNodeId`,`toolName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

NODEGROUP CREATE TABLE `NODEGROUP` (
`entryID` int(11) NOT NULL auto_increment,
`groupID` int(11) NOT NULL default '0',
`nodeID` int(11) NOT NULL default '0',
PRIMARY KEY (`entryID`),
UNIQUE KEY `groupID` (`groupID`,`nodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

NODES CREATE TABLE `NODES` (
`nodeID` int(11) NOT NULL auto_increment,
`aliasv4` varchar(255) default NULL,
`aliasv6` varchar(255) default NULL,
`ipv4Name` varchar(255) default NULL,
`ipv4Addr` varchar(15) default NULL,
`ipv6Name` varchar(255) default NULL,
`ipV6Addr` varchar(40) default NULL,
`contactName` varchar(255) default NULL,
`contactEmail` varchar(255) default NULL,
`contactPhone` varchar(255) default NULL,
`contactFax` varchar(255) default NULL,
`contactAddr` varchar(255) default NULL,
`country` varchar(255) default NULL,
`latitude` double default NULL,
`longitude` double default NULL,
`hostType` varchar(255) default NULL,
`homeDir` varchar(255) default NULL,
`remUser` varchar(255) default NULL,
`perlPath` varchar(255) default NULL,
`sshOpts` varchar(255) default NULL,
`psOpts` varchar(255) default NULL,
`masterTime2run` int(11) default NULL,
`siteUrl` varchar(255) default NULL,
`domain` varchar(255) default NULL,
`active` int(11) default NULL,
`greppath` varchar(255) default '/bin/grep',
`gnuplotpath` varchar(255) default '/usr/bin/gnuplot',
`pingpath` varchar(255) default '/bin/ping',
`mailpath` varchar(255) default '/bin/mail',
`locallink` varchar(255) default NULL,
`tracepath` varchar(255) default '/usr/sbin/traceroute',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`abbreviation` varchar(255) default NULL,
PRIMARY KEY (`nodeID`),
UNIQUE KEY `aliasv4` (`aliasv4`),
KEY `ipv4Addr` (`ipv4Addr`),
KEY `ipV6Addr` (`ipV6Addr`),
KEY `aliasv6` (`aliasv6`),
KEY `ipv4Name` (`ipv4Name`),
KEY `ipv6Name` (`ipv6Name`),
KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

NODESPECS CREATE TABLE `NODESPECS` (
`nodespecsid` int(11) NOT NULL auto_increment,
`nodeid` int(11) NOT NULL default '0',
`winsize` int(11) default NULL,
`streams` int(11) default NULL,
`nicspeed` int(11) default NULL,
`cpus` int(11) default NULL,
`cpuspeed` double default NULL,
`processors` int(11) default NULL,
`wmem_max` int(11) default NULL,
`rmem_max` int(11) default NULL,
`wmem_default` int(11) default NULL,
`rmem_default` int(11) default NULL,
`tcp_rmem_min` int(11) default NULL,
`tcp_rmem_default` int(11) default NULL,
`tcp_rmem_max` int(11) default NULL,
`tcp_wmem_min` int(11) default NULL,
`tcp_wmem_default` int(11) default NULL,
`tcp_wmem_max` int(11) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`nodespecsid`),
UNIQUE KEY `nodeid_2` (`nodeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

OWAMPDATA CREATE TABLE `OWAMPDATA` (
`owampdataid` int(11) NOT NULL auto_increment,
`epoch` int(10) unsigned NOT NULL default '0',
`srcnodeid` int(11) NOT NULL default '0',
`destnodeid` int(11) NOT NULL default '0',
`scheduleid` int(11) NOT NULL default '0',
`testtype` varchar(255) default NULL,
`numhops` varchar(255) default NULL,
`pktssent` int(11) NOT NULL default '0',
`pktslost` int(11) NOT NULL default '0',
`pktsdup` int(11) NOT NULL default '0',
`error` double default NULL,
`jitter` double default NULL,
`reordering` varchar(255) default NULL,
`min` double default NULL,
`median` double default NULL,
`max` double default NULL,
`results` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`owampdataid`),
KEY `epoch` (`epoch`),
KEY `epoch_4` (`epoch`,`destnodeid`,`srcnodeid`),
KEY `destnodeid` (`destnodeid`,`srcnodeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

PINGDATA CREATE TABLE `PINGDATA` (
`pingdataID` int(11) NOT NULL auto_increment,
`epoch` int(10) unsigned NOT NULL default '0',
`pkt_size` int(11) NOT NULL default '0',
`pkts_sent` int(11) NOT NULL default '0',
`pkts_rcvd` int(11) NOT NULL default '0',
`rttmin` double default NULL,
`rttmax` double default NULL,
`rttavg` double default NULL,
`loss` double default NULL,
`icmp_seq` varchar(255) default NULL,
`results` text,
`testtype` varchar(255) default NULL,
`scheduleID` int(11) NOT NULL default '0',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`pingdataID`),
KEY `epoch` (`epoch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

PLOTSPECS CREATE TABLE `PLOTSPECS` (
`plotspecID` int(11) NOT NULL auto_increment,
`srcNode` varchar(255) default NULL,
`destNode` varchar(255) default NULL,
`datatype` varchar(255) default NULL,
`plottype` varchar(255) default NULL,
`xtitle` varchar(255) default NULL,
`ytitle` varchar(255) default NULL,
`title` varchar(255) default NULL,
`plotspec` varchar(255) default NULL,
`insubdir` varchar(255) default NULL,
`outsubdir` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`plotspecID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

REGDATA CREATE TABLE `REGDATA` (
`regdataID` int(11) NOT NULL auto_increment,
`epoch` int(10) unsigned NOT NULL default '0',
`srcnode` varchar(255) NOT NULL default '',
`destnode` varchar(255) NOT NULL default '',
`regdata` double NOT NULL default '0',
`toolname` varchar(255) NOT NULL default '',
`datacol` varchar(255) NOT NULL default '',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`regdataID`),
KEY `srcnode` (`srcnode`,`destnode`,`epoch`,`toolname`,`datacol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ROUTEDATA CREATE TABLE `ROUTEDATA` (
`routedataID` int(11) NOT NULL auto_increment,
`epoch` int(10) unsigned NOT NULL default '0',
`routenumber` int(11) default NULL,
`numhops` int(11) default NULL,
`route` text,
`results` text,
`testtype` varchar(255) default NULL,
`scheduleID` int(11) NOT NULL default '0',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`routedataID`),
KEY `epoch` (`epoch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ROUTENO CREATE TABLE `ROUTENO` (
`routenoID` int(11) NOT NULL auto_increment,
`srcNodeID` int(11) NOT NULL default '0',
`destNodeID` int(11) NOT NULL default '0',
`firstseen` int(10) unsigned NOT NULL default '0',
`lastseen` int(10) unsigned NOT NULL default '0',
`routenumber` int(11) NOT NULL default '0',
`route` text NOT NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`routenoID`),
UNIQUE KEY `srcNodeID` (`srcNodeID`,`destNodeID`,`routenumber`),
KEY `routenumber` (`routenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SCHEDULE CREATE TABLE `SCHEDULE` (
`scheduleID` int(11) NOT NULL auto_increment,
`srcNodeID` int(11) NOT NULL default '0',
`destNodeID` int(11) NOT NULL default '0',
`toolName` varchar(255) default NULL,
`toolPath` varchar(255) default NULL,
`testtype` varchar(255) default NULL,
`toolOpts1` varchar(255) default NULL,
`toolOpts2` varchar(255) default NULL,
`toolOpts3` varchar(255) default NULL,
`toolsrcdatafile` varchar(255) default NULL,
`tooldestdatafile` varchar(255) default NULL,
`tablename` varchar(255) default NULL,
`starttime` int(10) unsigned NOT NULL default '0',
`endtime` int(10) unsigned default NULL,
`time2run` int(10) unsigned default NULL,
`failcode` varchar(255) default NULL,
`comment` varchar(255) default NULL,
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`scheduleID`),
UNIQUE KEY `srcNodeID` (`srcNodeID`,`destNodeID`,`starttime`,`toolName`),
KEY `sndnst` (`srcNodeID`,`destNodeID`,`starttime`),
KEY `toolstart` (`toolName`,`starttime`),
KEY `allfields` (`testtype`,`starttime`,`srcNodeID`,`destNodeID`,`toolName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

TOOLSPECS CREATE TABLE `TOOLSPECS` (
`toolspecsID` int(11) NOT NULL auto_increment,
`srcNodeID` int(11) NOT NULL default '0',
`destNodeID` int(11) NOT NULL default '0',
`toolName` varchar(255) default NULL,
`toolPath` varchar(255) default NULL,
`toolDo` char(1) default NULL,
`testtype` varchar(255) default NULL,
`toolOpts1` varchar(255) default NULL,
`toolOpts2` varchar(255) default NULL,
`toolOpts3` varchar(255) default NULL,
`toolSrcDataFile` varchar(255) default NULL,
`toolDestDataFile` varchar(255) default NULL,
`time2run` int(11) default NULL,
`daysToAnalyze` int(11) default NULL,
`dataToAnalyze` text,
`runInterval` int(11) default NULL,
`lastrunepoch` int(11) default '0',
`f_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`toolspecsID`),
UNIQUE KEY `srcNodeID_2` (`srcNodeID`,`destNodeID`,`toolName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1