PageRenderTime 71ms CodeModel.GetById 17ms app.highlight 42ms RepoModel.GetById 1ms app.codeStats 1ms

/opennms-base-assembly/src/main/filtered/etc/create.sql

https://github.com/ajakubo1/opennms
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