create.sql

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

--#
--########################################################################

create table vulnerabilities (
	vulnerabilityID		integer not null,
	nodeID			integer,
	ipAddr			varchar(16),
	serviceID		integer,
	creationTime		timestamp without time zone not null,
	lastAttemptTime		timestamp without time zone not null,
	lastScanTime		timestamp without time zone not null,
	resolvedTime		timestamp without time zone,
	severity		integer not null,
	pluginID		integer not null,
	pluginSubID             integer not null,
	logmsg                  varchar(256),
	descr			text,
	port			integer,
	protocol		varchar(32),
	cveEntry		varchar(14),

	constraint pk_vulnerabilityID primary key (vulnerabilityID)
);

create index vulnerabilities_nodeid_idx on vulnerabilities(nodeID);
create index vulnerabilities_ipaddr_idx on vulnerabilities(ipAddr);
create index vulnerabilities_severity_idx on vulnerabilities(severity);
create index vulnerabilities_port_idx on vulnerabilities(port);
create index vulnerabilities_protocol_idx on vulnerabilities(protocol);

--########################################################################
--#
--# vulnPlugins table -- This table contains a list of information about
--#                      Nessus plugins that are in use by the nessusd
--#                      daemons that are being used by vulnscand.
--#
--# This table provides the following information:
--#
--#  pluginID          : Plugin ID number (from Nessus)
--#  pluginSubID       : Specific vulnerability type within the plugin
--#  name              : Short name of the plugin
--#  category          : Category of the plugin's behavior (scanner,
--#                      attack, etc)
--#  copyright         : Copyright notice for the plugin
--#  descr             : Verbose description of vulnerability
--#  summary           : Short description of plugin behavior
--#  family            : User-comprehensible type of attack (CGI abuses,
--#                      Backdoors, etc)
--#  version           : Version of the plugin code
--#  cveEntry          : CVE entry associated with the vulnerability
--#                      that this plugin tests
--#  md5               : 128-bit hex MD5 checksum of the plugin that
--#                      can be used to detect changes in the plugin code
--#
--########################################################################

create table vulnPlugins (
        pluginID                integer not null,
        pluginSubID             integer not null,
        name                    varchar(128),
        category                varchar(32),
        copyright               varchar(128),
        descr                   text,
        summary                 varchar(256),
        family                  varchar(32),
        version                 varchar(32),
        cveEntry                varchar(14),
        md5                     varchar(32)
);

--#  This constraint not understood installer
--#        CONSTRAINT pk_vulnplugins PRIMARY KEY (pluginID,pluginSubID));
--#

create unique index vulnplugins_plugin_idx on vulnPlugins(pluginID, pluginSubID);

--########################################################################
--# notification table - Contains information on acknowleged and outstanding
--#                      pages listed by user/groups
--#
--# This table contains the following fields:
--#
--# textMsg     : The message being sent in the page.
--# numericMsg  : The message being sent to a numeric pager
--# notifyID    : The primary key of this row, populated with the value from
--#               the notifyNxtId sequence.
--# pageTime    : A timestamp of when the page was originally sent.
--# respondTime : A timestamp of when the page was acknowleged. A null in this
--#               field means that the page has not been answered yet.
--# answeredBy  : The user id of the user that answered the page, set the same
--#               for all rows with the same groupId field.
--# nodeId      : The id of the node that has the problem
--# interfaceId : The id of the interface on the node that has the problem
--# serviceID   : The id of the service on the interface that has the problem
--# eventID     : The primary key of the event that spawned the notification
--# eventUEI    : The uei of the event that spawned the notification, placed here
--#               for speed of lookup as notifications are processed.
--#
--########################################################################

create table notifications (
       textMsg      varchar(4000) not null,
       subject      varchar(256),
       numericMsg   varchar(256),
       notifyID	    integer not null,
       pageTime     timestamp without time zone,
       respondTime  timestamp without time zone,
       answeredBy   varchar(256),
       nodeID	    integer,
       interfaceID  varchar(16),
       serviceID    integer,
       queueID		varchar(256),
       eventID      integer,
       eventUEI     varchar(256) not null,

       constraint pk_notifyID primary key (notifyID),
       constraint fk_nodeID7 foreign key (nodeID) references node (nodeID) ON DELETE CASCADE,
       constraint fk_eventID3 foreign key (eventID) references events (eventID) ON DELETE CASCADE
);

create index notifications_ipaddr_idx on notifications(interfaceID);
create index notifications_serviceid_idx on notifications(serviceID);
create index notifications_eventid_idx on notifications(eventID);
create index notifications_respondtime_idx on notifications(respondTime);
create index notifications_answeredby_idx on notifications(answeredBy);

--########################################################################
--#
--# This table contains the following fields:
--# userID      : The user id of the person being paged, from the users.xml
--#               file.
--# notifyID    : The index of the row from the notification table.
--# media       : A string describing the type of contact being made, ie text
--#               page, numeric page, email, etc...
--# contactInfo : A field for storing the information used to contact the user,
--#               e.g. an email address, the phone number and pin of the pager...
--# autonotify	: A character to determine how auto acknowledge is handled for
--#               this entry
--#
--########################################################################

create table usersNotified (
        userID          varchar(256) not null,
        notifyID        integer,
        notifyTime      timestamp without time zone,
        media           varchar(32),
        contactinfo     varchar(64),
        autonotify      char(1),

	constraint fk_notifID2 foreign key (notifyID) references notifications (notifyID) ON DELETE CASCADE
);

create index userid_notifyid_idx on usersNotified(userID, notifyID);

--# This constraint not understood by installer
--#        CONSTRAINT pk_usersNotified PRIMARY KEY (userID,notifyID) );
--#
--########################################################################
--# asset table - Contains inventory and other user-entered information
--#                     for nodes
--#
--# This table contains the following fields:
--#
--# nodeID           : The node id for the node this asset information belongs.
--# category         : A broad idea of what this asset does (examples are
--#                    desktop, printer, server, infrastructure, etc.).
--# manufacturer     : Name of the manufacturer of this asset.
--# vendor           : Vendor from whom this asset was purchased.
--# modelNumber      : The model number of this asset.
--# serialNumber     : The serial number of this asset.
--# description      : A free-form description.
--# circuitId        : The electrical/network circuit this asset connects to.
--# assetNumber      : A business-specified asset number.
--# operatingSystem  : The operating system, if any.
--# rack             : For servers, the rack it is installed in.
--# slot             : For servers, the slot in the rack it is installed in.
--# port             : For servers, the port in the slot it is installed in.
--# region           : A broad geographical or organizational area.
--# division         : A broad geographical or organizational area.
--# department       : The department this asset belongs to.
--# address1         : Address of geographical location of asset, line 1.
--# address2         : Address of geographical location of asset, line 2.
--# city             : The city where this asset resides.
--# state            : The state where this asset resides.
--# zip              : The zip code where this asset resides.
--# building         : The building where this asset resides.
--# floor            : The floor of the building where this asset resides.
--# room             : The room where this asset resides.
--# vendorPhone      : A contact number for the vendor.
--# vendorFax        : A fax number for the vendor.
--# userCreated      : The username who created this record.
--# userLastModified : The last user who modified this record.
--# lastModifiedDate : The last time this record was modified.
--# dateInstalled    : The date the asset was installed.
--# lease            : The lease number of this asset.
--# leaseExpires     : The date the lease expires for this asset.
--# supportPhone     : A support phone number for this asset.
--# maintContract    : The maintenance contract number for this asset.
--#
--########################################################################

create table assets (
        nodeID          integer,
        category        varchar(64) not null,
        manufacturer    varchar(64),
        vendor          varchar(64),
        modelNumber     varchar(64),
        serialNumber    varchar(64),
        description     varchar(128),
        circuitId       varchar(64),
        assetNumber     varchar(64),
        operatingSystem varchar(64),
        rack            varchar(64),
        slot            varchar(64),
        port            varchar(64),
        region          varchar(64),
        division        varchar(64),
        department      varchar(64),
        address1        varchar(256),
        address2        varchar(256),
        city            varchar(64),
        state           varchar(64),
        zip             varchar(64),
        building        varchar(64),
        floor           varchar(64),
        room            varchar(64),
        vendorPhone     varchar(64),
        vendorFax       varchar(64),
        vendorAssetNumber varchar(64),
        userLastModified char(20) not null,
        lastModifiedDate timestamp without time zone not null,
        dateInstalled   varchar(64),
        lease           varchar(64),
        leaseExpires    varchar(64),
        supportPhone    varchar(64),
        maintContract   varchar(64),
        maintContractExpires varchar(64),
        displayCategory   varchar(64),
        notifyCategory   varchar(64),
        pollerCategory   varchar(64),
        thresholdCategory   varchar(64),
        comment         varchar(1024),

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


--##################################################################
--# The following commands set up automatic sequencing functionality
--# for fields which require this.
--#
--# DO NOT forget to add an "install" comment so that install.pl
--# knows to fix and renumber the sequences if need be
--##################################################################

--# Sequence for the nodeID column in the node table
--#          sequence, column, table
--# install: nodeNxtId nodeID   node
create sequence nodeNxtId minvalue 1;

--# Sequence for the serviceID column in the service table
--#          sequence,    column,   table
--# install: serviceNxtId serviceID service
create sequence serviceNxtId minvalue 1;

--# Sequence for the eventID column in the events table
--#          sequence,   column, table
--# install: eventsNxtId eventID events
create sequence eventsNxtId minvalue 1;

--# Sequence for the outageID column in the outages table
--#          sequence,   column,  table
--# install: outageNxtId outageID outages
create sequence outageNxtId minvalue 1;

--# Sequence for the notifyID column in the notification table
--#          sequence,   column,  table
--# install: notifyNxtId notifyID notifications
create sequence notifyNxtId minvalue 1;

--# Sequence for the vulnerabilityID column in the vulnerabilities table
--#          sequence, column,         table
--# install: vulnNxtId vulnerabilityID vulnerabilities
create sequence vulnNxtId minvalue 1;

--##################################################################
--# The following command adds the initial loopback poller entry to
--# the 'distPoller' table.
--##################################################################
insert into distPoller (dpName, dpIP, dpComment, dpDiscLimit, dpLastNodePull, dpLastEventPull, dpLastPackagePush, dpAdminState, dpRunState) values ('localhost', '127.0.0.1', 'This is the default poller.', 0.10, null, null, null, 1, 1);