Vikas put me over creating the SQL database format for this new version of
NOCOL he's working on, as well as rewrite the PERL API. Here is the final
database format. I separated it into two tables, events and
events_history. I did this so that when the history grows large, it will
not affect the current events or the speed at which they update. It's a
bit redundant, but I think it will work better for everyone to generate
reports.
Finally, I'm writing NOCOL.pm which will be a more object oriented
API for perl. I'm including DBI support allowing each process to
write to the database for scalability's sake, and am also creating a
multi-threaded version of this which will utilize a shared pool of
processes to perform polling.
Here are the tables....
CREATE TABLE EVENTS (
EVENT_ID INT, <- unique event id
PHYS_ID VARCHAR(32), <- physical is for parenting & Billing/financial
integration - something like a CID
LOGICAL_ID VARCHAR(32), <- logical id for billing/financial (optional)
DEVICE_HOSTNAME VARCHAR(128),
DEVICE_IP VARCHAR(15),
DESCRIPTION VARCHAR(64), <- text description for newer interfaces coming
out
VARIABLE VARCHAR(16), <- BGPpeer, IPPING, etc.
VALUE INT, <- 0, 11, etc...
PARENT_PHYS_ID VARCHAR(32), <- parent's physical id
PARENT_VARIABLE VARCHAR(16), <- parent's variable (we do this so that we
can stop monitoring/displaying items with dependencies down)
SERVICE VARCHAR(16), <- service name (ippingmon, etc) used for
purging, and multithreaded services
LAST_UPDATED INT
);
CREATE TABLE EVENT_HISTORY (
EVENT_ID INT,
PHYS_ID VARCHAR(32),
LOGICAL_ID VARCHAR(32),
DEVICE_HOSTNAME VARCHAR(128),
DEVICE_IP VARCHAR(15),
DESCRIPTION VARCHAR(64),
VARIABLE VARCHAR(16),
VALUE INT,
PARENT_PHYS_ID VARCHAR(32),
PARENT_VARIABLE VARCHAR(16),
SERVICE VARCHAR(16),
STATUS_BEGIN INT,
STATUS_END INT,
LAST_UPDATED INT
);
I had to cut out a bunch of stuff that i *wanted* to do basically due to
the limitations of the three popular free SQL's out there (mysql,
postgres, and minisql). subqueries are not supported, and neither is
autonumbering (or sequences). Chances are, when I adapt each of the
PERL modules (which *will* need to be adapted), I will create some oracle
versions of it for those of you who want to run NOCOL off of oracle rather
than mysql.
If we write this properly, then we should be able to add onto the database
pretty easily without affecting any part of NOCOL..by forming INSERT and
UPDATE commands properly, this shouldn't be a problem.
Still open for comments/suggestions
Jonathan A. Zdziarski
Sr. Systems Administrator
NetRail, inc.
888.NET.RAIL x240
http://www.netrail.net
|