Developers:SNMP tables

From OCS Inventory NG
Jump to: navigation, search

SNMP tables for ocsweb database

This the SQL script to create the tables associated to SNMP in ocsweb database :

 
CREATE TABLE snmp (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  IPADDR VARCHAR(255) DEFAULT NULL,
  MACADDR VARCHAR(255) NOT NULL,
  SNMPDEVICEID VARCHAR(255) NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  CONTACT VARCHAR(255) DEFAULT NULL,
  LOCATION VARCHAR(255) DEFAULT NULL,
  UPTIME VARCHAR(255) DEFAULT NULL,
  DOMAIN VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  LASTDATE DATETIME default NULL,
  CHECKSUM BIGINT UNSIGNED DEFAULT 0,
  PRIMARY KEY (ID)
);

CREATE TABLE snmp_printers (
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  COUNTER VARCHAR(255) DEFAULT NULL,
  STATUS VARCHAR(255) DEFAULT NULL,
  ERRORSTATE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
);

CREATE TABLE snmp_trays (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  LEVEL VARCHAR(255) DEFAULT NULL,
  MAXCAPACITY INTEGER DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_cartridges (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  LEVEL INTEGER DEFAULT NULL,
  MAXCAPACITY INTEGER DEFAULT NULL,
  COLOR VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_networks (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  MACADDR VARCHAR(255) DEFAULT NULL,
  DEVICEMACADDR VARCHAR(255) DEFAULT NULL,
  SLOT VARCHAR(255) DEFAULT NULL,
  STATUS VARCHAR(255) DEFAULT NULL,
  SPEED VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  DEVICEADDRESS VARCHAR(255) DEFAULT NULL,
  DEVICENAME VARCHAR(255) DEFAULT NULL,
  DEVICEPORT VARCHAR(255) DEFAULT NULL,
  DEVICETYPE VARCHAR(255) DEFAULT NULL,
  TYPEMIB VARCHAR(255) DEFAULT NULL,
  IPADDR VARCHAR(255) DEFAULT NULL,
  IPMASK VARCHAR(255) DEFAULT NULL,
  IPGATEWAY VARCHAR(255) DEFAULT NULL,
  IPSUBNET VARCHAR(255) DEFAULT NULL,
  IPDHCP VARCHAR(255) DEFAULT NULL,
  DRIVER VARCHAR(255) DEFAULT NULL,
  VIRTUALDEV INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_switchs (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  SOFTVERSION VARCHAR(255) DEFAULT NULL,
  FIRMVERSION VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(255) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
	
CREATE TABLE snmp_blades (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  SYSTEM VARCHAR(255) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
);
		
CREATE TABLE snmp_storages (
  ID INTEGER NOT NULL AUTO_INCREMENT DEFAULT NULL,
  SNMP_ID INTEGER DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  MODEL VARCHAR(255) DEFAULT NULL,
  DISKSIZE INTEGER DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  FIRMWARE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_drives (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  LETTER VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  FILESYSTEM VARCHAR(255) DEFAULT NULL,
  TOTAL INTEGER DEFAULT NULL,
  FREE INTEGER DEFAULT NULL,
  NUMFILES INTEGER DEFAULT NULL,
  VOLUMN VARCHAR(255) DEFAULT NULL,
  LABEL VARCHAR(255) DEFAULT NULL,
  SERIAL VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_powersupplies (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER DEFAULT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_fans (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);
		
CREATE TABLE snmp_firewalls (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  SYSTEM VARCHAR(255) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
);
		
CREATE TABLE snmp_switchinfos (
  SNMP_ID INTEGER NOT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
);
		
CREATE TABLE snmp_loadbalancers (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  SYSTEM VARCHAR(255) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
);

CREATE TABLE snmp_computers (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SYSTEM VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
);
		
CREATE TABLE snmp_cards (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  FIRMWARE VARCHAR(255) DEFAULT NULL,
  SOFTWARE VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(255) DEFAULT NULL,
  SERIALNUMBER VARCHAR(255) DEFAULT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_softwares (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  INSTALLDATE VARCHAR(255) DEFAULT NULL,
  COMMENTS TEXT,
  VERSION VARCHAR(255) default NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_memories (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  CAPACITY VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_cpus (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  SPEED VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  MANUFACTURER VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_inputs (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_ports (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_sounds (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
);

CREATE TABLE snmp_videos (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
);


CREATE TABLE snmp_modems (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
);


CREATE TABLE snmp_localprinters (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(255) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
);


CREATE TABLE snmp_accountinfo (
SNMP_ID INTEGER NOT NULL,
TAG VARCHAR(255) default 'NA',
primary key(SNMP_ID),
INDEX TAG (TAG)
) ENGINE=INNODB ;

CREATE TABLE snmp_laststate (
SNMP_ID INTEGER NOT NULL,
COMMON VARCHAR(255) DEFAULT NULL,
PRINTERS VARCHAR(255) DEFAULT NULL,
TRAYS VARCHAR(255) DEFAULT NULL,
CARTRIDGES VARCHAR(255) DEFAULT NULL,
NETWORKS VARCHAR(255) DEFAULT NULL,
SWITCHS VARCHAR(255) DEFAULT NULL,
BLADES VARCHAR(255) DEFAULT NULL,
STORAGES VARCHAR(255) DEFAULT NULL,
DRIVES VARCHAR(255) DEFAULT NULL,
POWERSUPPLIES VARCHAR(255) DEFAULT NULL,
FANS VARCHAR(255) DEFAULT NULL,
SWITCHINFOS VARCHAR(255) DEFAULT NULL,
LOADBALANCERS VARCHAR(255) DEFAULT NULL,
CARDS VARCHAR(255) DEFAULT NULL,
COMPUTERS VARCHAR(255) DEFAULT NULL,
SOFTWARES VARCHAR(255) DEFAULT NULL,
MEMORIES VARCHAR(255) DEFAULT NULL,
CPUS VARCHAR(255) DEFAULT NULL,
INPUTS VARCHAR(255) DEFAULT NULL,
PORTS VARCHAR(255) DEFAULT NULL,
SOUNDS VARCHAR(255) DEFAULT NULL,
VIDEOS VARCHAR(255) DEFAULT NULL,
MODEMS VARCHAR(255) DEFAULT NULL,
LOCALPRINTERS VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (SNMP_ID) );

DATA for example:

INSERT INTO `snmp` VALUES (4,'192.168.11.4','00:10:5a:49:34:98','9021d632632d6a546542b7189106f4b3','HPMAISON','HP ETHERNET MULTI-ENVIRONMENT,ROM V.29.11,JETDIRECT,JD115,EEPROM V.29.13,CIDATE 08/11/2005',NULL,NULL,'8 days, 06:14:21.40',NULL,NULL,NULL);

INSERT INTO `snmp_cartridges` VALUES (6,4,'toner',-2,-23,'','Cartouche d encre');

INSERT INTO `snmp_printers` VALUES (4,'HP LaserJet 2100 Series','FRGW102336','11733 impressions');

INSERT INTO `snmp_trays` VALUES (42,4,'Tray 2','Bac 2','-2',250),(41,4,'Tray 1','Bac 1','-2',100),(40,4,'Tray 3','Bac 3','-2',250);