NAV Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Database documentation ======

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.











====== NAVdb ("manage") ======

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:

 
  * [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|Diagram 1]] shows these groups of tables: 
    * Netbox related tables 
    * The OID database
    * Tables for serviceMon
    * Tables used by Device Management 
  * [[http://domen.uninett.no/~faltin/nav/navdb/topology.png|Diagram 2]] shows topology related tables:
    * Router related topology
    * Switch related topology 
  * [[http://domen.uninett.no/~faltin/nav/navdb/eventDB.png|Diagram 3]] shows tables for:
    * The event and alert system   
  * A diagram 4 (currently non existent) should show the tables for:
    * The message and maintenance systems      
  * [[http://domen.uninett.no/~faltin/nav/navdb/trafficmap.png|Diagram 5]] shows tables for:  
    * The traffic map (vlanplot)







===== Netbox related tables =====

Diagram 1 shows the relations between the group of tables in this section:

{{devel:db:netbox_related.png?600|}} 

==== netbox ====

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
information and relations.

<code>
netboxid        primary key
ip              IP address of the netbox
roomid          room the box is placed in
deviceid        the device this is (foreign key to device)
typeid          sysobjectid of the box
sysname         name of the box, based on fully qualified dns name with fall back to IP address.
                Historically MibII system.sysname was used as source, but we now use dns.
catid           category of the box (GW,SW,SRV, etc)
subcat          DEPRECATED? - check before delete!!!
orgid           organization that manages the box
ro              snmp read community 
rw              snmp write community
prefixid        prefix the netbox is on
up              whether the box is up and running (as seen from pping)
snmp_version    version of the snmp agent (1 or 2)
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
uptodate        whether gDD has done OID classification
discovered      timestamp when the box was first discovered by NAV
</code>

==== netboxinfo ====

The netboxinfo table is the place to store additional info on a netbox.

<code>
netboxinfoid    primary key
netboxid        netbox the info relates to
key             not alway used, adds another dimension to (var,val). gDD uses i.e. this when storing collected
                CDP data for a netbox.
var             variable
val             value
</code>


==== device ====

The device table contains all physical devices in the network. As opposed to
the netbox table, the device table focuses on the physical box with its serial
number. The device may appear as different net boxes or may appear in different
modules throughout its lifetime.

<code>
deviceid        primary key
serial          serial number
hw_ver          hardware version
fw_ver          firmware version
sw_ver          software version
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)
</code>

==== module ====

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 physical
device with a serial number.

<code>
moduleid        primary key
deviceid        the device this is
netboxid        netbox the module is a part of
module          the module number (integer - may be confusing)
model           the model description (as given by the vendor)
descr           further description (as given by the vendor)
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
name            Modules aren't necessarily identified using integers (as in module.module), so we add names in v3.6
</code>

==== mem ====

The mem table describes the memory (memory and nvram) of a netbox. 

<code>
memid           primary key
netboxid        netbox the memory is part of
memtype         type of memory (flash or memory)
device          memory device (bootflash, slot0, disk1, etc)
size            size of the memory in byte
used            how much of the memory that is used
</code>


==== room ====

The room table defines a wiring closes / network room / server room

<code>
roomid          primary key - text - also the name of the room
locationid      in which location the room is
descr           description of the room
opt1            additional info
opt2            "
opt3            "
opt4            "
</code>

==== location ====

The location table defines a group of rooms; i.e. a campus.

<code>
locationid      primary key - text - also name of location
descr           further description
</code>

==== org ====

The org table defines an organization. 
  * An organization is in charge of a given netbox. 
  * An organization is using a given prefix (to derive this relation you must adopt the NAV guidelines for router descriptions as explained in SubnetsAndVlans).

<code>
orgid           primary key - text - also name of org
parent          parent organization, if any
descr           further description
contact         contact info
opt1            additional info
opt2            "
opt3            "
</code>

==== cat ====

The cat table defines the categories of a netbox (GW,GSW,SW,EDGE,WLAN,SRV,OTHER).

<code>
catid           primary key - predefined categories 
                (GW,GSW,SW,EDGE,WLAN,SRV,OTHER)
descr           further description
req_snmp        whether NAV requires snmp support for the category
                (false for SRV and OTHER, true for the five others)
</code>

==== subcat ====

The subcat table defines subcategories within a category. A category may have many subcategories. 
A subcategory belong to one and only one category.

<code>
subcatid        primary key - text - also name of subcat
descr           further description
catid           category the subcat belongs to
</code>

==== netboxcategory ====

A netbox may be in many subcategories. This relation is defined here.

<code>
netboxid        netbox in question
category        _subcat_ in question 
</code>

==== type ====

The type table defines the type of a netbox, the sysobjectid being the unique identifier.

<code>
typeid          primary key
vendorid        vendor of this equipment type
typename        name of the type
descr           further description
sysobjectid     sysobjectid
cs_at_vlan      whether the type uses community@vlan snmp polls
chassis         whether this is a chassis type box or not
frequency       DEPRECATED - check before delete!!! 
cdp             whether the type supports cdp 
tftp            whether the type supports tftp storage
</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 =====

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

The snmpoid table defines all OIDs used during snmp data gathering and/or Cricket data collection.

<code>
snmpoidid       primary key
snmpoid         the OID
oidkey          the variable used in NAV code for the OID
oidsource       whether Cricket uses the oid or not
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.
                If false; this OID is a leaf OID.
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
                the Edit Database tool this value will initially be false.
defaultfreq     Default gDD poll frequency for this OID. Netboxes that are classified to answer to this OID will
                inherit this value in netboxsnmpoid. Most OIDs default to 6hrs (21600), modulemon to 1 hrs (3600).
descr           further description
oidname         the official name of the oid
mib             the MIB the oid belongs to
</code>

==== netboxsnmpoid ====

The netboxsnmpoid table defines which netboxes answers to which snmpoids.

<code>
netboxid        which netbox
snmpoidid       which OID
frequency       How often gDD polls this OID for this netbox. The value is inherited from snmpoid.deaultfreq.
</code>

===== Tables for serviceMon =====

This [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|diagram]] shows tables for serviceMon (and more).

==== service ====

The service table defines the services on a netbox that serviceMon monitors.

<code>
serviceid       primary key
netboxid        which netbox the service for monitoring is for
active          DEPRECATED? - check before remove. Was used for maintenance.
handler         type of service (ssh,http,dns,dc etc)
version         version of the service, i.e. apache/ssh version
up              whether serviceMon finds that the service is up/down
</code>

==== serviceproperty ====

Each service may have an additional set of attributes. They are defined here.

<code>
serviceid       primary key
property        additional property of the service
value           the value of the property
</code>

===== The RRD database =====

This [[http://domen.uninett.no/~faltin/nav/navdb/netbox_related.png|diagram]] shows the RRD database (and more).

==== rrd_file ====

The rrd_file contains meta information on all RRD files that NAV 
uses. Each RRD file has statistics for a certain netbox

<code>
rrd_fileid       primary key
path             file system path to the rrd file in question
filename         filename of the rrd file
step             how often the rrd fil is updated (in seconds)
subsystem        the subsystem that is updating the file
                 (typically cricket, pping or servicemon) 
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. 
                 which table; service / interface
value            i.e. which serviceid / interfaceid
</code>

==== rrd_datasource ====

An rrd_file consists of a set of data sources defined in this table.
A data source is a data set, i.e. outOctets for a given switch port on a given switch.

<code>
rrd_datasourceid primary key
rrd_fileid       the rrd file the data source is within
name             name of the source (ds0,ds1,RESPONSE TIME etc)
descr            type of datasource, i.e. ifInOctets, ifInErrors, cpu5min, etc
dstype           type (DERIVE / GAUGE)
units            units used on y-axis (seconds, bytes, etc)
threshold        for thresholdmon: the threshold value to be checked on this data source.
                 May be stored as an integer or a percentage number.
max              for thresholdmon: the max value that this data source may be. Important if 
                 the threshold is stored as percentage.
delimiter        for thresholdmon: either '<' or '>'. Tells us if the value must be higher or
                 lower than the threshold.
thresholdstate   for thresholdmon: if this is set to 'active' we have sent an alert on this
                 data source, if it is inactive, we have not.
</code>



===== Router related topology tables =====

Diagram 2 shows the relations between the topology related tables:

{{devel:db:topology.png?600|}} 

==== interface ====

New table in v3.6. General interface table, combines the old gwport and swport in one. 
In cases where attribute starts with if* it is the exact equivalent of the IF-MIB (RFC 1229) instance.

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

==== rproto_attr ====

New table in v3.6. General table for routing protocol metrics. Relates to intervace table. 


<code>
id            primary key
interfaceid   which interface this is (foreign key) 
protoname     protocol name, i.e. ospf, isis, etc
metric        metric value
</code>


==== gwport ====

The gwport table defines the router ports connected to a module.
Only router ports that are //not// shutdown are included. 
Router ports without defined IP addresses are also excluded.

<code>
gwportid        primary key
moduleid        module the router port is on
ifindex         ifindex of the router port
link            whether the router port is operState up or down, apparently not in use
masterindex     if the router port is a subinterface, this value
                points to the gwportid of the master port.
interface       interface textual name (i.e Ethernet1/2)
speed           speed in Mbps (1000 => 1 Gbps)
metric          ospf metric defined on the router port, if relevant.
portname        router interface description (the whole string)
to_netboxid     the netbox the router port connects to (if any)
to_swportid     the switch port the router connects to (if any)
 

</code>

==== gwportprefix ====

The gwportprefix table defines the router port IP addresses, one or more.
HSRP is also supported.

<code>
interfaceid     interface (router port) in question
prefixid        prefix in question
gwip            ip address defines on the router port
hsrp            boolean. If true, the ip address is an hsrp address
 
Note: this allows for secondary addresses on router ports.
</code>

==== prefix ====

The prefix table stores IP prefixes.

<code>
prefixid        primary key
netaddr         cidr prefix 
vlanid          broadcast / vlan the prefix is running on
</code>


==== vlan ====

The vlan table defines the IP broadcast domain / vlan.
A broadcast domain often has a vlan value, it may consist of many IP prefixes, it 
is of a network type, it is used by an organization (org) and has a user group (usage) within the org.

<code>
vlanid          primary key
vlan            vlan value
nettype         nettype of the vlan, reference to the nettype table
orgid           org. of the vlan, reference to the org table
usageid         usage of the vlan, reference to the usage table
netident        a substring of the router port description derived
                acccording to the suggested NAV syntax.
description     also a substring of the router port description derived
                acccording to the suggested NAV syntax.  
</code>

==== nettype ====

The nettype table defines network type;lan, core, link, elink, loopback, closed, static, reserved, scope.
For a definition se SubnetsAndVlans. The network types are predefined in NAV and may not be altered.

<code>
nettypeid       primary key, also nettype value;
                - lan, link core, elink, static, scope etc
descr           further description
edit            Prefixes that are reserved, but not in operation or define your outer scope may be
                added manually in the Edit Database tool. For these netttypes (reserved and scope) edit='t'.
</code>


==== usage ====

The usage table defines the user group (student, staff etc).
Usage categories are maintained in the edit database tool.

<code>
usageid         primary key, also usage value
descr           further description
</code>

==== arp ====

The arp table contains (ip, mac, time start, time end)

<code>
arpid           primary key
netboxid        router the arp entry comes from
sysname         the same router in name (in case the
                router is deleted, arp has historic data)
prefixid        prefix the arp entry belongs to
ip              ip address of the arp entry
mac             mac address of the arp entry
start_time      time the arp entry was first discovered
end_time        time the arp entry disappeared
                (typically 4 hours after the last packet sent). 
</code>

===== Switch  related topology tables =====

This [[http://domen.uninett.no/~faltin/nav/navdb/topology.png|diagram]] showns the relations between the
topology related tables.


==== swport ====

The swport table defines the switchports connected to a module.

<code>
swportid        primary key
moduleid        module the swith port is on
ifindex         ifindex of the swport
port            port number (integer) if appliqable
interface       interface name
link            wheather the port has link. 'd' means ???               
speed           speed of the port in Mbps (1000 => 1 Gbps)
duplex          half or full duplex (h/f)
media           media type - not collected ??? USED?
vlan            vlan value of non trunk ports. This value is
                also in the swportvlan table - in report it
                is taken from swport vlan, not from here.
trunk           whether the port is trunk (boolean).
                May be NULL if it cannot be determined.
portname        the configured port name (description/ifAlias value)
to_netboxid     the netbox the port connects to (if any)
to_swportid     the switch port the port connects to (if any)
</code>

==== swportvlan ====

The swportvlan table defines the vlan values on all switch ports.
dot1q trunk ports typically have several rows in this table.

<code>
swportvlanid    table primary key 
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)
direction       Direction of the link, seen from the root of the topology (the vlan's router).
                'o' = 'Up', 'n' = 'Down', 'b' = 'Blocked', 'x' = Unknown
</code>

==== swportallowedvlan ====

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:

  * 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 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>
interfaceid     switch trunk port in question (foreign key to the interface table)
hexstring       hexstring that defines the allowed vlans for the trunk
</code>

==== swportblocked ====

This table defines the spanning tree blocked ports for a given vlan for a given switch port.

<code>
interfaceid     switch port in question (foreign key to interface table)
vlan            vlan value
</code>

==== swp_netbox ====

A help table used in the process of building the physical
topology of the network. swp_netbox defines the candidates 
for next hop physical neighborship.

<code>
swp_netboxid    primary key
netboxid        switch in question
ifindex         ifindex on the switch
to_netboxid     candidate next hop netbox
to_interfaceid  candidate next hop switch port (foreign key to interface table)
misscnt         see misscnt in the cam table below. Same thing.
</code>

==== netbox_vtpvlan ====

A help table that contains the vtp vlan database of a switch.
For certain cisco switches cam information is gathered
using a community@vlan string. It is then necessary to know all
vlans that are active on a switch. The vtp vlan table is an
extra source of information.

<code>
netboxid       relation to the netbox (switch) in question  
vtpvlan        vlan value from the vtp table of the switch
</code>

==== cam ====

The cam table defines (swport, mac, time start, time end)

<code>
camid           primary key
netboxid        switch that has the cam entry
sysname         name of the same switch, in case switch is deleted,
                cam data are historic
ifindex         infindex of the switch port for the cam entry
module          module number for the cam entry
port            port number for the cam entry
mac             mac address found on the port 
start_time      time the mac address was first seen
end_time        time the mac address disappeared (idle timer in 
                bridge tables are typically 5 minutes) 
misscnt         count how many times the cam entry has been tried updated
                and failed. We do not want to terminate these cam entries
                right away. It is configurable how many misscnt the camlogger
                should tolerate.
</code>

===== Cabling system database =====

==== cabling ====

The cabling table documents the cabling from the wirering closet's jack
number to the end user's room number.

<code>
cablingid       primary key
roomid          room number 
jack            jack number
building        building where the cabling is done
targetroom      room the remote end of the cabling is terminated
descr           further description, if any
category        type of cabling (i.e. cat5e, cat6 ...)
</code>

==== patch ====

The patch table documents the cross connect from switch port to jack.

<code>
patchid         primary key
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
split           If a split cable is used in the jack end, specify type and leaf.
</code>



===== Event system =====

Diagram 3 shows the relations between the event system tables:

{{devel:db:eventDB.png?600|}} 

==== eventq ====

The event queue. Additional data in eventqvar.
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
queue and process it (getDeviceData was in some cases the target, not anymore).

<code>
eventqid        primary key
source          the subsystem that has posted the event
                (reference to subsystem table)
target          the subsystem that is the target of the event
                (reference to subsystem table). 
                Usually event engine, may be gDD
deviceid        the device that the event is about
netboxid        the netbox that the event is about
subid           a sub value on the netbox, may be ...
time            time the event was posted
eventtypeid     event type, reference to the eventtype table
state           state of the event; s=start, e=end, x=not stateful
value           value if applickable
severity        severity of the event
eventtype       defines eventtypes
</code>

==== eventtype ====

Defines event types.

<code>
eventtypeid     primary key, also event type value
                (i.e boxState, serviceState, info etc)
eventtypedesc   further description of the event
stateful        boolean value; wheather the event is statefull

subsystem       defines subsystem that post or receives events
</code>

==== subsystem ====

Defines the subsystems that post or receives an event.

<code>
name            name of subsystem that posts (source) or 
                receives (target) event on the eventq
descr           further descr of the subsystem (not used)
</code>


==== eventqvar ====

Defines additional (key,value) tuples that follow events.

<code>
eventqid        event in question
var             variable
val             value
</code>

==== alertq ====

The alert queue. Additional data in alertqvar and alertmsg.
Event engine posts alerts on the alert queue (and in addition
on the alerthist table). Alert engine will process the data on
the alert queue and send alerts to users based on their alert
profiles. When all signed up users have received the alert, alert engine
will delete the alert from alertq (but not from alert history).

<code>
alertqid        primary key
source          the subsystem that has posted the event
                (reference to subsystem table)
deviceid        the device that the event is about
netboxid        the netbox that the event is about
subid           a sub value on the netbox, may be ...
time            time the event was posted
eventtypeid     event type, reference to the eventtype table
alerttypeid     alert type, reference to the eventtype table
state           state of the event; s=start, e=end, x=not stateful
value           value if applickable
severity        severity of the event
</code>

==== alerttype ====

Defines the alert types. An event type may have many alert types.

<code>
alerttypeid     primary key
eventtypeid     event type
alerttype       alert type name
alerttypedesc   alert type description
</code>

==== alertqmsg ====

Event engine will, based on alertmsg.conf, preformat the alarm messages,
one message for each configured alert channel (email, sms), one message for
each configured language. The data are stored in the alertqmsg table.

<code>
alertqid        primary key
msgtype         email | sms
language        language of the message 
msg             the message text
</code>

==== alertqvar ====

Defines additional (key,value) tuples that follow alert.
Note: the eventqvar tuples are passed along to the alertqvar table
so that the variables may be used in alert profiles.

<code>
alertqid        primary key
var             variable
val             value
</code>


==== alerthist ====

The alert history. Simular to the alert queue with one important distinction;
alert history stores statefull events as one row, with the start and end time
of the event. 

<code>
alerthistid     primary key
source          the subsystem that has posted the event
                (reference to subsystem table)
deviceid        the device that the event is about
netboxid        the netbox that the event is about
subid           a sub value on the netbox, may be ...
start_time      when the event occurred. if stateful; start time
end_time        if stateful; end time
eventtypeid     event type, reference to the eventtype table
alerttypeid     alert type, reference to the eventtype table
value           value if applickable
severity        severity of the event
</code>

==== alerthistvar ====

Defines additional (key,value) tuples that follow the alerthist record.

<code>
alerthistid     primary key
state           state of the event; s=start, e=end, x=not stateful
var             variable
val             value
</code>

==== alerthistmsg ====

To have a history of the formatted messages too, they are stored in alerthistmsg.

<code>
alerthistid     primary key
state           state of the event; s=start, e=end, x=not stateful
msgtype         email | sms
language        language of the message 
msg             the message text
</code>



===== Messages =====

==== table: message ====

The table contains the messages registered in the messages tool.
Each message has a timeframe for when it is published on the NAV main page.

<code>
messageid         primary key
title             title of the message (text)
description       further description (text)
tech_description  optional technical description (text)
publish_start     start time for when the message is published (timestamp)
publish_end       end time for when the message is published (timestamp)
author            user who posted the message (text: username)
last_changed      when the message was last changed (timestamp)
replaces_message  if the message is a follow up to an old message,
                  this is a reference to the old message
replaced_by       for convenience, if a message is replaced, the
                  trig_message_replace database trigger will create
                  a reference to the new message that replaced it
</code>

==== view: message_with_replaced ====

A message joined with the message it replaces and the message it was replaced by. Columns returned:

<code>
messageid          replaces_message                   replaced_by
title              replaces_message_title             replaced_by_title
description        replaces_message_description       replaced_by_description 
tech_description   replaces_message_tech_description  replaced_by_tech_description
publish_start      replaces_message_publish_start     replaced_by_publish_start
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>

==== trigger: trig_message_replace ====

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.


===== Maintenance =====

==== table: maint_task ====

The maintenance task created in the maintenance task tool.

<code>
maint_taskid    primary key
maint_start     start of maintenance window (timestamp)
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>


==== table: maint_component ====

The components that are put on maintenance in the maintenance tool.

<code>
maint_taskid    reference to the maintenance task the component relates to
key             may be 'location', 'room', 'netbox' or 'service'
value           depending on key this may be locationid, roomid,
                netboxid or moduleid
(primary key: the triplet (maint_taskid, key, value))
</code>

==== table: message_to_maint_task ====

The connection between messages and related maintenance tasks.

<code>
messageid       reference to the message
maint_taskid    reference to the maintenance task
(primary key: the pair (messageid, maint_taskid))
</code>

==== view: maint ====

maint_task natural joined with maint_component.





====== The "NAV profiles database" ======

The "NAV Profiles" database contains 27 tables. 
This [[http://domen.uninett.no/~faltin/nav/navdb/NAVProfiles.png|figure]] shows 21 of them. 
As the figure suggests, the "NAV Profiles" database can be subdivided into the following groups of tables:

  * Tables managed by the **user admin** tool (yellow tables)
  * Tables managed by **alertEngine** (green tables)
  * The rest (blue and red) are used by **Alert Profiles**.

The last 6 tables, not included on the figure, can be grouped as follows:
  * accountorg relates users to organizations, so a part of **user admin**
  * privilege and accountgroupPrivilege relates privilege to users, administered by **user admin**
  * navbarlink and accountbarlink relates to the users setup for **nav bar preferences**
  * defaultfilter is an additional table uses by **Alert Profiles**.

To sum up, the 27 tables can be grouped as follows:

  * **user admin** has 10 tables.
  * **nav bar user preference** has 2 tables.
  * **alert Engine** has 2 tables.
  * **Alert Profiles** has 13 tables. 

We do not document the database in further detail here, but refer to
[[http://svn.itea.ntnu.no/repos/nav/navme/branches/3.1.x/doc/sql/navprofiles.sql|navprofiles.sql]] that is
quite well documented!

====== The "Arnold" database ======

The "Arnold" database contains 4 tables. 

==== blocked_reason ====

<code>
blocked_reasonid      primary key
text                  A reason for blocking a given set of switch ports
</code>

==== identity ====

:!: Not up to date!

<code>
identityid            primary key
mac                   MAC-address of computer
blocked_status        boolean
blocked_reasonid      reason of the block, reference to the blocked_reason table
swportid              reference to the switch port found in the interface table.
                      We find sysname,ip,module and port from this
swsysname             current sysname of switch, kept for consistency check
swvendor              vendor of switch, used to determine snmp-query
swip                  current ip of switch, kept for consistency check
swmodule              current module, kept for consistency check
swport                current port, kept for consistency check
swifindex             current ifindex, kept for consistency check
community             community of switch
ip                    current ip of computer
dns                   current dns-name of computer
netbios               current netbios-name of computer
starttime             time of first event on this computer-swport combo
lastchanged           time of last current event on this computer-swport combo
autoenable            time for autoenable
autoenablestep        number of days to wait for autoenable
multiple              stores the amount of computers detected behind this port
mail                  the mail address the warning was sent to
secret                flag this tuple as visible only to members of the secret club
userlock              lock this tuple to the specified user, it is visible but not possible to enable by other users
orgid                 organization
determined            set to y if this mac/port combo is blocked with the -d option.
</code>

==== event ====

<code>
eventid               primary key
identityid            reference to identity
event_comment         comment
blocked_status        boolean
blocked_reasonid      reason of block, reference to blocked_reason
eventtime             timestamp of the event
autoenablestep        ?
username 
</code>


==== block ====

The block table, in lack of a better name, is a run where we do automatic blocking 
of computers based on input ip-list.

<code>
blockid               primary key
blocktitle            title of block
blockdesc             description of block
mailfile              path to mailfile to use to send mail when blocking
reasonid              reason of block, reference to blocket_reason
private               if set uses the -k parameter in Arnold
determined            if set uses the -d parameter in Arnold
incremental           if set uses the -e parameter in Arnold
blocktime             days from block to autoenable
userid                the user that blocks the ip-adresses
active                if set to n will not do blocking of this kind
lastedited            timestamp of last time this block was edited
lastedituser          username of user who last edited this block
inputfile             path to file where list of ip-adresses is, if applicable
</code>





====== The "logger" database ======

The logger database contains 6 tables. The database is used for storing Cisco syslog messages in a
structured manner. The database has no relation to other parts of NAV, thus the logger system can be looked upon
as a separate system (the origin table could potentially relate to the netbox table in a later
version...). 

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. For an explanation of the front-end tool, see [[..sysloganalyzer|here]].

==== message ====

Contains the syslog messages with reference  to other tables
<code>
id                    primary key
message               the message itself
time                  timestamp when the message was posted
origin                reference to origin table, the box who has sent the message
type                  reference to the type table, the message type, indirectly priority
newpriority           reference to priority table, used only if the priority of the message is overridden
                      by rules defined in the syslog config file.
</code>

==== origin ====

The box that has sent the syslog message
<code>
origin                primary key
name                  name of the box (switch,router,base station) that sent the syslog message               
category              reference to category 
</code>

==== category ====

<code>
category              ? (gw,sw etc?)
</code>


==== type ====

<code>
type                 primary key
priority             reference to priority table (value 0-7)
facility             component of the Cisco defined syslog structure (ex: IP / LINK etc)
mnemonic             component of the Cisco defined syslog structure (ex: TCP_BADCKSUM, ERROR etc)
</code>

==== priority ====

Defines the eight Cisco priority levels. This table is predefined in NAV filled with
the eight rowa of data.

<code>
priority              primary key, value 0-7
keyword               name of the priority
description           explaination
</code>

==== errorerror ====

Contains messages that deviate from the cisco syslog message format and thus cannot be stored in
the given structure.

<code>
id                    primary key
message               the message that was erroranous
</code>
NAV Wiki

User Tools

Site Tools

Page Tools
