1-- Pandora FMS - the Flexible Monitoring System 2-- ============================================ 3-- Copyright (c) 2005-2011 Artica Soluciones Tecnológicas, http://www.artica.es 4-- Please see http://pandora.sourceforge.net for full contribution list 5 6-- This program is free software; you can redistribute it and/or 7-- modify it under the terms of the GNU General Public License 8-- as published by the Free Software Foundation for version 2. 9-- This program is distributed in the hope that it will be useful, 10-- but WITHOUT ANY WARRANTY; without even the implied warranty of 11-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12-- GNU General Public License for more details. 13-- You should have received a copy of the GNU General Public License 14-- along with this program; if not, write to the Free Software 15-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 16 17-- PLEASE NO NOT USE MULTILINE COMMENTS 18-- Because Pandora Installer don't understand them 19-- and fails creating database !!! 20-- ----------------------------------------------------------- 21-- Pandora FMS official tables for 3.2 version -- 22-- ----------------------------------------------------------- 23 24-- The charset is for all DB not only table. 25--CREATE DATABASE "pandora" WITH ENCODING 'utf8'; 26 27--\c "pandora" 28 29-- For previous PostgreSQL version 9.0 30CREATE OR REPLACE LANGUAGE plpgsql; 31 32CREATE OR REPLACE FUNCTION unix_timestamp(TIMESTAMP without time zone = CURRENT_TIMESTAMP) RETURNS double precision AS 'SELECT ceil(date_part(''epoch'', $1)); ' LANGUAGE SQL; 33 34-- --------------------------------------------------------------------- 35-- Table `taddress` 36-- --------------------------------------------------------------------- 37CREATE TABLE "taddress" ( 38 "id_a" SERIAL NOT NULL PRIMARY KEY, 39 "ip" VARCHAR(60) NOT NULL default '', 40 "ip_pack" INTEGER NOT NULL default 0 41); 42CREATE INDEX "taddress_ip_idx" ON "taddress"("ip"); 43 44-- --------------------------------------------------------------------- 45-- Table `taddress_agent` 46-- --------------------------------------------------------------------- 47CREATE TABLE "taddress_agent" ( 48 "id_ag" BIGSERIAL NOT NULL PRIMARY KEY, 49 "id_a" BIGINT NOT NULL default 0, 50 "id_agent" BIGINT NOT NULL default 0 51); 52 53-- --------------------------------------------------------------------- 54-- Table `tagente` 55-- --------------------------------------------------------------------- 56CREATE TABLE "tagente" ( 57 "id_agente" SERIAL NOT NULL PRIMARY KEY, 58 "nombre" varchar(600) NOT NULL default '', 59 "direccion" varchar(100) default NULL, 60 "comentarios" varchar(255) default '', 61 "id_grupo" INTEGER NOT NULL default 0, 62 "ultimo_contacto" TIMESTAMP without time zone default '1970-01-01 00:00:00', 63 "modo" SMALLINT NOT NULL default 0, 64 "intervalo" INTEGER NOT NULL default 300, 65 "id_os" INTEGER default 0, 66 "os_version" varchar(100) default '', 67 "agent_version" varchar(100) default '', 68 "ultimo_contacto_remoto" TIMESTAMP without time zone default '1970-01-01 00:00:00', 69 "disabled" SMALLINT NOT NULL default 0, 70 "id_parent" INTEGER default 0, 71 "custom_id" varchar(255) default '', 72 "server_name" varchar(100) default '', 73 "cascade_protection" SMALLINT NOT NULL default 0, 74 --number of hours of diference with the server timezone 75 "timezone_offset" SMALLINT NULL DEFAULT 0, 76 --path in the server to the image of the icon representing the agent 77 "icon_path" VARCHAR(127) NULL DEFAULT NULL, 78 --set it to one to update the position data (altitude, longitude, latitude) when getting information from the agent or to 0 to keep the last value and don\'t update it 79 "update_gis_data" SMALLINT NOT NULL DEFAULT 1, 80 "url_address" TEXT NULL default '', 81 "quiet" SMALLINT NOT NULL default 0, 82 "normal_count" INTEGER NOT NULL default 0, 83 "warning_count" INTEGER NOT NULL default 0, 84 "critical_count" INTEGER NOT NULL default 0, 85 "unknown_count" INTEGER NOT NULL default 0, 86 "notinit_count" INTEGER NOT NULL default 0, 87 "total_count" INTEGER NOT NULL default 0, 88 "fired_count" INTEGER NOT NULL default 0, 89 "update_module_count" SMALLINT NOT NULL DEFAULT 1, 90 "update_alert_count" SMALLINT NOT NULL DEFAULT 1 91); 92CREATE INDEX "tagente_nombre_idx" ON "tagente"("nombre"); 93CREATE INDEX "tagente_direccion_idx" ON "tagente"("direccion"); 94CREATE INDEX "tagente_disabled_idx" ON "tagente"("disabled"); 95CREATE INDEX "tagente_id_grupo_idx" ON "tagente"("id_grupo"); 96 97-- --------------------------------------------------------------------- 98-- Table `tagente_datos` 99-- --------------------------------------------------------------------- 100CREATE TABLE "tagente_datos" ( 101 "id_agente_modulo" INTEGER NOT NULL default 0, 102 "datos" DOUBLE PRECISION default NULL, 103 "utimestamp" BIGINT default 0 104); 105CREATE INDEX "tagente_datos_id_agente_modulo_idx" ON "tagente_datos"("id_agente_modulo"); 106CREATE INDEX "tagente_datos_utimestamp_idx" ON "tagente_datos"("utimestamp"); 107 108-- --------------------------------------------------------------------- 109-- Table `tagente_datos_inc` 110-- --------------------------------------------------------------------- 111CREATE TABLE "tagente_datos_inc" ( 112 "id_agente_modulo" INTEGER NOT NULL default 0, 113 "datos" DOUBLE PRECISION default NULL, 114 "utimestamp" INTEGER NOT NULL default 0 115); 116CREATE INDEX "tagente_datos_inc_id_agente_modulo_idx" ON "tagente_datos_inc"("id_agente_modulo"); 117 118-- --------------------------------------------------------------------- 119-- Table `tagente_datos_string` 120-- --------------------------------------------------------------------- 121CREATE TABLE "tagente_datos_string" ( 122 "id_agente_modulo" INTEGER NOT NULL default 0, 123 "datos" TEXT NOT NULL, 124 "utimestamp" INTEGER NOT NULL default 0 125); 126CREATE INDEX "tagente_datos_string_id_agente_modulo_idx" ON "tagente_datos_string"("id_agente_modulo"); 127CREATE INDEX "tagente_datos_string_utimestamp_idx" ON "tagente_datos_string"("utimestamp"); 128 129-- ----------------------------------------------------- 130-- Table `tagente_datos_log4x` 131-- ----------------------------------------------------- 132CREATE TABLE "tagente_datos_log4x" ( 133 "id_tagente_datos_log4x" BIGSERIAL NOT NULL PRIMARY KEY, 134 "id_agente_modulo" INTEGER NOT NULL default 0, 135 "severity" text NOT NULL, 136 "message" text NOT NULL, 137 "stacktrace" text NOT NULL, 138 "utimestamp" INTEGER NOT NULL default 0 139); 140CREATE INDEX "tagente_datos_log4x_id_agente_modulo_idx" ON "tagente_datos_log4x"("id_agente_modulo"); 141 142-- --------------------------------------------------------------------- 143-- Table `tagente_estado` 144-- --------------------------------------------------------------------- 145CREATE TABLE "tagente_estado" ( 146 "id_agente_estado" SERIAL NOT NULL PRIMARY KEY, 147 "id_agente_modulo" INTEGER NOT NULL default 0, 148 "datos" text NOT NULL default '', 149 "timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00', 150 "estado" INTEGER NOT NULL default 0, 151 "id_agente" INTEGER NOT NULL default 0, 152 "last_try" TIMESTAMP without time zone default '1970-01-01 00:00:00', 153 "utimestamp" BIGINT NOT NULL default 0, 154 "current_interval" INTEGER NOT NULL default 0, 155 "running_by" INTEGER default 0, 156 "last_execution_try" BIGINT NOT NULL default 0, 157 "status_changes" INTEGER default 0, 158 "last_status" INTEGER default 0, 159 "last_known_status" INTEGER default 0, 160 "last_error" INTEGER default 0, 161 "ff_start_utimestamp" BIGINT default 0 162); 163CREATE INDEX "tagente_estado_id_agente_modulo_idx" ON "tagente_estado"("id_agente_modulo"); 164CREATE INDEX "tagente_estado_id_agente_idx" ON "tagente_estado"("id_agente"); 165CREATE INDEX "tagente_estado_estado_idx" ON "tagente_estado"("estado"); 166CREATE INDEX "tagente_estado_current_interval_idx" ON "tagente_estado"("current_interval"); 167CREATE INDEX "tagente_estado_running_by_idx" ON "tagente_estado"("running_by"); 168CREATE INDEX "tagente_estado_last_execution_try_idx" ON "tagente_estado"("last_execution_try"); 169 170-- Probably last_execution_try index is not useful and loads more than benefits 171 172-- id_modulo now uses tmodule 173-- --------------------------- 174-- 1 - Data server modules (agent related modules) 175-- 2 - Network server modules 176-- 4 - Plugin server 177-- 5 - Predictive server 178-- 6 - WMI server 179-- 7 - WEB Server (enteprise) 180 181CREATE TYPE type_tagente_modulo_wizard_level AS ENUM ('basic','advanced','nowizard'); 182CREATE TABLE "tagente_modulo" ( 183 "id_agente_modulo" SERIAL NOT NULL PRIMARY KEY, 184 "id_agente" INTEGER NOT NULL default 0, 185 "id_tipo_modulo" INTEGER NOT NULL default 0, 186 "descripcion" TEXT NOT NULL default '', 187 "extended_info" TEXT NOT NULL default '', 188 "nombre" TEXT NOT NULL default '', 189 "unit" TEXT default '', 190 "id_policy_module" INTEGER NOT NULL default 0, 191 "max" BIGINT NOT NULL default 0, 192 "min" BIGINT NOT NULL default 0, 193 "module_interval" INTEGER NOT NULL default 0, 194 "cron_interval" varchar(100) default '', 195 "module_ff_interval" INTEGER NOT NULL default 0, 196 "tcp_port" INTEGER NOT NULL default 0, 197 "tcp_send" TEXT default '', 198 "tcp_rcv" TEXT default '', 199 "snmp_community" varchar(100) default '', 200 "snmp_oid" varchar(255) default '0', 201 "ip_target" varchar(100) default '', 202 "id_module_group" INTEGER NOT NULL default 0, 203 "flag" SMALLINT NOT NULL default 1, 204 "id_modulo" INTEGER NOT NULL default 0, 205 "disabled" SMALLINT NOT NULL default 0, 206 "id_export" INTEGER NOT NULL default 0, 207 "plugin_user" text default '', 208 "plugin_pass" text default '', 209 "plugin_parameter" text, 210 "id_plugin" INTEGER default 0, 211 "post_process" DOUBLE PRECISION default NULL, 212 "prediction_module" BIGINT default 0, 213 "max_timeout" INTEGER default 0, 214 "max_retries" INTEGER default 0, 215 "custom_id" varchar(255) default '', 216 "history_data" SMALLINT default 1, 217 "min_warning" DOUBLE PRECISION default 0, 218 "max_warning" DOUBLE PRECISION default 0, 219 "str_warning" text, 220 "min_critical" DOUBLE PRECISION default 0, 221 "max_critical" DOUBLE PRECISION default 0, 222 "str_critical" text, 223 "min_ff_event" INTEGER default 0, 224 "delete_pending" SMALLINT NOT NULL default 0, 225 "policy_linked" SMALLINT NOT NULL default 0, 226 "policy_adopted" SMALLINT NOT NULL default 0, 227 "custom_string_1" text default '', 228 "custom_string_2" text default '', 229 "custom_string_3" text default '', 230 "custom_integer_1" INTEGER default 0, 231 "custom_integer_2" INTEGER default 0, 232 "wizard_level" type_tagente_modulo_wizard_level default 'nowizard', 233 "macros" TEXT default '', 234 "critical_instructions" TEXT default '', 235 "warning_instructions" TEXT default '', 236 "unknown_instructions" TEXT default '', 237 "quiet" SMALLINT NOT NULL default 0, 238 "critical_inverse" SMALLINT NOT NULL default 0, 239 "warning_inverse" SMALLINT NOT NULL default 0, 240 "id_category" INTEGER NOT NULL default 0, 241 "disabled_types_event" TEXT default '', 242 "module_macros" TEXT default '', 243 "min_ff_event_normal" INTEGER default 0, 244 "min_ff_event_warning" INTEGER default 0, 245 "min_ff_event_critical" INTEGER default 0, 246 "each_ff" SMALLINT default 0, 247 "ff_timeout" INTEGER default 0 248); 249CREATE INDEX "tagente_modulo_id_agente_idx" ON "tagente_modulo"("id_agente"); 250CREATE INDEX "tagente_modulo_id_tipo_modulo_idx" ON "tagente_modulo"("id_tipo_modulo"); 251CREATE INDEX "tagente_modulo_disabled_idx" ON "tagente_modulo"("disabled"); 252 253-- snmp_oid is also used for WMI query 254 255CREATE TABLE "tagent_access" ( 256 "id_agent" INTEGER NOT NULL default 0, 257 "utimestamp" BIGINT NOT NULL default 0 258); 259CREATE INDEX "tagent_access_id_agent_idx" ON "tagent_access"("id_agent"); 260CREATE INDEX "tagent_access_utimestamp_idx" ON "tagent_access"("utimestamp"); 261 262CREATE TABLE "talert_snmp" ( 263 "id_as" SERIAL NOT NULL PRIMARY KEY, 264 "id_alert" INTEGER NOT NULL default 0, 265 "al_field1" text NOT NULL default '', 266 "al_field2" text NOT NULL default '', 267 "al_field3" text NOT NULL default '', 268 "al_field4" text NOT NULL default '', 269 "al_field5" text NOT NULL default '', 270 "al_field6" text NOT NULL default '', 271 "al_field7" text NOT NULL default '', 272 "al_field8" text NOT NULL default '', 273 "al_field9" text NOT NULL default '', 274 "al_field10" text NOT NULL default '', 275 "description" varchar(255) default '', 276 "alert_type" SMALLINT NOT NULL default 0, 277 "agent" varchar(100) default '', 278 "custom_oid" text default '', 279 "oid" varchar(255) NOT NULL default '', 280 "time_threshold" INTEGER NOT NULL default 0, 281 "times_fired" SMALLINT NOT NULL default 0, 282 "last_fired" TIMESTAMP without time zone default '1970-01-01 00:00:00', 283 "max_alerts" INTEGER NOT NULL default 1, 284 "min_alerts" INTEGER NOT NULL default 1, 285 "internal_counter" INTEGER NOT NULL default 0, 286 "priority" INTEGER default 0, 287 "_snmp_f1_" text DEFAULT '', 288 "_snmp_f2_" text DEFAULT '', 289 "_snmp_f3_" text DEFAULT '', 290 "_snmp_f4_" text DEFAULT '', 291 "_snmp_f5_" text DEFAULT '', 292 "_snmp_f6_" text DEFAULT '', 293 "_snmp_f7_" text DEFAULT '', 294 "_snmp_f8_" text DEFAULT '', 295 "_snmp_f9_" text DEFAULT '', 296 "_snmp_f10_" text DEFAULT '', 297 "_snmp_f11_" text DEFAULT '', 298 "_snmp_f12_" text DEFAULT '', 299 "_snmp_f13_" text DEFAULT '', 300 "_snmp_f14_" text DEFAULT '', 301 "_snmp_f15_" text DEFAULT '', 302 "_snmp_f16_" text DEFAULT '', 303 "_snmp_f17_" text DEFAULT '', 304 "_snmp_f18_" text DEFAULT '', 305 "_snmp_f19_" text DEFAULT '', 306 "_snmp_f20_" text DEFAULT '', 307 "trap_type" INTEGER NOT NULL DEFAULT '-1', 308 "single_value" varchar(255) DEFAULT '', 309 "position" INTEGER NOT NULL default 0, 310 "id_group" INTEGER NOT NULL default 0, 311 "order_1" INTEGER NOT NULL default 1, 312 "order_2" INTEGER NOT NULL default 2, 313 "order_3" INTEGER NOT NULL default 3, 314 "order_4" INTEGER NOT NULL default 4, 315 "order_5" INTEGER NOT NULL default 5, 316 "order_6" INTEGER NOT NULL default 6, 317 "order_7" INTEGER NOT NULL default 7, 318 "order_8" INTEGER NOT NULL default 8, 319 "order_9" INTEGER NOT NULL default 9, 320 "order_10" INTEGER NOT NULL default 10, 321 "order_11" INTEGER NOT NULL default 11, 322 "order_12" INTEGER NOT NULL default 12, 323 "order_13" INTEGER NOT NULL default 13, 324 "order_14" INTEGER NOT NULL default 14, 325 "order_15" INTEGER NOT NULL default 15, 326 "order_16" INTEGER NOT NULL default 16, 327 "order_17" INTEGER NOT NULL default 17, 328 "order_18" INTEGER NOT NULL default 18, 329 "order_19" INTEGER NOT NULL default 19, 330 "order_20" INTEGER NOT NULL default 20 331); 332 333CREATE TABLE "talert_commands" ( 334 "id" SERIAL NOT NULL PRIMARY KEY, 335 "name" varchar(100) NOT NULL default '', 336 "command" text default '', 337 "description" text default '', 338 "internal" SMALLINT default 0, 339 "fields_descriptions" text default '', 340 "fields_values" text default '' 341); 342 343CREATE TABLE "talert_actions" ( 344 "id" SERIAL NOT NULL PRIMARY KEY, 345 "name" text default '', 346 "id_alert_command" INTEGER NOT NULL default 0 REFERENCES talert_commands("id") ON DELETE CASCADE ON UPDATE CASCADE, 347 "field1" text NOT NULL default '', 348 "field2" text NOT NULL default '', 349 "field3" text NOT NULL default '', 350 "field4" text NOT NULL default '', 351 "field5" text NOT NULL default '', 352 "field6" text NOT NULL default '', 353 "field7" text NOT NULL default '', 354 "field8" text NOT NULL default '', 355 "field9" text NOT NULL default '', 356 "field10" text NOT NULL default '', 357 "id_group" BIGINT NOT NULL default 0, 358 "action_threshold" BIGINT NOT NULL default 0, 359 "field1_recovery" text NOT NULL default '', 360 "field2_recovery" text NOT NULL default '', 361 "field3_recovery" text NOT NULL default '', 362 "field4_recovery" text NOT NULL default '', 363 "field5_recovery" text NOT NULL default '', 364 "field6_recovery" text NOT NULL default '', 365 "field7_recovery" text NOT NULL default '', 366 "field8_recovery" text NOT NULL default '', 367 "field9_recovery" text NOT NULL default '', 368 "field10_recovery" text NOT NULL default '' 369); 370 371CREATE TYPE type_talert_templates_alert_template AS ENUM ('regex', 'max_min', 'max', 'min', 'equal', 'not_equal', 'warning', 'critical', 'onchange', 'unknown', 'always'); 372CREATE TYPE type_talert_templates_wizard_level AS ENUM ('basic','advanced','nowizard'); 373CREATE TABLE "talert_templates" ( 374 "id" SERIAL NOT NULL PRIMARY KEY, 375 "name" text default '', 376 "description" TEXT, 377 "id_alert_action" INTEGER REFERENCES talert_actions("id") ON DELETE SET NULL ON UPDATE CASCADE, 378 "field1" text NOT NULL default '', 379 "field2" text NOT NULL default '', 380 "field3" text NOT NULL default '', 381 "field4" text NOT NULL default '', 382 "field5" text NOT NULL default '', 383 "field6" text NOT NULL default '', 384 "field7" text NOT NULL default '', 385 "field8" text NOT NULL default '', 386 "field9" text NOT NULL default '', 387 "field10" text NOT NULL default '', 388 "type" type_talert_templates_alert_template, 389 "value" varchar(255) default '', 390 "matches_value" SMALLINT default 0, 391 "max_value" DOUBLE PRECISION default NULL, 392 "min_value" DOUBLE PRECISION default NULL, 393 "time_threshold" INTEGER NOT NULL default 0, 394 "max_alerts" INTEGER NOT NULL default 1, 395 "min_alerts" INTEGER NOT NULL default 0, 396 "time_from" TIME without time zone default '00:00:00', 397 "time_to" TIME without time zone default '00:00:00', 398 "monday" SMALLINT default 1, 399 "tuesday" SMALLINT default 1, 400 "wednesday" SMALLINT default 1, 401 "thursday" SMALLINT default 1, 402 "friday" SMALLINT default 1, 403 "saturday" SMALLINT default 1, 404 "sunday" SMALLINT default 1, 405 "recovery_notify" SMALLINT default 0, 406 "field1_recovery" text NOT NULL default '', 407 "field2_recovery" text NOT NULL default '', 408 "field3_recovery" text NOT NULL default '', 409 "field4_recovery" text NOT NULL default '', 410 "field5_recovery" text NOT NULL default '', 411 "field6_recovery" text NOT NULL default '', 412 "field7_recovery" text NOT NULL default '', 413 "field8_recovery" text NOT NULL default '', 414 "field9_recovery" text NOT NULL default '', 415 "field10_recovery" text NOT NULL default '', 416 "priority" INTEGER NOT NULL default 0, 417 "id_group" INTEGER NOT NULL default 0, 418 "special_day" SMALLINT default 0, 419 "wizard_level" type_talert_templates_wizard_level default 'nowizard' 420); 421CREATE INDEX "talert_templates_id_alert_action_idx" ON "talert_templates"("id_alert_action"); 422 423CREATE TABLE "talert_template_modules" ( 424 "id" SERIAL NOT NULL PRIMARY KEY, 425 "id_agent_module" INTEGER NOT NULL REFERENCES tagente_modulo("id_agente_modulo") ON DELETE CASCADE ON UPDATE CASCADE, 426 "id_alert_template" INTEGER NOT NULL REFERENCES talert_templates("id") ON DELETE CASCADE ON UPDATE CASCADE, 427 "id_policy_alerts" INTEGER NOT NULL default 0, 428 "internal_counter" INTEGER default 0, 429 "last_fired" BIGINT NOT NULL default 0, 430 "last_reference" BIGINT NOT NULL default 0, 431 "times_fired" INTEGER NOT NULL default 0, 432 "disabled" SMALLINT default 0, 433 "standby" SMALLINT default 0, 434 "priority" INTEGER default 0, 435 "force_execution" SMALLINT default 0 436); 437CREATE UNIQUE INDEX "talert_template_modules_id_agent_module_idx" ON "talert_template_modules"("id_agent_module"); 438 439CREATE TABLE "talert_template_module_actions" ( 440 "id" SERIAL NOT NULL PRIMARY KEY, 441 "id_alert_template_module" INTEGER NOT NULL REFERENCES talert_template_modules("id") ON DELETE CASCADE ON UPDATE CASCADE, 442 "id_alert_action" INTEGER NOT NULL REFERENCES talert_actions("id") ON DELETE CASCADE ON UPDATE CASCADE, 443 "fires_min" INTEGER NOT NULL default 0, 444 "fires_max" INTEGER NOT NULL default 0 445); 446 447CREATE TYPE type_talert_special_days_same_day AS ENUM ('monday','tuesday','wednesday','thursday','friday','saturday','sunday'); 448CREATE TABLE "talert_special_days" ( 449 "id" SERIAL NOT NULL PRIMARY KEY, 450 "id_group" INTEGER NOT NULL default 0, 451 "date" DATE NOT NULL default '0001-01-01', 452 "same_day" type_talert_special_days_same_day NOT NULL default 'sunday', 453 "description" TEXT 454); 455 456-- Priority : 0 - Maintance (grey) 457-- Priority : 1 - Low (green) 458-- Priority : 2 - Normal (blue) 459-- Priority : 3 - Warning (yellow) 460-- Priority : 4 - Critical (red) 461CREATE TABLE "tattachment" ( 462 "id_attachment" SERIAL NOT NULL PRIMARY KEY, 463 "id_incidencia" INTEGER NOT NULL default 0, 464 "id_usuario" varchar(60) NOT NULL default '', 465 "filename" varchar(255) NOT NULL default '', 466 "description" varchar(150) default '', 467 "size" BIGINT NOT NULL default 0 468); 469 470CREATE TABLE "tconfig" ( 471 "id_config" SERIAL NOT NULL PRIMARY KEY, 472 "token" varchar(100) NOT NULL default '', 473 "value" text NOT NULL default '' 474); 475 476CREATE TABLE "tconfig_os" ( 477 "id_os" SERIAL NOT NULL PRIMARY KEY, 478 "name" varchar(100) NOT NULL default '', 479 "description" varchar(250) default '', 480 "icon_name" varchar(100) default '' 481); 482 483-- --------------------------------------------------------------------- 484-- Table `tevento` 485-- --------------------------------------------------------------------- 486CREATE TYPE type_tevento_event AS ENUM ('going_unknown','unknown','alert_fired','alert_recovered','alert_ceased','alert_manual_validation','recon_host_detected','system','error','new_agent','going_up_warning','going_up_critical','going_down_warning','going_down_normal','going_down_critical','going_up_normal', 'configuration_change'); 487CREATE TABLE "tevento" ( 488 "id_evento" BIGSERIAL NOT NULL PRIMARY KEY, 489 "id_agente" INTEGER NOT NULL default 0, 490 "id_usuario" varchar(100) NOT NULL default '0', 491 "id_grupo" INTEGER NOT NULL default 0, 492 "estado" INTEGER NOT NULL default 0, 493 "timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00', 494 "evento" text NOT NULL default '', 495 "utimestamp" BIGINT NOT NULL default 0, 496 "event_type" type_tevento_event default 'unknown', 497 "id_agentmodule" INTEGER NOT NULL default 0, 498 "id_alert_am" INTEGER NOT NULL default 0, 499 "criticity" INTEGER NOT NULL default 0, 500 "user_comment" text NOT NULL, 501 "tags" text NOT NULL, 502 "source" text NOT NULL default '', 503 "id_extra" text NOT NULL default '', 504 "critical_instructions" TEXT default '', 505 "warning_instructions" TEXT default '', 506 "unknown_instructions" TEXT default '', 507 "owner_user" varchar(100) NOT NULL default '0', 508 "ack_utimestamp" BIGINT NOT NULL default 0, 509 "custom_data" text NOT NULL 510); 511CREATE INDEX "tevento_id_1_idx" ON "tevento"("id_agente", "id_evento"); 512CREATE INDEX "tevento_id_2_idx" ON "tevento"("utimestamp", "id_evento"); 513CREATE INDEX "tevento_id_agentmodule_idx" ON "tevento"("id_agentmodule"); 514 515-- --------------------------------------------------------------------- 516-- Table `tgrupo` 517-- --------------------------------------------------------------------- 518-- Criticity: 0 - Maintance (grey) 519-- Criticity: 1 - Informational (blue) 520-- Criticity: 2 - Normal (green) (status 0) 521-- Criticity: 3 - Warning (yellow) (status 2) 522-- Criticity: 4 - Critical (red) (status 1) 523CREATE TABLE "tgrupo" ( 524 "id_grupo" SERIAL NOT NULL PRIMARY KEY, 525 "nombre" text NOT NULL default '', 526 "icon" varchar(50) default 'world', 527 "parent" INTEGER NOT NULL default 0, 528 "propagate" SMALLINT default 0, 529 "disabled" SMALLINT default 0, 530 "custom_id" varchar(255) default '', 531 "id_skin" INTEGER NOT NULL DEFAULT 0, 532 "description" text, 533 "contact" text, 534 "other" text 535); 536 537-- --------------------------------------------------------------------- 538-- Table `tincidencia` 539-- --------------------------------------------------------------------- 540CREATE TABLE "tincidencia" ( 541 "id_incidencia" BIGSERIAL NOT NULL PRIMARY KEY, 542 "inicio" TIMESTAMP without time zone default '1970-01-01 00:00:00', 543 "cierre" TIMESTAMP without time zone default '1970-01-01 00:00:00', 544 "titulo" text NOT NULL default '', 545 "descripcion" text NOT NULL, 546 "id_usuario" varchar(60) NOT NULL default '', 547 "origen" varchar(100) NOT NULL default '', 548 "estado" INTEGER NOT NULL default 0, 549 "prioridad" INTEGER NOT NULL default 0, 550 "id_grupo" INTEGER NOT NULL default 0, 551 "actualizacion" TIMESTAMP without time zone default CURRENT_TIMESTAMP, 552 "id_creator" varchar(60) default NULL, 553 "id_lastupdate" varchar(60) default NULL, 554 "id_agente_modulo" BIGINT NOT NULL, 555 "notify_email" INTEGER NOT NULL default 0, 556 "id_agent" INTEGER NULL default 0 557); 558CREATE INDEX "tincidencia_id_1_idx" ON "tincidencia"("id_usuario","id_incidencia"); 559CREATE INDEX "tincidencia_id_agente_modulo_idx" ON "tincidencia"("id_agente_modulo"); 560--This function is for to tranlate "on update CURRENT_TIMESTAMP" of MySQL. 561 --It is in only one line because the parser of Pandora installer execute the code at the end with ; 562CREATE OR REPLACE FUNCTION update_tincidencia_actualizacion() RETURNS TRIGGER AS $$ BEGIN NEW.actualizacion = now(); RETURN NEW; END; $$ language 'plpgsql'; 563CREATE TRIGGER trigger_tincidencia_actualizacion BEFORE UPDATE ON tincidencia FOR EACH ROW EXECUTE PROCEDURE update_tincidencia_actualizacion(); 564 565-- --------------------------------------------------------------------- 566-- Table `tlanguage` 567-- --------------------------------------------------------------------- 568CREATE TABLE "tlanguage" ( 569 "id_language" varchar(6) NOT NULL default '', 570 "name" varchar(100) NOT NULL default '' 571); 572 573-- --------------------------------------------------------------------- 574-- Table `tlink` 575-- --------------------------------------------------------------------- 576CREATE TABLE "tlink" ( 577 "id_link" SERIAL NOT NULL PRIMARY KEY, 578 "name" varchar(100) NOT NULL default '', 579 "link" varchar(255) NOT NULL default '' 580); 581 582-- --------------------------------------------------------------------- 583-- Table `tmensajes` 584-- --------------------------------------------------------------------- 585CREATE TABLE "tmensajes" ( 586 "id_mensaje" SERIAL NOT NULL PRIMARY KEY, 587 "id_usuario_origen" varchar(60) NOT NULL default '', 588 "id_usuario_destino" varchar(60) NOT NULL default '', 589 "mensaje" TEXT NOT NULL, 590 "timestamp" BIGINT NOT NULL default 0, 591 "subject" varchar(255) NOT NULL default '', 592 "estado" INTEGER NOT NULL default 0 593); 594 595-- --------------------------------------------------------------------- 596-- Table `tmodule_group` 597-- --------------------------------------------------------------------- 598CREATE TABLE "tmodule_group" ( 599 "id_mg" SERIAL NOT NULL PRIMARY KEY, 600 "name" varchar(150) NOT NULL default '' 601); 602 603-- This table was moved cause the "tmodule_relationship" will add 604-- a foreign key for the trecon_task(id_rt) 605-- --------------------------------------------------------------------- 606-- Table `trecon_task` 607-- --------------------------------------------------------------------- 608CREATE TABLE "trecon_task" ( 609 "id_rt" SERIAL NOT NULL PRIMARY KEY, 610 "name" varchar(100) NOT NULL default '', 611 "description" varchar(250) NOT NULL default '', 612 "subnet" TEXT default NULL, 613 "id_network_profile" INTEGER NOT NULL default 0, 614 "create_incident" INTEGER NOT NULL default 0, 615 "id_group" INTEGER NOT NULL default 1, 616 "utimestamp" BIGINT NOT NULL default 0, 617 "status" INTEGER NOT NULL default 0, 618 "interval_sweep" INTEGER NOT NULL default 0, 619 "id_recon_server" INTEGER NOT NULL default 0, 620 "id_os" INTEGER NOT NULL default 0, 621 "recon_ports" varchar(250) NOT NULL default '', 622 "snmp_community" varchar(64) NOT NULL default 'public', 623 "id_recon_script" INTEGER, 624 "field1" TEXT default NULL, 625 "field2" varchar(250) NOT NULL default '', 626 "field3" varchar(250) NOT NULL default '', 627 "field4" varchar(250) NOT NULL default '', 628 "os_detect" SMALLINT NOT NULL default 1, 629 "resolve_names" SMALLINT NOT NULL default 1, 630 "parent_detection" SMALLINT NOT NULL default 1, 631 "parent_recursion" SMALLINT NOT NULL default 1, 632 "disabled" SMALLINT NOT NULL default 1, 633 "macros" TEXT NOT NULL default '' 634); 635CREATE INDEX "trecon_task_id_recon_server_idx" ON "trecon_task"("id_recon_server"); 636 637-- ---------------------------------------------------------------------- 638-- Table `tmodule_relationship` 639-- ---------------------------------------------------------------------- 640CREATE TABLE "tmodule_relationship" ( 641 "id" SERIAL NOT NULL PRIMARY KEY, 642 "id_rt" INTEGER NOT NULL REFERENCES trecon_task("id_rt") 643 ON DELETE CASCADE, 644 "module_a" INTEGER NOT NULL REFERENCES tagente_modulo("id_agente_modulo") 645 ON DELETE CASCADE, 646 "module_b" INTEGER NOT NULL REFERENCES tagente_modulo("id_agente_modulo") 647 ON DELETE CASCADE, 648 "disable_update" SMALLINT NOT NULL default 0 649); 650 651-- --------------------------------------------------------------------- 652-- Table `tnetwork_component` 653-- --------------------------------------------------------------------- 654CREATE TYPE type_tlocal_component_wizard_level AS ENUM ('basic','advanced','nowizard'); 655CREATE TABLE "tnetwork_component" ( 656 "id_nc" SERIAL NOT NULL PRIMARY KEY, 657 "name" text NOT NULL, 658 "description" text NOT NULL default '', 659 "id_group" INTEGER NOT NULL default 1, 660 "type" INTEGER NOT NULL default 6, 661 "max" BIGINT NOT NULL default 0, 662 "min" BIGINT NOT NULL default 0, 663 "module_interval" BIGINT NOT NULL default 0, 664 "tcp_port" INTEGER NOT NULL default 0, 665 "tcp_send" text NOT NULL, 666 "tcp_rcv" text NOT NULL, 667 "snmp_community" varchar(255) NOT NULL default 'NULL', 668 "snmp_oid" varchar(400) NOT NULL, 669 "id_module_group" INTEGER NOT NULL default 0, 670 "id_modulo" INTEGER NOT NULL default 0, 671 "id_plugin" INTEGER default 0, 672 "plugin_user" text default '', 673 "plugin_pass" text default '', 674 "plugin_parameter" text, 675 "max_timeout" INTEGER default 0, 676 "max_retries" INTEGER default 0, 677 "history_data" SMALLINT default 1, 678 "min_warning" DOUBLE PRECISION default 0, 679 "max_warning" DOUBLE PRECISION default 0, 680 "str_warning" text, 681 "min_critical" DOUBLE PRECISION default 0, 682 "max_critical" DOUBLE PRECISION default 0, 683 "str_critical" text, 684 "min_ff_event" INTEGER default 0, 685 "custom_string_1" text default '', 686 "custom_string_2" text default '', 687 "custom_string_3" text default '', 688 "custom_integer_1" INTEGER default 0, 689 "custom_integer_2" INTEGER default 0, 690 "post_process" DOUBLE PRECISION default 0, 691 "unit" TEXT default '', 692 "wizard_level" type_tlocal_component_wizard_level default 'nowizard', 693 "macros" TEXT default '', 694 "critical_instructions" TEXT default '', 695 "warning_instructions" TEXT default '', 696 "unknown_instructions" TEXT default '', 697 "critical_inverse" SMALLINT NOT NULL default 0, 698 "warning_inverse" SMALLINT NOT NULL default 0, 699 "id_category" INTEGER NOT NULL default 0, 700 "tags" text NOT NULL, 701 "disabled_types_event" TEXT default '', 702 "module_macros" TEXT default '', 703 "min_ff_event_normal" INTEGER default 0, 704 "min_ff_event_warning" INTEGER default 0, 705 "min_ff_event_critical" INTEGER default 0, 706 "each_ff" SMALLINT default 0 707); 708 709-- --------------------------------------------------------------------- 710-- Table `tnetwork_component_group` 711-- --------------------------------------------------------------------- 712CREATE TABLE "tnetwork_component_group" ( 713 "id_sg" SERIAL NOT NULL PRIMARY KEY, 714 "name" varchar(200) NOT NULL default '', 715 "parent" BIGINT NOT NULL default 0 716); 717 718-- --------------------------------------------------------------------- 719-- Table `tnetwork_profile` 720-- --------------------------------------------------------------------- 721CREATE TABLE "tnetwork_profile" ( 722 "id_np" SERIAL NOT NULL PRIMARY KEY, 723 "name" varchar(100) NOT NULL default '', 724 "description" varchar(250) default '' 725); 726 727-- --------------------------------------------------------------------- 728-- Table `tnetwork_profile_component` 729-- --------------------------------------------------------------------- 730CREATE TABLE "tnetwork_profile_component" ( 731 "id_nc" BIGINT NOT NULL default 0, 732 "id_np" BIGINT NOT NULL default 0 733); 734CREATE INDEX "tnetwork_profile_id_np_idx" ON "tnetwork_profile_component"("id_np"); 735 736-- --------------------------------------------------------------------- 737-- Table `tnota` 738-- --------------------------------------------------------------------- 739CREATE TABLE "tnota" ( 740 "id_nota" BIGSERIAL NOT NULL PRIMARY KEY, 741 "id_incident" BIGINT NOT NULL, 742 "id_usuario" varchar(100) NOT NULL default '0', 743 "timestamp" TIMESTAMP without time zone default CURRENT_TIMESTAMP, 744 "nota" TEXT NOT NULL 745); 746CREATE INDEX "tnota_id_incident_idx" ON "tnota"("id_incident"); 747 748-- --------------------------------------------------------------------- 749-- Table `torigen` 750-- --------------------------------------------------------------------- 751CREATE TABLE "torigen" ( 752 "origen" varchar(100) NOT NULL default '' 753); 754 755-- --------------------------------------------------------------------- 756-- Table `tperfil` 757-- --------------------------------------------------------------------- 758CREATE TABLE "tperfil" ( 759 "id_perfil" SERIAL NOT NULL PRIMARY KEY, 760 "name" TEXT NOT NULL default '', 761 "incident_edit" SMALLINT NOT NULL default 0, 762 "incident_view" SMALLINT NOT NULL default 0, 763 "incident_management" SMALLINT NOT NULL default 0, 764 "agent_view" SMALLINT NOT NULL default 0, 765 "agent_edit" SMALLINT NOT NULL default 0, 766 "alert_edit" SMALLINT NOT NULL default 0, 767 "user_management" SMALLINT NOT NULL default 0, 768 "db_management" SMALLINT NOT NULL default 0, 769 "alert_management" SMALLINT NOT NULL default 0, 770 "pandora_management" SMALLINT NOT NULL default 0, 771 "report_view" SMALLINT NOT NULL default 0, 772 "report_edit" SMALLINT NOT NULL default 0, 773 "report_management" SMALLINT NOT NULL default 0, 774 "event_view" SMALLINT NOT NULL default 0, 775 "event_edit" SMALLINT NOT NULL default 0, 776 "event_management" SMALLINT NOT NULL default 0, 777 "agent_disable" SMALLINT NOT NULL default 0, 778 "map_view" SMALLINT NOT NULL default 0, 779 "map_edit" SMALLINT NOT NULL default 0, 780 "map_management" SMALLINT NOT NULL default 0, 781 "vconsole_view" SMALLINT NOT NULL default 0, 782 "vconsole_edit" SMALLINT NOT NULL default 0, 783 "vconsole_management" SMALLINT NOT NULL default 0 784); 785 786-- --------------------------------------------------------------------- 787-- Table `trecon_script` 788-- --------------------------------------------------------------------- 789CREATE TABLE "trecon_script" ( 790 "id_recon_script" SERIAL NOT NULL PRIMARY KEY, 791 "name" varchar(100) default '', 792 "description" TEXT default NULL, 793 "script" varchar(250) default '', 794 "macros" TEXT NOT NULL default '' 795); 796 797CREATE TABLE "tserver" ( 798 "id_server" SERIAL NOT NULL PRIMARY KEY, 799 "name" varchar(100) NOT NULL default '', 800 "ip_address" varchar(100) NOT NULL default '', 801 "status" INTEGER NOT NULL default 0, 802 "laststart" TIMESTAMP without time zone default '1970-01-01 00:00:00', 803 "keepalive" TIMESTAMP without time zone default '1970-01-01 00:00:00', 804 "snmp_server" INTEGER NOT NULL default 0, 805 "network_server" INTEGER NOT NULL default 0, 806 "data_server" INTEGER NOT NULL default 0, 807 "master" INTEGER NOT NULL default 0, 808 "checksum" INTEGER NOT NULL default 0, 809 "description" varchar(255) default NULL, 810 "recon_server" INTEGER NOT NULL default 0, 811 "version" varchar(20) NOT NULL default '', 812 "plugin_server" INTEGER NOT NULL default 0, 813 "prediction_server" INTEGER NOT NULL default 0, 814 "wmi_server" INTEGER NOT NULL default 0, 815 "export_server" INTEGER NOT NULL default 0, 816 "server_type" INTEGER NOT NULL default 0, 817 "queued_modules" INTEGER NOT NULL default 0, 818 "threads" INTEGER NOT NULL default 0, 819 "lag_time" INTEGER NOT NULL default 0, 820 "lag_modules" INTEGER NOT NULL default 0, 821 "total_modules_running" INTEGER NOT NULL default 0, 822 "my_modules" INTEGER NOT NULL default 0, 823 "stat_utimestamp" BIGINT NOT NULL default 0 824); 825CREATE INDEX "tserver_name_idx" ON "tserver"("name"); 826CREATE INDEX "tserver_keepalive_idx" ON "tserver"("keepalive"); 827CREATE INDEX "tserver_status_idx" ON "tserver"("status"); 828 829-- server types: 830-- 0 data 831-- 1 network 832-- 2 snmp trap console 833-- 3 recon 834-- 4 plugin 835-- 5 prediction 836-- 6 wmi 837-- 7 export 838-- 8 inventory 839-- 9 web 840-- TODO: drop 2.x xxxx_server fields, unused since server_type exists. 841 842CREATE TABLE "tsesion" ( 843 "id_sesion" BIGSERIAL NOT NULL PRIMARY KEY, 844 "id_usuario" varchar(60) NOT NULL default '0', 845 "ip_origen" varchar(100) NOT NULL default '', 846 "accion" varchar(100) NOT NULL default '', 847 "descripcion" text NOT NULL default '', 848 "fecha" TIMESTAMP without time zone default '1970-01-01 00:00:00', 849 "utimestamp" BIGINT NOT NULL default 0 850); 851CREATE INDEX "tsesion_utimestamp_idx" ON "tsesion"("utimestamp"); 852CREATE INDEX "tsesion_id_usuario_idx" ON "tsesion"("id_usuario"); 853 854-- ----------------------------------------------------- 855-- Table `ttipo_modulo` 856-- ----------------------------------------------------- 857CREATE TABLE "ttipo_modulo" ( 858 "id_tipo" SERIAL NOT NULL PRIMARY KEY, 859 "nombre" varchar(100) NOT NULL default '', 860 "categoria" INTEGER NOT NULL default 0, 861 "descripcion" varchar(100) NOT NULL default '', 862 "icon" varchar(100) default NULL 863); 864 865-- ----------------------------------------------------- 866-- Table `ttrap` 867-- ----------------------------------------------------- 868CREATE TABLE "ttrap" ( 869 "id_trap" BIGSERIAL NOT NULL PRIMARY KEY, 870 "source" varchar(50) NOT NULL default '', 871 "oid" text NOT NULL default '', 872 "oid_custom" text default '', 873 "type" INTEGER NOT NULL default 0, 874 "type_custom" varchar(100) default '', 875 "value" text default '', 876 "value_custom" text default '', 877 "alerted" SMALLINT NOT NULL default 0, 878 "status" SMALLINT NOT NULL default 0, 879 "id_usuario" varchar(150) default '', 880 "timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00', 881 "priority" INTEGER NOT NULL default 2, 882 "text" varchar(255) default '', 883 "description" varchar(255) default '', 884 "severity" INTEGER NOT NULL default 2 885); 886 887-- ----------------------------------------------------- 888-- Table `tusuario` 889-- ----------------------------------------------------- 890CREATE TYPE type_tusuario_metaconsole_access AS ENUM ('basic','advanced'); 891CREATE TABLE "tusuario" ( 892 "id_user" varchar(60) NOT NULL PRIMARY KEY, 893 "fullname" varchar(255) NOT NULL, 894 "firstname" varchar(255) NOT NULL, 895 "lastname" varchar(255) NOT NULL, 896 "middlename" varchar(255) NOT NULL default '', 897 "password" varchar(45) default NULL, 898 "comments" varchar(200) default NULL, 899 "last_connect" BIGINT NOT NULL default 0, 900 "registered" BIGINT NOT NULL default 0, 901 "email" varchar(100) default NULL, 902 "phone" varchar(100) default NULL, 903 "is_admin" SMALLINT NOT NULL default 0, 904 "language" varchar(10) default NULL, 905 "timezone" varchar(50) default '', 906 "block_size" INTEGER NOT NULL default 20, 907 "flash_chart" INTEGER NOT NULL default 1, 908 "id_skin" INTEGER NOT NULL DEFAULT 0, 909 "disabled" INTEGER NOT NULL default 0, 910 "shortcut" SMALLINT DEFAULT 0, 911 "shortcut_data" text default '', 912 "section" varchar(255) NOT NULL DEFAULT '', 913 "data_section" varchar(255) NOT NULL DEFAULT '', 914 "force_change_pass" SMALLINT NOT NULL default 0, 915 "last_pass_change" BIGINT NOT NULL default 0, 916 "last_failed_login" BIGINT NOT NULL default 0, 917 "failed_attempt" INTEGER NOT NULL DEFAULT 0, 918 "login_blocked" SMALLINT NOT NULL default 0, 919 "not_login" SMALLINT NOT NULL default 0, 920 "metaconsole_agents_manager" SMALLINT DEFAULT 0, 921 "metaconsole_assigned_server" INTEGER NOT NULL default 0, 922 "metaconsole_access_node" SMALLINT DEFAULT 0, 923 "metaconsole_access" type_tusuario_metaconsole_access default 'basic', 924 "strict_acl" SMALLINT DEFAULT 0 925); 926 927-- ----------------------------------------------------- 928-- Table `tusuario_perfil` 929-- ----------------------------------------------------- 930CREATE TABLE "tusuario_perfil" ( 931 "id_up" BIGSERIAL NOT NULL PRIMARY KEY, 932 "id_usuario" varchar(100) NOT NULL default '', 933 "id_perfil" INTEGER NOT NULL default 0, 934 "id_grupo" INTEGER NOT NULL default 0, 935 "assigned_by" varchar(100) NOT NULL default '', 936 "id_policy" INTEGER DEFAULT 0 NOT NULL, 937 "tags" text NOT NULL 938); 939 940-- ---------------------------------------------------------------------- 941-- Table `tuser_double_auth` 942-- ---------------------------------------------------------------------- 943CREATE TABLE "tuser_double_auth" ( 944 "id" SERIAL NOT NULL PRIMARY KEY, 945 "id_user" varchar(60) NOT NULL UNIQUE REFERENCES "tusuario"("id_user") ON DELETE CASCADE, 946 "secret" varchar(20) NOT NULL 947); 948 949-- ----------------------------------------------------- 950-- Table `tnews` 951-- ----------------------------------------------------- 952CREATE TABLE "tnews" ( 953 "id_news" SERIAL NOT NULL PRIMARY KEY, 954 "author" varchar(255) NOT NULL DEFAULT '', 955 "subject" varchar(255) NOT NULL DEFAULT '', 956 "text" TEXT NOT NULL, 957 "timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00', 958 "id_group" INTEGER NOT NULL default 0, 959 "modal" SMALLINT DEFAULT 0, 960 "expire" SMALLINT DEFAULT 0, 961 "expire_timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00' 962); 963 964-- ----------------------------------------------------- 965-- Table `tgraph` 966-- ----------------------------------------------------- 967CREATE TABLE "tgraph" ( 968 "id_graph" SERIAL NOT NULL PRIMARY KEY, 969 "id_user" varchar(100) NOT NULL default '', 970 "name" varchar(150) NOT NULL default '', 971 "description" TEXT NOT NULL, 972 "period" INTEGER NOT NULL default 0, 973 "width" INTEGER NOT NULL default 0, 974 "height" INTEGER NOT NULL default 0, 975 "private" SMALLINT NOT NULL default 0, 976 "events" SMALLINT NOT NULL default 0, 977 "stacked" SMALLINT NOT NULL default 0, 978 "id_group" BIGINT NOT NULL default 0, 979 "id_graph_template" INTEGER NOT NULL default 0 980); 981 982-- ----------------------------------------------------- 983-- Table `tgraph_source` 984-- ----------------------------------------------------- 985CREATE TABLE "tgraph_source" ( 986 "id_gs" SERIAL NOT NULL PRIMARY KEY, 987 "id_graph" BIGINT NOT NULL default 0, 988 "id_agent_module" BIGINT NOT NULL default 0, 989 "weight" DOUBLE PRECISION default 0 990); 991 992-- ----------------------------------------------------- 993-- Table "treport" 994-- ----------------------------------------------------- 995CREATE TABLE "treport" ( 996 "id_report" SERIAL NOT NULL PRIMARY KEY, 997 "id_user" varchar(100) NOT NULL default '', 998 "name" varchar(150) NOT NULL default '', 999 "description" TEXT NOT NULL, 1000 "private" SMALLINT NOT NULL default 0, 1001 "id_group" BIGINT NOT NULL default 0, 1002 "custom_logo" varchar(200) default NULL, 1003 "header" TEXT default NULL, 1004 "first_page" TEXT default NULL, 1005 "footer" TEXT default NULL, 1006 "custom_font" varchar(200) default NULL, 1007 "id_template" BIGINT NOT NULL default 0, 1008 "id_group_edit" BIGINT NOT NULL default 0, 1009 "metaconsole" SMALLINT DEFAULT 0, 1010 "non_interactive" SMALLINT DEFAULT 0 1011); 1012 1013-- ----------------------------------------------------- 1014-- Table "treport_content" 1015-- ----------------------------------------------------- 1016CREATE TABLE "treport_content" ( 1017 "id_rc" SERIAL NOT NULL PRIMARY KEY, 1018 "id_report" INTEGER NOT NULL default 0 REFERENCES treport("id_report") ON UPDATE CASCADE ON DELETE CASCADE, 1019 "id_gs" INTEGER default NULL, 1020 "id_agent_module" BIGINT default NULL, 1021 "type" varchar(30) default 'simple_graph', 1022 "period" BIGINT NOT NULL default 0, 1023 "order" BIGINT NOT NULL default 0, 1024 "name" varchar(150) NULL, 1025 "description" TEXT, 1026 "id_agent" BIGINT NOT NULL default 0, 1027 "text" TEXT default NULL, 1028 "external_source" TEXT default NULL, 1029 "treport_custom_sql_id" INTEGER default 0, 1030 "header_definition" TEXT default NULL, 1031 "column_separator" TEXT default NULL, 1032 "line_separator" TEXT default NULL, 1033 "time_from" TIME without time zone default '00:00:00', 1034 "time_to" TIME without time zone default '00:00:00', 1035 "monday" SMALLINT NOT NULL default 1, 1036 "tuesday" SMALLINT NOT NULL default 1, 1037 "wednesday" SMALLINT NOT NULL default 1, 1038 "thursday" SMALLINT NOT NULL default 1, 1039 "friday" SMALLINT NOT NULL default 1, 1040 "saturday" SMALLINT NOT NULL default 1, 1041 "sunday" SMALLINT NOT NULL default 1, 1042 "only_display_wrong" SMALLINT NOT NULL default 0, 1043 "top_n" INTEGER NOT NULL default 0, 1044 "top_n_value" INTEGER NOT NULL default 10, 1045 "exception_condition" INTEGER NOT NULL default 0, 1046 "exception_condition_value" DOUBLE PRECISION NOT NULL default 0, 1047 "show_resume" INTEGER NOT NULL default 0, 1048 "order_uptodown" INTEGER NOT NULL default 0, 1049 "show_graph" INTEGER NOT NULL default 0, 1050 "group_by_agent" INTEGER NOT NULL default 0, 1051 "style" TEXT NOT NULL DEFAULT '', 1052 "id_group" INTEGER NOT NULL default 0, 1053 "id_module_group" INTEGER NOT NULL default 0, 1054 "server_name" TEXT DEFAULT '' 1055); 1056 1057-- ----------------------------------------------------- 1058-- Table "treport_content_sla_combined" 1059-- ----------------------------------------------------- 1060CREATE TABLE "treport_content_sla_combined" ( 1061 "id" SERIAL NOT NULL PRIMARY KEY, 1062 "id_report_content" INTEGER NOT NULL REFERENCES treport_content("id_rc") ON UPDATE CASCADE ON DELETE CASCADE, 1063 "id_agent_module" INTEGER NOT NULL, 1064 "sla_max" DOUBLE PRECISION NOT NULL default 0, 1065 "sla_min" DOUBLE PRECISION NOT NULL default 0, 1066 "sla_limit" DOUBLE PRECISION NOT NULL default 0, 1067 "server_name" TEXT DEFAULT '' 1068); 1069 1070-- ----------------------------------------------------- 1071-- Table "treport_content_item" 1072-- ----------------------------------------------------- 1073CREATE TABLE "treport_content_item" ( 1074 "id" SERIAL NOT NULL PRIMARY KEY, 1075 "id_report_content" INTEGER NOT NULL REFERENCES treport_content("id_rc") ON UPDATE CASCADE ON DELETE CASCADE, 1076 "id_agent_module" INTEGER NOT NULL, 1077 "server_name" TEXT DEFAULT '', 1078 "operation" TEXT DEFAULT '' 1079); 1080 1081-- ----------------------------------------------------- 1082-- Table "treport_custom_sql" 1083-- ----------------------------------------------------- 1084CREATE TABLE "treport_custom_sql" ( 1085 "id" SERIAL NOT NULL PRIMARY KEY, 1086 "name" varchar(150) NOT NULL default '', 1087 "sql" TEXT default NULL 1088); 1089 1090-- ----------------------------------------------------- 1091-- Table "tlayout" 1092-- ----------------------------------------------------- 1093CREATE TABLE "tlayout" ( 1094 "id" SERIAL NOT NULL PRIMARY KEY, 1095 "name" varchar(50) NOT NULL, 1096 "id_group" INTEGER NOT NULL, 1097 "background" varchar(200) NOT NULL, 1098 "height" INTEGER NOT NULL default 0, 1099 "width" INTEGER NOT NULL default 0 1100); 1101 1102-- --------------------------------------------------------------------- 1103-- Table "tlayout_data" 1104-- --------------------------------------------------------------------- 1105CREATE TABLE "tlayout_data" ( 1106 "id" SERIAL NOT NULL PRIMARY KEY, 1107 "id_layout" INTEGER NOT NULL default 0, 1108 "pos_x" INTEGER NOT NULL default 0, 1109 "pos_y" INTEGER NOT NULL default 0, 1110 "height" INTEGER NOT NULL default 0, 1111 "width" INTEGER NOT NULL default 0, 1112 "label" TEXT default '', 1113 "image" varchar(200) DEFAULT '', 1114 "type" SMALLINT NOT NULL default 0, 1115 "period" INTEGER NOT NULL default 3600, 1116 "id_agente_modulo" BIGINT NOT NULL default 0, 1117 "id_agent" INTEGER NOT NULL default 0, 1118 "id_layout_linked" INTEGER NOT NULL default 0, 1119 "parent_item" INTEGER NOT NULL default 0, 1120 "enable_link" SMALLINT NOT NULL default 1, 1121 "id_metaconsole" INTEGER NOT NULL default 0, 1122 "id_group" INTEGER NOT NULL default 0, 1123 "id_custom_graph" INTEGER NOT NULL default 0, 1124 "border_width" INTEGER NOT NULL default 0, 1125 "border_color" varchar(200) DEFAULT '', 1126 "fill_color" varchar(200) DEFAULT '' 1127); 1128 1129-- --------------------------------------------------------------------- 1130-- Table "tplugin" 1131-- --------------------------------------------------------------------- 1132CREATE TABLE "tplugin" ( 1133 "id" SERIAL NOT NULL PRIMARY KEY, 1134 "name" varchar(200) NOT NULL, 1135 "description" TEXT, 1136 "max_timeout" INTEGER NOT NULL default 0, 1137 "max_retries" INTEGER NOT NULL default 0, 1138 "execute" varchar(250) NOT NULL, 1139 "net_dst_opt" varchar(50) default '', 1140 "net_port_opt" varchar(50) default '', 1141 "user_opt" varchar(50) default '', 1142 "pass_opt" varchar(50) default '', 1143 "plugin_type" SMALLINT NOT NULL default 0, 1144 "macros" TEXT default '', 1145 "parameters" TEXT default '' 1146); 1147 1148-- --------------------------------------------------------------------- 1149-- Table "tmodule" 1150-- --------------------------------------------------------------------- 1151CREATE TABLE "tmodule" ( 1152 "id_module" SERIAL NOT NULL PRIMARY KEY, 1153 "name" varchar(100) NOT NULL default '' 1154); 1155 1156-- --------------------------------------------------------------------- 1157-- Table "tserver_export" 1158-- --------------------------------------------------------------------- 1159CREATE TYPE type_tserver_export_connect_mode AS ENUM ('tentacle', 'ssh', 'local'); 1160CREATE TABLE "tserver_export" ( 1161 "id" SERIAL NOT NULL PRIMARY KEY, 1162 "name" varchar(100) NOT NULL default '', 1163 "preffix" varchar(100) NOT NULL default '', 1164 "interval" INTEGER NOT NULL default 300, 1165 "ip_server" varchar(100) NOT NULL default '', 1166 "connect_mode" type_tserver_export_connect_mode default 'local', 1167 "id_export_server" INTEGER default NULL , 1168 "user" varchar(100) NOT NULL default '', 1169 "pass" varchar(100) NOT NULL default '', 1170 "port" INTEGER NOT NULL default 0, 1171 "directory" varchar(100) NOT NULL default '', 1172 "options" varchar(100) NOT NULL default '', 1173 --Number of hours of diference with the server timezone 1174 "timezone_offset" SMALLINT NOT NULL default 0 1175); 1176 1177-- --------------------------------------------------------------------- 1178-- Table "tserver_export_data" 1179-- --------------------------------------------------------------------- 1180-- id_export_server is real pandora fms export server process that manages this server 1181-- id is the "destination" server to export 1182CREATE TABLE "tserver_export_data" ( 1183 "id" SERIAL NOT NULL PRIMARY KEY, 1184 "id_export_server" INTEGER NOT NULL default 0, 1185 "agent_name" varchar(100) NOT NULL default '', 1186 "module_name" varchar(100) NOT NULL default '', 1187 "module_type" varchar(100) NOT NULL default '', 1188 "data" varchar(255) default NULL, 1189 "timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00' 1190); 1191 1192-- --------------------------------------------------------------------- 1193-- Table "tplanned_downtime" 1194-- --------------------------------------------------------------------- 1195CREATE TABLE "tplanned_downtime" ( 1196 "id" BIGSERIAL NOT NULL PRIMARY KEY, 1197 "name" VARCHAR( 100 ) NOT NULL, 1198 "description" TEXT NOT NULL, 1199 "date_from" BIGINT NOT NULL default 0, 1200 "date_to" BIGINT NOT NULL default 0, 1201 "executed" SMALLINT NOT NULL default 0, 1202 "id_group" BIGINT NOT NULL default 0, 1203 "only_alerts" SMALLINT NOT NULL default 0, 1204 "monday" SMALLINT default 0, 1205 "tuesday" SMALLINT default 0, 1206 "wednesday" SMALLINT default 0, 1207 "thursday" SMALLINT default 0, 1208 "friday" SMALLINT default 0, 1209 "saturday" SMALLINT default 0, 1210 "sunday" SMALLINT default 0, 1211 "periodically_time_from" TIME default NULL, 1212 "periodically_time_to" TIME default NULL, 1213 "periodically_day_from" SMALLINT default NULL, 1214 "periodically_day_to" SMALLINT default NULL, 1215 "type_downtime" VARCHAR( 100 ) NOT NULL default 'disabled_agents_alerts', 1216 "type_execution" VARCHAR( 100 ) NOT NULL default 'once', 1217 "type_periodicity" VARCHAR( 100 ) NOT NULL default 'weekly', 1218 "id_user" varchar(100) NOT NULL default '0' 1219); 1220 1221-- --------------------------------------------------------------------- 1222-- Table "tplanned_downtime_agents" 1223-- --------------------------------------------------------------------- 1224CREATE TABLE "tplanned_downtime_agents" ( 1225 "id" BIGSERIAL NOT NULL PRIMARY KEY, 1226 "id_agent" BIGINT NOT NULL default 0, 1227 "id_downtime" BIGINT NOT NULL REFERENCES tplanned_downtime("id") ON DELETE CASCADE, 1228 "all_modules" SMALLINT default 1 1229); 1230 1231-- --------------------------------------------------------------------- 1232-- Table "tplanned_downtime_modules" 1233-- --------------------------------------------------------------------- 1234CREATE TABLE "tplanned_downtime_modules" ( 1235 "id" BIGSERIAL NOT NULL PRIMARY KEY, 1236 "id_agent" BIGINT NOT NULL default 0, 1237 "id_agent_module" INTEGER NOT NULL default 0, 1238 "id_downtime" BIGINT NOT NULL REFERENCES tplanned_downtime("id") ON DELETE CASCADE 1239); 1240 1241 1242-- GIS extension Tables 1243-- --------------------------------------------------------------------- 1244-- Table "tgis_data_history" 1245-- --------------------------------------------------------------------- 1246--Table to store historical GIS information of the agents 1247CREATE TABLE "tgis_data_history" ( 1248 --key of the table 1249 "id_tgis_data" SERIAL NOT NULL PRIMARY KEY, 1250 "longitude" DOUBLE PRECISION NOT NULL, 1251 "latitude" DOUBLE PRECISION NOT NULL, 1252 "altitude" DOUBLE PRECISION NOT NULL, 1253 --timestamp on wich the agente started to be in this position 1254 "start_timestamp" TIMESTAMP without time zone DEFAULT CURRENT_TIMESTAMP, 1255 --timestamp on wich the agent was placed for last time on this position 1256 "end_timestamp" TIMESTAMP without time zone default '1970-01-01 00:00:00', 1257 --description of the region correoponding to this placemnt 1258 "description" TEXT DEFAULT NULL, 1259 -- 0 to show that the position cames from the agent, 1 to show that the position was established manualy 1260 "manual_placement" SMALLINT NOT NULL default 0, 1261 -- Number of data packages received with this position from the start_timestampa to the_end_timestamp 1262 "number_of_packages" INTEGER NOT NULL default 1, 1263 --reference to the agent 1264 "tagente_id_agente" INTEGER NOT NULL 1265); 1266CREATE INDEX "tgis_data_history_start_timestamp_idx" ON "tgis_data_history"("start_timestamp"); 1267CREATE INDEX "tgis_data_history_end_timestamp_idx" ON "tgis_data_history"("end_timestamp"); 1268 1269-- --------------------------------------------------------------------- 1270-- Table "tgis_data_status" 1271-- --------------------------------------------------------------------- 1272--Table to store last GIS information of the agents 1273CREATE TABLE "tgis_data_status" ( 1274 --Reference to the agent 1275 "tagente_id_agente" INTEGER NOT NULL REFERENCES "tagente"("id_agente") ON DELETE CASCADE ON UPDATE NO ACTION, 1276 --Last received longitude 1277 "current_longitude" DOUBLE PRECISION NOT NULL, 1278 --Last received latitude 1279 "current_latitude" DOUBLE PRECISION NOT NULL, 1280 --Last received altitude 1281 "current_altitude" DOUBLE PRECISION NOT NULL, 1282 --Reference longitude to see if the agent has moved 1283 "stored_longitude" DOUBLE PRECISION NOT NULL, 1284 --Reference latitude to see if the agent has moved 1285 "stored_latitude" DOUBLE PRECISION NOT NULL, 1286 --Reference altitude to see if the agent has moved 1287 "stored_altitude" DOUBLE PRECISION DEFAULT NULL, 1288 --Number of data packages received with this position since start_timestampa 1289 "number_of_packages" INTEGER NOT NULL default 1, 1290 --Timestamp on wich the agente started to be in this position 1291 "start_timestamp" TIMESTAMP without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 1292 --0 to show that the position cames from the agent, 1 to show that the position was established manualy 1293 "manual_placement" SMALLINT NOT NULL default 0, 1294 --description of the region correoponding to this placemnt 1295 "description" TEXT NULL, 1296 PRIMARY KEY("tagente_id_agente") 1297); 1298CREATE INDEX "tgis_data_status_start_timestamp_idx" ON "tgis_data_status"("start_timestamp"); 1299CREATE INDEX "tgis_data_status_tagente_id_agente_idx" ON "tgis_data_status"("tagente_id_agente"); 1300 1301-- ----------------------------------------------------- 1302-- Table "tgis_map" 1303-- ----------------------------------------------------- 1304--Table containing information about a gis map 1305CREATE TABLE "tgis_map" ( 1306 --table identifier 1307 "id_tgis_map" SERIAL NOT NULL PRIMARY KEY, 1308 --Name of the map 1309 "map_name" VARCHAR(63) NOT NULL, 1310 --longitude of the center of the map when it\'s loaded 1311 "initial_longitude" DOUBLE PRECISION DEFAULT NULL, 1312 --latitude of the center of the map when it\'s loaded 1313 "initial_latitude" DOUBLE PRECISION DEFAULT NULL, 1314 --altitude of the center of the map when it\'s loaded 1315 "initial_altitude" DOUBLE PRECISION DEFAULT NULL, 1316 --Zoom level to show when the map is loaded. 1317 "zoom_level" SMALLINT NOT NULL default 1, 1318 --path on the server to the background image of the map 1319 "map_background" VARCHAR(127) DEFAULT NULL, 1320 --default longitude for the agents placed on the map 1321 "default_longitude" DOUBLE PRECISION DEFAULT NULL, 1322 --default latitude for the agents placed on the map 1323 "default_latitude" DOUBLE PRECISION DEFAULT NULL, 1324 --default altitude for the agents placed on the map 1325 "default_altitude" DOUBLE PRECISION DEFAULT NULL, 1326 --Group that owns the map 1327 "group_id" INTEGER NOT NULL default 0, 1328 --1 if this is the default map, 0 in other case 1329 "default_map" SMALLINT NOT NULL default 0 1330); 1331CREATE INDEX "tgis_map_tagente_map_name_idx" ON "tgis_map"("map_name"); 1332 1333-- ----------------------------------------------------- 1334-- Table "tgis_map_connection" 1335-- ----------------------------------------------------- 1336--Table to store the map connection information 1337CREATE TABLE "tgis_map_connection" ( 1338 --table id 1339 "id_tmap_connection" SERIAL NOT NULL PRIMARY KEY, 1340 --Name of the connection (name of the base layer) 1341 "conection_name" VARCHAR(45) DEFAULT NULL, 1342 --Type of map server to connect 1343 "connection_type" VARCHAR(45) DEFAULT NULL, 1344 --connection information (this can probably change to fit better the possible connection parameters) 1345 "conection_data" TEXT DEFAULT NULL, 1346 --Number of zoom levels available 1347 "num_zoom_levels" SMALLINT DEFAULT NULL, 1348 --Default Zoom Level for the connection 1349 "default_zoom_level" SMALLINT NOT NULL default 16, 1350 --default longitude for the agents placed on the map 1351 "default_longitude" DOUBLE PRECISION DEFAULT NULL, 1352 --default latitude for the agents placed on the map 1353 "default_latitude" DOUBLE PRECISION DEFAULT NULL, 1354 --default altitude for the agents placed on the map 1355 "default_altitude" DOUBLE PRECISION DEFAULT NULL, 1356 --longitude of the center of the map when it\'s loaded 1357 "initial_longitude" DOUBLE PRECISION DEFAULT NULL, 1358 --latitude of the center of the map when it\'s loaded 1359 "initial_latitude" DOUBLE PRECISION DEFAULT NULL, 1360 --altitude of the center of the map when it\'s loaded 1361 "initial_altitude" DOUBLE PRECISION DEFAULT NULL, 1362 --Group that owns the map 1363 "group_id" INTEGER NOT NULL default 0 1364); 1365 1366-- ----------------------------------------------------- 1367-- Table "tgis_map_has_tgis_map_connection" 1368-- ----------------------------------------------------- 1369--Table to asociate a connection to a gis map 1370CREATE TABLE "tgis_map_has_tgis_map_connection" ( 1371 --reference to tgis_map 1372 "tgis_map_id_tgis_map" INTEGER NOT NULL REFERENCES "tgis_map"("id_tgis_map") ON DELETE CASCADE ON UPDATE NO ACTION, 1373 --reference to tgis_map_connection 1374 "tgis_map_connection_id_tmap_connection" INTEGER NOT NULL REFERENCES "tgis_map_connection" ("id_tmap_connection") ON DELETE CASCADE ON UPDATE NO ACTION, 1375 --Last Modification Time of the Connection 1376 "modification_time" TIMESTAMP without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 1377 --Flag to mark the default map connection of a map 1378 "default_map_connection" SMALLINT NOT NULL default 0, 1379 PRIMARY KEY ("tgis_map_id_tgis_map", "tgis_map_connection_id_tmap_connection") 1380); 1381CREATE INDEX "tgis_map_has_tgis_map_connection_map_tgis_map_id_tgis_map_idx" ON "tgis_map_has_tgis_map_connection"("tgis_map_id_tgis_map"); 1382CREATE INDEX "tgis_map_has_tgis_map_connection_map_tgis_map_connection_id_tmap_connection_idx" ON "tgis_map_has_tgis_map_connection"("tgis_map_connection_id_tmap_connection"); 1383--This function is for to tranlate "ON UPDATE CURRENT_TIMESTAMP" of MySQL. 1384 --It is in only one line because the parser of Pandora installer execute the code at the end with ; 1385CREATE OR REPLACE FUNCTION update_tgis_map_has_tgis_map_connection_modification_time() RETURNS TRIGGER AS $$ BEGIN NEW.modification_time = now(); RETURN NEW; END; $$ language 'plpgsql'; 1386CREATE TRIGGER trigger_tgis_map_has_tgis_map_connection_modification_time BEFORE UPDATE ON tgis_map_has_tgis_map_connection FOR EACH ROW EXECUTE PROCEDURE update_tgis_map_has_tgis_map_connection_modification_time(); 1387 1388-- ----------------------------------------------------- 1389-- Table "tgis_map_layer" 1390-- ----------------------------------------------------- 1391--Table containing information about the map layers 1392CREATE TABLE "tgis_map_layer" ( 1393 --table id 1394 "id_tmap_layer" SERIAL NOT NULL PRIMARY KEY, 1395 --Name of the layer 1396 "layer_name" VARCHAR(45) NOT NULL, 1397 --True if the layer must be shown 1398 "view_layer" SMALLINT NOT NULL default 1, 1399 --Number of order of the layer in the layer stack, bigger means upper on the stack.\n 1400 "layer_stack_order" SMALLINT NOT NULL default 0, 1401 --reference to the map containing the layer 1402 "tgis_map_id_tgis_map" INTEGER NOT NULL default 0 REFERENCES "tgis_map"("id_tgis_map") ON DELETE CASCADE ON UPDATE NO ACTION, 1403 --reference to the group shown in the layer 1404 "tgrupo_id_grupo" BIGINT NOT NULL 1405); 1406CREATE INDEX "tgis_map_layer_id_tmap_layer_idx" ON "tgis_map_layer"("id_tmap_layer"); 1407 1408 1409-- ----------------------------------------------------- 1410-- Table "tgis_map_layer_has_tagente" 1411-- ----------------------------------------------------- 1412--Table to define wich agents are shown in a layer 1413CREATE TABLE "tgis_map_layer_has_tagente" ( 1414 "tgis_map_layer_id_tmap_layer" INTEGER NOT NULL REFERENCES "tgis_map_layer"("id_tmap_layer") ON DELETE CASCADE ON UPDATE NO ACTION, 1415 "tagente_id_agente" INTEGER NOT NULL REFERENCES "tagente"("id_agente") ON DELETE CASCADE ON UPDATE NO ACTION, 1416 PRIMARY KEY ("tgis_map_layer_id_tmap_layer", "tagente_id_agente") 1417); 1418CREATE INDEX "tgis_map_layer_has_tagente_tgis_map_layer_id_tmap_layer_idx" ON "tgis_map_layer_has_tagente"("tgis_map_layer_id_tmap_layer"); 1419CREATE INDEX "tgis_map_layer_has_tagente_tagente_id_agente_idx" ON "tgis_map_layer_has_tagente"("tagente_id_agente"); 1420 1421------------------------------------------------------------------------ 1422-- Table "tgroup_stat" 1423------------------------------------------------------------------------ 1424--Table to store global system stats per group 1425CREATE TABLE "tgroup_stat" ( 1426 "id_group" INTEGER NOT NULL default 0 PRIMARY KEY, 1427 "modules" INTEGER NOT NULL default 0, 1428 "normal" INTEGER NOT NULL default 0, 1429 "critical" INTEGER NOT NULL default 0, 1430 "warning" INTEGER NOT NULL default 0, 1431 "unknown" INTEGER NOT NULL default 0, 1432 "non-init" INTEGER NOT NULL default 0, 1433 "alerts" INTEGER NOT NULL default 0, 1434 "alerts_fired" INTEGER NOT NULL default 0, 1435 "agents" INTEGER NOT NULL default 0, 1436 "agents_unknown" INTEGER NOT NULL default 0, 1437 "utimestamp" INTEGER NOT NULL default 0 1438); 1439 1440------------------------------------------------------------------------ 1441-- Table "tnetwork_map" 1442------------------------------------------------------------------------ 1443CREATE TABLE "tnetwork_map" ( 1444 "id_networkmap" SERIAL NOT NULL PRIMARY KEY, 1445 "id_user" VARCHAR(60) NOT NULL, 1446 "name" VARCHAR(100) NOT NULL, 1447 "type" VARCHAR(20) NOT NULL, 1448 "layout" VARCHAR(20) NOT NULL, 1449 "nooverlap" SMALLINT NOT NULL default 0, 1450 "simple" SMALLINT NOT NULL default 0, 1451 "regenerate" SMALLINT NOT NULL default 1, 1452 "font_size" INTEGER NOT NULL default 12, 1453 "id_group" INTEGER NOT NULL default 0, 1454 "id_module_group" INTEGER NOT NULL default 0, 1455 "id_policy" INTEGER NOT NULL default 0, 1456 "depth" VARCHAR(20) NOT NULL, 1457 "only_modules_with_alerts" SMALLINT NOT NULL default 0, 1458 "hide_policy_modules" SMALLINT NOT NULL default 0, 1459 "zoom" DOUBLE PRECISION default 1, 1460 "distance_nodes" DOUBLE PRECISION default 2.5, 1461 "center" INTEGER NOT NULL default 0, 1462 "contracted_nodes" TEXT, 1463 "show_snmp_modules" SMALLINT NOT NULL default 0, 1464 "text_filter" VARCHAR(100) DEFAULT '', 1465 "dont_show_subgroups" INTEGER NOT NULL default 0, 1466 "pandoras_children" INTEGER NOT NULL default 0, 1467 "show_modules" INTEGER NOT NULL default 0, 1468 "show_groups" INTEGER NOT NULL default 0, 1469 "id_agent" INTEGER NOT NULL default 0, 1470 "server_name" VARCHAR(100) NOT NULL, 1471 "show_modulegroup" INTEGER NOT NULL default 0, 1472 "l2_network" SMALLINT NOT NULL default 0, 1473 "id_tag" INTEGER default 0, 1474 "store_group" INTEGER default 0 1475); 1476 1477------------------------------------------------------------------------ 1478-- Table "tsnmp_filter" 1479------------------------------------------------------------------------ 1480CREATE TABLE "tsnmp_filter" ( 1481 "id_snmp_filter" SERIAL NOT NULL PRIMARY KEY, 1482 "description" varchar(255) default '', 1483 "filter" varchar(255) default '' 1484); 1485 1486------------------------------------------------------------------------ 1487-- Table "tagent_custom_fields" 1488------------------------------------------------------------------------ 1489CREATE TABLE "tagent_custom_fields" ( 1490 "id_field" SERIAL NOT NULL PRIMARY KEY, 1491 "name" varchar(45) NOT NULL default '', 1492 "display_on_front" SMALLINT NOT NULL default 0 1493); 1494 1495-- --------------------------------------------------------------------- 1496-- Table "tagent_custom_data" 1497-- --------------------------------------------------------------------- 1498CREATE TABLE "tagent_custom_data" ( 1499 "id_field" INTEGER NOT NULL REFERENCES tagent_custom_fields("id_field") ON UPDATE CASCADE ON DELETE CASCADE, 1500 "id_agent" INTEGER NOT NULL REFERENCES tagente("id_agente") ON UPDATE CASCADE ON DELETE CASCADE, 1501 "description" text default '', 1502 PRIMARY KEY ("id_field", "id_agent") 1503); 1504 1505-- --------------------------------------------------------------------- 1506-- Table "ttag" 1507-- --------------------------------------------------------------------- 1508 1509CREATE TABLE "ttag" ( 1510 "id_tag" SERIAL NOT NULL PRIMARY KEY, 1511 "name" VARCHAR(100) NOT NULL default '', 1512 "description" text NOT NULL default '', 1513 "url" text NOT NULL default '', 1514 "email" text NULL, 1515 "phone" text NULL 1516); 1517 1518-- --------------------------------------------------------------------- 1519-- Table "ttag_module" 1520-- --------------------------------------------------------------------- 1521 1522CREATE TABLE "ttag_module" ( 1523 "id_tag" INTEGER NOT NULL, 1524 "id_agente_modulo" INTEGER NOT NULL DEFAULT 0, 1525 "id_policy_module" INTEGER NOT NULL DEFAULT 0, 1526 PRIMARY KEY (id_tag, id_agente_modulo) 1527); 1528 1529CREATE INDEX "ttag_module_id_ag_modulo_idx" ON "ttag_module"("id_agente_modulo"); 1530 1531-- ----------------------------------------------------- 1532-- Table "ttag_policy_module" 1533-- ----------------------------------------------------- 1534 1535CREATE TABLE "ttag_policy_module" ( 1536 "id_tag" INTEGER NOT NULL, 1537 "id_policy_module" INTEGER NOT NULL DEFAULT 0, 1538 PRIMARY KEY (id_tag, id_policy_module) 1539); 1540 1541CREATE INDEX "ttag_poli_mod_id_pol_mo_idx" ON "ttag_policy_module"("id_policy_module"); 1542 1543-- ----------------------------------------------------- 1544-- Table `tnetflow_filter` 1545-- ----------------------------------------------------- 1546CREATE TABLE "tnetflow_filter" ( 1547 "id_sg" SERIAL NOT NULL PRIMARY KEY, 1548 "id_name" varchar(600) NOT NULL default '', 1549 "id_group" INTEGER, 1550 "ip_dst" TEXT NOT NULL, 1551 "ip_src" TEXT NOT NULL, 1552 "dst_port" TEXT NOT NULL, 1553 "src_port" TEXT NOT NULL, 1554 "advanced_filter" TEXT NOT NULL, 1555 "filter_args" TEXT NOT NULL, 1556 "aggregate" varchar(60), 1557 "output" varchar(60) 1558); 1559 1560-- ----------------------------------------------------- 1561-- Table `tnetflow_report` 1562-- ----------------------------------------------------- 1563CREATE TABLE "tnetflow_report" ( 1564 "id_report" SERIAL NOT NULL PRIMARY KEY, 1565 "id_name" varchar(150) NOT NULL default '', 1566 "description" TEXT, 1567 "id_group" INTEGER, 1568 "server_name" TEXT 1569); 1570 1571-- ----------------------------------------------------- 1572-- Table `tnetflow_report_content` 1573-- ----------------------------------------------------- 1574CREATE TABLE "tnetflow_report_content" ( 1575 "id_rc" SERIAL NOT NULL PRIMARY KEY, 1576 "id_report" INTEGER NOT NULL default 0 REFERENCES tnetflow_report("id_report") ON DELETE CASCADE, 1577 "id_filter" INTEGER NOT NULL default 0 REFERENCES tnetflow_filter("id_sg") ON DELETE CASCADE, 1578 "description" TEXT, 1579 "date" BIGINT NOT NULL default 0, 1580 "period" INTEGER NOT NULL default 0, 1581 "max" INTEGER NOT NULL default 0, 1582 "show_graph" varchar(60), 1583 "order" INTEGER NOT NULL default 0 1584); 1585 1586-- --------------------------------------------------------------------- 1587-- Table `tevent_filter` 1588-- --------------------------------------------------------------------- 1589CREATE TABLE "tevent_filter" ( 1590 "id_filter" SERIAL NOT NULL PRIMARY KEY, 1591 "id_group_filter" INTEGER NOT NULL default 0, 1592 "id_name" varchar(600) NOT NULL, 1593 "id_group" INTEGER NOT NULL default 0, 1594 "event_type" TEXT NOT NULL default '', 1595 "severity" INTEGER NOT NULL default -1, 1596 "status" INTEGER NOT NULL default -1, 1597 "search" TEXT default '', 1598 "text_agent" TEXT default '', 1599 "id_agent" int(10) default 0, 1600 "id_agent_module" int(10) default 0, 1601 "pagination" INTEGER NOT NULL default 25, 1602 "event_view_hr" INTEGER NOT NULL default 8, 1603 "id_user_ack" TEXT, 1604 "group_rep" INTEGER NOT NULL default 0, 1605 "tag_with" text NOT NULL, 1606 "tag_without" text NOT NULL, 1607 "filter_only_alert" INTEGER NOT NULL default -1 1608); 1609 1610-- --------------------------------------------------------------------- 1611-- Table `tpassword_history` 1612-- --------------------------------------------------------------------- 1613CREATE TABLE "tpassword_history" ( 1614 "id_pass" INTEGER NOT NULL PRIMARY KEY, 1615 "id_user" varchar(60) NOT NULL, 1616 "password" varchar(45) default NULL, 1617 "date_begin" BIGINT NOT NULL default 0, 1618 "date_end" BIGINT NOT NULL default 0 1619); 1620 1621-- ----------------------------------------------------- 1622-- Table `tevent_response` 1623-- ----------------------------------------------------- 1624CREATE TABLE "tevent_response" ( 1625 "id" SERIAL NOT NULL PRIMARY KEY, 1626 "name" varchar(600) NOT NULL default '', 1627 "description" TEXT, 1628 "target" TEXT, 1629 "type" varchar(60) NOT NULL, 1630 "id_group" INTEGER NOT NULL default 0, 1631 "modal_width" INTEGER NOT NULL DEFAULT 0, 1632 "modal_height" INTEGER NOT NULL DEFAULT 0, 1633 "new_window" INTEGER NOT NULL DEFAULT 0, 1634 "params" TEXT 1635); 1636 1637-- --------------------------------------------------------------------- 1638-- Table "tcategory" 1639-- --------------------------------------------------------------------- 1640CREATE TABLE "tcategory" ( 1641 "id" SERIAL NOT NULL PRIMARY KEY, 1642 "name" varchar(600) NOT NULL default '' 1643); 1644 1645-- ----------------------------------------------------- 1646-- Table `tupdate_settings` 1647-- ----------------------------------------------------- 1648CREATE TABLE "tupdate_settings" ( 1649 "key" varchar(255) default '' PRIMARY KEY, 1650 "value" varchar(255) default '' 1651); 1652 1653-- ----------------------------------------------------- 1654-- Table `tupdate_package` 1655-- ----------------------------------------------------- 1656CREATE TABLE "tupdate_package"( 1657 "id" SERIAL NOT NULL PRIMARY KEY, 1658 "timestamp" TIMESTAMP without time zone default NULL, 1659 "description" varchar(255) default '' 1660); 1661 1662CREATE TYPE type_tupdate_type AS ENUM ('code', 'db_data', 'db_schema', 'binary'); 1663 1664-- ----------------------------------------------------- 1665-- Table `tupdate` 1666-- ----------------------------------------------------- 1667CREATE TABLE "tupdate" ( 1668 "id" SERIAL NOT NULL PRIMARY KEY, 1669 "type" type_tupdate_type, 1670 "id_update_package" INTEGER default 0 REFERENCES "tupdate_package"("id") ON UPDATE CASCADE ON DELETE CASCADE, 1671 "filename" varchar(250) default '', 1672 "checksum" varchar(250) default '', 1673 "previous_checksum" varchar(250) default '', 1674 "svn_version" INTEGER default 0, 1675 "data" TEXT default '', 1676 "data_rollback" TEXT default '', 1677 "description" TEXT default '', 1678 "db_table_name" varchar(140) default '', 1679 "db_field_name" varchar(140) default '', 1680 "db_field_value" varchar(1024) default '' 1681); 1682 1683-- ----------------------------------------------------- 1684-- Table `tupdate_journal` 1685-- ----------------------------------------------------- 1686CREATE TABLE "tupdate_journal" ( 1687 "id" SERIAL NOT NULL PRIMARY KEY, 1688 "id_update" INTEGER default 0 REFERENCES "tupdate"("id") ON UPDATE CASCADE ON DELETE CASCADE 1689); 1690 1691-- --------------------------------------------------------------------- 1692-- Table talert_snmp_action 1693-- --------------------------------------------------------------------- 1694CREATE TABLE "talert_snmp_action" ( 1695 "id" SERIAL NOT NULL PRIMARY KEY, 1696 "id_alert_snmp" INTEGER default 0, 1697 "alert_type" INTEGER default 0, 1698 "al_field1" TEXT default '', 1699 "al_field2" TEXT default '', 1700 "al_field3" TEXT default '', 1701 "al_field4" TEXT default '', 1702 "al_field5" TEXT default '', 1703 "al_field6" TEXT default '', 1704 "al_field7" TEXT default '', 1705 "al_field8" TEXT default '', 1706 "al_field9" TEXT default '', 1707 "al_field10" TEXT default '' 1708); 1709 1710-- --------------------------------------------------------------------- 1711-- Table tsessions_php 1712-- --------------------------------------------------------------------- 1713CREATE TABLE "tsessions_php" ( 1714 "id_session" SERIAL NOT NULL PRIMARY KEY, 1715 "last_active" INTEGER NOT NULL, 1716 "data" TEXT default '' 1717); 1718