-- InterMapper Database Schema -- -- Copyright (c) 2007-2009, Dartware, LLC. All rights reserved. -- Schema Version (3.9) -- Generated for POSTGRES on 2009-04-08 by sqlalchemy 0.5.2 -- Reply-To: David Schnur -- -- The initial release of InterMapper Database only works with PostgreSQL 8.1 or later. -- Support for MySQL, Oracle, and others is not available. The built-in database shipped -- with InterMapper DataCenter is PostgreSQL 8.3. You can connect to it using a variety of -- admin tools, including pgAdmin III, which you can download for all platforms here: -- -- http://www.pgadmin.org/ -- -- Documentation for PostgreSQL 8.3 can be found here: -- -- http://www.postgresql.org/docs/8.3/interactive/ -- -- The schema is divided into three sections. The Reference section contains definitions for -- lookup tables and database control tables. The Element section specifies the base tables -- for the different elements of the InterMapper system. The Temporal section defines tables -- that store time-based historical data and events. -- -- Tables defined by this schema -- -- [Reference] -- SchemaVersion -- DeviceKind -- RetentionPolicy -- IANAEnterprise -- IANAIfType -- -- [Element] -- Server -- Notifier -- Map -- Device -- Interface -- Address -- NotifierRule -- DataSet -- -- [Temporal] -- ServerPoll -- Event -- Acknowledgment -- Notification -- DataPoint -- DataSample -- DataSampleHourly -- DataSampleDaily -- -- -- Soft-Delete Strategy -- -- Since the InterMapper database contains historical information, clients may want to -- retrieve data or events for elements that no longer exist. To make this possible, every -- element has a delete_time attribute, behaving as follows: -- -- - A "live" device or element will have a delete_time of 'infinity'. Some SQL clients -- may display this as a time far into the future. -- -- - All deleted elements will have a delete_time corresponding to the date and time of -- deletion, and describe their state at the moment of deletion. -- -- - When a container's delete_time attribute is set, the delete_time attribute of all its -- sub-elements will be automatically set also. For example, when a device is deleted, -- all of its interfaces are marked as deleted as well, by setting their delete_times. -- -- - Because Notifier Rule relationships are intimately tied to Devices, we will not retain -- deleted Notifier Rules, and they have no delete_time. -- -- -- Reconciliation Strategy -- -- The Device and Interface tables include a "customer_name_reference" column for use in -- reconciling the InterMapper database with other databases used by a customer. -- -- -- Case-Sensitivity of Hexadecimal Values -- -- Where values are entered in hexadecimal, e.g. UUID's, MAC addresses and IPv6 addresses, -- the convention is to use *lower case* for characters A-F. -- -- -- Document History -- -- Schema version 1.0 - February 20, 2008 -- - Initial release of the schema to alpha testers. -- -- Schema version 2.0 - May 7, 2008 -- - Added recv_rows column to serverpoll table. -- - Changed server.oldest_time to oldest_utc, an integer. -- - Added event table indices on (end_time) and (server_id, map_id, device_id, alarmpt). -- - Set next_utc autoincrement to false, to prevent it from becoming BIGSERIAL. -- -- Schema version 3.0 - June 30, 2008 -- - Added minor_version field to schemaversion, for more granular versioning. -- - Updated schemaversion primary key to include minor_version. -- - Created schema 'private' for Dartware testing tables and views. -- - Created schema 'customer' for customer-created tables and views. -- - Added Crystal Reports Template views to the private schema. -- -- Schema version 3.1 - July 16, 2008 -- - Increased the range of notify_attempt to allow larger values. -- -- Schema version 3.2 - August 22, 2008 -- - Updated schemaversion and retentionpolicy comments. -- - Added 'trap' back to the list of allowed event statuses. -- -- Schema version 3.3 - November 4, 2008 -- - Changed XML (probe_xml, notifier_xml) from VARCHAR(4096) to TEXT. -- -- Schema version 3.4 - January 1, 2009 -- - Added the stat_counts table to the private schema. -- - Added the stat_diskusage table to the private schema. -- - Added the asserts table to the private schema. -- -- Schema version 3.5 - January 9, 2009 -- - Added additional columns to the private schema Crystal Reports views. -- -- Schema version 3.6 - January 17, 2009 -- - Added the parent column to the devices table, for probe groups. -- - Added the server_start column to the serverpoll table. -- -- Schema version 3.7 - March 17, 2009 -- - Added 'smstext' to the notification.notify_method constraint, for SMS notifications. -- -- Schema version 3.8 - March 25, 2009 -- - Built-in retention policies now properly default custom_minutes to '5 minutes'. -- - The built-in '24 Hours' retention policy now generates data samples. -- -- Schema version 3.9 - April 1, 2009 -- - Indexes on the event table are now properly created for new and upgraded databases. -- The SchemaVersion table contains meta-data about the database schema in use. In -- particular, this table contains the schema major and minor version, a date indicating -- the version of data in the reference tables, and the date when the schema was installed. -- -- The schema major version number starts at 1 and increments by 1 each time a change is -- made that significantly affects public use of the schema, i.e. removing a column. The -- schema minor version number starts at 0 and increments by 1 each time any change is made -- to the schema, including changes to private schema tables not intended for customer use. -- -- Schema versions < 0 are reserved; they are not upgradable. Schema version 0 is invalid. -- A schema version of -9999 forces the database to drop all data and re-install the schema -- from scratch. This is primarily for testing and debugging. -- -- There should always be exactly one row in this table. To fetch the schema version: -- -- SELECT version FROM SchemaVersion; CREATE TABLE schemaversion ( version INTEGER NOT NULL, minor_version INTEGER NOT NULL, reference_date DATE NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (version, minor_version) ) -- The DeviceKind table defines the available device types. Customers can define their own -- device types with devicekind_id's greater than zero. The devicekind_id zero represents -- 'Unspecified'. All devicekind_id's less than or equal to zero are reserved by Dartware. CREATE TABLE devicekind ( devicekind_id SERIAL NOT NULL, name VARCHAR(80) NOT NULL, PRIMARY KEY (devicekind_id) ) -- The RetentionPolicy table stores the rules that maps, devices and interfaces use to -- reduce the disk usage of historical data. A retention policy defines how long to keep -- raw data in the datapoints table, and reduced data in the datasample, datasample_hourly -- and datasample_daily tables. Every dataset is assigned a retention policy. If the data -- from that dataset passes the limit defined in the policy, it is deleted permanently. A -- retention policy can also define data as either not being collected at all, or kept -- forever, with no limit. -- -- There are four pre-defined retention policies: -- -- None Datasets given this policy do not retain any data. This special policy -- causes InterMapper to not even export the data in the first place. -- -- Forever Data and samples from datasets with the 'Forever' policy never expire. -- -- IM46Charts This special policy is applied to chart data converted from IM 4.6 -- format. By default, it is the same as 'Forever', matching the behavior -- of the IM server, which keeps chart data forever. Editing this policy -- allows you to easily change this default behavior for all 4.6 datasets. -- -- 24 Hours Data is automatically deleted at the start of every day. -- -- All retentionpolicy_ids less than or equal to zero are reserved. Data retention policies -- defined by customers through the InterMapper Database web admin interface are given -- retentionpolicy_ids greater than zero. -- -- Expiration values are represented in days. Zero means that data or samples for that -- category are not retained at all, and will not be exported by the InterMapper Server. -- A value of -1 indicates that data never expires (the policy is not applied). -- -- Samples for each dataset are created in real-time as new data arrives. So for every data -- point produced by a Dataset, the original raw value is stored in the Datapoint table, and -- x-minute, hourly and daily samples are created or updated in the DataSample, -- DataSample_Hourly and DataSample_Daily tables. -- -- custom_minutes When producing samples for the datasample table, this is the number -- of minutes covered by each sample. For example, with a value of five, -- any dataset with this retention policy generates 12 five-minute samples, -- each with the min, max, average, etc. over five minutes of datapoints. CREATE TABLE retentionpolicy ( retentionpolicy_id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, raw_expiration SMALLINT NOT NULL, hourly_expiration SMALLINT NOT NULL, daily_expiration SMALLINT NOT NULL, custom_minutes SMALLINT NOT NULL, custom_expiration SMALLINT NOT NULL, PRIMARY KEY (retentionpolicy_id), UNIQUE (name) ) -- The IANAEnterprise table stores the organization enterprise numbers assigned by the -- Internet Assigned Numbers Authority (IANA). The SMI Network Management Private Enterprise -- Codes are defined in "http://www.iana.org/assignments/enterprise-numbers". -- -- The enterprise_id is typically the seventh subid of sysObjectID when it begins with -- "1.3.6.1.4.1". For example, a sysObjectID of "1.3.6.1.4.1.9.1.2.3" will have an -- enterprise_id of 9, which belongs to Cisco. -- -- All enterprise_ids are assigned positive values. Enterprise_id 0 is reserved by the -- IANA. The enterprise_id -1 is reserved in this schema for 'Unspecified'. CREATE TABLE ianaenterprise ( enterprise_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (enterprise_id) ) -- The IANAIfType table stores the interface types assigned by the Internet Assigned Numbers -- Authority (IANA). The interface types (ifTypes) are defined in the IANAifType-MIB located -- at "http://www.iana.org/assignments/ianaiftype-mib". -- -- All iftype_ids are assigned positive values. The iftype_id 0 is not assigned by the IANA. -- The iftype_id -1 is reserved in this schema for 'Unspecified'. CREATE TABLE ianaiftype ( iftype_id INTEGER NOT NULL, name VARCHAR(40) NOT NULL, PRIMARY KEY (iftype_id) ) -- The Server table stores information about each InterMapper Server reporting to this -- database. The information in this table will be used to identify data belonging to each -- server, to contact the server through the network, to locate the server machine -- physically, and to discern between servers on different RFC 1918 networks. -- -- The primary key for the Server table is [server_id]. A server_id is an integer in the -- range [1,4095] (12 bits). It is assigned by InterMapper Database when the InterMapper -- server first begins reporting to the database. -- -- uuid This value uniquely identifies an InterMapper server. It takes the form -- '%08x-%04x-%04x-%04x-%012x', and is sent when the server first registers -- with the database. Note that if you manually-copy your InterMapper -- Settings folder, you will end up with two servers with the same UUID. -- If both are set to report to the same InterMapper Database server, you -- will see an error when you try to register the second server, and a -- prompt will ask you whether you want to generate a new UUID for it. CREATE TABLE server ( server_id SMALLINT NOT NULL, name VARCHAR(255) NOT NULL, uuid VARCHAR(36) NOT NULL CHECK (uuid ~ '[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}'), url VARCHAR(1000) NOT NULL, authsecret VARCHAR(1000) NOT NULL, host_location VARCHAR(1000) NOT NULL, host_contact VARCHAR(1000) NOT NULL, network_name VARCHAR(255) NOT NULL, poll_type VARCHAR(4) NOT NULL CHECK (poll_type IN ('pull','push')), device_interval INTEGER NOT NULL, event_interval INTEGER NOT NULL, data_interval INTEGER NOT NULL, oldest_utc BIGINT NOT NULL, import_profile VARCHAR(7) NOT NULL CHECK (import_profile IN ('now','weekend','nightly','hourly','never')), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id), UNIQUE (uuid), CHECK (create_time <= delete_time) ) -- The Notifier table stores information about the Notifiers on all InterMapper Servers. -- -- The primary key for the Notifier table is [server_id, notifier_id]. The notifier_id is an -- integer in the range [1,262143] (18 bits). The notifier_id is assigned by the InterMapper -- server, not by the database. The notifier_id is unique for the InterMapper server that -- assigned it, but not across all servers. CREATE TABLE notifier ( server_id SMALLINT NOT NULL, notifier_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, enabled BOOLEAN NOT NULL, notifier_xml TEXT NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, notifier_id), FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (create_time <= delete_time) ) -- The Map table stores information about each map on each InterMapper Server reporting to -- InterMapper Database. -- -- The primary key for the Map table is [server_id, map_id]. The map_id is an integer in the -- range [1,16777215] (24 bits). The map_id is assigned by the InterMapper server, not by -- the database. The map_id is unique on the InterMapper server that assigned it, but not -- across all servers. -- -- enabled Used to indicate whether a map is enabled or disabled on its InterMapper -- server. This can be used in queries to select only those devices or -- interfaces that are on enabled maps. CREATE TABLE map ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, path VARCHAR(255) NOT NULL, enabled BOOLEAN NOT NULL, graphid VARCHAR(9) NOT NULL, retentionpolicy_id INTEGER NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id), FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (create_time <= delete_time) ) -- The Device table stores information about the devices on each map on each InterMapper -- Server reporting to InterMapper Database. -- -- The primary key for the Device table is [server_id, map_id, device_id]. The device_id is -- an integer in the range [1,16777215] (24 bits). The device_id is assigned by the -- InterMapper server, not by the database. The combination of map_id and device_id is -- unique on the InterMapper server that assigned it, but not across all servers. -- -- parent_id Used to indicate whether a device is part of a probe group. If this -- device is not part of a probe group, parent_id will be zero. If it is -- a member of a probe group, parent_id is set to the device_id of the -- device that contains the rest of the group (the icon on the map). CREATE TABLE device ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, parent_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, ip INET NOT NULL, ipresolve VARCHAR(4) NOT NULL CHECK (ipresolve IN ('name','addr','none')), port INTEGER NOT NULL, mac MACADDR, notes VARCHAR(4096) NOT NULL, dnsname VARCHAR(255) NOT NULL, netbios VARCHAR(255) NOT NULL, probe VARCHAR(255) NOT NULL, probekind VARCHAR(6) NOT NULL CHECK (probekind IN ('none','other','snmp','tcp','udp','icmp','cmd','bigbro','ntsvcs')), probe_xml TEXT NOT NULL, devicekind_id INTEGER NOT NULL, enterprise_id INTEGER NOT NULL, retentionpolicy_id INTEGER NOT NULL, latitude FLOAT(53), longitude FLOAT(53), snmpversion VARCHAR(4) NOT NULL CHECK (snmpversion IN ('none','v1','v2c','v3')), sysobjectid VARCHAR(255) NOT NULL, sysdescr VARCHAR(4096) NOT NULL, sysname VARCHAR(255) NOT NULL, syslocation VARCHAR(255) NOT NULL, syscontact VARCHAR(255) NOT NULL, sysuptime TIMESTAMP WITH TIME ZONE, mfgname VARCHAR(255) NOT NULL, modelname VARCHAR(255) NOT NULL, serialnum VARCHAR(255) NOT NULL, customer_name_reference VARCHAR(255), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id, device_id), FOREIGN KEY(devicekind_id) REFERENCES devicekind (devicekind_id) ON DELETE RESTRICT, FOREIGN KEY(server_id, map_id) REFERENCES map (server_id, map_id) ON DELETE CASCADE, FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(enterprise_id) REFERENCES ianaenterprise (enterprise_id) ON DELETE RESTRICT, CHECK (create_time <= delete_time) ) -- The Interface table stores information about the interfaces for each device on each -- InterMapper Server reporting to InterMapper Database. -- -- The primary key for the Interface table is [server_id, map_id, device_id, interface_id]. -- The interface_id is an integer in the range [1,16777215] (24 bits). The combination of -- map_id, device_id and interface_id is unique on the InterMapper server that assigned it, -- but not across all servers. CREATE TABLE interface ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, interface_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, ifindex BIGINT NOT NULL, ifdescr VARCHAR(255) NOT NULL, ifname VARCHAR(255) NOT NULL, ifalias VARCHAR(255) NOT NULL, iftype_id INTEGER NOT NULL, ifmtu INTEGER NOT NULL, iflastchange TIMESTAMP WITH TIME ZONE NOT NULL, dot3duplex VARCHAR(7) NOT NULL CHECK (dot3duplex IN ('none','unknown','half','full')), retentionpolicy_id INTEGER NOT NULL, txspeed BIGINT NOT NULL, rxspeed BIGINT NOT NULL, mac MACADDR, status VARCHAR(8) NOT NULL CHECK (status IN ('up','down','ack','acklocal')), enabled BOOLEAN NOT NULL, pvid INTEGER NOT NULL, vlans VARCHAR(1000) NOT NULL, alarmpt VARCHAR(30) NOT NULL CHECK (alarmpt = ('@' || interface_id)), customer_name_reference VARCHAR(255), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id, device_id, interface_id), FOREIGN KEY(iftype_id) REFERENCES ianaiftype (iftype_id), CHECK (create_time <= delete_time), FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT ) -- The Address table stores information about the IPv4 and IPv6 addresses associated with -- each Interface. An Interface that is numbered will have one or more addresses. So called -- "unnumbered" interfaces do not have any associated addresses in this table. -- -- The primary key of the Address table is [server_id, map_id, device_id, interface_id, ip]. -- The IPv4 address is stored as a dotted-decimal. The IPv6 address is stored in "canonical" -- colon-hexadecimal format, which means that it is in lower-case, fully-expanded colon-hex -- format, with no double colons. CREATE TABLE address ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, interface_id INTEGER NOT NULL, ip INET NOT NULL, prefix SMALLINT NOT NULL, PRIMARY KEY (server_id, map_id, device_id, interface_id, ip), FOREIGN KEY(server_id, map_id, device_id, interface_id) REFERENCES interface (server_id, map_id, device_id, interface_id) ON DELETE CASCADE ) -- The NotifierRule table stores information about the notifier triggers contained within an -- Escalation. Each row in the NotifierRule table corresponds to a row in a Device's -- Notifier window (with the six checkboxes). -- -- The primary key for the NotifierRule table is [server_id, map_id, device_id, notifier_id]. CREATE TABLE notifierrule ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, notifier_id INTEGER NOT NULL, down BOOLEAN NOT NULL, up BOOLEAN NOT NULL, critical BOOLEAN NOT NULL, alarm BOOLEAN NOT NULL, warning BOOLEAN NOT NULL, okay BOOLEAN NOT NULL, trap BOOLEAN NOT NULL, delay_interval INTEGER NOT NULL, repeat_interval INTEGER NOT NULL, repeat_count INTEGER NOT NULL, PRIMARY KEY (server_id, map_id, device_id, notifier_id), FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, FOREIGN KEY(server_id, notifier_id) REFERENCES notifier (server_id, notifier_id) ON DELETE CASCADE ) -- The DataSet table stores information about the datasets produced by each device or -- interface on each InterMapper Server. A dataset represents all data points for a -- particular variable (i.e. round-trip time, uptime, etc.). The data itself is stored in -- the Datapoint table, described separately in this schema. -- -- A DataSet inherits the retentionpolicy_id of its owning Device or Interface. -- -- The primary key of the DataSet table is [dataset_id]. This key is used to link a dataset -- with the Datapoint or Datasample tables. An alternate key, used to link a dataset with -- its device or interface, is [server_id, map_id, device_id, alarmpt, name]. -- -- alarmpt Used to link a dataset to an interface. When a dataset belongs to a -- device, the alarmpt is empty. When a dataset belongs to an interface, -- the alarmpt is '@ifID', where ifID is the interface's interface_id. CREATE TABLE dataset ( dataset_id BIGSERIAL NOT NULL, server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, alarmpt VARCHAR(255) NOT NULL, name VARCHAR(30) NOT NULL, label VARCHAR(255) NOT NULL, tags VARCHAR(1024) NOT NULL, retentionpolicy_id INTEGER NOT NULL, PRIMARY KEY (dataset_id), FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, UNIQUE (server_id, map_id, device_id, alarmpt, name) ) -- The ServerPoll table stores the recent IMDC polling history for each InterMapper server. -- Only a fully complete, successful import results in a set of entries in this table. -- -- The ServerPoll table is used by InterMapper Database to track its progress when -- importing both current and historical data. When InterMapper database is restarted, it -- can resume from where it left off by consulting this table. The table also stores extra -- information, such as the number of bytes and rows in each import, and the InterMapper -- server's reported uptime on each import. This data is used to tune import performance, -- or generate statistics about the InterMapper server. -- -- next_utc With each import, the InterMapper server returns a UTC time-stamp for -- the next time at which data is available. In the next import request, -- InterMapper Database asks for all data from this time forward (or back, -- when requesting historical data). Having the server submit this time -- avoids problems when the clock on the InterMapper Database server reads -- a different time from that on the InterMapper server. It is also a -- convenient way to gradually 'step back' through the InterMapper server's -- history when collecting historical data. -- -- server_start Reported by InterMapper servers version 5.1 or later. This value is the -- UTC time at which the InterMapper server started up. By querying for -- differences in this value between imports, it is possible to roughly -- track an InterMapper server's uptime. CREATE TABLE serverpoll ( server_id SMALLINT NOT NULL, next_utc BIGINT NOT NULL, begin_time TIMESTAMP WITH TIME ZONE NOT NULL, end_time TIMESTAMP WITH TIME ZONE NOT NULL, report VARCHAR(32) NOT NULL, recv_bytes INTEGER NOT NULL, recv_rows INTEGER NOT NULL, server_start BIGINT NOT NULL, PRIMARY KEY (server_id, next_utc, begin_time, report), FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (begin_time <= end_time) ) -- The Event table stores event history for Devices and their alarm points. Events for -- interfaces are stored using special alarm point names. -- -- Each event has a begin_time and end_time. When a device's condition changes; for example, -- going from 'okay' into 'alarm', a new event is created. The begin_time of this event is -- the current UTC time on the InterMapper server. The end_time is 'infinity', which may -- show up as a time far into the future when viewed by some SQL clients. -- -- When the device then transitions into a different state; for example, going back to -- 'okay', the previous 'down' event has its delete_time set to the current time. It is now -- considered 'closed'. A new event, with a end_time of 'infinity' is then produced to -- represent the new 'okay' condition. By these rules, each device or interface can only -- have one event at a time with an end_time of 'infinity', matching the way a device in -- InterMapper map can only have one condition at a time. -- -- When querying for all devices and interfaces currently that are, for example, 'down', -- one would look for all the Event table entries with a status of 'down' and end_time of -- infinity. These events represent every time a device or interface went down, but has not -- yet come back up or gone into a different state. -- -- As another example, a query could compare the begin_time and end_time of a 'down' event, -- to find out how long the device or interface remained down. -- -- The 'memo', 'trap' and 'syslog' events describe instantaneous events. As such, their -- begin_time and end_time values are always equal. CREATE TABLE event ( event_id BIGSERIAL NOT NULL, server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, alarmpt VARCHAR(255) NOT NULL, begin_time TIMESTAMP WITH TIME ZONE NOT NULL, end_time TIMESTAMP WITH TIME ZONE NOT NULL, status VARCHAR(8) NOT NULL CHECK (status IN ('down','critical','alarm','warning','okay','unknown','trap')), probe VARCHAR(255) NOT NULL, reason VARCHAR(4096) NOT NULL, PRIMARY KEY (event_id), FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, CHECK (begin_time <= end_time) ) -- The Acknowledgment table stores acknowlegment history for each Event. -- -- The 'current' ack state of an event is the ack_kind of the most recent Acknowledgement, -- or unack if there is none. CREATE TABLE acknowledgment ( event_id BIGINT NOT NULL, ack_time TIMESTAMP WITH TIME ZONE NOT NULL, ack_kind VARCHAR(5) NOT NULL CHECK (ack_kind IN ('ack','unack')), ack_source VARCHAR(255) NOT NULL, ack_message VARCHAR(4096) NOT NULL, PRIMARY KEY (event_id, ack_time), FOREIGN KEY(event_id) REFERENCES event (event_id) ON DELETE CASCADE ) -- The Notification table stores notification history for each Event. For example, you may -- have a sound notifier set to play a sound when a device goes down. When the device goes -- down, an entry with a delete_time of 'infinity' is created in the Event table. When the -- sound plays, an entry linked to that event is created in the Notification table. -- -- notify_attempt The number of notification attempts. For instantaneous events, like -- 'trap', which don't support delayed or repeated notifications, the -- notify_attempt is zero. When using, for example, E-Mail notifiers, it's -- possible for the first attempt to send mail to fail. In that case, the -- notify_attempt will be incremented for each attempt made to send mail. CREATE TABLE notification ( event_id BIGINT NOT NULL, notifier_id INTEGER NOT NULL, notify_time TIMESTAMP WITH TIME ZONE NOT NULL, notify_method VARCHAR(10) NOT NULL CHECK (notify_method IN ('smtpmail','audible','snmptrap','snpppager','modempager','winpopup','cmdline','syslog','group','smstext')), notify_attempt INTEGER NOT NULL, notify_result VARCHAR(4) NOT NULL CHECK (notify_result IN ('okay','fail')), PRIMARY KEY (event_id, notifier_id, notify_time), FOREIGN KEY(event_id) REFERENCES event (event_id) ON DELETE CASCADE ) -- The DataPoint table stores the data readings (referred to as Points) for each DataSet. CREATE TABLE datapoint ( dataset_id BIGINT NOT NULL, data_time TIMESTAMP WITH TIME ZONE NOT NULL, data_value FLOAT(53) NOT NULL, PRIMARY KEY (dataset_id, data_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSample table stores samples of data readings consolidated at a pre-defined number -- of minutes within each hour. Each sample stores the arithmetic mean (average), standard -- deviation, minimum reading and maximum reading over the time interval. CREATE TABLE datasample ( dataset_id BIGINT NOT NULL, sample_time TIMESTAMP WITH TIME ZONE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size SMALLINT NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSampleHourly table stores samples of data readings consolidated every hour. Each -- sample stores the arithmetic mean (average), standard deviation, minimum reading and -- maximum reading for the entire hour. CREATE TABLE datasamplehourly ( dataset_id BIGINT NOT NULL, sample_time TIMESTAMP WITH TIME ZONE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size SMALLINT NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSampleDaily table stores samples of data readings consolidated every day. Each -- sample stores the arithmetic mean (average), standard deviation, minimum reading and -- maximum reading for the entire day. -- -- A day is defined UTC time; it starts at 00:00:00 UTC and ends at 23:59:59 UTC. CREATE TABLE datasampledaily ( dataset_id BIGINT NOT NULL, sample_time DATE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size INTEGER NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The Stat_Counts table stores entries counting the number of rows in each of the other -- database tables. These entries are then used in lieue of performing a full row count, -- which can be a very time-consuming operation. Each entry has a sub-type identifier, which -- allows counts of, for example, only the number of enabled maps. -- -- This table is updated as part of an automatic task every morning. -- -- NOTE: This is a private-schema table, and is subject to change without notice. CREATE TABLE StatCounts -- NOT FOUND -- The Stat_DiskUsage table stores entries containing the approximate disk usage, in bytes, -- of a particular table. This is used to generate and display statistics about how much -- disk space the database was consuming on a given day in the past. -- -- This table is updated as part of an automatic task every morning. -- -- NOTE: This is a private-schema table, and is subject to change without notice. CREATE TABLE StatDiskUsage -- NOT FOUND -- The Asserts table stores some basic queries used to test the integrity of the database. -- Each row corresponds to a test, and includes the SQL query defining the test, as well -- as the result for the last time the test was run. -- -- NOTE: This is a private-schema table, and is subject to change without notice. CREATE TABLE Asserts -- NOT FOUND