create.sql

来自「opennms得相关源码 请大家看看」· SQL 代码 · 共 876 行 · 第 1/3 页

	constraint fk_nodeID2 foreign key (nodeID) references node ON DELETE CASCADE
);

create index snmpinterface_nodeid_ifindex_idx on snmpinterface(nodeID, snmpIfIndex);
create index snmpinterface_nodeid_idx on snmpinterface(nodeID);
create index snmpinterface_ipaddr_idx on snmpinterface(ipaddr);

--########################################################################
--# service Table - Contains a name<->number mapping for services
--#                 (e.g., poller packages)
--#
--# This table provides the following information:
--#
--#  serviceID   : Unique integer mapping to service/poller package
--#  serviceName : Name associated with service/poller package
--########################################################################

create table service (
	serviceID		integer not null,
	serviceName		varchar(32) not null,

	constraint pk_serviceID primary key (serviceID)
);

--########################################################################
--# ifServices Table - Contains a mapping of interfaces to services available
--#                    on those interfaces (e.g., FTP, SMTP, DNS, etc.) and
--#                    recent polling status information.
--#
--# This table provides the following information:
--#
--#  nodeID    : Unique integer identifier for node
--#  ipAddr    : IP Address of node's interface
--#  ifIndex   : SNMP ifIndex, if available
--#  serviceID : Unique integer identifier of service/poller package
--#  lastGood  : Date and time of last successful poll by this poller package
--#  lastFail  : Date and time of last failed poll by this poller package
--#  qualifier : Service qualifier.  May be used to distinguish two
--#		 services which have the same serviceID.  For example, in the
--#              case of the HTTP service a qualifier might be the specific
--#              port on which the HTTP server was found.
--#  status    : Flag indicating the status of the service.
--#                'A' - Active
--#                'D' - Deleted
--#                'U' - Unmanaged (per capsd configuration change and CAPSD)
--#                'F' - Forced unmanaged (via user interface)
--#                'N' - Not polled as part of any of the packages that the
--#                      interface belongs to
--#  source    : Flag indicating how the service was detected.
--#                'P' - Plugin
--#                'F' - Forced (via CapsdPluginBehavior.conf)
--#  notify    : Flag indicating if this service should be notified on or not
--#                'Y' - to notify
--#                'N' = not to notify
--########################################################################

create table ifServices (
	nodeID			integer,
	ipAddr			varchar(16) not null,
	ifIndex			integer,
	serviceID		integer,
	lastGood		timestamp without time zone,
	lastFail		timestamp without time zone,
	qualifier		char(16),
	status         		char(1),
	source			char(1),
	notify                  char(1),

	constraint fk_nodeID3 foreign key (nodeID) references node ON DELETE CASCADE,
	constraint fk_serviceID1 foreign key (serviceID) references service ON DELETE CASCADE
);

create index ifservices_nodeid_ipaddr_status on ifservices(nodeID, ipAddr, status);
create index ifservices_nodeid_status on ifservices(nodeid, status);
create index ifservices_nodeid_idx on ifservices(nodeID);
create index ifservices_serviceid_idx on ifservices(serviceID);
create index ifservices_nodeid_serviceid_idx on ifservices(nodeID, serviceID);

--##################################################################
--# events Table -- This table provides information on the events
--#                 that are passed into the event subsystem.  It
--#                 contains information defining the event as
--#                 unique, while additional information is stored
--#                 in the eventsDetail table.
--#
--# This table provides the following information:
--#
--#  eventID   		: Unique identifier for the event
--#  eventUei		: Universal Event Identifer (UEI) for this event
--#  eventSnmp		: Contains the eid, eidtext (optionally), specific,
--#			  and generic identifier for the SNMP Trap.  This
--#			  maps directly to the <snmp> element in the
--#			  Event Data Stream DTD.
--#  eventTime		: The <time> element from the Event Data Stream DTD,
--#			  which is the time the event was received by the
--#			  source process.
--#  eventCreateTime 	: Creation time of event in database
--#  eventHost   	: The <host> element from the Event Data Stream DTD
--#  eventSource        : The entity/process which generated the event.
--#  eventSnmphost	: The <snmphost> element from the Event Data Stream DTD
--#  eventDpName	: The dpName of the Dist Poller which received the
--#			  event
--#  eventParms		: The <parms> element from the Event Data Stream DTD
--#  nodeID             : Unique integer identifier for node
--#  ipAddr             : IP Address of node's interface
--#  serviceID          : Unique integer identifier of service/poller package
--#  eventDescr		: Free-form textual description of the event
--#  eventLogmsg	: The log message for the event
--#  eventSeverity	: Severity of event
--#			   1 = Indeterminate
--#			   2 = Cleared (unimplemented at this time)
--#			   3 = Normal
--#			   4 = Warning
--#			   5 = Minor
--#			   6 = Major
--#			   7 = Critical
--#  eventPathOutage	: Event Path outage information	
--#  eventCorrelation	: The event correlation configured for this event
--#			  (stored as an XML string)
--#  eventSuppressedCount	: The number of times the event was suppressed
--#			  (if event correlation was set for suppression)
--#  eventOperInstruct 	: Operator instruction for event.
--#  eventAutoAction	: Automated Action for event.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventOperAction   	: Operator Action for event.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventOperActionMenuText	: Menu text displayed to Operator, which if
--#			  selected, will invoke action described in
--#			  eventOperAction
--#  eventLoggroup	: Logical group with which to associate event.
--#			  This field provides a means of logically
--#			  grouping related events.
--#  eventNotification  : Notification string.  Should consist of
--#			  a fully-qualfied pathname to an executable
--#			  which invokes the notification software, and
--#			  will likely contain event-specific variables
--#  eventTticket       : Trouble ticket integration string.  Should
--#			  consist of fully-qualfied pathname to
--#			  executable command, with possible variables
--#			  used to reference event-specific data
--#  eventTticketState  : Trouble ticket on/off boolean
--#   				1=on, 0=off
--#  eventForward       : Contains a list of triplets:
--#	  		    Destination,State,Mechanism;Destination,State,Mechanism;
--#			  which reflect the following:
--#			      - State is a boolean flag as to whether the
--#				entry is active or not.  1=on, 0=off.
--#			      - Destination is hostname or IP of system to
--#				forward the event to
--#			      - Method is the means by which it will be
--#				forwarded.  A keyword, e.g., SNMP
--#  eventMouseOverText : Text to be displayed on MouseOver event, if
--#			  the event is displayed in the browser and
--#			  the operator needs additional info.
--#  eventLog		: Flag indicating if the event is to be logged, set
--#			  from the 'dest' attribute on the incoming event
--#                       Y = log, N = do not log
--#  eventDisplay	: Flag indicating if the event is to be displayed, set
--#			  from the 'dest' attribute on the incoming event
--#                       Y = display, N = do not display
--#  eventAckUser	: The user who acknowledged this event.  If
--#			  null, then this event has not been acknowledged.
--#  eventAckTime	: The time this event was acknowledged.
--#
--##################################################################

create table events (
	eventID			integer not null,
	eventUei		varchar(256) not null,
	nodeID			integer,
	eventTime		timestamp without time zone not null,
	eventHost		varchar(256),
	eventSource		varchar(128) not null,
	ipAddr			varchar(16),
	eventDpName		varchar(12) not null,
	eventSnmphost		varchar(256),
	serviceID		integer,
	eventSnmp		varchar(256),
	eventParms		text,
	eventCreateTime		timestamp without time zone not null,
	eventDescr		varchar(4000),
	eventLoggroup		varchar(32),
	eventLogmsg		varchar(256),
	eventSeverity		integer not null,
	eventPathOutage		varchar(1024),
	eventCorrelation	varchar(1024),
	eventSuppressedCount	integer,
	eventOperInstruct	varchar(1024),
	eventAutoAction		varchar(256),
	eventOperAction		varchar(256),
	eventOperActionMenuText	varchar(64),
	eventNotification	varchar(128),
	eventTticket		varchar(128),
	eventTticketState	integer,
	eventForward		varchar(256),
	eventMouseOverText	varchar(64),
	eventLog		char(1) not null,
	eventDisplay		char(1) not null,
	eventAckUser		varchar(256),
	eventAckTime		timestamp without time zone,

	constraint pk_eventID primary key (eventID),
	constraint fk_nodeID6 foreign key (nodeID) references node ON DELETE CASCADE
);

create index events_uei_idx on events(eventUei);
create index events_nodeid_idx on events(nodeID);
create index events_ipaddr_idx on events(ipaddr);
create index events_serviceid_idx on events(serviceID);
create index events_time_idx on events(eventTime);
create index events_severity_idx on events(eventSeverity);
create index events_log_idx on events(eventLog);
create index events_display_idx on events(eventDisplay);
create index events_ackuser_idx on events(eventAckUser);
create index events_acktime_idx on events(eventAckTime);

--########################################################################
--#
--# outages table -- This table maintains a record of outage periods for
--#                  given services on specific interfaces.
--#
--# This table provides the following information:
--#
--#  outageID          : Unique integer identifier for the outage
--#  svcLostEventID    : ID of the event that caused the outage. Will be
--#                      a non-null value when a new outage is inserted
--#                      but might be null in case of an opennms upgrade
--#  svcRegainedEventID: ID of the event that cleared the outage
--#  nodeID            : Unique integer identifier for node
--#  ipAddr            : IP Address of node's interface
--#  serviceID         : Unique integer identifier of service/poller package
--#  ifLostService     : Time of lost service event
--#  ifRegainedService : Time of regained service event
--#
--########################################################################

create table outages (
	outageID		integer not null,
	svcLostEventID		integer,
	svcRegainedEventID	integer,
	nodeID			integer,
	ipAddr			varchar(16) not null,
	serviceID		integer,
	ifLostService		timestamp without time zone not null,
	ifRegainedService	timestamp without time zone,

	constraint pk_outageID primary key (outageID),
	constraint fk_eventID1 foreign key (svcLostEventID) references events (eventID) ON DELETE CASCADE,
	constraint fk_eventID2 foreign key (svcRegainedEventID) references events (eventID) ON DELETE CASCADE,
	constraint fk_nodeID4 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE,
	constraint fk_serviceID2 foreign key (serviceID) references service (serviceID) ON DELETE CASCADE
);

create index outages_svclostid_idx on outages(svcLostEventID);
create index outages_svcregainedid_idx on outages(svcRegainedEventID);
create index outages_nodeid_idx on outages(nodeID);
create index outages_ipaddr_idx on outages(ipaddr);
create index outages_serviceid_idx on outages(serviceID);
create index outages_regainedservice_idx on outages(ifRegainedService);

--########################################################################
--#
--# vulnerabilities table -- This table maintains a record of vulnerabilites
--#                          that have been detected on target IP addresses.
--#
--# This table provides the following information:
--#
--#  vulnerabilityID   : Unique integer identifier for the outage
--#  nodeID            : Unique integer identifier for node
--#  ipAddr            : IP Address of node's interface
--#  serviceID         : Unique integer identifier of service/poller package
--#
--#  creationTime      : Initial creation time of the vulnerability
--#  lastAttemptTime   : Last time that an attempt was made to scan for
--#                      this vulnerability
--#  lastScanTime      : Most recent successful scan time
--#  resolvedTime      : Time after which the vulnerability was no longer
--#                      detected
--#
--#  severity          : Severity of the vulnerability (identical to event
--#                      severities
--#  pluginID          : ID number of the plugin that produced the vulnerability
--#  pluginSubID       : Specific vulnerability type generated by the plugin
--#  logmsg            : Terse description of vulnerability (usually
--#                      the plugin name plus short description)
--#  descr             : Verbose description of vulnerability
--#  port              : Port that the vulnerability affects
--#  protocol          : Network protocol of the attack (TCP, UDP, ICMP)