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:
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:
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.
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
The netboxinfo table is the place to store additional info on a netbox.
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
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.
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)
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.
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
The mem table describes the memory (memory and nvram) of a netbox.
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
The room table defines a wiring closes / network room / server room
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 "
The location table defines a group of rooms; i.e. a campus.
locationid primary key - text - also name of location descr further description
The org table defines an organization.
orgid primary key - text - also name of org parent parent organization, if any descr further description contact contact info opt1 additional info opt2 " opt3 "
The cat table defines the categories of a netbox (GW,GSW,SW,EDGE,WLAN,SRV,OTHER).
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)
The subcat table defines subcategories within a category. A category may have many subcategories. A subcategory belong to one and only one category.
subcatid primary key - text - also name of subcat descr further description catid category the subcat belongs to
A netbox may be in many subcategories. This relation is defined here.
netboxid netbox in question category _subcat_ in question
The type table defines the type of a netbox, the sysobjectid being the unique identifier.
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
The vendor table defines vendors. A type is of a vendor.
vendorid table identifier, also the name of the vendor.
This 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.
The snmpoid table defines all OIDs used during snmp data gathering and/or Cricket data collection.
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
The netboxsnmpoid table defines which netboxes answers to which snmpoids.
netboxid which netbox snmpoidid which OID frequency How often gDD polls this OID for this netbox. The value is inherited from snmpoid.deaultfreq.
This diagram shows tables for serviceMon (and more).
The service table defines the services on a netbox that serviceMon monitors.
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
Each service may have an additional set of attributes. They are defined here.
serviceid primary key property additional property of the service value the value of the property
This diagram shows the RRD database (and more).
The rrd_file contains meta information on all RRD files that NAV uses. Each RRD file has statistics for a certain netbox
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
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.
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.
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.
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)
New table in v3.6. General table for routing protocol metrics. Relates to intervace table.
id primary key interfaceid which interface this is (foreign key) protoname protocol name, i.e. ospf, isis, etc metric metric value
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.
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)
The gwportprefix table defines the router port IP addresses, one or more. HSRP is also supported.
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.
The prefix table stores IP prefixes.
prefixid primary key netaddr cidr prefix vlanid broadcast / vlan the prefix is running on
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.
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.
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.
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'.
The usage table defines the user group (student, staff etc). Usage categories are maintained in the edit database tool.
usageid primary key, also usage value descr further description
The arp table contains (ip, mac, time start, time end)
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).
This diagram showns the relations between the topology related tables.
The swport table defines the switchports connected to a module.
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)
The swportvlan table defines the vlan values on all switch ports. dot1q trunk ports typically have several rows in this table.
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
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:
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.
interfaceid switch trunk port in question (foreign key to the interface table) hexstring hexstring that defines the allowed vlans for the trunk
This table defines the spanning tree blocked ports for a given vlan for a given switch port.
interfaceid switch port in question (foreign key to interface table) vlan vlan value
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.
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.
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.
netboxid relation to the netbox (switch) in question vtpvlan vlan value from the vtp table of the switch
The cam table defines (swport, mac, time start, time end)
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.
The cabling table documents the cabling from the wirering closet's jack number to the end user's room number.
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 ...)
The patch table documents the cross connect from switch port to jack.
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.
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).
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
Defines event types.
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
Defines the subsystems that post or receives an event.
name name of subsystem that posts (source) or receives (target) event on the eventq descr further descr of the subsystem (not used)
Defines additional (key,value) tuples that follow events.
eventqid event in question var variable val value
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).
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
Defines the alert types. An event type may have many alert types.
alerttypeid primary key eventtypeid event type alerttype alert type name alerttypedesc alert type description
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.
alertqid primary key msgtype email | sms language language of the message msg the message text
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.
alertqid primary key var variable val value
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.
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
Defines additional (key,value) tuples that follow the alerthist record.
alerthistid primary key state state of the event; s=start, e=end, x=not stateful var variable val value
To have a history of the formatted messages too, they are stored in alerthistmsg.
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
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.
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
A message joined with the message it replaces and the message it was replaced by. Columns returned:
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
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.
The maintenance task created in the maintenance task tool.
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
The components that are put on maintenance in the maintenance tool.
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))
The connection between messages and related maintenance tasks.
messageid reference to the message maint_taskid reference to the maintenance task (primary key: the pair (messageid, maint_taskid))
maint_task natural joined with maint_component.
The “NAV Profiles” database contains 27 tables. This figure shows 21 of them. As the figure suggests, the “NAV Profiles” database can be subdivided into the following groups of tables:
The last 6 tables, not included on the figure, can be grouped as follows:
To sum up, the 27 tables can be grouped as follows:
We do not document the database in further detail here, but refer to navprofiles.sql that is quite well documented!
The “Arnold” database contains 4 tables.
blocked_reasonid primary key text A reason for blocking a given set of switch ports
Not up to date!
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.
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
The block table, in lack of a better name, is a run where we do automatic blocking of computers based on input ip-list.
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
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 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 here.
Contains the syslog messages with reference to other tables
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.
The box that has sent the syslog message
origin primary key name name of the box (switch,router,base station) that sent the syslog message category reference to category
category ? (gw,sw etc?)
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)
Defines the eight Cisco priority levels. This table is predefined in NAV filled with the eight rowa of data.
priority primary key, value 0-7 keyword name of the priority description explaination
Contains messages that deviate from the cisco syslog message format and thus cannot be stored in the given structure.
id primary key message the message that was erroranous