MySQL Schema change

[cedlecomte]

From [email protected] on January 26, 2012 17:18:19

I have a different structure for tables interfaces and router, this is my structures :

CREATE TABLE interface (
id int(11) unsigned NOT NULL auto_increment,
name char(255) NOT NULL default '',
rid int(11) NOT NULL default '0',
speed bigint(11) default NULL,
description char(255) default NULL,
status enum('active','inactive') default 'active',
mac_address varchar(50) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=2239 DEFAULT CHARSET=latin1;

id = 965
name = FastEthernet0/1
rid = 53
speed = 100000000
descrition = uplink-with-peer-google
status = active
mac_address = 00:11:22:33:44:55 or NULL for !port entry

CREATE TABLE router (
rid int(11) unsigned NOT NULL auto_increment,
name char(120) NOT NULL default '',
pop char(10) NOT NULL default '',
popid tinyint(3) unsigned NOT NULL default '0',
description varchar(250) default NULL,
PRIMARY KEY (rid)
) ENGINE=MyISAM AUTO_INCREMENT=96 DEFAULT CHARSET=latin1;

ex of router line :

rid = 80
name = 8.8.8.8
pop = NULL
popid = 3 (for me 3 it's a specific DC in spain)
description = sw666.rack4.room6.mydc.mytld.net

With the right targetmaker that can be more powerful, the rid description can be print on the view.php. The mac address can be used by a external system, and add a important information. (the rid description must be set manually i think).

What do you think about it ?

Original issue: http://code.google.com/p/rtg2/issues/detail?id=25

[cedlecomte]

From [email protected] on February 16, 2012 06:20:21

I'm not sure if mac_address makes sense in the interfaces table. Assuming this is the mac address of the device plugged into that port depending on the network that can change a lot.

For other things to store in the tables I record the ifIndex value in the interfaces table in a column called port. This is then used by other systems that know the switch and port number of a particular port to create the URL for the RTG graph (on our switches the ifIndex matches the physical port number of real ports).

[cedlecomte]

From [email protected] on February 16, 2012 07:17:10

I'm not talking about the device plugged into the port but the mac address used by the port and that you can get by SNMP.

ifIndex can be get by SNMP if i'am right ?
All that data can be populate by targetmaker.

Cédric.