devel:database
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
devel:database [2007/10/04 14:22] – Document new messages and maintenance database schema. jodal | devel:database [2012/05/07 11:22] (current) – fix url morten | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Database documentation ====== | ====== Database documentation ====== | ||
- | FIXME: Update for NAV 3.3 | + | This documents gives detailed information on the design of the NAV database. Currently (version |
- | This documents gives detailed information on the design | + | * The main database " |
+ | * The [[# | ||
+ | * The [[# | ||
+ | * The [[# | ||
- | * The main database " | ||
- | * The "NAV profiles" | ||
- | * The " | ||
- | * The " | ||
Line 35: | Line 34: | ||
* [[http:// | * [[http:// | ||
* The event and alert system | * The event and alert system | ||
- | * [[http:// | + | * A diagram |
- | * The message and maintenance | + | * The message and maintenance |
* [[http:// | * [[http:// | ||
* The traffic map (vlanplot) | * The traffic map (vlanplot) | ||
Line 54: | 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 62: | Line 61: | ||
ip IP address of the netbox | ip IP address of the netbox | ||
roomid | roomid | ||
+ | deviceid | ||
typeid | typeid | ||
sysname | sysname | ||
Line 76: | Line 76: | ||
upsince | upsince | ||
uptodate | uptodate | ||
+ | discovered | ||
</ | </ | ||
Line 90: | Line 91: | ||
val value | val value | ||
</ | </ | ||
+ | |||
==== device ==== | ==== device ==== | ||
Line 100: | Line 102: | ||
< | < | ||
deviceid | deviceid | ||
- | productid | ||
serial | serial | ||
hw_ver | hw_ver | ||
fw_ver | fw_ver | ||
sw_ver | sw_ver | ||
- | auto whether this device is discovered automatically by gDD or not | + | auto whether this device is discovered automatically by ipdevpoll |
+ | discovered | ||
active | active | ||
- | deviceorderid | ||
</ | </ | ||
Line 125: | 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 | downsince | ||
+ | name Modules aren't necessarily identified using integers (as in module.module), | ||
</ | </ | ||
Line 174: | Line 176: | ||
parent | parent | ||
descr | descr | ||
+ | contact | ||
opt1 additional info | opt1 additional info | ||
opt2 " | opt2 " | ||
Line 227: | Line 230: | ||
tftp whether the type supports tftp storage | tftp whether the type supports tftp storage | ||
</ | </ | ||
+ | |||
+ | ==== vendor ==== | ||
+ | |||
+ | The vendor table defines vendors. A type is of a vendor. | ||
+ | |||
+ | < | ||
+ | vendorid | ||
+ | </ | ||
+ | |||
===== The OID database ===== | ===== The OID database ===== | ||
This [[http:// | This [[http:// | ||
+ | |||
+ | 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 309: | Line 325: | ||
netboxid | netboxid | ||
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 |
</ | </ | ||
Line 322: | Line 338: | ||
rrd_fileid | rrd_fileid | ||
name name of the source (ds0, | name name of the source (ds0, | ||
- | descr | + | descr |
dstype | dstype | ||
units units used on y-axis (seconds, bytes, etc) | units units used on y-axis (seconds, bytes, etc) | ||
Line 335: | Line 351: | ||
</ | </ | ||
- | ===== Tables used by Device Management ===== | ||
- | This [[http:// | ||
- | ==== 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 compulsory. | + | |
- | < | + | {{devel: |
- | productid | + | |
- | productno | + | |
- | descr | + | |
- | vendorid | + | |
- | </ | + | |
- | ==== deviceorder | + | ==== interface |
- | The devicerorder | + | New table in v3.6. General interface table, combines the old gwport and swport in one. |
- | Not compulsary. An order consists | + | In cases where attribute starts with if* it is the exact equivalent |
- | of a certain product. | + | |
+ | < | ||
+ | interfaceid | ||
+ | netboxid | ||
+ | moduleid | ||
+ | ifindex | ||
+ | ifname | ||
+ | ifdescr | ||
+ | iftype | ||
+ | speed | ||
+ | ifphysaddress | ||
+ | ifadminstatus | ||
+ | ifoperstatus | ||
+ | iflastchange | ||
+ | ifconnectorpresent | ||
+ | ifpromiscuousmode | ||
+ | ifalias | ||
+ | baseport | ||
+ | media | ||
+ | vlan | ||
+ | trunk | ||
+ | duplex | ||
+ | to_netboxid | ||
+ | to_interfaceid | ||
- | < | ||
- | deviceorderid | ||
- | registered | ||
- | ordered | ||
- | arrived | ||
- | username | ||
- | orgid | ||
- | retailer | ||
- | ordernumber | ||
- | comment | ||
- | productid | ||
- | updatedby | ||
- | lastupdated | ||
- | |||
- | note: the amount is not registered in device order, but if the amount | ||
- | i.e. is 5, then 5 device order events are posted on the eventq (I think...). | ||
</ | </ | ||
- | ==== 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. | ||
< | < | ||
- | vendorid | + | id primary key |
+ | interfaceid | ||
+ | protoname | ||
+ | metric | ||
</ | </ | ||
- | |||
- | ===== Router related topology tables ===== | ||
- | |||
- | Diagram 2 shows the relations between the topology related tables: | ||
- | |||
- | {{devel: | ||
==== gwport ==== | ==== gwport ==== | ||
Line 421: | Line 432: | ||
< | < | ||
- | gwportid | + | interfaceid |
prefixid | prefixid | ||
gwip ip address defines on the router port | gwip ip address defines on the router port | ||
Line 438: | Line 449: | ||
vlanid | vlanid | ||
</ | </ | ||
+ | |||
==== vlan ==== | ==== vlan ==== | ||
Line 450: | Line 462: | ||
nettype | nettype | ||
orgid org. of the vlan, reference to the org table | orgid org. of the vlan, reference to the org table | ||
- | unageid | + | usageid |
netident | netident | ||
acccording to the suggested NAV syntax. | acccording to the suggested NAV syntax. | ||
Line 469: | Line 481: | ||
added manually in the Edit Database tool. For these netttypes (reserved and scope) edit=' | added manually in the Edit Database tool. For these netttypes (reserved and scope) edit=' | ||
</ | </ | ||
+ | |||
==== usage ==== | ==== usage ==== | ||
Line 477: | Line 490: | ||
< | < | ||
usageid | usageid | ||
- | descr firther | + | descr further |
</ | </ | ||
Line 501: | Line 514: | ||
This [[http:// | This [[http:// | ||
topology related tables. | topology related tables. | ||
+ | |||
==== swport ==== | ==== swport ==== | ||
Line 519: | 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 | + | trunk whether |
+ | May be NULL if it cannot be determined. | ||
portname | portname | ||
to_netboxid | to_netboxid | ||
Line 532: | Line 547: | ||
< | < | ||
swportvlanid | swportvlanid | ||
- | swportid | + | interfaceid |
vlanid | vlanid | ||
direction | direction | ||
- | ' | + | ' |
</ | </ | ||
==== swportallowedvlan ==== | ==== swportallowedvlan ==== | ||
- | Stores a hexstring | + | Stores a string of hexadecimal digits |
- | allowed to traverse a given trunk. | + | |
+ | * 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. | ||
+ | * If the hexadecimal string is less than 256 characters long, the bit order is reversed, and the least significant bit represents VLAN 0. | ||
+ | |||
+ | 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 this information more readable | + | To make interpretation of this data simpler using pure SQL, a help table (//range//) and two views (//allowedvlan// and //allowedvlan_both//) are provided. The // |
- | help table (range) and two views (allowedvlan and allowedvlan_both) are added to NAV | + | |
- | in version 3.1. They are not documented in any further detail for the time being. | + | |
< | < | ||
- | swportid | + | interfaceid |
- | hextring | + | hexstring |
</ | </ | ||
Line 557: | Line 574: | ||
< | < | ||
- | swportid | + | interfaceid |
vlan vlan value | vlan vlan value | ||
</ | </ | ||
Line 572: | Line 589: | ||
ifindex | ifindex | ||
to_netboxid | to_netboxid | ||
- | to_swportid | + | to_interfaceid |
misscnt | misscnt | ||
</ | </ | ||
Line 634: | Line 651: | ||
< | < | ||
patchid | patchid | ||
- | swportid | + | interfaceid |
cablingid | cablingid | ||
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. | ||
Line 652: | 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 | + | queue and process it (getDeviceData |
< | < | ||
Line 694: | Line 711: | ||
descr | descr | ||
</ | </ | ||
+ | |||
==== eventqvar ==== | ==== eventqvar ==== | ||
Line 740: | Line 758: | ||
</ | </ | ||
- | ==== alertmsg | + | ==== alertqmsg |
Event engine will, based on alertmsg.conf, | Event engine will, based on alertmsg.conf, | ||
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 | + | each configured language. The data are stored in the alertqmsg |
< | < | ||
Line 810: | Line 828: | ||
</ | </ | ||
- | ==== alertengine ==== | ||
- | Used by alert engine to keep track of processed alerts. | ||
- | |||
- | < | ||
- | lastalertqid | ||
- | </ | ||
===== Messages ===== | ===== Messages ===== | ||
Line 880: | Line 892: | ||
maintengine changes this value | maintengine changes this value | ||
</ | </ | ||
+ | |||
==== table: maint_component ==== | ==== table: maint_component ==== | ||
Line 887: | Line 900: | ||
< | < | ||
maint_taskid | maint_taskid | ||
- | key may be ' | + | key may be ' |
value | value | ||
netboxid or moduleid | netboxid or moduleid | ||
Line 908: | Line 921: | ||
- | ===== Traffic map ===== | ||
- | |||
- | The traffic map (vlanplot) uses three tables to store values on positioning of | ||
- | routers in the graph and on the use of containers. | ||
- | This [[http:// | ||
- | traffic map tables. | ||
- | |||
- | Also, see more documentation on the TrafficMap. | ||
- | |||
- | ==== vp_netbox_xy ==== | ||
- | |||
- | The table is used for positioning nodes (routers) on the traffic map. | ||
- | 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). | ||
- | |||
- | < | ||
- | vp_netbox_xyid | ||
- | pnetboxid | ||
- | x the X coordinate | ||
- | y the Y coordinate | ||
- | vp_netbox_grp_infoid | ||
- | </ | ||
- | |||
- | ==== vp_netbox_grp_info ==== | ||
- | |||
- | The table keeps information on all containers used by the traffic map. | ||
- | Editing containers is done in Edit Database. | ||
- | |||
- | < | ||
- | vp_netbox_grp_infoid | ||
- | name name of the container. The top container is named _Top. | ||
- | hideicons | ||
- | iconname | ||
- | 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. | ||
- | </ | ||
- | |||
- | ==== vp_netbox_grp ==== | ||
- | |||
- | The table maps netboxes to containers. This information is maintained by Edit Database. | ||
- | |||
- | < | ||
- | vp_netbox_grp_infoid | ||
- | pnetboxid | ||
- | </ | ||
Line 996: | Line 963: | ||
==== identity ==== | ==== identity ==== | ||
+ | |||
+ | :!: Not up to date! | ||
< | < | ||
Line 1002: | Line 971: | ||
blocked_status | blocked_status | ||
blocked_reasonid | blocked_reasonid | ||
- | swportid | + | swportid |
We find sysname, | We find sysname, | ||
swsysname | swsysname | ||
Line 1061: | Line 1030: | ||
inputfile | inputfile | ||
</ | </ | ||
+ | |||
Line 1072: | Line 1042: | ||
version...). | version...). | ||
- | See [[http://metanav.uninett.no/ | + | See [[http://nav.uninett.no/ |
- | 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 ==== |
devel/database.1191507759.txt.gz · Last modified: by jodal