Post 2: Green Monster DB Info

Part 2 of N.  See part 1 first
Green Monster Database design

The key to Green Monster is the database.  The database holds information about “assets” which are any physical item that should be inventoried or will be used for automation.  For the EEC we inventory Servers, Laptops, desktops, Rack PDUs, KVM Switches, Network Switches, HP OA interfaces, Blade Chassis, SAN storage, PCIe Flash Storage (Fusion IO), Load Balancers (F5, Brocade), Encryption devices (nCypher HSM), Racks, Blades, DAS shelves (MSA 70), and many other things. 

The database has tables for Manufactures, Models, Device Type (Network Switch, KVM switch, other items from above).  An “asset” links to a Model which then links to Device Type and Manufacture. 

99% of Assets will have common attributes (though with different values), for example Serial Number, warranty start Date, Cost.  Some additional values that we find most of our Assets have include parent Asset (for child parent relationship),  status code, Purchase Order number, Asset tag (inventory tracking).

 In addition to these attributes there is a SQL table for additional attributes that are more specific to a device type and not all devices will hold.  Unlike the attributes in the Assets table these attributes allow NULL values.  They include IP Address, SNMP community string, username, password, size in Rack Units, number of disks, interface speed (for SAN devices), CPU ID, plugin name (more about this later) and many more.

CPU ID from above links to a table which tracks information about what CPUs exit in a Server.  This allows us to track usage of CPU features tested and also find systems that meet specific customer requirements for a scenario. 

The Models table which has a direct relationship to the Assets table as the following fields, Model Name, Model Number, Code Name, Size in RU, Weight, Power Voltage, Power Current, and like the attributes table there is a plugin name field (more on this field in the following post).

When the EEC does performance or scale testing on equipment it is important to know the exact specifications of all the systems that were used in the test.  These systems can be all the same model or a mix of model, manufacture, architecture (Intel, AMD).  Over the life cycle of a server we will upgrade the CPUs to faster (maybe pre-release) options, or change the memory speed/quantity.  Even with these changes we need to be track what the exact specs were at any given point in time.  The solution to that issue is an Audit table.  The Audit table is populated from triggers that exist on the Asset, attribute and CPU tables.  It tracks current and previous values anytime a field is changed in one of the monitored tables. 

With these tables and a few others we have a DB design for basic inventory tracking that offers a child/parent relationship, tracks information about specific systems, provides a history of a Servers upgrade/downgrade and will scale very well.

For the automation pieces of Green Monster to work it relies on knowing how each component (Network, Server, NIC, Power Port, KVM) all relate to each other.For Network and Power control each asset has a 1 to many relationship.  This means that a Server can have >1 network port and >1 Power ports. 
 

Each port has a “parent” of the asset and a neighbor of where it connects to.  If a Server has 2 nics it will have 2 entries in the network ports table.  If a switch has 48 ports it will have 48 entries in the table.  From that a simple mapping from server nic 1 to switch port 1 can be done.  This allows the automation pieces to know how everything relates. 

For network ports the MAC address of the NIC is also tracked.  This is used for assigning IP address during the OS image phase and for the “audit” tool to make sure that a Mac address is showing on the correct Network switch port when dumping the MAC table.  More on those later.