This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
devel:database [2007/09/30 16:48] faltin |
devel:database [2012/05/07 11:22] (current) morten fix url |
||
---|---|---|---|
Line 1: | Line 1: | ||
====== Database documentation ====== | ====== Database documentation ====== | ||
- | This documents gives detailed information on the design of the NAV database. Currently (version 3.1) NAV is split into four separate databases, with a total of **94 tables**: | + | This documents gives detailed information on the design of the NAV database. Currently (version 3.3) NAV is split into four separate databases, with a total of **95 tables**: |
+ | |||
+ | * The main database "manage" is also referred to as NAVdb and contains the topology information of the network, machine tracker data, tables for the event- and alert system, for the messages system and more. The "manage" database has 58 tables. | ||
+ | * The [[#the_nav_profiles_database|NAV profiles]] database has information on NAV users and their selected alert profiles. The "NAV profiles" database has 27 tables. | ||
+ | * The [[#the_arnold_database|Arnold]] database has information on switch ports that are blocked by the Arnold front end tool. The "Arnold" database has 4 tables. | ||
+ | * The [[#the_logger_database|logger]] database is a small database for the Cisco syslog analyzer component of NAV. The database has 6 tables. | ||
- | * The main database "manage" is also refered to as NAVdb and contains the topology information of the network, machine tracker data, tables for the event- and alert system, for the messages system and more. The "manage" database has 57 tables. | ||
- | * The "NAV profiles" database has information on NAV users and their selectet alert profiles. The "NAV profiles" database has 27 tables. | ||
- | * The "Arnold" database has information on switch ports that are blocked by the Arnold front end tool. The "Arnold" database has 4 tables. | ||
- | * The "logger" database is a small database for the Cisco syslog analyzer component of NAV. The database has 6 tables. | ||
Line 19: | Line 20: | ||
====== NAVdb ("manage") ====== | ====== NAVdb ("manage") ====== | ||
- | Allthough this is one big database, we have here split NAVdb in 9 different logical groups of tables. This | + | Although this is one big database, we have here split NAVdb in 9 different logical groups of tables. This |
is shown on 5 different diagrams: | is shown on 5 different diagrams: | ||
Line 33: | Line 34: | ||
* [[http://domen.uninett.no/~faltin/nav/navdb/eventDB.png|Diagram 3]] shows tables for: | * [[http://domen.uninett.no/~faltin/nav/navdb/eventDB.png|Diagram 3]] shows tables for: | ||
* The event and alert system | * The event and alert system | ||
- | * [[http://domen.uninett.no/~faltin/nav/navdb/message.png|Diagram 4]] shows tables for: | + | * A diagram 4 (currently non existent) should show the tables for: |
- | * The message and maintenance system | + | * The message and maintenance systems |
* [[http://domen.uninett.no/~faltin/nav/navdb/trafficmap.png|Diagram 5]] shows tables for: | * [[http://domen.uninett.no/~faltin/nav/navdb/trafficmap.png|Diagram 5]] shows tables for: | ||
* The traffic map (vlanplot) | * The traffic map (vlanplot) | ||
Line 52: | Line 53: | ||
==== netbox ==== | ==== netbox ==== | ||
- | The netbox table is the hart of the hart so to speak, the most central table of them all. | + | The netbox table is the heart of the heart so to speak, the most central table of them all. |
The netbox tables contains information on all IP devices that NAV manages with adhering | The netbox tables contains information on all IP devices that NAV manages with adhering | ||
information and relations. | information and relations. | ||
Line 60: | Line 61: | ||
ip IP address of the netbox | ip IP address of the netbox | ||
roomid room the box is placed in | roomid room the box is placed in | ||
+ | deviceid the device this is (foreign key to device) | ||
typeid sysobjectid of the box | typeid sysobjectid of the box | ||
- | sysname name of the box, based on fully qualified dns name with fallback to IP address. | + | sysname name of the box, based on fully qualified dns name with fall back to IP address. |
- | Histically MibII system.sysname was used as source, but we now use dns. | + | Historically MibII system.sysname was used as source, but we now use dns. |
catid category of the box (GW,SW,SRV, etc) | catid category of the box (GW,SW,SRV, etc) | ||
subcat DEPRECATED? - check before delete!!! | subcat DEPRECATED? - check before delete!!! | ||
Line 71: | Line 73: | ||
up whether the box is up and running (as seen from pping) | up whether the box is up and running (as seen from pping) | ||
snmp_version version of the snmp agent (1 or 2) | snmp_version version of the snmp agent (1 or 2) | ||
- | snmp_agent DEPRECATED? - check before delet!!! Was used by a server collection module once. | + | snmp_agent DEPRECATED? - check before delete!!! Was used by a server collection module once. |
upsince the timestamp when the box was last booted. Data is taken from mibII system.uptime | upsince the timestamp when the box was last booted. Data is taken from mibII system.uptime | ||
uptodate whether gDD has done OID classification | uptodate whether gDD has done OID classification | ||
+ | discovered timestamp when the box was first discovered by NAV | ||
</code> | </code> | ||
Line 88: | Line 91: | ||
val value | val value | ||
</code> | </code> | ||
+ | |||
==== device ==== | ==== device ==== | ||
Line 98: | Line 102: | ||
<code> | <code> | ||
deviceid primary key | deviceid primary key | ||
- | productid product the device is of (if this info is maintained) | ||
serial serial number | serial serial number | ||
hw_ver hardware version | hw_ver hardware version | ||
fw_ver firmware version | fw_ver firmware version | ||
sw_ver software version | sw_ver software version | ||
- | auto whether this device is discovered automatically by gDD or not | + | auto whether this device is discovered automatically by ipdevpoll or not |
+ | discovered when the device was first discovered by NAV. | ||
active whether the device should be in operation (not just ordered) | active whether the device should be in operation (not just ordered) | ||
- | deviceorderid the order (if any) the device is part of | ||
</code> | </code> | ||
Line 111: | Line 114: | ||
The module table defines modules. A module is a part of a netbox of category GW, SW and GSW. | The module table defines modules. A module is a part of a netbox of category GW, SW and GSW. | ||
- | A module has ports; i.e router ports and/or switch ports. A module is also a phyiscal | + | A module has ports; i.e router ports and/or switch ports. A module is also a physical |
device with a serial number. | device with a serial number. | ||
Line 123: | Line 126: | ||
up whether the module is up and running (as detected by moduleMon. modeleMon is a plugin to gDD) | up whether the module is up and running (as detected by moduleMon. modeleMon is a plugin to gDD) | ||
downsince since when the module has been down | downsince since when the module has been down | ||
+ | name Modules aren't necessarily identified using integers (as in module.module), so we add names in v3.6 | ||
</code> | </code> | ||
Line 137: | Line 141: | ||
used how much of the memory that is used | used how much of the memory that is used | ||
</code> | </code> | ||
+ | |||
==== room ==== | ==== room ==== | ||
- | The room table defines a wiring closes / netork room / server room | + | The room table defines a wiring closes / network room / server room |
<code> | <code> | ||
Line 171: | Line 176: | ||
parent parent organization, if any | parent parent organization, if any | ||
descr further description | descr further description | ||
+ | contact contact info | ||
opt1 additional info | opt1 additional info | ||
opt2 " | opt2 " | ||
Line 224: | Line 230: | ||
tftp whether the type supports tftp storage | tftp whether the type supports tftp storage | ||
</code> | </code> | ||
+ | |||
+ | ==== vendor ==== | ||
+ | |||
+ | The vendor table defines vendors. A type is of a vendor. | ||
+ | |||
+ | <code> | ||
+ | vendorid table identifier, also the name of the vendor. | ||
+ | </code> | ||
+ | |||
===== The OID database ===== | ===== The OID database ===== | ||
This [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|diagram]] shows OID database (and more). | This [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|diagram]] shows OID database (and more). | ||
+ | |||
+ | The role of the OID database has changed with NAV 3.6. ipdevpoll (that replaces getDeviceData) does not depend | ||
+ | on the OID database. Cricket still does and getBoksMacs still uses the oid database to figure out device | ||
+ | capabilities. | ||
==== snmpoid ==== | ==== snmpoid ==== | ||
Line 238: | Line 257: | ||
oidkey the variable used in NAV code for the OID | oidkey the variable used in NAV code for the OID | ||
oidsource whether Cricket uses the oid or not | oidsource whether Cricket uses the oid or not | ||
- | match_regex the response from the OID must mach the regex defined her, if empty any response is ok. | + | match_regex the response from the OID must match the regexp defined her, if empty any response is ok. |
getnext If true; the OID is the root of a subtree, the OID tester then does a getnext poll to verify this. | getnext If true; the OID is the root of a subtree, the OID tester then does a getnext poll to verify this. | ||
If false; this OID is a leaf OID. | If false; this OID is a leaf OID. | ||
- | decodehex If true; the data is in hex and shuld be decoded to ASCII before doing the match_regex | + | decodehex If true; the data is in hex and should be decoded to ASCII before doing the match_regex |
uptodate boolean. If false netboxes are not OID tested against this OID. If you add snmpoids in | uptodate boolean. If false netboxes are not OID tested against this OID. If you add snmpoids in | ||
the Edit Database tool this value will initially be false. | the Edit Database tool this value will initially be false. | ||
Line 273: | Line 292: | ||
netboxid which netbox the service for monitoring is for | netboxid which netbox the service for monitoring is for | ||
active DEPRECATED? - check before remove. Was used for maintenance. | active DEPRECATED? - check before remove. Was used for maintenance. | ||
- | handler type of service (ssh,http,dns,dvc etc) | + | handler type of service (ssh,http,dns,dc etc) |
- | version version of the service, i.e. apache/ssh verson | + | version version of the service, i.e. apache/ssh version |
up whether serviceMon finds that the service is up/down | up whether serviceMon finds that the service is up/down | ||
</code> | </code> | ||
Line 294: | Line 313: | ||
==== rrd_file ==== | ==== rrd_file ==== | ||
- | The rrd_file contains metainformation on all RRD files that NAV | + | The rrd_file contains meta information on all RRD files that NAV |
uses. Each RRD file has statistics for a certain netbox | uses. Each RRD file has statistics for a certain netbox | ||
<code> | <code> | ||
rrd_fileid primary key | rrd_fileid primary key | ||
- | path filesystem path to the rrd file in question | + | path file system path to the rrd file in question |
filename filename of the rrd file | filename filename of the rrd file | ||
step how often the rrd fil is updated (in seconds) | step how often the rrd fil is updated (in seconds) | ||
Line 306: | Line 325: | ||
netboxid the netbox the rrd file has statistics for | netboxid the netbox the rrd file has statistics for | ||
key if relevant, which part of the netbox the rrd file has statistics for, i.e. | key if relevant, which part of the netbox the rrd file has statistics for, i.e. | ||
- | which table; service / swport or gwport | + | which table; service / interface |
- | value i.e. which serviceid / swportid / gwportid | + | value i.e. which serviceid / interfaceid |
</code> | </code> | ||
==== rrd_datasource ==== | ==== rrd_datasource ==== | ||
- | An rrd_file consists of a set of datasources defined in this table. | + | An rrd_file consists of a set of data sources defined in this table. |
- | A datasource is a data set, i.e. outOctets for a given switchport on a given switch. | + | A data source is a data set, i.e. outOctets for a given switch port on a given switch. |
<code> | <code> | ||
rrd_datasourceid primary key | rrd_datasourceid primary key | ||
- | rrd_fileid the rrd file the datasource is within | + | rrd_fileid the rrd file the data source is within |
- | name name of the source (ds0,ds1,RESPOSETIME etc) | + | name name of the source (ds0,ds1,RESPONSE TIME etc) |
- | descr further description | + | descr type of datasource, i.e. ifInOctets, ifInErrors, cpu5min, etc |
dstype type (DERIVE / GAUGE) | dstype type (DERIVE / GAUGE) | ||
units units used on y-axis (seconds, bytes, etc) | units units used on y-axis (seconds, bytes, etc) | ||
threshold for thresholdmon: the threshold value to be checked on this data source. | threshold for thresholdmon: the threshold value to be checked on this data source. | ||
May be stored as an integer or a percentage number. | May be stored as an integer or a percentage number. | ||
- | max for thresholdmon: the max value that this datasource may be. Important if | + | max for thresholdmon: the max value that this data source may be. Important if |
the threshold is stored as percentage. | the threshold is stored as percentage. | ||
delimiter for thresholdmon: either '<' or '>'. Tells us if the value must be higher or | delimiter for thresholdmon: either '<' or '>'. Tells us if the value must be higher or | ||
Line 332: | Line 351: | ||
</code> | </code> | ||
- | ===== Tables used by Device Management ===== | ||
- | This [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|diagram]] shows tables for Device Management (and more). | ||
- | ==== product ==== | + | ===== Router related topology tables ===== |
- | The product table is used be Device Management to register products. | + | Diagram 2 shows the relations between the topology related tables: |
- | Not compulsary. A product has a product number and is of a vendor. | + | |
- | <code> | + | {{devel:db:topology.png?600|}} |
- | productid primary key | + | |
- | productno product identifier, i.e. "WS-X4148-RJ" | + | |
- | descr further descr, i.e. "48 ports 10/100BaseTX (RJ45)" | + | |
- | vendorid Vendor of the product | + | |
- | </code> | + | |
- | ==== deviceorder ==== | + | ==== interface ==== |
- | The devicerorder table is used by Device Management to place orders. | + | New table in v3.6. General interface table, combines the old gwport and swport in one. |
- | Not compulsary. An order consists of a set of devices (on or more) | + | In cases where attribute starts with if* it is the exact equivalent of the IF-MIB (RFC 1229) instance. |
- | of a certain product. | + | |
+ | <code> | ||
+ | interfaceid primary key | ||
+ | netboxid netbox the interface belongs to | ||
+ | moduleid module the interace is on (if found) | ||
+ | ifindex ifindex of the router port | ||
+ | ifname interface name | ||
+ | ifdescr interface | ||
+ | iftype | ||
+ | speed | ||
+ | ifphysaddress | ||
+ | ifadminstatus | ||
+ | ifoperstatus | ||
+ | iflastchange | ||
+ | ifconnectorpresent | ||
+ | ifpromiscuousmode | ||
+ | ifalias | ||
+ | baseport port number (integer) if appliqable ??? | ||
+ | media | ||
+ | vlan | ||
+ | trunk | ||
+ | duplex | ||
+ | to_netboxid the netbox the interface connects to (if any) | ||
+ | to_interfaceid the interface this interface connects to (if any) | ||
- | <code> | ||
- | deviceorderid primary key | ||
- | registered timestamp when the order is registered in NAV | ||
- | ordered date the order is made in NAV. | ||
- | arrived timestamp when the order has arrived | ||
- | username NAV user placinrg the order | ||
- | orgid organization placing the order | ||
- | retailer retailer where the order is placed | ||
- | ordernumber ordernumber used internally or by retailer | ||
- | comment further comments made regarding the order | ||
- | productid product that is ordered | ||
- | updatedby ??? | ||
- | lastupdated ??? | ||
- | |||
- | note: the amount is not registered in deviceorder, but if the amount | ||
- | i.e. is 5, then 5 deviceorder events are posted on the eventq (I think...). | ||
</code> | </code> | ||
- | ==== vendor ==== | + | ==== rproto_attr ==== |
+ | |||
+ | New table in v3.6. General table for routing protocol metrics. Relates to intervace table. | ||
- | The vendor table defines vendors. A type is of a vendor. | ||
- | A product is of a vendor. | ||
<code> | <code> | ||
- | vendorid table identifier, also the name of the vendor. | + | id primary key |
+ | interfaceid which interface this is (foreign key) | ||
+ | protoname protocol name, i.e. ospf, isis, etc | ||
+ | metric metric value | ||
</code> | </code> | ||
- | |||
- | ===== Router related topology tables ===== | ||
- | |||
- | This [[http://domen.uninett.no/~faltin/nav/navdb/topology.png|diagram]] showns the relations between the | ||
- | topology related tables. | ||
Line 392: | Line 407: | ||
The gwport table defines the router ports connected to a module. | The gwport table defines the router ports connected to a module. | ||
Only router ports that are //not// shutdown are included. | Only router ports that are //not// shutdown are included. | ||
- | Router ports without defined IP adresses are also excluded. | + | Router ports without defined IP addresses are also excluded. |
<code> | <code> | ||
gwportid primary key | gwportid primary key | ||
moduleid module the router port is on | moduleid module the router port is on | ||
- | ifindex ifindex of the routerport | + | ifindex ifindex of the router port |
link whether the router port is operState up or down, apparently not in use | link whether the router port is operState up or down, apparently not in use | ||
- | masterindex if the routerport is a subinterface, this value | + | masterindex if the router port is a subinterface, this value |
points to the gwportid of the master port. | points to the gwportid of the master port. | ||
interface interface textual name (i.e Ethernet1/2) | interface interface textual name (i.e Ethernet1/2) | ||
Line 406: | Line 421: | ||
portname router interface description (the whole string) | portname router interface description (the whole string) | ||
to_netboxid the netbox the router port connects to (if any) | to_netboxid the netbox the router port connects to (if any) | ||
- | to_swportid the swith port the router connestc to (if any) | + | to_swportid the switch port the router connects to (if any) |
Line 417: | Line 432: | ||
<code> | <code> | ||
- | gwportid router port in question | + | interfaceid interface (router port) in question |
prefixid prefix in question | prefixid prefix in question | ||
gwip ip address defines on the router port | gwip ip address defines on the router port | ||
Line 434: | Line 449: | ||
vlanid broadcast / vlan the prefix is running on | vlanid broadcast / vlan the prefix is running on | ||
</code> | </code> | ||
+ | |||
==== vlan ==== | ==== vlan ==== | ||
Line 446: | Line 462: | ||
nettype nettype of the vlan, reference to the nettype table | nettype nettype of the vlan, reference to the nettype table | ||
orgid org. of the vlan, reference to the org table | orgid org. of the vlan, reference to the org table | ||
- | unageid usage of the vlan, reference to the usage table | + | usageid usage of the vlan, reference to the usage table |
netident a substring of the router port description derived | netident a substring of the router port description derived | ||
acccording to the suggested NAV syntax. | acccording to the suggested NAV syntax. | ||
Line 465: | Line 481: | ||
added manually in the Edit Database tool. For these netttypes (reserved and scope) edit='t'. | added manually in the Edit Database tool. For these netttypes (reserved and scope) edit='t'. | ||
</code> | </code> | ||
+ | |||
==== usage ==== | ==== usage ==== | ||
Line 473: | Line 490: | ||
<code> | <code> | ||
usageid primary key, also usage value | usageid primary key, also usage value | ||
- | descr firther description | + | descr further description |
</code> | </code> | ||
Line 497: | Line 514: | ||
This [[http://domen.uninett.no/~faltin/nav/navdb/topology.png|diagram]] showns the relations between the | This [[http://domen.uninett.no/~faltin/nav/navdb/topology.png|diagram]] showns the relations between the | ||
topology related tables. | topology related tables. | ||
+ | |||
==== swport ==== | ==== swport ==== | ||
Line 515: | Line 533: | ||
also in the swportvlan table - in report it | also in the swportvlan table - in report it | ||
is taken from swport vlan, not from here. | is taken from swport vlan, not from here. | ||
- | trunk wheater the port is trunk (boolean) | + | trunk whether the port is trunk (boolean). |
+ | May be NULL if it cannot be determined. | ||
portname the configured port name (description/ifAlias value) | portname the configured port name (description/ifAlias value) | ||
to_netboxid the netbox the port connects to (if any) | to_netboxid the netbox the port connects to (if any) | ||
Line 528: | Line 547: | ||
<code> | <code> | ||
swportvlanid table primary key | swportvlanid table primary key | ||
- | swportid switch port id (foreign key ref to swport table) | + | interfaceid interface id (foreign key ref to interface table, i.e. which switch port is this) |
vlanid vlan id (foreign key ref to vlan table, not an actual vlan number) | vlanid vlan id (foreign key ref to vlan table, not an actual vlan number) | ||
direction Direction of the link, seen from the root of the topology (the vlan's router). | direction Direction of the link, seen from the root of the topology (the vlan's router). | ||
- | 'o' = 'Up', 'n' = 'Down', 'x' = ??? | + | 'o' = 'Up', 'n' = 'Down', 'b' = 'Blocked', 'x' = Unknown |
</code> | </code> | ||
==== swportallowedvlan ==== | ==== swportallowedvlan ==== | ||
- | Stores a hexstring that has "hidden" information about the vlans that are | + | Stores a string of hexadecimal digits that represents the set of VLANs that are allowed to traverse a given trunk port. The hexadecimal string can be converted to an octet string, representing a string of bits, or a very large number, if you will: |
- | allowed to traverse a given trunk. | + | |
- | To make this information more readable a | + | * If the hexadecimal string is 256 digits long, the most significant bit in the octet string represents VLAN 0, while the least significant bit represents VLAN 1023. A set bit indicates that a VLAN is allowed, an unset bit indicates that it is not allowed. |
- | help table (range) and two views (allowedvlan and allowedvlan_both) are added to NAV | + | * If the hexadecimal string is less than 256 characters long, the bit order is reversed, and the least significant bit represents VLAN 0. |
- | in version 3.1. They are not documented in any further detail for the time being. | + | |
+ | The octet string representation is taken directly from a couple of CISCO MIBs, and as many will configure their trunks to allow any VLAN 1-1023, this representation will save space by reducing the number of necessary table rows. | ||
+ | |||
+ | To make interpretation of this data simpler using pure SQL, a help table (//range//) and two views (//allowedvlan// and //allowedvlan_both//) are provided. The //allowedvlan// view will decompose the hexstring into a set of VLAN rows for each trunk port. | ||
<code> | <code> | ||
- | swportid switch trunk port in question | + | interfaceid switch trunk port in question (foreign key to the interface table) |
- | hextring hextring that defines the allowed vlans for the trunk | + | hexstring hexstring that defines the allowed vlans for the trunk |
</code> | </code> | ||
Line 553: | Line 574: | ||
<code> | <code> | ||
- | swportid switch port in question | + | interfaceid switch port in question (foreign key to interface table) |
vlan vlan value | vlan vlan value | ||
</code> | </code> | ||
Line 568: | Line 589: | ||
ifindex ifindex on the switch | ifindex ifindex on the switch | ||
to_netboxid candidate next hop netbox | to_netboxid candidate next hop netbox | ||
- | to_swportid candidate next hop switch port | + | to_interfaceid candidate next hop switch port (foreign key to interface table) |
misscnt see misscnt in the cam table below. Same thing. | misscnt see misscnt in the cam table below. Same thing. | ||
</code> | </code> | ||
Line 630: | Line 651: | ||
<code> | <code> | ||
patchid primary key | patchid primary key | ||
- | swportid reference to the swport the cross connect is connected to | + | interfaceid reference to the swport the cross connect is connected to (foreign key to interface table) |
cablingid reference to the jakc the cross connect is connected to | cablingid reference to the jakc the cross connect is connected to | ||
split If a split cable is used in the jack end, specify type and leaf. | split If a split cable is used in the jack end, specify type and leaf. | ||
</code> | </code> | ||
+ | |||
===== Event system ===== | ===== Event system ===== | ||
- | This [[http://domen.uninett.no/~faltin/nav/navdb/eventDB.png|diagram]] showns the relations between the | + | Diagram 3 shows the relations between the event system tables: |
- | event system tables. | + | |
+ | {{devel:db:eventDB.png?600|}} | ||
==== eventq ==== | ==== eventq ==== | ||
Line 646: | Line 669: | ||
Different subsystem (specified in source) post events on the event queue. | Different subsystem (specified in source) post events on the event queue. | ||
Normally event engine is the target and will take the event off the event | Normally event engine is the target and will take the event off the event | ||
- | queue and process it. getDeviceData are in some cases the target. | + | queue and process it (getDeviceData was in some cases the target, not anymore). |
<code> | <code> | ||
Line 688: | Line 711: | ||
descr further descr of the subsystem (not used) | descr further descr of the subsystem (not used) | ||
</code> | </code> | ||
+ | |||
==== eventqvar ==== | ==== eventqvar ==== | ||
Line 734: | Line 758: | ||
</code> | </code> | ||
- | ==== alertmsg ==== | + | ==== alertqmsg ==== |
Event engine will, based on alertmsg.conf, preformat the alarm messages, | Event engine will, based on alertmsg.conf, preformat the alarm messages, | ||
one message for each configured alert channel (email, sms), one message for | one message for each configured alert channel (email, sms), one message for | ||
- | each configured language. The data are stored in the alertmsg table. | + | each configured language. The data are stored in the alertqmsg table. |
<code> | <code> | ||
Line 804: | Line 828: | ||
</code> | </code> | ||
- | ==== alertengine ==== | ||
- | Used by alert engine to keep track of processed alerts. | ||
- | <code> | + | ===== Messages ===== |
- | lastalertqid value of the last alertqid that alert engine has processed. | + | |
- | </code> | + | |
- | + | ||
- | ===== Messages and maintenance ===== | + | |
- | + | ||
- | FIXME **This section is OUTDATED as of NAV 3.2.0.** | + | |
- | + | ||
- | This [[http://domen.uninett.no/~faltin/nav/navdb/message.png|diagram]] shows the relations between the | + | |
- | message and maintenance tables. | + | |
- | ==== emotd ==== | + | ==== table: message ==== |
The table contains the messages registered in the messages tool. | The table contains the messages registered in the messages tool. | ||
Line 825: | Line 838: | ||
<code> | <code> | ||
- | emotdid primary key | + | messageid primary key |
- | author user (NAV logged in user) who posts the message | + | title title of the message (text) |
- | last_changed timestamp when the message was edited the last | + | description further description (text) |
- | time | + | tech_description optional technical description (text) |
- | type type of message: error (unplanned situation), | + | publish_start start time for when the message is published (timestamp) |
- | scheduled outage (planned), information, internal. | + | publish_end end time for when the message is published (timestamp) |
- | title title of the message (text) | + | author user who posted the message (text: username) |
- | description further description (text) | + | last_changed when the message was last changed (timestamp) |
- | detail details, not shown on the NAV main page (text) | + | replaces_message if the message is a follow up to an old message, |
- | affected end users affected (text) | + | this is a reference to the old message |
- | downtime downtime the message impicates (text) | + | replaced_by for convenience, if a message is replaced, the |
- | publish_start start time for when the message is published | + | trig_message_replace database trigger will create |
- | publish_end end time for publish | + | a reference to the new message that replaced it |
- | published boolean... ??? | + | |
- | replaces_emotd if the message is a follow up, reference to | + | |
- | the message it replaces. | + | |
- | title_en DEPRECATED? | + | |
- | detail_en DEPRECATED? | + | |
- | affected_en DEPRECATED? | + | |
- | downtime_en DEPRECATED? | + | |
</code> | </code> | ||
- | ==== maintenance ==== | + | ==== view: message_with_replaced ==== |
- | Each message may have a maintenance window. This is stored here. | + | A message joined with the message it replaces and the message it was replaced by. Columns returned: |
- | A location, room, netbox or service may be set on maintenance. | + | |
- | This is given by emotd_related. | + | |
<code> | <code> | ||
- | maintenanceid primary key | + | messageid replaces_message replaced_by |
- | emotdid the message the mainenance window relates to | + | title replaces_message_title replaced_by_title |
- | maint_start start of maintenance | + | description replaces_message_description replaced_by_description |
- | maint_end end of maintenance | + | tech_description replaces_message_tech_description replaced_by_tech_description |
- | state takes the values 'scheduled', 'active', 'passed' | + | publish_start replaces_message_publish_start replaced_by_publish_start |
- | or 'overridden'. maintengine changes this value. | + | publish_end replaces_message_publish_end replaced_by_publish_end |
+ | author replaces_message_author replaced_by_author | ||
+ | last_changed replaces_message_last_changed replaced_by_last_changed | ||
</code> | </code> | ||
- | ==== emotd_related ==== | + | ==== trigger: trig_message_replace ==== |
- | The (key,value) pairs that are on maintenance. | + | As described in message.replaced_by, this trigger automatically updates message.replaced_by |
+ | based on the message.replaces_message. The trigger is run on all inserts or updates to message. | ||
+ | replaced_by and replaces_message is in essence duplication of data, but this is defended by the | ||
+ | trigger which ensures that the two fields will always be consistent. | ||
- | <code> | ||
- | emotdid the message the maintenance relates to | ||
- | key may be location / room / netbox or module | ||
- | value relevant locationid/roomid/netboxid/serviceid | ||
- | </code> | ||
+ | ===== Maintenance ===== | ||
- | ===== Traffic map ===== | + | ==== table: maint_task ==== |
+ | The maintenance task created in the maintenance task tool. | ||
- | The traffic map (vlanplot) uses three tables to store values on positioning of | + | <code> |
- | routers in the graph and on the use of containers. | + | maint_taskid primary key |
- | This [[http://domen.uninett.no/~faltin/nav/navdb/trafficmap.png|diagram]] shows the releations between the | + | maint_start start of maintenance window (timestamp) |
- | traffic map tables. | + | maint_end end of maintenance window (timestamp) |
+ | description description of the maintenance task (text) | ||
+ | author user who posted the message (text: username) | ||
+ | state state of the maintenance task, | ||
+ | e.g. 'scheduled', 'active', 'passed' (text) | ||
+ | maintengine changes this value | ||
+ | </code> | ||
- | Also, see more documentation on the TrafficMap. | ||
- | ==== vp_netbox_xy ==== | + | ==== table: maint_component ==== |
- | The table is used for positioning nodes (routers) on the traffic map. | + | The components that are put on maintenance in the maintenance tool. |
- | A netbox may be positioned in more than one container (because we show the | + | |
- | other side of the link, the netboxes themself always only belong to one container). | + | |
<code> | <code> | ||
- | vp_netbox_xyid primary key | + | maint_taskid reference to the maintenance task the component relates to |
- | pnetboxid the netboxid of this box (router) | + | key may be 'location', 'room', 'netbox' or 'service' |
- | x the X coordinate | + | value depending on key this may be locationid, roomid, |
- | y the Y coordinate | + | netboxid or moduleid |
- | vp_netbox_grp_infoid reference to the container this node belongs to. | + | (primary key: the triplet (maint_taskid, key, value)) |
</code> | </code> | ||
- | ==== vp_netbox_grp_info ==== | + | ==== table: message_to_maint_task ==== |
- | The table keeps information on all containers used by the traffic map. | + | The connection between messages and related maintenance tasks. |
- | Editing containers is done in Edit Database. | + | |
<code> | <code> | ||
- | vp_netbox_grp_infoid primary key | + | messageid reference to the message |
- | name name of the container. The top container is named _Top. | + | maint_taskid reference to the maintenance task |
- | hideicons boolean. The value has different meanings, see the TrafficMap wiki page. | + | (primary key: the pair (messageid, maint_taskid)) |
- | iconname if a name is given and an equivalent icon is placed in the vlanPlot/icons | + | |
- | directory this will be used. | + | |
- | x the X coordinate of the container placement on _Top. | + | |
- | y the Y coordinate of the container placement on _Top. | + | |
</code> | </code> | ||
- | ==== vp_netbox_grp ==== | + | ==== view: maint ==== |
+ | |||
+ | maint_task natural joined with maint_component. | ||
- | The table maps netboxes to containers. This information is maintained by Edit Database. | ||
- | <code> | ||
- | vp_netbox_grp_infoid the container | ||
- | pnetboxid the netboxid of this box (router) | ||
- | </code> | ||
Line 962: | Line 963: | ||
==== identity ==== | ==== identity ==== | ||
+ | |||
+ | :!: Not up to date! | ||
<code> | <code> | ||
Line 968: | Line 971: | ||
blocked_status boolean | blocked_status boolean | ||
blocked_reasonid reason of the block, reference to the blocked_reason table | blocked_reasonid reason of the block, reference to the blocked_reason table | ||
- | swportid reference to the switch port found in the manage.swport table. | + | swportid reference to the switch port found in the interface table. |
We find sysname,ip,module and port from this | We find sysname,ip,module and port from this | ||
swsysname current sysname of switch, kept for consistency check | swsysname current sysname of switch, kept for consistency check | ||
Line 1027: | Line 1030: | ||
inputfile path to file where list of ip-adresses is, if applicable | inputfile path to file where list of ip-adresses is, if applicable | ||
</code> | </code> | ||
+ | |||
Line 1038: | Line 1042: | ||
version...). | version...). | ||
- | See [[http://metanav.uninett.no/static/reports/NAVMore.pdf|NAVMore report ch 2.4]] (in Norwegian) for a further explaination of | + | See [[http://nav.uninett.no/static/reports/NAVMore.pdf|NAVMore report ch 2.4]] (in Norwegian) for a further explaination of |
- | the NAV Cisco syslog analyzer tool. The chapter also includes a database figure, almost uptodate; the system table no longer exists and there is a newpriority reference from message to priority. | + | the NAV Cisco syslog analyzer tool. The chapter also includes a database figure, almost uptodate; the system table no longer exists and there is a newpriority reference from message to priority. For an explanation of the front-end tool, see [[..sysloganalyzer|here]]. |
==== message ==== | ==== message ==== |