tyler.durden/icinga2
1
0
Fork 0
mirror of https://github.com/Icinga/icinga2.git synced 2025-08-21 09:38:16 +02:00
Code Issues Packages Projects Releases Wiki Activity
icinga2/lib/db_ido_mysql/schema/mysql.sql
Julian Brost 1d49e5c405 Make MySQL schema version in full schema file and upgrade files consistent 
In the 2.12.6 release, the full schema file sets the version to 1.14.3, whereas
the latest available upgrade file 2.11.0.sql sets it to 1.15.0. Therefore, ship
a new upgrade file 2.12.7.sql for all users who imported their schema with
version 2.11.0 or later and never performed an upgrade since then. Their
databases incorrectly state schema version 1.14.3 and is bumped to the correct
version 1.15.0 by the upgrade.

In the 2.13.2 release, the full schema file sets the version to 1.15.0, whereas
the latest available upgrade file 2.13.0.sql sets it to 1.15.1. Therefore,
rename the incorrectly named upgrade file 2.13.1.sql (it was not shipped in
this or any other release so far) to 2.13.3.sql for users who imported their
schema with version 2.13.0 or later and never performed an upgrade since then.
Their databases incorrectly state schema version 1.15.0 and are bumped to the
correct version 1.15.1 by the upgrade. Additionally, the version number in the
full schema is also bumped to the correct version 1.15.1.
2021-12-16 16:01:04 +01:00

1667 lines
65 KiB
SQL
Raw Permalink Blame History

-- --------------------------------------------------------
-- mysql.sql
-- DB definition for IDO MySQL
--
-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+
--
-- -- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: icinga
--
-- --------------------------------------------------------
--
-- Table structure for table icinga_acknowledgements
--
CREATE TABLE IF NOT EXISTS icinga_acknowledgements (
acknowledgement_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
entry_time timestamp NULL,
entry_time_usec int default 0,
acknowledgement_type smallint default 0,
object_id bigint unsigned default 0,
state smallint default 0,
author_name varchar(64) character set latin1 default '',
comment_data TEXT character set latin1,
is_sticky smallint default 0,
persistent_comment smallint default 0,
notify_contacts smallint default 0,
end_time timestamp NULL,
PRIMARY KEY (acknowledgement_id)
) ENGINE=InnoDB COMMENT='Current and historical host and service acknowledgements';
-- --------------------------------------------------------
--
-- Table structure for table icinga_commands
--
CREATE TABLE IF NOT EXISTS icinga_commands (
command_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
object_id bigint unsigned default 0,
command_line TEXT character set latin1,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (command_id),
UNIQUE KEY instance_id (instance_id,object_id,config_type)
) ENGINE=InnoDB COMMENT='Command definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_commenthistory
--
CREATE TABLE IF NOT EXISTS icinga_commenthistory (
commenthistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
entry_time timestamp NULL,
entry_time_usec int default 0,
comment_type smallint default 0,
entry_type smallint default 0,
object_id bigint unsigned default 0,
comment_time timestamp NULL,
internal_comment_id bigint unsigned default 0,
author_name varchar(64) character set latin1 default '',
comment_data TEXT character set latin1,
is_persistent smallint default 0,
comment_source smallint default 0,
expires smallint default 0,
expiration_time timestamp NULL,
deletion_time timestamp NULL,
deletion_time_usec int default 0,
name TEXT character set latin1 default NULL,
PRIMARY KEY (commenthistory_id)
) ENGINE=InnoDB COMMENT='Historical host and service comments';
-- --------------------------------------------------------
--
-- Table structure for table icinga_comments
--
CREATE TABLE IF NOT EXISTS icinga_comments (
comment_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
entry_time timestamp NULL,
entry_time_usec int default 0,
comment_type smallint default 0,
entry_type smallint default 0,
object_id bigint unsigned default 0,
comment_time timestamp NULL,
internal_comment_id bigint unsigned default 0,
author_name varchar(64) character set latin1 default '',
comment_data TEXT character set latin1,
is_persistent smallint default 0,
comment_source smallint default 0,
expires smallint default 0,
expiration_time timestamp NULL,
name TEXT character set latin1 default NULL,
session_token int default NULL,
PRIMARY KEY (comment_id)
) ENGINE=InnoDB COMMENT='Usercomments on Icinga objects';
-- --------------------------------------------------------
--
-- Table structure for table icinga_configfiles
--
CREATE TABLE IF NOT EXISTS icinga_configfiles (
configfile_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
configfile_type smallint default 0,
configfile_path varchar(255) character set latin1 default '',
PRIMARY KEY (configfile_id),
UNIQUE KEY instance_id (instance_id,configfile_type,configfile_path)
) ENGINE=InnoDB COMMENT='Configuration files';
-- --------------------------------------------------------
--
-- Table structure for table icinga_configfilevariables
--
CREATE TABLE IF NOT EXISTS icinga_configfilevariables (
configfilevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
configfile_id bigint unsigned default 0,
varname varchar(64) character set latin1 default '',
varvalue TEXT character set latin1,
PRIMARY KEY (configfilevariable_id)
) ENGINE=InnoDB COMMENT='Configuration file variables';
-- --------------------------------------------------------
--
-- Table structure for table icinga_conninfo
--
CREATE TABLE IF NOT EXISTS icinga_conninfo (
conninfo_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
agent_name varchar(32) character set latin1 default '',
agent_version varchar(32) character set latin1 default '',
disposition varchar(32) character set latin1 default '',
connect_source varchar(32) character set latin1 default '',
connect_type varchar(32) character set latin1 default '',
connect_time timestamp NULL,
disconnect_time timestamp NULL,
last_checkin_time timestamp NULL,
data_start_time timestamp NULL,
data_end_time timestamp NULL,
bytes_processed bigint unsigned default '0',
lines_processed bigint unsigned default '0',
entries_processed bigint unsigned default '0',
PRIMARY KEY (conninfo_id)
) ENGINE=InnoDB COMMENT='IDO2DB daemon connection information';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contactgroups
--
CREATE TABLE IF NOT EXISTS icinga_contactgroups (
contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
contactgroup_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (contactgroup_id),
UNIQUE KEY instance_id (instance_id,config_type,contactgroup_object_id)
) ENGINE=InnoDB COMMENT='Contactgroup definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contactgroup_members
--
CREATE TABLE IF NOT EXISTS icinga_contactgroup_members (
contactgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
contactgroup_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
PRIMARY KEY (contactgroup_member_id)
) ENGINE=InnoDB COMMENT='Contactgroup members';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contactnotificationmethods
--
CREATE TABLE IF NOT EXISTS icinga_contactnotificationmethods (
contactnotificationmethod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
contactnotification_id bigint unsigned default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
command_object_id bigint unsigned default 0,
command_args TEXT character set latin1,
PRIMARY KEY (contactnotificationmethod_id),
UNIQUE KEY instance_id (instance_id,contactnotification_id,start_time,start_time_usec)
) ENGINE=InnoDB COMMENT='Historical record of contact notification methods';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contactnotifications
--
CREATE TABLE IF NOT EXISTS icinga_contactnotifications (
contactnotification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
notification_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
PRIMARY KEY (contactnotification_id),
UNIQUE KEY instance_id (instance_id,contact_object_id,start_time,start_time_usec)
) ENGINE=InnoDB COMMENT='Historical record of contact notifications';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contacts
--
CREATE TABLE IF NOT EXISTS icinga_contacts (
contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
contact_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
email_address varchar(255) character set latin1 default '',
pager_address varchar(64) character set latin1 default '',
host_timeperiod_object_id bigint unsigned default 0,
service_timeperiod_object_id bigint unsigned default 0,
host_notifications_enabled smallint default 0,
service_notifications_enabled smallint default 0,
can_submit_commands smallint default 0,
notify_service_recovery smallint default 0,
notify_service_warning smallint default 0,
notify_service_unknown smallint default 0,
notify_service_critical smallint default 0,
notify_service_flapping smallint default 0,
notify_service_downtime smallint default 0,
notify_host_recovery smallint default 0,
notify_host_down smallint default 0,
notify_host_unreachable smallint default 0,
notify_host_flapping smallint default 0,
notify_host_downtime smallint default 0,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (contact_id),
UNIQUE KEY instance_id (instance_id,config_type,contact_object_id)
) ENGINE=InnoDB COMMENT='Contact definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contactstatus
--
CREATE TABLE IF NOT EXISTS icinga_contactstatus (
contactstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
status_update_time timestamp NULL,
host_notifications_enabled smallint default 0,
service_notifications_enabled smallint default 0,
last_host_notification timestamp NULL,
last_service_notification timestamp NULL,
modified_attributes int default 0,
modified_host_attributes int default 0,
modified_service_attributes int default 0,
PRIMARY KEY (contactstatus_id),
UNIQUE KEY contact_object_id (contact_object_id)
) ENGINE=InnoDB COMMENT='Contact status';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contact_addresses
--
CREATE TABLE IF NOT EXISTS icinga_contact_addresses (
contact_address_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
contact_id bigint unsigned default 0,
address_number smallint default 0,
address varchar(255) character set latin1 default '',
PRIMARY KEY (contact_address_id),
UNIQUE KEY contact_id (contact_id,address_number)
) ENGINE=InnoDB COMMENT='Contact addresses';
-- --------------------------------------------------------
--
-- Table structure for table icinga_contact_notificationcommands
--
CREATE TABLE IF NOT EXISTS icinga_contact_notificationcommands (
contact_notificationcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
contact_id bigint unsigned default 0,
notification_type smallint default 0,
command_object_id bigint unsigned default 0,
command_args varchar(255) character set latin1 default '',
PRIMARY KEY (contact_notificationcommand_id),
UNIQUE KEY contact_id (contact_id,notification_type,command_object_id,command_args)
) ENGINE=InnoDB COMMENT='Contact host and service notification commands';
-- --------------------------------------------------------
--
-- Table structure for table icinga_customvariables
--
CREATE TABLE IF NOT EXISTS icinga_customvariables (
customvariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
object_id bigint unsigned default 0,
config_type smallint default 0,
has_been_modified smallint default 0,
varname varchar(255) character set latin1 collate latin1_general_cs default NULL,
varvalue TEXT character set latin1,
is_json smallint default 0,
PRIMARY KEY (customvariable_id),
UNIQUE KEY object_id_2 (object_id,config_type,varname),
KEY varname (varname)
) ENGINE=InnoDB COMMENT='Custom variables';
-- --------------------------------------------------------
--
-- Table structure for table icinga_customvariablestatus
--
CREATE TABLE IF NOT EXISTS icinga_customvariablestatus (
customvariablestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
object_id bigint unsigned default 0,
status_update_time timestamp NULL,
has_been_modified smallint default 0,
varname varchar(255) character set latin1 collate latin1_general_cs default NULL,
varvalue TEXT character set latin1,
is_json smallint default 0,
PRIMARY KEY (customvariablestatus_id),
UNIQUE KEY object_id_2 (object_id,varname),
KEY varname (varname)
) ENGINE=InnoDB COMMENT='Custom variable status information';
-- --------------------------------------------------------
--
-- Table structure for table icinga_dbversion
--
CREATE TABLE IF NOT EXISTS icinga_dbversion (
dbversion_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(10) character set latin1 default '',
version varchar(10) character set latin1 default '',
create_time timestamp NULL,
modify_time timestamp NULL,
PRIMARY KEY (dbversion_id),
UNIQUE KEY dbversion (name)
) ENGINE=InnoDB;
-- --------------------------------------------------------
--
-- Table structure for table icinga_downtimehistory
--
CREATE TABLE IF NOT EXISTS icinga_downtimehistory (
downtimehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
downtime_type smallint default 0,
object_id bigint unsigned default 0,
entry_time timestamp NULL,
author_name varchar(64) character set latin1 default '',
comment_data TEXT character set latin1,
internal_downtime_id bigint unsigned default 0,
triggered_by_id bigint unsigned default 0,
is_fixed smallint default 0,
duration bigint(20) default 0,
scheduled_start_time timestamp NULL,
scheduled_end_time timestamp NULL,
was_started smallint default 0,
actual_start_time timestamp NULL,
actual_start_time_usec int default 0,
actual_end_time timestamp NULL,
actual_end_time_usec int default 0,
was_cancelled smallint default 0,
is_in_effect smallint default 0,
trigger_time timestamp NULL,
name TEXT character set latin1 default NULL,
PRIMARY KEY (downtimehistory_id)
) ENGINE=InnoDB COMMENT='Historical scheduled host and service downtime';
-- --------------------------------------------------------
--
-- Table structure for table icinga_eventhandlers
--
CREATE TABLE IF NOT EXISTS icinga_eventhandlers (
eventhandler_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
eventhandler_type smallint default 0,
object_id bigint unsigned default 0,
state smallint default 0,
state_type smallint default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
command_object_id bigint unsigned default 0,
command_args TEXT character set latin1,
command_line TEXT character set latin1,
timeout smallint default 0,
early_timeout smallint default 0,
execution_time double default '0',
return_code smallint default 0,
output TEXT character set latin1,
long_output TEXT,
PRIMARY KEY (eventhandler_id),
UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec)
) ENGINE=InnoDB COMMENT='Historical host and service event handlers';
-- --------------------------------------------------------
--
-- Table structure for table icinga_externalcommands
--
CREATE TABLE IF NOT EXISTS icinga_externalcommands (
externalcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
entry_time timestamp NULL,
command_type smallint default 0,
command_name varchar(128) character set latin1 default '',
command_args TEXT character set latin1,
PRIMARY KEY (externalcommand_id)
) ENGINE=InnoDB COMMENT='Historical record of processed external commands';
-- --------------------------------------------------------
--
-- Table structure for table icinga_flappinghistory
--
CREATE TABLE IF NOT EXISTS icinga_flappinghistory (
flappinghistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
event_time timestamp NULL,
event_time_usec int default 0,
event_type smallint default 0,
reason_type smallint default 0,
flapping_type smallint default 0,
object_id bigint unsigned default 0,
percent_state_change double default '0',
low_threshold double default '0',
high_threshold double default '0',
comment_time timestamp NULL,
internal_comment_id bigint unsigned default 0,
PRIMARY KEY (flappinghistory_id)
) ENGINE=InnoDB COMMENT='Current and historical record of host and service flapping';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostchecks
--
CREATE TABLE IF NOT EXISTS icinga_hostchecks (
hostcheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
host_object_id bigint unsigned default 0,
check_type smallint default 0,
is_raw_check smallint default 0,
current_check_attempt smallint default 0,
max_check_attempts smallint default 0,
state smallint default 0,
state_type smallint default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
command_object_id bigint unsigned default 0,
command_args TEXT character set latin1,
command_line TEXT character set latin1,
timeout smallint default 0,
early_timeout smallint default 0,
execution_time double default '0',
latency double default '0',
return_code smallint default 0,
output TEXT character set latin1,
long_output TEXT,
perfdata TEXT character set latin1,
PRIMARY KEY (hostcheck_id)
) ENGINE=InnoDB COMMENT='Historical host checks';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostdependencies
--
CREATE TABLE IF NOT EXISTS icinga_hostdependencies (
hostdependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
host_object_id bigint unsigned default 0,
dependent_host_object_id bigint unsigned default 0,
dependency_type smallint default 0,
inherits_parent smallint default 0,
timeperiod_object_id bigint unsigned default 0,
fail_on_up smallint default 0,
fail_on_down smallint default 0,
fail_on_unreachable smallint default 0,
PRIMARY KEY (hostdependency_id),
KEY instance_id (instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable)
) ENGINE=InnoDB COMMENT='Host dependency definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostescalations
--
CREATE TABLE IF NOT EXISTS icinga_hostescalations (
hostescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
host_object_id bigint unsigned default 0,
timeperiod_object_id bigint unsigned default 0,
first_notification smallint default 0,
last_notification smallint default 0,
notification_interval double default '0',
escalate_on_recovery smallint default 0,
escalate_on_down smallint default 0,
escalate_on_unreachable smallint default 0,
PRIMARY KEY (hostescalation_id),
UNIQUE KEY instance_id (instance_id,config_type,host_object_id,timeperiod_object_id,first_notification,last_notification)
) ENGINE=InnoDB COMMENT='Host escalation definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostescalation_contactgroups
--
CREATE TABLE IF NOT EXISTS icinga_hostescalation_contactgroups (
hostescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
hostescalation_id bigint unsigned default 0,
contactgroup_object_id bigint unsigned default 0,
PRIMARY KEY (hostescalation_contactgroup_id),
UNIQUE KEY instance_id (hostescalation_id,contactgroup_object_id)
) ENGINE=InnoDB COMMENT='Host escalation contact groups';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostescalation_contacts
--
CREATE TABLE IF NOT EXISTS icinga_hostescalation_contacts (
hostescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
hostescalation_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
PRIMARY KEY (hostescalation_contact_id),
UNIQUE KEY instance_id (instance_id,hostescalation_id,contact_object_id)
) ENGINE=InnoDB COMMENT='Host escalation contacts';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostgroups
--
CREATE TABLE IF NOT EXISTS icinga_hostgroups (
hostgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
hostgroup_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
notes TEXT character set latin1 default NULL,
notes_url TEXT character set latin1 default NULL,
action_url TEXT character set latin1 default NULL,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (hostgroup_id),
UNIQUE KEY instance_id (instance_id,hostgroup_object_id)
) ENGINE=InnoDB COMMENT='Hostgroup definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hostgroup_members
--
CREATE TABLE IF NOT EXISTS icinga_hostgroup_members (
hostgroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
hostgroup_id bigint unsigned default 0,
host_object_id bigint unsigned default 0,
PRIMARY KEY (hostgroup_member_id)
) ENGINE=InnoDB COMMENT='Hostgroup members';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hosts
--
CREATE TABLE IF NOT EXISTS icinga_hosts (
host_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
host_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
display_name varchar(255) character set latin1 collate latin1_general_cs default '',
address varchar(128) character set latin1 default '',
address6 varchar(128) character set latin1 default '',
check_command_object_id bigint unsigned default 0,
check_command_args TEXT character set latin1,
eventhandler_command_object_id bigint unsigned default 0,
eventhandler_command_args TEXT character set latin1,
notification_timeperiod_object_id bigint unsigned default 0,
check_timeperiod_object_id bigint unsigned default 0,
failure_prediction_options varchar(128) character set latin1 default '',
check_interval double default '0',
retry_interval double default '0',
max_check_attempts smallint default 0,
first_notification_delay double default '0',
notification_interval double default '0',
notify_on_down smallint default 0,
notify_on_unreachable smallint default 0,
notify_on_recovery smallint default 0,
notify_on_flapping smallint default 0,
notify_on_downtime smallint default 0,
stalk_on_up smallint default 0,
stalk_on_down smallint default 0,
stalk_on_unreachable smallint default 0,
flap_detection_enabled smallint default 0,
flap_detection_on_up smallint default 0,
flap_detection_on_down smallint default 0,
flap_detection_on_unreachable smallint default 0,
low_flap_threshold double default '0',
high_flap_threshold double default '0',
process_performance_data smallint default 0,
freshness_checks_enabled smallint default 0,
freshness_threshold int default 0,
passive_checks_enabled smallint default 0,
event_handler_enabled smallint default 0,
active_checks_enabled smallint default 0,
retain_status_information smallint default 0,
retain_nonstatus_information smallint default 0,
notifications_enabled smallint default 0,
obsess_over_host smallint default 0,
failure_prediction_enabled smallint default 0,
notes TEXT character set latin1,
notes_url TEXT character set latin1,
action_url TEXT character set latin1,
icon_image TEXT character set latin1,
icon_image_alt TEXT character set latin1,
vrml_image TEXT character set latin1,
statusmap_image TEXT character set latin1,
have_2d_coords smallint default 0,
x_2d smallint default 0,
y_2d smallint default 0,
have_3d_coords smallint default 0,
x_3d double default '0',
y_3d double default '0',
z_3d double default '0',
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (host_id),
UNIQUE KEY instance_id (instance_id,config_type,host_object_id)
) ENGINE=InnoDB COMMENT='Host definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_hoststatus
--
CREATE TABLE IF NOT EXISTS icinga_hoststatus (
hoststatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
host_object_id bigint unsigned default 0,
status_update_time timestamp NULL,
output TEXT character set latin1,
long_output TEXT,
perfdata TEXT character set latin1,
check_source varchar(255) character set latin1 default '',
current_state smallint default 0,
has_been_checked smallint default 0,
should_be_scheduled smallint default 0,
current_check_attempt smallint default 0,
max_check_attempts smallint default 0,
last_check timestamp NULL,
next_check timestamp NULL,
check_type smallint default 0,
last_state_change timestamp NULL,
last_hard_state_change timestamp NULL,
last_hard_state smallint default 0,
last_time_up timestamp NULL,
last_time_down timestamp NULL,
last_time_unreachable timestamp NULL,
state_type smallint default 0,
last_notification timestamp NULL,
next_notification timestamp NULL,
no_more_notifications smallint default 0,
notifications_enabled smallint default 0,
problem_has_been_acknowledged smallint default 0,
acknowledgement_type smallint default 0,
current_notification_number int unsigned default 0,
passive_checks_enabled smallint default 0,
active_checks_enabled smallint default 0,
event_handler_enabled smallint default 0,
flap_detection_enabled smallint default 0,
is_flapping smallint default 0,
percent_state_change double default '0',
latency double default '0',
execution_time double default '0',
scheduled_downtime_depth smallint default 0,
failure_prediction_enabled smallint default 0,
process_performance_data smallint default 0,
obsess_over_host smallint default 0,
modified_host_attributes int default 0,
original_attributes TEXT character set latin1 default NULL,
event_handler TEXT character set latin1,
check_command TEXT character set latin1,
normal_check_interval double default '0',
retry_check_interval double default '0',
check_timeperiod_object_id bigint unsigned default 0,
is_reachable smallint default 0,
PRIMARY KEY (hoststatus_id),
UNIQUE KEY object_id (host_object_id)
) ENGINE=InnoDB COMMENT='Current host status information';
-- --------------------------------------------------------
--
-- Table structure for table icinga_host_contactgroups
--
CREATE TABLE IF NOT EXISTS icinga_host_contactgroups (
host_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
host_id bigint unsigned default 0,
contactgroup_object_id bigint unsigned default 0,
PRIMARY KEY (host_contactgroup_id)
) ENGINE=InnoDB COMMENT='Host contact groups';
-- --------------------------------------------------------
--
-- Table structure for table icinga_host_contacts
--
CREATE TABLE IF NOT EXISTS icinga_host_contacts (
host_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
host_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
PRIMARY KEY (host_contact_id)
) ENGINE=InnoDB COMMENT='Host contacts';
-- --------------------------------------------------------
--
-- Table structure for table icinga_host_parenthosts
--
CREATE TABLE IF NOT EXISTS icinga_host_parenthosts (
host_parenthost_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
host_id bigint unsigned default 0,
parent_host_object_id bigint unsigned default 0,
PRIMARY KEY (host_parenthost_id)
) ENGINE=InnoDB COMMENT='Parent hosts';
-- --------------------------------------------------------
--
-- Table structure for table icinga_instances
--
CREATE TABLE IF NOT EXISTS icinga_instances (
instance_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_name varchar(64) character set latin1 default '',
instance_description varchar(128) character set latin1 default '',
PRIMARY KEY (instance_id)
) ENGINE=InnoDB COMMENT='Location names of various Icinga installations';
-- --------------------------------------------------------
--
-- Table structure for table icinga_logentries
--
CREATE TABLE IF NOT EXISTS icinga_logentries (
logentry_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
logentry_time timestamp NULL,
entry_time timestamp NULL,
entry_time_usec int default 0,
logentry_type int default 0,
logentry_data TEXT character set latin1,
realtime_data smallint default 0,
inferred_data_extracted smallint default 0,
object_id bigint unsigned default NULL,
PRIMARY KEY (logentry_id)
) ENGINE=InnoDB COMMENT='Historical record of log entries';
-- --------------------------------------------------------
--
-- Table structure for table icinga_notifications
--
CREATE TABLE IF NOT EXISTS icinga_notifications (
notification_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
notification_type smallint default 0,
notification_reason smallint default 0,
object_id bigint unsigned default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
state smallint default 0,
output TEXT character set latin1,
long_output TEXT,
escalated smallint default 0,
contacts_notified smallint default 0,
PRIMARY KEY (notification_id),
UNIQUE KEY instance_id (instance_id,object_id,start_time,start_time_usec)
) ENGINE=InnoDB COMMENT='Historical record of host and service notifications';
-- --------------------------------------------------------
--
-- Table structure for table icinga_objects
--
CREATE TABLE IF NOT EXISTS icinga_objects (
object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
objecttype_id bigint unsigned default 0,
name1 varchar(255) character set latin1 collate latin1_general_cs default '',
name2 varchar(255) character set latin1 collate latin1_general_cs default NULL,
is_active smallint default 0,
PRIMARY KEY (object_id),
KEY objecttype_id (objecttype_id,name1,name2)
) ENGINE=InnoDB COMMENT='Current and historical objects of all kinds';
-- --------------------------------------------------------
--
-- Table structure for table icinga_processevents
--
CREATE TABLE IF NOT EXISTS icinga_processevents (
processevent_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
event_type smallint default 0,
event_time timestamp NULL,
event_time_usec int default 0,
process_id bigint unsigned default 0,
program_name varchar(16) character set latin1 default '',
program_version varchar(20) character set latin1 default '',
program_date varchar(10) character set latin1 default '',
PRIMARY KEY (processevent_id)
) ENGINE=InnoDB COMMENT='Historical Icinga process events';
-- --------------------------------------------------------
--
-- Table structure for table icinga_programstatus
--
CREATE TABLE IF NOT EXISTS icinga_programstatus (
programstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
program_version varchar(64) character set latin1 collate latin1_general_cs default NULL,
status_update_time timestamp NULL,
program_start_time timestamp NULL,
program_end_time timestamp NULL,
endpoint_name varchar(255) character set latin1 collate latin1_general_cs default NULL,
is_currently_running smallint default 0,
process_id bigint unsigned default 0,
daemon_mode smallint default 0,
last_command_check timestamp NULL,
last_log_rotation timestamp NULL,
notifications_enabled smallint default 0,
disable_notif_expire_time timestamp NULL,
active_service_checks_enabled smallint default 0,
passive_service_checks_enabled smallint default 0,
active_host_checks_enabled smallint default 0,
passive_host_checks_enabled smallint default 0,
event_handlers_enabled smallint default 0,
flap_detection_enabled smallint default 0,
failure_prediction_enabled smallint default 0,
process_performance_data smallint default 0,
obsess_over_hosts smallint default 0,
obsess_over_services smallint default 0,
modified_host_attributes int default 0,
modified_service_attributes int default 0,
global_host_event_handler TEXT character set latin1,
global_service_event_handler TEXT character set latin1,
config_dump_in_progress smallint default 0,
PRIMARY KEY (programstatus_id),
UNIQUE KEY instance_id (instance_id)
) ENGINE=InnoDB COMMENT='Current program status information';
-- --------------------------------------------------------
--
-- Table structure for table icinga_runtimevariables
--
CREATE TABLE IF NOT EXISTS icinga_runtimevariables (
runtimevariable_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
varname varchar(64) character set latin1 default '',
varvalue TEXT character set latin1,
PRIMARY KEY (runtimevariable_id)
) ENGINE=InnoDB COMMENT='Runtime variables from the Icinga daemon';
-- --------------------------------------------------------
--
-- Table structure for table icinga_scheduleddowntime
--
CREATE TABLE IF NOT EXISTS icinga_scheduleddowntime (
scheduleddowntime_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
downtime_type smallint default 0,
object_id bigint unsigned default 0,
entry_time timestamp NULL,
author_name varchar(64) character set latin1 default '',
comment_data TEXT character set latin1,
internal_downtime_id bigint unsigned default 0,
triggered_by_id bigint unsigned default 0,
is_fixed smallint default 0,
duration bigint(20) default 0,
scheduled_start_time timestamp NULL,
scheduled_end_time timestamp NULL,
was_started smallint default 0,
actual_start_time timestamp NULL,
actual_start_time_usec int default 0,
is_in_effect smallint default 0,
trigger_time timestamp NULL,
name TEXT character set latin1 default NULL,
session_token int default NULL,
PRIMARY KEY (scheduleddowntime_id)
) ENGINE=InnoDB COMMENT='Current scheduled host and service downtime';
-- --------------------------------------------------------
--
-- Table structure for table icinga_servicechecks
--
CREATE TABLE IF NOT EXISTS icinga_servicechecks (
servicecheck_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
service_object_id bigint unsigned default 0,
check_type smallint default 0,
current_check_attempt smallint default 0,
max_check_attempts smallint default 0,
state smallint default 0,
state_type smallint default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
command_object_id bigint unsigned default 0,
command_args TEXT character set latin1,
command_line TEXT character set latin1,
timeout smallint default 0,
early_timeout smallint default 0,
execution_time double default '0',
latency double default '0',
return_code smallint default 0,
output TEXT character set latin1,
long_output TEXT,
perfdata TEXT character set latin1,
PRIMARY KEY (servicecheck_id)
) ENGINE=InnoDB COMMENT='Historical service checks';
-- --------------------------------------------------------
--
-- Table structure for table icinga_servicedependencies
--
CREATE TABLE IF NOT EXISTS icinga_servicedependencies (
servicedependency_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
service_object_id bigint unsigned default 0,
dependent_service_object_id bigint unsigned default 0,
dependency_type smallint default 0,
inherits_parent smallint default 0,
timeperiod_object_id bigint unsigned default 0,
fail_on_ok smallint default 0,
fail_on_warning smallint default 0,
fail_on_unknown smallint default 0,
fail_on_critical smallint default 0,
PRIMARY KEY (servicedependency_id),
KEY instance_id (instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical)
) ENGINE=InnoDB COMMENT='Service dependency definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_serviceescalations
--
CREATE TABLE IF NOT EXISTS icinga_serviceescalations (
serviceescalation_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
service_object_id bigint unsigned default 0,
timeperiod_object_id bigint unsigned default 0,
first_notification smallint default 0,
last_notification smallint default 0,
notification_interval double default '0',
escalate_on_recovery smallint default 0,
escalate_on_warning smallint default 0,
escalate_on_unknown smallint default 0,
escalate_on_critical smallint default 0,
PRIMARY KEY (serviceescalation_id),
UNIQUE KEY instance_id (instance_id,config_type,service_object_id,timeperiod_object_id,first_notification,last_notification)
) ENGINE=InnoDB COMMENT='Service escalation definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_serviceescalation_contactgroups
--
CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contactgroups (
serviceescalation_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
serviceescalation_id bigint unsigned default 0,
contactgroup_object_id bigint unsigned default 0,
PRIMARY KEY (serviceescalation_contactgroup_id),
UNIQUE KEY instance_id (serviceescalation_id,contactgroup_object_id)
) ENGINE=InnoDB COMMENT='Service escalation contact groups';
-- --------------------------------------------------------
--
-- Table structure for table icinga_serviceescalation_contacts
--
CREATE TABLE IF NOT EXISTS icinga_serviceescalation_contacts (
serviceescalation_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
serviceescalation_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
PRIMARY KEY (serviceescalation_contact_id),
UNIQUE KEY instance_id (instance_id,serviceescalation_id,contact_object_id)
) ENGINE=InnoDB COMMENT='Service escalation contacts';
-- --------------------------------------------------------
--
-- Table structure for table icinga_servicegroups
--
CREATE TABLE IF NOT EXISTS icinga_servicegroups (
servicegroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
servicegroup_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
notes TEXT character set latin1 default NULL,
notes_url TEXT character set latin1 default NULL,
action_url TEXT character set latin1 default NULL,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (servicegroup_id),
UNIQUE KEY instance_id (instance_id,config_type,servicegroup_object_id)
) ENGINE=InnoDB COMMENT='Servicegroup definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_servicegroup_members
--
CREATE TABLE IF NOT EXISTS icinga_servicegroup_members (
servicegroup_member_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
servicegroup_id bigint unsigned default 0,
service_object_id bigint unsigned default 0,
PRIMARY KEY (servicegroup_member_id)
) ENGINE=InnoDB COMMENT='Servicegroup members';
-- --------------------------------------------------------
--
-- Table structure for table icinga_services
--
CREATE TABLE IF NOT EXISTS icinga_services (
service_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
host_object_id bigint unsigned default 0,
service_object_id bigint unsigned default 0,
display_name varchar(255) character set latin1 collate latin1_general_cs default '',
check_command_object_id bigint unsigned default 0,
check_command_args TEXT character set latin1,
eventhandler_command_object_id bigint unsigned default 0,
eventhandler_command_args TEXT character set latin1,
notification_timeperiod_object_id bigint unsigned default 0,
check_timeperiod_object_id bigint unsigned default 0,
failure_prediction_options varchar(64) character set latin1 default '',
check_interval double default '0',
retry_interval double default '0',
max_check_attempts smallint default 0,
first_notification_delay double default '0',
notification_interval double default '0',
notify_on_warning smallint default 0,
notify_on_unknown smallint default 0,
notify_on_critical smallint default 0,
notify_on_recovery smallint default 0,
notify_on_flapping smallint default 0,
notify_on_downtime smallint default 0,
stalk_on_ok smallint default 0,
stalk_on_warning smallint default 0,
stalk_on_unknown smallint default 0,
stalk_on_critical smallint default 0,
is_volatile smallint default 0,
flap_detection_enabled smallint default 0,
flap_detection_on_ok smallint default 0,
flap_detection_on_warning smallint default 0,
flap_detection_on_unknown smallint default 0,
flap_detection_on_critical smallint default 0,
low_flap_threshold double default '0',
high_flap_threshold double default '0',
process_performance_data smallint default 0,
freshness_checks_enabled smallint default 0,
freshness_threshold int default 0,
passive_checks_enabled smallint default 0,
event_handler_enabled smallint default 0,
active_checks_enabled smallint default 0,
retain_status_information smallint default 0,
retain_nonstatus_information smallint default 0,
notifications_enabled smallint default 0,
obsess_over_service smallint default 0,
failure_prediction_enabled smallint default 0,
notes TEXT character set latin1,
notes_url TEXT character set latin1,
action_url TEXT character set latin1,
icon_image TEXT character set latin1,
icon_image_alt TEXT character set latin1,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (service_id),
UNIQUE KEY instance_id (instance_id,config_type,service_object_id)
) ENGINE=InnoDB COMMENT='Service definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_servicestatus
--
CREATE TABLE IF NOT EXISTS icinga_servicestatus (
servicestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
service_object_id bigint unsigned default 0,
status_update_time timestamp NULL,
output TEXT character set latin1,
long_output TEXT,
perfdata TEXT character set latin1,
check_source varchar(255) character set latin1 default '',
current_state smallint default 0,
has_been_checked smallint default 0,
should_be_scheduled smallint default 0,
current_check_attempt smallint default 0,
max_check_attempts smallint default 0,
last_check timestamp NULL,
next_check timestamp NULL,
check_type smallint default 0,
last_state_change timestamp NULL,
last_hard_state_change timestamp NULL,
last_hard_state smallint default 0,
last_time_ok timestamp NULL,
last_time_warning timestamp NULL,
last_time_unknown timestamp NULL,
last_time_critical timestamp NULL,
state_type smallint default 0,
last_notification timestamp NULL,
next_notification timestamp NULL,
no_more_notifications smallint default 0,
notifications_enabled smallint default 0,
problem_has_been_acknowledged smallint default 0,
acknowledgement_type smallint default 0,
current_notification_number int unsigned default 0,
passive_checks_enabled smallint default 0,
active_checks_enabled smallint default 0,
event_handler_enabled smallint default 0,
flap_detection_enabled smallint default 0,
is_flapping smallint default 0,
percent_state_change double default '0',
latency double default '0',
execution_time double default '0',
scheduled_downtime_depth smallint default 0,
failure_prediction_enabled smallint default 0,
process_performance_data smallint default 0,
obsess_over_service smallint default 0,
modified_service_attributes int default 0,
original_attributes TEXT character set latin1 default NULL,
event_handler TEXT character set latin1,
check_command TEXT character set latin1,
normal_check_interval double default '0',
retry_check_interval double default '0',
check_timeperiod_object_id bigint unsigned default 0,
is_reachable smallint default 0,
PRIMARY KEY (servicestatus_id),
UNIQUE KEY object_id (service_object_id)
) ENGINE=InnoDB COMMENT='Current service status information';
-- --------------------------------------------------------
--
-- Table structure for table icinga_service_contactgroups
--
CREATE TABLE IF NOT EXISTS icinga_service_contactgroups (
service_contactgroup_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
service_id bigint unsigned default 0,
contactgroup_object_id bigint unsigned default 0,
PRIMARY KEY (service_contactgroup_id)
) ENGINE=InnoDB COMMENT='Service contact groups';
-- --------------------------------------------------------
--
-- Table structure for table icinga_service_contacts
--
CREATE TABLE IF NOT EXISTS icinga_service_contacts (
service_contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
service_id bigint unsigned default 0,
contact_object_id bigint unsigned default 0,
PRIMARY KEY (service_contact_id)
) ENGINE=InnoDB COMMENT='Service contacts';
-- --------------------------------------------------------
--
-- Table structure for table icinga_statehistory
--
CREATE TABLE IF NOT EXISTS icinga_statehistory (
statehistory_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
state_time timestamp NULL,
state_time_usec int default 0,
object_id bigint unsigned default 0,
state_change smallint default 0,
state smallint default 0,
state_type smallint default 0,
current_check_attempt smallint default 0,
max_check_attempts smallint default 0,
last_state smallint default 0,
last_hard_state smallint default 0,
output TEXT character set latin1,
long_output TEXT,
check_source varchar(255) character set latin1 default NULL,
PRIMARY KEY (statehistory_id)
) ENGINE=InnoDB COMMENT='Historical host and service state changes';
-- --------------------------------------------------------
--
-- Table structure for table icinga_systemcommands
--
CREATE TABLE IF NOT EXISTS icinga_systemcommands (
systemcommand_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
start_time timestamp NULL,
start_time_usec int default 0,
end_time timestamp NULL,
end_time_usec int default 0,
command_line TEXT character set latin1,
timeout smallint default 0,
early_timeout smallint default 0,
execution_time double default '0',
return_code smallint default 0,
output TEXT character set latin1,
long_output TEXT,
PRIMARY KEY (systemcommand_id),
UNIQUE KEY instance_id (instance_id,start_time,start_time_usec)
) ENGINE=InnoDB COMMENT='Historical system commands that are executed';
-- --------------------------------------------------------
--
-- Table structure for table icinga_timeperiods
--
CREATE TABLE IF NOT EXISTS icinga_timeperiods (
timeperiod_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
timeperiod_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (timeperiod_id),
UNIQUE KEY instance_id (instance_id,config_type,timeperiod_object_id)
) ENGINE=InnoDB COMMENT='Timeperiod definitions';
-- --------------------------------------------------------
--
-- Table structure for table icinga_timeperiod_timeranges
--
CREATE TABLE IF NOT EXISTS icinga_timeperiod_timeranges (
timeperiod_timerange_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
timeperiod_id bigint unsigned default 0,
day smallint default 0,
start_sec int default 0,
end_sec int default 0,
PRIMARY KEY (timeperiod_timerange_id)
) ENGINE=InnoDB COMMENT='Timeperiod definitions';
-- --------------------------------------------------------
-- Icinga 2 specific schema extensions
-- --------------------------------------------------------
--
-- Table structure for table icinga_endpoints
--
CREATE TABLE IF NOT EXISTS icinga_endpoints (
endpoint_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
endpoint_object_id bigint(20) unsigned DEFAULT '0',
zone_object_id bigint(20) unsigned DEFAULT '0',
config_type smallint(6) DEFAULT '0',
identity varchar(255) DEFAULT NULL,
node varchar(255) DEFAULT NULL,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (endpoint_id)
) ENGINE=InnoDB COMMENT='Endpoint configuration';
-- --------------------------------------------------------
--
-- Table structure for table icinga_endpointstatus
--
CREATE TABLE IF NOT EXISTS icinga_endpointstatus (
endpointstatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
endpoint_object_id bigint(20) unsigned DEFAULT '0',
zone_object_id bigint(20) unsigned DEFAULT '0',
status_update_time timestamp NULL,
identity varchar(255) DEFAULT NULL,
node varchar(255) DEFAULT NULL,
is_connected smallint(6),
PRIMARY KEY (endpointstatus_id)
) ENGINE=InnoDB COMMENT='Endpoint status';
--
-- Table structure for table icinga_zones
--
CREATE TABLE IF NOT EXISTS icinga_zones (
zone_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
zone_object_id bigint(20) unsigned DEFAULT '0',
config_type smallint(6) DEFAULT '0',
parent_zone_object_id bigint(20) unsigned DEFAULT '0',
is_global smallint(6),
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (zone_id)
) ENGINE=InnoDB COMMENT='Zone configuration';
-- --------------------------------------------------------
--
-- Table structure for table icinga_zonestatus
--
CREATE TABLE IF NOT EXISTS icinga_zonestatus (
zonestatus_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
zone_object_id bigint(20) unsigned DEFAULT '0',
status_update_time timestamp NULL,
parent_zone_object_id bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (zonestatus_id)
) ENGINE=InnoDB COMMENT='Zone status';
ALTER TABLE icinga_servicestatus ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_hoststatus ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_contactstatus ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_programstatus ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_comments ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_scheduleddowntime ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_runtimevariables ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_customvariablestatus ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_acknowledgements ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_commenthistory ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_contactnotifications ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_downtimehistory ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_eventhandlers ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_externalcommands ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_flappinghistory ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_hostchecks ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_logentries ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_notifications ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_processevents ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_servicechecks ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_statehistory ADD COLUMN endpoint_object_id bigint default NULL;
ALTER TABLE icinga_systemcommands ADD COLUMN endpoint_object_id bigint default NULL;
-- -----------------------------------------
-- add index (delete)
-- -----------------------------------------
-- for periodic delete
-- instance_id and
-- SYSTEMCOMMANDS, SERVICECHECKS, HOSTCHECKS, EVENTHANDLERS => start_time
-- EXTERNALCOMMANDS => entry_time
-- instance_id
CREATE INDEX servicechecks_i_id_idx on icinga_servicechecks(instance_id);
CREATE INDEX hostchecks_i_id_idx on icinga_hostchecks(instance_id);
CREATE INDEX externalcommands_i_id_idx on icinga_externalcommands(instance_id);
-- time
CREATE INDEX systemcommands_time_id_idx on icinga_systemcommands(start_time);
CREATE INDEX servicechecks_time_id_idx on icinga_servicechecks(start_time);
CREATE INDEX hostchecks_time_id_idx on icinga_hostchecks(start_time);
CREATE INDEX eventhandlers_time_id_idx on icinga_eventhandlers(start_time);
CREATE INDEX externalcommands_time_id_idx on icinga_externalcommands(entry_time);
-- for starting cleanup - referenced in dbhandler.c:882
-- instance_id only
-- realtime data
CREATE INDEX hoststatus_i_id_idx on icinga_hoststatus(instance_id);
CREATE INDEX servicestatus_i_id_idx on icinga_servicestatus(instance_id);
CREATE INDEX contactstatus_i_id_idx on icinga_contactstatus(instance_id);
CREATE INDEX customvariablestatus_i_id_idx on icinga_customvariablestatus(instance_id);
-- config data
CREATE INDEX configfilevariables_i_id_idx on icinga_configfilevariables(instance_id);
CREATE INDEX customvariables_i_id_idx on icinga_customvariables(instance_id);
CREATE INDEX timeperiod_timeranges_i_id_idx on icinga_timeperiod_timeranges(instance_id);
CREATE INDEX contactgroup_members_i_id_idx on icinga_contactgroup_members(instance_id);
CREATE INDEX hostgroup_members_i_id_idx on icinga_hostgroup_members(instance_id);
CREATE INDEX servicegroup_members_i_id_idx on icinga_servicegroup_members(instance_id);
CREATE INDEX contact_addresses_i_id_idx on icinga_contact_addresses(instance_id);
CREATE INDEX contact_notifcommands_i_id_idx on icinga_contact_notificationcommands(instance_id);
CREATE INDEX host_parenthosts_i_id_idx on icinga_host_parenthosts(instance_id);
CREATE INDEX host_contacts_i_id_idx on icinga_host_contacts(instance_id);
CREATE INDEX service_contacts_i_id_idx on icinga_service_contacts(instance_id);
CREATE INDEX service_contactgroups_i_id_idx on icinga_service_contactgroups(instance_id);
CREATE INDEX host_contactgroups_i_id_idx on icinga_host_contactgroups(instance_id);
CREATE INDEX hostesc_cgroups_i_id_idx on icinga_hostescalation_contactgroups(instance_id);
CREATE INDEX serviceesc_cgroups_i_id_idx on icinga_serviceescalation_contactgroups(instance_id);
-- -----------------------------------------
-- more index stuff (WHERE clauses)
-- -----------------------------------------
-- hosts
CREATE INDEX hosts_host_object_id_idx on icinga_hosts(host_object_id);
-- hoststatus
CREATE INDEX hoststatus_stat_upd_time_idx on icinga_hoststatus(status_update_time);
CREATE INDEX hoststatus_current_state_idx on icinga_hoststatus(current_state);
CREATE INDEX hoststatus_check_type_idx on icinga_hoststatus(check_type);
CREATE INDEX hoststatus_state_type_idx on icinga_hoststatus(state_type);
CREATE INDEX hoststatus_last_state_chg_idx on icinga_hoststatus(last_state_change);
CREATE INDEX hoststatus_notif_enabled_idx on icinga_hoststatus(notifications_enabled);
CREATE INDEX hoststatus_problem_ack_idx on icinga_hoststatus(problem_has_been_acknowledged);
CREATE INDEX hoststatus_act_chks_en_idx on icinga_hoststatus(active_checks_enabled);
CREATE INDEX hoststatus_pas_chks_en_idx on icinga_hoststatus(passive_checks_enabled);
CREATE INDEX hoststatus_event_hdl_en_idx on icinga_hoststatus(event_handler_enabled);
CREATE INDEX hoststatus_flap_det_en_idx on icinga_hoststatus(flap_detection_enabled);
CREATE INDEX hoststatus_is_flapping_idx on icinga_hoststatus(is_flapping);
CREATE INDEX hoststatus_p_state_chg_idx on icinga_hoststatus(percent_state_change);
CREATE INDEX hoststatus_latency_idx on icinga_hoststatus(latency);
CREATE INDEX hoststatus_ex_time_idx on icinga_hoststatus(execution_time);
CREATE INDEX hoststatus_sch_downt_d_idx on icinga_hoststatus(scheduled_downtime_depth);
-- services
CREATE INDEX services_host_object_id_idx on icinga_services(host_object_id);
-- servicestatus
CREATE INDEX srvcstatus_stat_upd_time_idx on icinga_servicestatus(status_update_time);
CREATE INDEX srvcstatus_current_state_idx on icinga_servicestatus(current_state);
CREATE INDEX srvcstatus_check_type_idx on icinga_servicestatus(check_type);
CREATE INDEX srvcstatus_state_type_idx on icinga_servicestatus(state_type);
CREATE INDEX srvcstatus_last_state_chg_idx on icinga_servicestatus(last_state_change);
CREATE INDEX srvcstatus_notif_enabled_idx on icinga_servicestatus(notifications_enabled);
CREATE INDEX srvcstatus_problem_ack_idx on icinga_servicestatus(problem_has_been_acknowledged);
CREATE INDEX srvcstatus_act_chks_en_idx on icinga_servicestatus(active_checks_enabled);
CREATE INDEX srvcstatus_pas_chks_en_idx on icinga_servicestatus(passive_checks_enabled);
CREATE INDEX srvcstatus_event_hdl_en_idx on icinga_servicestatus(event_handler_enabled);
CREATE INDEX srvcstatus_flap_det_en_idx on icinga_servicestatus(flap_detection_enabled);
CREATE INDEX srvcstatus_is_flapping_idx on icinga_servicestatus(is_flapping);
CREATE INDEX srvcstatus_p_state_chg_idx on icinga_servicestatus(percent_state_change);
CREATE INDEX srvcstatus_latency_idx on icinga_servicestatus(latency);
CREATE INDEX srvcstatus_ex_time_idx on icinga_servicestatus(execution_time);
CREATE INDEX srvcstatus_sch_downt_d_idx on icinga_servicestatus(scheduled_downtime_depth);
-- hostchecks
CREATE INDEX hostchks_h_obj_id_idx on icinga_hostchecks(host_object_id);
-- servicechecks
CREATE INDEX servicechks_s_obj_id_idx on icinga_servicechecks(service_object_id);
-- objects
CREATE INDEX objects_name1_idx ON icinga_objects(name1);
CREATE INDEX objects_name2_idx ON icinga_objects(name2);
CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id);
-- instances
-- CREATE INDEX instances_name_idx on icinga_instances(instance_name);
-- logentries
-- CREATE INDEX loge_instance_id_idx on icinga_logentries(instance_id);
-- #236
CREATE INDEX loge_time_idx on icinga_logentries(logentry_time);
-- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data);
CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logentry_time DESC);
-- commenthistory
-- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id);
-- CREATE INDEX c_hist_c_time_idx on icinga_logentries(comment_time);
-- CREATE INDEX c_hist_i_c_id_idx on icinga_logentries(internal_comment_id);
-- downtimehistory
-- CREATE INDEX d_t_hist_nstance_id_idx on icinga_downtimehistory(instance_id);
-- CREATE INDEX d_t_hist_type_idx on icinga_downtimehistory(downtime_type);
-- CREATE INDEX d_t_hist_object_id_idx on icinga_downtimehistory(object_id);
-- CREATE INDEX d_t_hist_entry_time_idx on icinga_downtimehistory(entry_time);
-- CREATE INDEX d_t_hist_sched_start_idx on icinga_downtimehistory(scheduled_start_time);
-- CREATE INDEX d_t_hist_sched_end_idx on icinga_downtimehistory(scheduled_end_time);
-- scheduleddowntime
-- CREATE INDEX sched_d_t_downtime_type_idx on icinga_scheduleddowntime(downtime_type);
-- CREATE INDEX sched_d_t_object_id_idx on icinga_scheduleddowntime(object_id);
-- CREATE INDEX sched_d_t_entry_time_idx on icinga_scheduleddowntime(entry_time);
-- CREATE INDEX sched_d_t_start_time_idx on icinga_scheduleddowntime(scheduled_start_time);
-- CREATE INDEX sched_d_t_end_time_idx on icinga_scheduleddowntime(scheduled_end_time);
-- statehistory
CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
-- #2274
create index statehist_state_idx on icinga_statehistory(object_id,state);
-- Icinga Web Notifications
CREATE INDEX notification_idx ON icinga_notifications(notification_type, object_id, start_time);
CREATE INDEX notification_object_id_idx ON icinga_notifications(object_id);
CREATE INDEX contact_notification_idx ON icinga_contactnotifications(notification_id, contact_object_id);
CREATE INDEX contacts_object_id_idx ON icinga_contacts(contact_object_id);
CREATE INDEX contact_notif_meth_notif_idx ON icinga_contactnotificationmethods(contactnotification_id, command_object_id);
CREATE INDEX command_object_idx ON icinga_commands(object_id);
CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id, host_object_id);
-- #2618
CREATE INDEX cntgrpmbrs_cgid_coid ON icinga_contactgroup_members (contactgroup_id,contact_object_id);
CREATE INDEX hstgrpmbrs_hgid_hoid ON icinga_hostgroup_members (hostgroup_id,host_object_id);
CREATE INDEX hstcntgrps_hid_cgoid ON icinga_host_contactgroups (host_id,contactgroup_object_id);
CREATE INDEX hstprnthsts_hid_phoid ON icinga_host_parenthosts (host_id,parent_host_object_id);
CREATE INDEX runtimevars_iid_varn ON icinga_runtimevariables (instance_id,varname);
CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,service_object_id);
CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id);
CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec);
-- #3649
CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC);
CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time);
CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1);
-- #4985
CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id);
-- #10066
CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id);
CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id);
CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id);
CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id);
CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id);
CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id);
CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id);
CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id);
-- #12210
CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token);
CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token);
-- #12107
CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time);
-- #12435
CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id);
CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id);
CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id);
CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id);
CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id);
CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id);
CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id);
-- #5458
create index idx_downtimehistory_remove on icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time);
create index idx_scheduleddowntime_remove on icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time);
-- #5492
CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time);
CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time);
-- -----------------------------------------
-- set dbversion
-- -----------------------------------------
INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.15.1', NOW(), NOW())
ON DUPLICATE KEY UPDATE version='1.15.1', modify_time=NOW();
Reference in New Issue View Git Blame Copy Permalink