/opennms-base-assembly/src/main/filtered/etc/create.sql
SQL | 2660 lines | 1209 code | 254 blank | 1197 comment | 1 complexity | 0a52967f0296d9fe4e220b03a6606906 MD5 | raw file
Large files files are truncated, but you can click here to view the full file
1--# create.sql -- SQL to build the initial tables for the OpenNMS Project 2--# 3--# Modifications: 4--# 2013 Nov 15: Added protocol field in datalinkinterface table 5--# 2009 Sep 29: Added linkTypeId field in datalinkinterface table 6--# 2009 Mar 27: Added Users, Groups tables 7--# 2009 Jan 28: Added Acks tables - david@opennms.org 8--# 2007 Apr 10: Added statistics report tables - dj@opennms.org 9--# 2006 Apr 17: Added pathOutage table 10--# 2005 Mar 11: Added alarms table 11--# 2004 Aug 30: See create.sql.changes 12--# 13--# Copyright (C) 2005-2006 The OpenNMS Group, Inc., Inc. All rights reserved. 14--# Parts Copyright (C) 1999-2001 Oculan Corp. All rights reserved. 15--# 16--# This program is free software; you can redistribute it and/or modify 17--# it under the terms of the GNU General Public License as published by 18--# the Free Software Foundation; either version 2 of the License, or 19--# (at your option) any later version. 20--# 21--# This program is distributed in the hope that it will be useful, 22--# but WITHOUT ANY WARRANTY; without even the implied warranty of 23--# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 24--# GNU General Public License for more details. 25--# 26--# You should have received a copy of the GNU General Public License 27--# along with this program; if not, write to the Free Software 28--# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. 29--# 30--# For more information contact: 31--# OpenNMS Licensing <license@opennms.org> 32--# http://www.opennms.org/ 33--# http://www.sortova.com/ 34--# 35 36drop table accessLocks cascade; 37drop table accesspoints cascade; 38drop table category_node cascade; 39drop table categories cascade; 40drop table assets cascade; 41drop table usersNotified cascade; 42drop table notifications cascade; 43drop table outages cascade; 44drop table ifServices cascade; 45drop table snmpInterface cascade; 46drop table ipInterface cascade; 47drop table alarms cascade; 48drop table memos cascade; 49drop table node cascade; 50drop table service cascade; 51drop table distPoller cascade; 52drop table events cascade; 53drop table vulnerabilities cascade; 54drop table vulnPlugins cascade; 55drop table serverMap cascade; 56drop table serviceMap cascade; 57drop table pathOutage cascade; 58drop table demandPolls cascade; 59drop table pollResults cascade; 60drop table reportLocator cascade; 61drop table atinterface cascade; 62drop table stpnode cascade; 63drop table stpinterface cascade; 64drop table iprouteinterface cascade; 65drop table datalinkinterface cascade; 66drop table inventory cascade; 67drop table element cascade; 68drop table map cascade; 69drop table location_monitors cascade; 70drop table location_specific_status_changes cascade; 71drop table vlan cascade; 72drop table statisticsReportData cascade; 73drop table resourceReference cascade; 74drop table statisticsReport cascade; 75drop table acks cascade; 76drop table users cascade; 77drop table groups cascade; 78drop table group_user cascade; 79drop table category_user cascade; 80drop table category_group cascade; 81drop table filterfavorites cascade; 82 83drop sequence catNxtId; 84drop sequence nodeNxtId; 85drop sequence serviceNxtId; 86drop sequence eventsNxtId; 87drop sequence alarmsNxtId; 88drop sequence memoNxtId; 89drop sequence outageNxtId; 90drop sequence notifyNxtId; 91drop sequence userNotifNxtId; 92drop sequence demandPollNxtId; 93drop sequence pollResultNxtId; 94drop sequence vulnNxtId; 95drop sequence reportNxtId; 96drop sequence reportCatalogNxtId; 97drop sequence mapNxtId; 98drop sequence opennmsNxtId; --# should be used for all sequences, eventually 99drop sequence filternextid; 100 101drop index filternamesidx; 102 103--# Begin quartz persistence 104 105drop table qrtz_job_listeners; 106drop table qrtz_trigger_listeners; 107drop table qrtz_fired_triggers; 108drop table qrtz_paused_trigger_grps; 109drop table qrtz_scheduler_state; 110drop table qrtz_locks; 111drop table qrtz_simple_triggers; 112drop table qrtz_cron_triggers; 113drop table qrtz_blob_triggers; 114drop table qrtz_triggers; 115drop table qrtz_job_details; 116drop table qrtz_calendars; 117 118--# End quartz persistence 119 120CREATE FUNCTION plpgsql_call_handler () 121 RETURNS OPAQUE AS '$libdir/plpgsql.so' LANGUAGE 'c'; 122 123CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' 124 HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; 125 126--################################################################## 127--# The following commands set up automatic sequencing functionality 128--# for fields which require this. 129--# 130--# DO NOT forget to add an "install" comment so that the installer 131--# knows to fix and renumber the sequences if need be 132--################################################################## 133 134--# Sequence for the nodeID column in the aggregate_status_views and the 135--# aggregate_status_definitions tables (eventually all tables, perhaps) 136--# sequence, column, table 137--# install: opennmsNxtId id aggregate_status_views 138create sequence opennmsNxtId minvalue 1; 139 140--# Sequence for the nodeID column in the node table 141--# sequence, column, table 142--# install: nodeNxtId nodeID node 143create sequence nodeNxtId minvalue 1; 144 145--# Sequence for the serviceID column in the service table 146--# sequence, column, table 147--# install: serviceNxtId serviceID service 148create sequence serviceNxtId minvalue 1; 149 150--# Sequence for the eventID column in the events table 151--# sequence, column, table 152--# install: eventsNxtId eventID events 153create sequence eventsNxtId minvalue 1; 154 155--# Sequence for the alarmId column in the alarms table 156--# sequence, column, table 157--# install: alarmsNxtId alarmId alarms 158create sequence alarmsNxtId minvalue 1; 159 160--# Sequence for the id column in the memos table 161--# sequence, column, table 162--# install: memoNxtId id memos 163create sequence memoNxtId minvalue 1; 164 165--# Sequence for the outageID column in the outages table 166--# sequence, column, table 167--# install: outageNxtId outageID outages 168create sequence outageNxtId minvalue 1; 169 170--# Sequence for the notifyID column in the notification table 171--# sequence, column, table 172--# install: notifyNxtId notifyID notifications 173create sequence notifyNxtId minvalue 1; 174 175--# Sequence for the vulnerabilityID column in the vulnerabilities table 176--# sequence, column, table 177--# install: vulnNxtId vulnerabilityID vulnerabilities 178create sequence vulnNxtId minvalue 1; 179 180--# Sequence for the id column in the categories table 181--# sequence, column, table 182--# install: catNxtId categoryid categories 183create sequence catNxtId minvalue 1; 184 185--# Sequence for the id column in the usersNotified table 186--# sequence, column, table 187--# install: userNotifNxtId id usersNotified 188create sequence userNotifNxtId minvalue 1; 189 190--# Sequence for the id column in the demandPolls table 191--# sequence, column, table 192--# install: demandPollNxtId id demandPolls 193create sequence demandPollNxtId minvalue 1; 194 195--# Sequence for the id column in the pollResults table 196--# sequence, column, table 197--# install: pollResultNxtId id pollResults 198create sequence pollResultNxtId minvalue 1; 199 200--# Sequence for the mapID column in the map table 201--# sequence, column, table 202--# install: mapNxtId mapid map 203create sequence mapNxtId minvalue 1; 204 205--# Sequence for the filterid column in the filterfavorites table 206--# sequence, column, table 207--# install: filternextid filterid filterfavorites 208create sequence filternextid minvalue 1; 209 210 211--# A table to use to manage upsert access 212 213CREATE TABLE accessLocks ( 214 lockName varchar(40) not null, 215 constraint pk_accessLocks PRIMARY KEY (lockName) 216); 217 218 219--# 220 221 222--######################################################################## 223--# serverMap table - Contains a list of IP Addresses mapped to 224--# OpenNMS servers 225--# 226--# This table contains the following fields: 227--# 228--# ipAddr : IP address of the device to be monitored 229--# serverName : Text field to store the server name 230--# 231--######################################################################## 232 233create table serverMap ( 234 ipAddr text not null, 235 serverName varchar(64) not null ); 236 237create index server_name_idx on serverMap(serverName); 238 239--######################################################################## 240--# serviceMap table - Contains a list of IP Addresses mapped to 241--# OpenNMS services 242--# 243--# This table contains the following fields: 244--# 245--# ipAddr : IP address of the device to be monitored 246--# serviceName : Text field to store the service name 247--# 248--######################################################################## 249 250create table serviceMap ( 251 ipAddr text not null, 252 serviceMapName varchar(255) not null 253); 254create index servicemap_name_idx on serviceMap(serviceMapName); 255create index serviceMap_ipaddr_idx on serviceMap(ipAddr); 256 257--######################################################################## 258--# distPoller table - Contains information on Distributed Pollers 259--# installed in this OpenNMS instance. 260--# 261--# This table contains the following fields: 262--# 263--# dpName : A human-readable name for each system. Typically, 264--# the system's hostname (not fully qualified). 265--# dpIP : IP address of the distributed poller. 266--# dpComment : Free-form text field 267--# dpDiscLimit : Numeric representation of percentage of interface speed 268--# available to discovery process. See documentation for 269--# "bandwidth troll" 270--# dpLastNodePull : Time of last pull of new nodes from the DP 271--# dpLastEventPull : Time of last pull of events from the DP 272--# dpLastPackagePush : Time of last push of Package (config) to the DP 273--# dpAdminState: Reflects desired state for this distributed poller. 274--# 1 = Up, 0 = Down 275--# dpRunState : Reflects the current perceived state of the distributed 276--# poller. 1 = Up, 0 = Down 277--# 278--######################################################################## 279 280create table distPoller ( 281 dpName varchar(12) not null, 282 dpIP text not null, 283 dpComment varchar(256), 284 dpDiscLimit numeric(5,2), 285 dpLastNodePull timestamp with time zone, 286 dpLastEventPull timestamp with time zone, 287 dpLastPackagePush timestamp with time zone, 288 dpAdminState integer, 289 dpRunState integer, 290 291 constraint pk_dpName primary key (dpName) 292); 293 294--######################################################################## 295--# node Table - Contains information on nodes discovered and potentially 296--# managed by OpenNMS. nodeSys* fields map to SNMP MIB 2 297--# system table information. 298--# 299--# This table contains the following fields: 300--# 301--# nodeID : Unique identifier for node. Note that this is the 302--# enabler for overlapping IP ranges and that uniquity 303--# is dependent on combination of dpName & IP address 304--# dpName : Distributed Poller responsible for this node 305--# nodeCreateTime : Time node was added to the database 306--# nodeParentID : In the case that the node is virtual or an independent 307--# device in a chassis that should be reflected as a 308--# subcomponent or "child", this field reflects the nodeID 309--# of the chassis/physical node/"parent" device. 310--# Currently unused. 311--# nodeType : Flag indicating status of node 312--# 'A' - active 313--# 'D' - deleted 314--# nodeSysOID : SNMP MIB-2 system.sysObjectID.0 315--# nodeSysName : SNMP MIB-2 system.sysName.0 316--# nodeSysDescription : SNMP MIB-2 system.sysDescr.0 317--# nodeSysLocation : SNMP MIB-2 system.sysLocation.0 318--# nodeSysContact : SNMP MIB-2 system.sysContact.0 319--# nodeLabel : User-friendly name associated with the node. 320--# nodeLabelSource : Flag indicating source of nodeLabel 321--# 'U' = user defined 322--# 'H' = IP hostname 323--# 'S' = sysName 324--# 'A' = IP address 325--# nodeNetBIOSName : NetBIOS workstation name associated with the node. 326--# nodeDomainName : NetBIOS damain name associated with the node. 327--# operatingSystem : Operating system running on the node. 328--# lastCapsdPoll : Date and time of last Capsd scan. 329--# foreignSource : When importing nodes this contains the source of the 330--# nodes, null otherwise 331--# foriegnId : When importing nodes this contains the id of the node 332--# as known to the foriegn source, null otherwise 333--######################################################################## 334 335create table node ( 336 nodeID integer not null, 337 dpName varchar(12), 338 nodeCreateTime timestamp with time zone not null, 339 nodeParentID integer, 340 nodeType char(1), 341 nodeSysOID varchar(256), 342 nodeSysName varchar(256), 343 nodeSysDescription varchar(256), 344 nodeSysLocation varchar(256), 345 nodeSysContact varchar(256), 346 nodeLabel varchar(256), 347 nodeLabelSource char(1), 348 nodeNetBIOSName varchar(16), 349 nodeDomainName varchar(16), 350 operatingSystem varchar(64), 351 lastCapsdPoll timestamp with time zone, 352 foreignSource varchar(64), 353 foreignId varchar(64), 354 355 constraint pk_nodeID primary key (nodeID), 356 constraint fk_dpName foreign key (dpName) references distPoller 357); 358 359create index node_id_type_idx on node(nodeID, nodeType); 360create index node_label_idx on node(nodeLabel); 361create index node_dpname_idx on node(dpName); 362create unique index node_foreign_unique_idx on node(foreignSource, foreignId); 363 364--######################################################################### 365--# snmpInterface Table - Augments the ipInterface table with information 366--# available from IP interfaces which also support 367--# SNMP. 368--# 369--# This table provides the following information: 370--# 371--# nodeID : Unique identifier for node to which this if belongs 372--# snmpIpAdEntNetMask : SNMP MIB-2 ipAddrTable.ipAddrEntry.ipAdEntNetMask 373--# Value is interface's subnet mask 374--# snmpPhysAddr : SNMP MIB-2 ifTable.ifEntry.ifPhysAddress 375--# Value is interface's MAC Address 376--# snmpIfIndex : SNMP MIB-2 ifTable.ifEntry.ifIndex 377--# Value is interface's arbitrarily assigned index, 378--# or -100 if we can query the agent, but we can't find 379--# this IP address in the ifTable. 380--# snmpIfDescr : SNMP MIB-2 ifTable.ifEntry.ifDescr 381--# Value is interface's manufacturer/product name/version 382--# snmpIfType : SNMP MIB-2 ifTable.ifEntry.ifType 383--# Value is interface's physical/link protocol 384--# snmpIfName : SNMP MIB-2 ifTable.ifEntry.ifName 385--# Value is interface's device name 386--# snmpIfSpeed : SNMP MIB-2 ifTable.ifEntry.ifSpeed 387--# Value is estimate of interface's data rate 388--# snmpIfAdminStatus : SNMP MIB-2 ifTable.ifEntry.ifAdminStatus 389--# Value is interface's desired status 390--# 1 = Up, 2 = Down, 3 = Testing 391--# snmpIfOperStatus : SNMP MIB-2 ifTable.ifEntry.ifOperStatus 392--# Value is interface's current operational status 393--# 1 = Up, 2 = Down, 3 = Testing 394--# snmpIfAlias : SNMP MIB-2 ifXTable.ifXEntry.ifAlias 395--# Value is interface's device alias 396--# snmpCollect : 'C' means collect 'N' means don't collect 397--# : 'UC' means collect 'UN' means don't collect (user override) 398--# This has been moved from the isSnmpPrimary field in the 399--# ipinterface table 400--# snmpLastCapsdPoll : Date and time of last poll by capsd or provisiond 401--# snmpPoll : 'P' means polled 'N' means not polled (interface admin and oper status) 402--# snmpLastSnmpPoll : Date and time of last snmp poll 403--# 404--# NOTE: Although not marked as "not null" the snmpIfIndex field 405--# should never be null. This table is considered to be uniquely 406--# keyed by nodeId and snmpIfIndex. Eventually ipAddr and 407--# snmpIpAdEntNetMask will be removed and netmask added to 408--# the ipInterface table. 409--######################################################################## 410 411create table snmpInterface ( 412 id INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL, 413 nodeID integer not null, 414 snmpIpAdEntNetMask varchar(45), 415 snmpPhysAddr varchar(32), 416 snmpIfIndex integer not null, 417 snmpIfDescr varchar(256), 418 snmpIfType integer, 419 snmpIfName varchar(96), 420 snmpIfSpeed bigint, 421 snmpIfAdminStatus integer, 422 snmpIfOperStatus integer, 423 snmpIfAlias varchar(256), 424 snmpLastCapsdPoll timestamp with time zone, 425 snmpCollect varchar(2) default 'N', 426 snmpPoll varchar(1) default 'N', 427 snmpLastSnmpPoll timestamp with time zone, 428 429 CONSTRAINT snmpinterface_pkey primary key (id), 430 constraint fk_nodeID2 foreign key (nodeID) references node ON DELETE CASCADE 431); 432 433create unique index snmpinterface_nodeid_ifindex_unique_idx on snmpinterface(nodeID, snmpIfIndex); 434create index snmpinterface_nodeid_idx on snmpinterface(nodeID); 435 436--######################################################################## 437--# ipInterface Table - Contains information on interfaces which support 438--# TCP/IP as well as current status information. 439--# ipAddr is integer, to support easier filtering. 440--# 441--# This table contains the following information: 442--# 443--# nodeID : Unique identifier of the node that "owns" this interface 444--# ipAddr : IP Address associated with this interface 445--# ifIndex : SNMP index of interface, used to uniquely identify 446--# unnumbered interfaces, or null if there is no mapping to 447--# snmpInterface table. Can be -100 if old code added an 448--# snmpInterface table entry but no SNMP data could be gathered. 449--# 450--# NOTE: The combination of nodeID, ipAddr, and ifIndex must be unique, 451--# and this must be enforced programmatically. 452--# 453--# ipHostname : IP Hostname associated with this interface 454--# isManaged : Character used as a boolean flag 455--# 'M' - Managed 456--# 'A' - Alias 457--# 'D' - Deleted 458--# 'U' - Unmanaged 459--# 'F' - Forced Unmanaged (via the user interface) 460--# 'N' - Not polled as part of any package 461--# 'X' - Remotely Monitored only 462--# ipStatus : If interface supports SNMP this field will 463--# hold a numeric representation of interface's 464--# operational status (same as 'snmpIfOperStatus' 465--# field in the snmpInterface table). 466--# 1 = Up, 2 = Down, 3 = Testing 467--# ipLastCapsdPoll : Date and time of last poll by capsd or provisiond 468--# isSnmpPrimary : Character used as a boolean flag 469--# 'P' - Primary SNMP 470--# 'S' - Secondary SNMP 471--# 'N' - Not eligible (does not support SNMP or 472--# or has no ifIndex) 473--# NOTE: 'C' is no longer a valid value for isSnmpPrimary 474--# this has moved to the snmpinterface table 475--# 476--######################################################################## 477 478create table ipInterface ( 479 id INTEGER DEFAULT nextval('opennmsNxtId') NOT NULL, 480 nodeID integer not null, 481 ipAddr text not null, 482 ifIndex integer, 483 ipHostname varchar(256), 484 isManaged char(1), 485 ipStatus integer, 486 ipLastCapsdPoll timestamp with time zone, 487 isSnmpPrimary char(1), 488 snmpInterfaceId integer, 489 490 CONSTRAINT ipinterface_pkey PRIMARY KEY (id), 491 CONSTRAINT snmpinterface_fkey2 FOREIGN KEY (snmpInterfaceId) REFERENCES snmpInterface (id) ON DELETE SET NULL, 492 constraint fk_nodeID1 foreign key (nodeID) references node ON DELETE CASCADE 493); 494 495create unique index ipinterface_nodeid_ipaddr_notzero_idx on ipInterface (nodeID, ipAddr) WHERE ipAddr != '0.0.0.0'; 496create index ipinterface_nodeid_ipaddr_ismanaged_idx on ipInterface (nodeID, ipAddr, isManaged); 497create index ipinterface_ipaddr_ismanaged_idx on ipInterface (ipAddr, isManaged); 498create index ipinterface_ipaddr_idx on ipInterface (ipAddr); 499create index ipinterface_nodeid_ismanaged_idx on ipInterface (ipAddr); 500create index ipinterface_nodeid_idx on ipInterface (nodeID); 501create index ipinterface_snmpInterfaceId_idx on ipInterface (snmpInterfaceId); 502 503--######################################################################## 504--# service Table - Contains a name<->number mapping for services 505--# (e.g., poller packages) 506--# 507--# This table provides the following information: 508--# 509--# serviceID : Unique integer mapping to service/poller package 510--# serviceName : Name associated with service/poller package 511--######################################################################## 512 513create table service ( 514 serviceID integer default nextval('serviceNxtId') not null, 515 serviceName varchar(255) not null, 516 517 constraint pk_serviceID primary key (serviceID) 518); 519 520create unique index service_servicename_key on service (serviceid); 521 522--######################################################################## 523--# ifServices Table - Contains a mapping of interfaces to services available 524--# on those interfaces (e.g., FTP, SMTP, DNS, etc.) and 525--# recent polling status information. 526--# 527--# This table provides the following information: 528--# 529--# nodeID : Unique integer identifier for node 530--# ipAddr : IP Address of node's interface 531--# ifIndex : SNMP ifIndex, if available, null otherwise 532--# serviceID : Unique integer identifier of service/poller package 533--# lastGood : Date and time of last successful poll by this poller package 534--# lastFail : Date and time of last failed poll by this poller package 535--# qualifier : Service qualifier. May be used to distinguish two 536--# services which have the same serviceID. For example, in the 537--# case of the HTTP service a qualifier might be the specific 538--# port on which the HTTP server was found. 539--# status : Flag indicating the status of the service. 540--# 'A' - Active 541--# 'D' - Deleted 542--# 'U' - Unmanaged (per capsd configuration change and CAPSD) 543--# 'F' - Forced unmanaged (via user interface) 544--# 'N' - Not polled as part of any of the packages that the 545--# interface belongs to 546--# 'X' - service is remotely monitored only 547--# source : Flag indicating how the service was detected. 548--# 'P' - Plugin 549--# 'F' - Forced (via CapsdPluginBehavior.conf) 550--# notify : Flag indicating if this service should be notified on or not 551--# 'Y' - to notify 552--# 'N' = not to notify 553--######################################################################## 554 555create table ifServices ( 556 id integer default nextval('opennmsNxtId') NOT NULL, 557 nodeID integer not null, 558 ipAddr text not null, 559 ifIndex integer, 560 serviceID integer not null, 561 lastGood timestamp with time zone, 562 lastFail timestamp with time zone, 563 qualifier char(16), 564 status char(1), 565 source char(1), 566 notify char(1), 567 ipInterfaceId integer not null, 568 569 CONSTRAINT ifservices_pkey PRIMARY KEY (id), 570 CONSTRAINT ipinterface_fkey FOREIGN KEY (ipInterfaceId) REFERENCES ipInterface (id) ON DELETE CASCADE, 571 constraint fk_nodeID3 foreign key (nodeID) references node ON DELETE CASCADE, 572 constraint fk_serviceID1 foreign key (serviceID) references service ON DELETE CASCADE 573); 574 575create unique index ifservices_nodeid_ipaddr_svc_unique on ifservices(nodeID, ipAddr, serviceId); 576create index ifservices_nodeid_ipaddr_status on ifservices(nodeID, ipAddr, status); 577create index ifservices_nodeid_status on ifservices(nodeid, status); 578create index ifservices_nodeid_idx on ifservices(nodeID); 579create index ifservices_serviceid_idx on ifservices(serviceID); 580create index ifservices_nodeid_serviceid_idx on ifservices(nodeID, serviceID); 581create index ifservicves_ipInterfaceId_idx on ifservices(ipInterfaceId); 582 583--################################################################## 584--# events Table -- This table provides information on the events 585--# that are passed into the event subsystem. It 586--# contains information defining the event as 587--# unique, while additional information is stored 588--# in the eventsDetail table. 589--# 590--# This table provides the following information: 591--# 592--# eventID : Unique identifier for the event 593--# eventUei : Universal Event Identifer (UEI) for this event 594--# eventSnmp : Contains the eid, eidtext (optionally), specific, 595--# and generic identifier for the SNMP Trap. This 596--# maps directly to the <snmp> element in the 597--# Event Data Stream DTD. 598--# eventTime : The <time> element from the Event Data Stream DTD, 599--# which is the time the event was received by the 600--# source process. 601--# eventCreateTime : Creation time of event in database 602--# eventHost : The <host> element from the Event Data Stream DTD 603--# eventSource : The entity/process which generated the event. 604--# eventSnmphost : The <snmphost> element from the Event Data Stream DTD 605--# eventDpName : The dpName of the Dist Poller which received the 606--# event 607--# eventParms : The <parms> element from the Event Data Stream DTD 608--# nodeID : Unique integer identifier for node 609--# ifindex : The <ifindex> element from the Event Data Stream DTD 610--# ipAddr : IP Address of node's interface 611--# serviceID : Unique integer identifier of service/poller package 612--# eventDescr : Free-form textual description of the event 613--# eventLogmsg : The log message for the event 614--# eventSeverity : Severity of event 615--# 1 = Indeterminate 616--# 2 = Cleared (implementation is now in alarms) 617--# 3 = Normal 618--# 4 = Warning 619--# 5 = Minor 620--# 6 = Major 621--# 7 = Critical 622--# eventPathOutage : Event Path outage information 623--# eventCorrelation : The event correlation configured for this event 624--# (stored as an XML string) 625--# eventSuppressedCount : The number of times the event was suppressed 626--# (if event correlation was set for suppression) 627--# eventOperInstruct : Operator instruction for event. 628--# eventAutoAction : Automated Action for event. Should 629--# consist of fully-qualfied pathname to 630--# executable command, with possible variables 631--# used to reference event-specific data 632--# eventOperAction : Operator Action for event. Should 633--# consist of fully-qualfied pathname to 634--# executable command, with possible variables 635--# used to reference event-specific data 636--# eventOperActionMenuText : Menu text displayed to Operator, which if 637--# selected, will invoke action described in 638--# eventOperAction 639--# eventLoggroup : Logical group with which to associate event. 640--# This field provides a means of logically 641--# grouping related events. 642--# eventNotification : Notification string. Should consist of 643--# a fully-qualfied pathname to an executable 644--# which invokes the notification software, and 645--# will likely contain event-specific variables 646--# eventTticket : Trouble ticket integration string. Should 647--# consist of fully-qualfied pathname to 648--# executable command, with possible variables 649--# used to reference event-specific data 650--# eventTticketState : Trouble ticket on/off boolean 651--# 1=on, 0=off 652--# eventForward : Contains a list of triplets: 653--# Destination,State,Mechanism;Destination,State,Mechanism; 654--# which reflect the following: 655--# - State is a boolean flag as to whether the 656--# entry is active or not. 1=on, 0=off. 657--# - Destination is hostname or IP of system to 658--# forward the event to 659--# - Method is the means by which it will be 660--# forwarded. A keyword, e.g., SNMP 661--# eventMouseOverText : Text to be displayed on MouseOver event, if 662--# the event is displayed in the browser and 663--# the operator needs additional info. 664--# eventLog : Flag indicating if the event is to be logged, set 665--# from the 'dest' attribute on the incoming event 666--# Y = log, N = do not log 667--# eventDisplay : Flag indicating if the event is to be displayed, set 668--# from the 'dest' attribute on the incoming event 669--# Y = display, N = do not display 670--# eventAckUser : The user who acknowledged this event. If 671--# null, then this event has not been acknowledged. 672--# eventAckTime : The time this event was acknowledged. 673--# alarmID : If this event is configured for alarmReduction, the alarmId 674--# of the reduced event will set in this column 675--# 676--################################################################## 677 678create table events ( 679 eventID integer not null, 680 eventUei varchar(256) not null, 681 nodeID integer, 682 eventTime timestamp with time zone not null, 683 eventHost varchar(256), 684 eventSource varchar(128) not null, 685 ipAddr text, 686 eventDpName varchar(12) not null, 687 eventSnmphost varchar(256), 688 serviceID integer, 689 eventSnmp varchar(256), 690 eventParms text, 691 eventCreateTime timestamp with time zone not null, 692 eventDescr text, 693 eventLoggroup varchar(32), 694 eventLogmsg text, 695 eventSeverity integer not null, 696 eventPathOutage varchar(1024), 697 eventCorrelation varchar(1024), 698 eventSuppressedCount integer, 699 eventOperInstruct varchar(1024), 700 eventAutoAction varchar(256), 701 eventOperAction varchar(256), 702 eventOperActionMenuText varchar(64), 703 eventNotification varchar(128), 704 eventTticket varchar(128), 705 eventTticketState integer, 706 eventForward varchar(256), 707 eventMouseOverText varchar(64), 708 eventLog char(1) not null, 709 eventDisplay char(1) not null, 710 ifIndex integer, 711 eventAckUser varchar(256), 712 eventAckTime timestamp with time zone, 713 alarmID integer, 714 715 constraint pk_eventID primary key (eventID) 716); 717 718create index events_uei_idx on events(eventUei); 719create index events_nodeid_idx on events(nodeID); 720create index events_ipaddr_idx on events(ipaddr); 721create index events_serviceid_idx on events(serviceID); 722create index events_time_idx on events(eventTime); 723create index events_severity_idx on events(eventSeverity); 724create index events_log_idx on events(eventLog); 725create index events_display_idx on events(eventDisplay); 726create index events_ackuser_idx on events(eventAckUser); 727create index events_acktime_idx on events(eventAckTime); 728create index events_alarmid_idx on events(alarmID); 729create index events_nodeid_display_ackuser on events(nodeid, eventdisplay, eventackuser); 730 731--######################################################################## 732--# 733--# outages table -- This table maintains a record of outage periods for 734--# given services on specific interfaces. 735--# 736--# This table provides the following information: 737--# 738--# outageID : Unique integer identifier for the outage 739--# svcLostEventID : ID of the event that caused the outage. Will be 740--# a non-null value when a new outage is inserted 741--# but might be null in case of an opennms upgrade 742--# svcRegainedEventID: ID of the event that cleared the outage 743--# nodeID : Unique integer identifier for node 744--# ipAddr : IP Address of node's interface 745--# serviceID : Unique integer identifier of service/poller package 746--# ifLostService : Time of lost service event 747--# ifRegainedService : Time of regained service event 748--# suppressTime : Time to suppress the outage 749--# suppressedBy : The suppressor 750--# 751--######################################################################## 752 753create table outages ( 754 outageID integer not null, 755 svcLostEventID integer, 756 svcRegainedEventID integer, 757 nodeID integer not null, 758 ipAddr text not null, 759 serviceID integer not null, 760 ifLostService timestamp with time zone not null, 761 ifRegainedService timestamp with time zone, 762 suppressTime timestamp with time zone, 763 suppressedBy varchar(256), 764 ifServiceId INTEGER not null, 765 766 constraint pk_outageID primary key (outageID), 767 constraint fk_eventID1 foreign key (svcLostEventID) references events (eventID) ON DELETE CASCADE, 768 constraint fk_eventID2 foreign key (svcRegainedEventID) references events (eventID) ON DELETE CASCADE, 769 constraint fk_nodeID4 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE, 770 constraint fk_serviceID2 foreign key (serviceID) references service (serviceID) ON DELETE CASCADE, 771 CONSTRAINT ifServices_fkey1 FOREIGN KEY (nodeId, ipAddr, serviceId) REFERENCES ifServices (nodeId, ipAddr, serviceId) ON DELETE CASCADE ON UPDATE CASCADE, 772 CONSTRAINT ifServices_fkey2 FOREIGN KEY (ifServiceId) REFERENCES ifServices (id) ON DELETE CASCADE 773); 774 775create index outages_nodeid_ipaddr_svc_idx on outages(nodeID, ipAddr, serviceId); 776create index outages_svclostid_idx on outages(svcLostEventID); 777create index outages_svcregainedid_idx on outages(svcRegainedEventID); 778create index outages_nodeid_idx on outages(nodeID); 779create index outages_serviceid_idx on outages(serviceID); 780create index outages_ipaddr_idx on outages(ipaddr); 781create index outages_regainedservice_idx on outages(ifRegainedService); 782create index outages_ifServivceId_idx on outages(ifServiceId); 783 784--######################################################################## 785--# 786--# vulnerabilities table -- This table maintains a record of vulnerabilites 787--# that have been detected on target IP addresses. 788--# 789--# This table provides the following information: 790--# 791--# vulnerabilityID : Unique integer identifier for the outage 792--# nodeID : Unique integer identifier for node 793--# ipAddr : IP Address of node's interface 794--# serviceID : Unique integer identifier of service/poller package 795--# 796--# creationTime : Initial creation time of the vulnerability 797--# lastAttemptTime : Last time that an attempt was made to scan for 798--# this vulnerability 799--# lastScanTime : Most recent successful scan time 800--# resolvedTime : Time after which the vulnerability was no longer 801--# detected 802--# 803--# severity : Severity of the vulnerability (identical to event 804--# severities 805--# pluginID : ID number of the plugin that produced the vulnerability 806--# pluginSubID : Specific vulnerability type generated by the plugin 807--# logmsg : Terse description of vulnerability (usually 808--# the plugin name plus short description) 809--# descr : Verbose description of vulnerability 810--# port : Port that the vulnerability affects 811--# protocol : Network protocol of the attack (TCP, UDP, ICMP) 812--# 813--######################################################################## 814 815create table vulnerabilities ( 816 vulnerabilityID integer not null, 817 nodeID integer, 818 ipAddr text, 819 serviceID integer, 820 creationTime timestamp with time zone not null, 821 lastAttemptTime timestamp with time zone not null, 822 lastScanTime timestamp with time zone not null, 823 resolvedTime timestamp with time zone, 824 severity integer not null, 825 pluginID integer not null, 826 pluginSubID integer not null, 827 logmsg text, 828 descr text, 829 port integer, 830 protocol varchar(32), 831 cveEntry varchar(255), 832 833 constraint pk_vulnerabilityID primary key (vulnerabilityID) 834); 835 836create index vulnerabilities_nodeid_idx on vulnerabilities(nodeID); 837create index vulnerabilities_ipaddr_idx on vulnerabilities(ipAddr); 838create index vulnerabilities_severity_idx on vulnerabilities(severity); 839create index vulnerabilities_port_idx on vulnerabilities(port); 840create index vulnerabilities_protocol_idx on vulnerabilities(protocol); 841 842--######################################################################## 843--# 844--# vulnPlugins table -- This table contains a list of information about 845--# Nessus plugins that are in use by the nessusd 846--# daemons that are being used by vulnscand. 847--# 848--# This table provides the following information: 849--# 850--# pluginID : Plugin ID number (from Nessus) 851--# pluginSubID : Specific vulnerability type within the plugin 852--# name : Short name of the plugin 853--# category : Category of the plugin's behavior (scanner, 854--# attack, etc) 855--# copyright : Copyright notice for the plugin 856--# descr : Verbose description of vulnerability 857--# summary : Short description of plugin behavior 858--# family : User-comprehensible type of attack (CGI abuses, 859--# Backdoors, etc) 860--# version : Version of the plugin code 861--# cveEntry : CVE entry associated with the vulnerability 862--# that this plugin tests 863--# md5 : 128-bit hex MD5 checksum of the plugin that 864--# can be used to detect changes in the plugin code 865--# 866--######################################################################## 867 868create table vulnPlugins ( 869 pluginID integer not null, 870 pluginSubID integer not null, 871 name varchar(128), 872 category varchar(32), 873 copyright varchar(128), 874 descr text, 875 summary varchar(256), 876 family varchar(32), 877 version varchar(32), 878 cveEntry varchar(255), 879 md5 varchar(32) 880); 881 882--# This constraint not understood installer 883--# CONSTRAINT pk_vulnplugins PRIMARY KEY (pluginID,pluginSubID)); 884--# 885 886create unique index vulnplugins_plugin_idx on vulnPlugins(pluginID, pluginSubID); 887 888--######################################################################## 889--# notification table - Contains information on acknowleged and outstanding 890--# pages listed by user/groups 891--# 892--# This table contains the following fields: 893--# 894--# textMsg : The message being sent in the page. 895--# numericMsg : The message being sent to a numeric pager 896--# notifyID : The primary key of this row, populated with the value from 897--# the notifyNxtId sequence. 898--# pageTime : A timestamp of when the page was originally sent. 899--# respondTime : A timestamp of when the page was acknowleged. A null in this 900--# field means that the page has not been answered yet. 901--# answeredBy : The user id of the user that answered the page, set the same 902--# for all rows with the same groupId field. 903--# nodeId : The id of the node that has the problem 904--# interfaceId : The id of the interface on the node that has the problem 905--# serviceID : The id of the service on the interface that has the problem 906--# eventID : The primary key of the event that spawned the notification 907--# eventUEI : The uei of the event that spawned the notification, placed here 908--# for speed of lookup as notifications are processed. 909--# 910--######################################################################## 911 912create table notifications ( 913 textMsg text not null, 914 subject text, 915 numericMsg varchar(256), 916 notifyID integer not null, 917 pageTime timestamp with time zone, 918 respondTime timestamp with time zone, 919 answeredBy varchar(256), 920 nodeID integer, 921 interfaceID varchar(16), 922 serviceID integer, 923 queueID varchar(256), 924 eventID integer, 925 eventUEI varchar(256) not null, 926 notifConfigName varchar(63), 927 928 constraint pk_notifyID primary key (notifyID), 929 constraint fk_nodeID7 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE, 930 constraint fk_eventID3 foreign key (eventID) references events (eventID) ON DELETE CASCADE 931); 932 933create index notifications_nodeid_idx on notifications(nodeid); 934create index notifications_ipaddr_idx on notifications(interfaceID); 935create index notifications_serviceid_idx on notifications(serviceID); 936create index notifications_eventid_idx on notifications(eventID); 937create index notifications_respondtime_idx on notifications(respondTime); 938create index notifications_answeredby_idx on notifications(answeredBy); 939create index notifications_eventuei_idx on notifications (eventuei); 940 941--######################################################################## 942--# 943--# This table contains the following fields: 944--# id : ID column for the table 945--# userID : The user id of the person being paged, from the users.xml 946--# file. 947--# notifyID : The index of the row from the notification table. 948--# notifyTime : The timestamp of when the notification was sent 949--# media : A string describing the type of contact being made, ie text 950--# page, numeric page, email, etc... 951--# contactInfo : A field for storing the information used to contact the user, 952--# e.g. an email address, the phone number and pin of the pager... 953--# autonotify : A character to determine how auto acknowledge is handled for 954--# this entry 955--# 956--######################################################################## 957 958create table usersNotified ( 959 id integer not null, 960 userID varchar(256) not null, 961 notifyID integer, 962 notifyTime timestamp with time zone, 963 media varchar(32), 964 contactinfo varchar(64), 965 autonotify char(1), 966 967 constraint pk_userNotificationID primary key (id), 968 constraint fk_notifID2 foreign key (notifyID) references notifications (notifyID) ON DELETE CASCADE 969); 970 971create index userid_notifyid_idx on usersNotified(userID, notifyID); 972 973--################################# 974--# This table contains memos used by alarms to represent StickyMemos and Journal / ReductionKeyMemos 975create table memos ( 976 id integer NOT NULL, 977 created timestamp with time zone, 978 updated timestamp with time zone, 979 author character varying(256), 980 body text, 981 reductionkey character varying(256), 982 type character varying(64), 983 CONSTRAINT memos_pkey PRIMARY KEY (id) 984); 985--######################################################################## 986--# 987--# This table contains the following fields: 988--# alarmID : The id created from the alarmsNxtId sequence. 989--# eventUei : A reference to the eventUei that created this alarm. 990--# nodeID : A reference to the node represented by this alarm. 991--# ipAddr : IP Address of node's interface 992--# serviceID : A reference to the service represented by the alarm. 993--# reductionKey: Used with nodeID and serviceID to match an event and 994--# increment the counter column. Set by configuring the 995--# optional alarm-data elment in the eventConf.xml file. 996--# alarmType : Customizable column designed for use in automations and 997--# can be set in the eventConf.xml file by configuring the 998--# optional alarm-data element. 999--# counter : Incremented by the AlarmWriter instead of inserting 1000--# a new row when matched node, service, and reductionKey 1001--# severity : Severity of the Alarm... Initially set by the event 1002--# can be changed with SQL update. 1003--# lastEventID : A reference to the event table with the ID of the last 1004--# matching event (typically node, service, reductionkey) 1005--# firstEventTime: timestamp of the first event matching this alarm 1006--# lastEventTime: timestamp of the last event matching this alarm 1007--# description : description from the event 1008--# logMsg : the logmsg from the event 1009--# ifIndex : the ifindex from the event 1010--# operInstruct: the operator instructions from the event 1011--# tticketID : helpdesk integration field 1012--# tticketState: helpdesk integration field 1013--# mouseOverTest: flyOverText for the webUI 1014--# suppressedUntil: used to suppress display an alarm until 1015--# : timestamp time is reached 1016--# suppressedUser : user that suppressed alarm 1017--# suppressedTime : time the alarm was suppressed 1018--# alarmAckUser : user that acknowledged the alarm 1019--# alarmAckTime : time user Ack'd the alarm 1020--# stickymemo : reference to the memo table 1021--######################################################################## 1022 1023create table alarms ( 1024 alarmID INTEGER, CONSTRAINT pk_alarmID PRIMARY KEY (alarmID), 1025 eventUei VARCHAR(256) NOT NULL, 1026 dpName VARCHAR(12) NOT NULL, 1027 nodeID INTEGER, CONSTRAINT fk_alarms_nodeid FOREIGN KEY (nodeID) REFERENCES node (nodeID) ON DELETE CASCADE, 1028 ipaddr VARCHAR(39), 1029 serviceID INTEGER, 1030 reductionKey VARCHAR(256), 1031 alarmType INTEGER, 1032 counter INTEGER NOT NULL, 1033 severity INTEGER NOT NULL, 1034 lastEventID INTEGER, CONSTRAINT fk_eventIDak2 FOREIGN KEY (lastEventID) REFERENCES events (eventID) ON DELETE CASCADE, 1035 firstEventTime timestamp with time zone, 1036 lastEventTime timestamp with time zone, 1037 firstAutomationTime timestamp with time zone, 1038 lastAutomationTime timestamp with time zone, 1039 description text, 1040 logMsg text, 1041 operInstruct VARCHAR(1024), 1042 tticketID VARCHAR(128), 1043 tticketState INTEGER, 1044 mouseOverText VARCHAR(64), 1045 suppressedUntil timestamp with time zone, 1046 suppressedUser VARCHAR(256), 1047 suppressedTime timestamp with time zone, 1048 alarmAckUser VARCHAR(256), 1049 alarmAckTime timestamp with time zone, 1050 managedObjectInstance VARCHAR(512), 1051 managedObjectType VARCHAR(512), 1052 applicationDN VARCHAR(512), 1053 ossPrimaryKey VARCHAR(512), 1054 x733AlarmType VARCHAR(31), 1055 x733ProbableCause INTEGER default 0 not null, 1056 qosAlarmState VARCHAR(31), 1057 ifIndex INTEGER, 1058 clearKey VARCHAR(256), 1059 eventParms text, 1060 stickymemo INTEGER, CONSTRAINT fk_stickyMemo FOREIGN KEY (stickymemo) REFERENCES memos (id) ON DELETE CASCADE 1061); 1062 1063CREATE INDEX alarm_uei_idx ON alarms(eventUei); 1064CREATE INDEX alarm_nodeid_idx ON alarms(nodeID); 1065CREATE UNIQUE INDEX alarm_reductionkey_idx ON alarms(reductionKey); 1066CREATE INDEX alarm_clearkey_idx ON alarms(clearKey); 1067CREATE INDEX alarm_reduction2_idx ON alarms(alarmID, eventUei, dpName, nodeID, serviceID, reductionKey); 1068CREATE INDEX alarm_app_dn ON alarms(applicationDN); 1069CREATE INDEX alarm_oss_primary_key ON alarms(ossPrimaryKey); 1070CREATE INDEX alarm_eventid_idx ON alarms(lastEventID); 1071CREATE INDEX alarm_lasteventtime_idx on alarms(lasteventtime); 1072CREATE INDEX alarm_firstautomationtime_idx on alarms(firstautomationtime); 1073CREATE INDEX alarm_lastautomationtime_idx on alarms(lastautomationtime); 1074 1075--######################################################################## 1076--# 1077--# Use this table to add additional custom data about an alarm... somewhat 1078--# usefull with automations and will be viewable/editable in the alarm 1079--# details WebUI page. 1080--# 1081--# This table contains the following fields: 1082--# alarmID : The id created from the alarmsNxtId sequence. 1083--# attribute : The custom attribute name 1084--# attributeValue : The custom attribute value 1085--######################################################################## 1086 1087CREATE TABLE alarm_attributes ( 1088 alarmID INTEGER, CONSTRAINT fk_alarmID1 FOREIGN KEY (alarmID) REFERENCES alarms (alarmID) ON DELETE CASCADE, 1089 attributeName VARCHAR(63), 1090 attributeValue VARCHAR(255) 1091); 1092 1093CREATE INDEX alarm_attributes_idx ON alarm_attributes(alarmID); 1094CREATE UNIQUE INDEX alarm_attributes_aan_idx ON alarm_attributes(alarmID, attributeName); 1095 1096--# This constraint not understood by installer 1097--# CONSTRAINT pk_usersNotified PRIMARY KEY (userID,notifyID) ); 1098--# 1099--######################################################################## 1100--# asset table - Contains inventory and other user-entered information 1101--# for nodes 1102--# 1103--# This table contains the following fields: 1104--# 1105--# nodeID : The node id for the node this asset information belongs. 1106--# category : A broad idea of what this asset does (examples are 1107--# desktop, printer, server, infrastructure, etc.). 1108--# manufacturer : Name of the manufacturer of this asset. 1109--# vendor : Vendor from whom this asset was purchased. 1110--# modelNumber : The model number of this asset. 1111--# serialNumber : The serial number of this asset. 1112--# description : A free-form description. 1113--# circuitId : The electrical/network circuit this asset connects to. 1114--# assetNumber : A business-specified asset number. 1115--# operatingSystem : The operating system, if any. 1116--# rack : For servers, the rack it is installed in. 1117--# slot : For servers, the slot in the rack it is installed in. 1118--# port : For servers, the port in the slot it is installed in. 1119--# region : A broad geographical or organizational area. 1120--# division : A broad geographical or organizational area. 1121--# department : The department this asset belongs to. 1122--# address1 : Add…
Large files files are truncated, but you can click here to view the full file