[Date Prev]   [Date Next] [Thread Prev]   [Thread Next] [Date Index]   [Thread Index]

 

     Re: NOCOL Database Format

then Jonathan A. Zdziarski's all..
> 
> 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

Final? I assume that this would be for a current version of nocol supporting
existing features and not missing ones that we've been wishing for. ;)
I'll point out a few things that may be of interest for current usage as
well as for immediate future expansion, if we can get Vikas/someone to
code them up (Vikas hasnt mentioned future design direction beyond
his initial points, but again, last night, I was reminded poignantly
of the need to MULTIPLE Parents/children for each id, as well as an
"action taken" indicator to cancel alarms by admins who have responded
to it, or to move the event back down/forward to a new level, as well
as to add time based systems ("event critical if in failure state for over
x minutes.")

> 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...

Should we not be allowed to have decimal numbers? Might be good to make
a float, as some monitors may return floats, and then we dont have to
track constants that these values are multiplied by to give an integer.

>  PARENT_PHYS_ID VARCHAR(32), <- parent's physical id

I think multiple parents should be allowed. There's no hierarchical system
in place yet, so this can only be you looking towards the future. I think
it would benefit to think about wether we are going to limit ourselves
to single parents, or to multiple 'parents' and children, combined into
some state with boolean logic. "if ((a || b) && c && d) then dont_bother_alerting
for state q" as I've mentioned before.

Im not sure how you'd code this into a db without just putting something
like a raw text field with an sql where statement in place to indicate
states its dependent on.

As well, to avoid having to travel backwards up the tree of dependants, I'd
suggest that we have a field for "children" or "dependants" on this
state. (I've writen parts of a WWW portal writen in mysql that thrashes the
DB because it only has a "parent" field for each entry - I redeisigned
it for next release to have children to avoid this problem of looking
for children.)

>  PARENT_VARIABLE VARCHAR(16), <- parent's variable (we do this so that we
> can stop monitoring/displaying items with dependencies down)

Need this for children too.

Again, multiple parents, multiple children. (I would guess the children
would just be a list of dependants, and not any logic need be applied.
Its more for the admin to know whats depending on this state - they can
go to that child immediately and then lookup how the parent factors
into the child's state.)

As my coworked Dave posted to this list, things should be setup
as objects that do I/O of state. The object takes IN some number of states
and puts out some number of states (perhaps none, perhaps many).
It would also have side effects. This concept can be coded with the
database you've designed, if we allow for some sort of combination of
parent states to react to for each 'object' in the database.

>  SERVICE VARCHAR(16),       <- service name (ippingmon, etc) used for
> purging, and multithreaded services

explain more for this one.

As well, we need an override field to indicate when an event has been
overriden.

Im losing sight of how all my ideas should be coded into the DB (its up
to you and Vikas if they should), but it would be really nice to have
the multiple parent dependency boolean expression, some sort of
mechanism to allow admins to override an alarm or to turn it off,
as well as some sort of system to indicate an events progression between
states based on how long its been in failure.

I guess Im confusing the DB with 'current state' of the system,with 
'configuration' of how the system's parts should react to one another.

You didnt give enough of an overview of the context and mechanism
your database will server, one or the other or both. The parent field
leads me to believe that you are indicating some sort of 'configuration'
notation for the system, while other parts indicate its a database
of current state. (Im not sure that the two could not be mixed - the
way objects are to react to particular other states of other objects
in the system could be coded along side what hte current state is).

So I probably shouldnt be posting this yet, cuz I dont quite understand
what you're getting at! :)

>  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  
> );

What about who responded to the event or cancelled it? That kind of stuff
would be useful. Also tracking the maximum value that the object incurred,
and when, before solved might be good for reporting. "What, you let disk
space get to 99% before you solved the problem! Heads roll!" (Is it
bad to code features of interest to PHBs? :)

> 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

mysql isnt free, and minisql is a hog :) 

Postgres does subqueries from what I recall, as well as sequences.

> 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.

Will it run with postgres as well? We like postgres! :)

> 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.

There's no reason you cant store items to ccause the database to query
and update itself to cause state to ripple through the system. The
items would be SQL statements in and of themselves. 

/kc
-- 
Ken Chase, Director Operations                  Velocet Communications Inc.
math@velocet.ca                                              Toronto CANADA
--
"Sometimes two [harmless] words, when put together, strike fear in the
  hearts of men -- Microsoft Wallet."                           - Dave Gilbert