1 --*************************************************************************
2 -- (c) Copyright 2002, De Winter Information Solutions
3 -- This is free software; you can redistribute it and/or modify it under the
4 -- terms of the GNU General Public License, see the file COPYING.
5 --*************************************************************************/
7 -- Gnucomo database creation script.
10 -- This SQL script creates the initial tables for the Gnucomo database.
11 -- Before running this script with 'psql -f', you should have your
12 -- DBA create the database and give access permissions.
14 -- $Log: create.sql,v $
15 -- Revision 1.20 2007-01-11 13:47:35 arjen
16 -- Log_adv and derived tables removed.
17 -- Create notifications from log entries with pattern matching.
19 -- Revision 1.19 2005/06/04 07:09:47 arjen
20 -- - Fixed field declaration for PostgreSQL 7.4
21 -- - New tables: log_abuse, object_abuse and service_pattern
22 -- - Added general service patterns in the service 'ANY'
23 -- - Added new issue types.
25 -- Revision 1.18 2003/12/03 08:06:57 arjen
26 -- Changed the type of log_adv_daemon_email.delay and log_adv_daemon_email.xdelay
27 -- from time to interval. These delays can be more than 24 hours.
29 -- Revision 1.17 2003/09/02 12:49:47 arjen
30 -- BUGFIX: Secondary indices on log_notification were unique.
31 -- Additional information in the 'usr' table: 'display_name' and 'email'.
32 -- Added new issues and services.
34 -- Revision 1.16 2003/08/16 14:29:02 arjen
37 -- Revision 1.15 2003/08/05 07:43:24 arjen
38 -- Added index to the history table.
40 -- Revision 1.14 2003/07/09 07:14:59 arjen
41 -- New database tables: notification_check, notification_check_buffer,
42 -- notification_check_line and object_statistics.
44 -- Revision 1.13 2003/03/29 08:27:05 arjen
45 -- New columns in the table 'log_adv_daemon_email': size, pri, relay,
46 -- status_details and dsn.
47 -- Added several indices for the table 'log_adv_daemon_email'.
49 -- Revision 1.12 2003/02/21 08:38:38 arjen
50 -- Added new table to the database: log_adv_daemon_email.
52 -- Revision 1.11 2003/02/16 08:24:38 arjen
53 -- Added a new entry to the action table: Notification was displayed in the listing
55 -- Revision 1.10 2003/02/14 06:32:27 arjen
56 -- Setup the groups and database permissions. The three groups
57 -- are: view, ops and admin.
58 -- Added a new entry to the action table.
60 -- Revision 1.9 2003/02/13 08:46:54 arjen
61 -- Added log, notification and parameter counters to the 'object' table.
62 -- Counting these things at the time a user interface needs them is
63 -- too slow. Other programs, like gcm_daemon en gcm_input should prepare
64 -- these counters for quick retrieval.
66 -- Revision 1.8 2003/02/08 07:36:41 arjen
67 -- Added new table to the database : log_adv_daemon
69 -- Revision 1.7 2003/02/05 09:29:08 arjen
70 -- Bug fix: action.statuscode was in upper case.
72 -- Revision 1.6 2003/01/20 07:29:48 arjen
73 -- Added new tables to the database: parameter_class and parameter_notification
75 -- Revision 1.5 2003/01/18 08:46:48 arjen
76 -- Added new records to the 'type_of_issue' table.
77 -- Changed semantics of actionid 9 in the 'action' table.
81 CREATE SEQUENCE "action_actionid_seq";
85 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
87 "statuscode" character varying(3),
92 COPY "action" FROM stdin;
93 1 Entry in the system new This indicates that a notification has been entered into the system.
94 2 Display to user opn The notification has been displayed to the user. It doesn't mean that the user actually read the notification, but he/she should be aware. For that reason we consider the notification to be OPEN
95 3 Remarks added pen Remarks have been added to the notification.
96 4 Priority changed manually pen The priority of the notification has been changed by the user.
97 5 Priority changed automatically pen The priority of the notification has been changed by the system. This can be the result of an action by the user or an automatical escalation.
98 6 Action taken pen An action has been taken.
99 7 Assignment to user pen The notification has been assigned to an user.
100 8 More information or research needed. inv The notification is relevant and will be handled, however more information or research will be needed. For that reason the status has been altered to UNDER INVESTIGATION.
101 9 Investigation completed pen Investigation has been done. Information is available to fix the problem.
102 10 Job output no longer valid cls By making a newer job output a valid reference, this data has been obsoleted. Since it was a reference once the output isn't interesting anymore and thus the notification can be closed.
103 11 Action taken - please verify cls An action has been taken and things should have been resolved. Before the notification can be closed a verification has to be done. The new status is now VERIFY.
104 12 Action not verified pen A check has been done and the results were not good. New verification is needed. The status has been changed back to PEN.
105 13 Action verified cls A check has been done and the results were approved. The notification has been closed.
106 15 SMS sent opn An SMS has been sent.
107 14 E-mail sent opn An e-mail has been sent.
108 16 Fax sent opn An fax has been sent.
109 17 Log entries shown \N The log entries relevant to the notification have been shown.
110 18 Notification closed cls The notification has been closed.
111 19 Notification reopend opn The notification has been reopend.
112 20 Redisplayed to user \N The notification has been presented to a user. This is not the first time
113 21 Displayed in list \N The notification has been presented in a list with other notifications.
116 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
118 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
120 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
122 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
124 SELECT setval ('"action_actionid_seq"', 21, true);
129 CREATE SEQUENCE "action_user_actionstepid_seq";
131 CREATE TABLE "action_user"
133 "actionstepid" bigint DEFAULT
134 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
137 "notificationid" bigint,
138 "timestamp" timestamp with time zone,
139 "statuscode" character varying(3),
143 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
145 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
147 CREATE INDEX anu_username ON action_user USING btree (username);
149 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
151 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
153 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
155 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
160 CREATE TABLE "db_value"
167 COPY "db_value" FROM stdin;
177 CREATE TABLE "history"
181 change_nature text, -- CREATED, MODIFIED or REMOVED
182 changed_property text,
188 CREATE INDEX history_pid_mod ON history(paramid, modified);
193 CREATE SEQUENCE "log_logid_seq";
197 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
199 "original_filename" text,
201 "object_timestamp" timestamp with time zone,
202 "timestamp" timestamp with time zone,
204 "processed" boolean DEFAULT false,
205 "recognized" boolean DEFAULT false
208 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
210 CREATE INDEX log_objectid ON log USING btree (objectid);
212 CREATE INDEX log_original_filename ON log USING btree (original_filename);
214 CREATE INDEX log_servicecode ON log USING btree (servicecode);
216 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
218 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
220 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
222 CREATE INDEX log_processed ON log USING btree (processed);
224 SELECT setval ('"log_logid_seq"', 1, false);
227 CREATE TABLE log_abuse
237 CREATE TABLE "log_notification"
239 "notificationid" bigint,
243 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
245 CREATE INDEX lon_notificationid ON log_notification USING btree (notificationid);
247 CREATE INDEX lon_logid ON log_notification USING btree (logid);
252 CREATE SEQUENCE "notification_notificationid_seq";
254 CREATE TABLE "notification"
256 "notificationid" bigint DEFAULT
257 nextval('"notification_notificationid_seq"'::text) NOT NULL,
259 "type_of_issueid" bigint,
260 "timestamp" timestamp with time zone,
261 "statuscode" character varying(3),
263 "escalation_count_timestamp" timestamp with time zone,
264 "repeat_notification_timestamp" timestamp with time zone,
265 "securitylevel_view" integer,
266 "securitylevel_add" integer,
267 "securitylevel_close" integer
270 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
272 CREATE INDEX not_objectid ON notification USING btree (objectid);
274 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
276 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
278 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
280 CREATE INDEX not_priority ON notification USING btree (priority);
282 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
284 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
286 SELECT setval ('"notification_notificationid_seq"', 1, false);
291 CREATE SEQUENCE checkid_seq;
293 CREATE TABLE notification_check
295 checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,
298 time_between_executions INTERVAL,
299 last_execution timestamp,
300 execution_counter BIGINT,
301 notificationcounter BIGINT,
302 decreasinglist BOOLEAN default false,
303 type_of_issueid BIGINT
306 CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid);
308 CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname);
310 CREATE INDEX not_check_check_lastexec ON notification_check (last_execution);
315 CREATE TABLE notification_check_buffer
323 CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid);
325 CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder);
327 CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid);
329 CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid);
334 CREATE SEQUENCE checklineid_seq;
336 CREATE TABLE notification_check_line
338 checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,
341 last_logid BIGINT default 0,
342 historicboundary INTERVAL default '0',
343 use_logid BOOLEAN default false,
347 CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid);
349 CREATE INDEX notcheckline_checkid ON notification_check_line (checkid);
351 CREATE INDEX notcheckline_sort ON notification_check_line (sortorder);
353 CREATE INDEX notcheckline_check_sort ON notification_check_line (checkid, sortorder);
358 CREATE SEQUENCE "object_objectid_seq";
360 CREATE TABLE "object"
362 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
365 "scp_enabled" boolean,
367 "mail_enabled" boolean,
369 "sms_enabled" boolean,
371 "fax_enabled" boolean,
373 "object_description" text,
375 "physical_location" text,
381 "notification_count" bigint,
382 "parameter_count" bigint
385 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
387 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
389 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
391 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
393 CREATE INDEX os ON object (os);
395 CREATE INDEX os_version ON object (os, os_version);
397 SELECT setval ('"object_objectid_seq"', 1, false);
402 CREATE TABLE "object_abuse"
408 last_change timestamp,
410 primary key (objectid, source)
416 CREATE TABLE "object_issue"
419 "type_of_issueid" bigint,
420 "default_priority" integer,
421 "escalation" boolean,
422 "escalation_time" time without time zone,
423 "max_priority" integer,
424 "adjust_setting" text
427 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
429 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
431 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
436 CREATE TABLE "object_priority"
439 "priorityid" integer,
443 "repeat_notification" boolean,
444 "interval_for_repeat" time without time zone
447 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
449 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
451 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
456 CREATE TABLE "object_service"
460 "expected_interval" bigint,
461 "last_entry" timestamp with time zone,
462 "default_priority" integer,
463 "maximum_priority" integer,
467 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
469 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
471 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
473 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
478 CREATE TABLE object_statistics
482 statvalue double precision,
484 primary key (objectid, statname)
487 CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid);
492 CREATE TABLE "object_user"
496 "security_level" integer
499 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
501 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
503 CREATE INDEX ous_username ON object_user USING btree (username);
505 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
510 CREATE SEQUENCE "paramid_seq";
512 CREATE TABLE "parameter"
514 "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
520 primary key (paramid)
523 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
525 SELECT setval ('"paramid_seq"', 1, true);
530 CREATE TABLE "parameter_class"
532 "name" text, -- Name of the class: see parameter.class
533 "property_name" text,
535 "property_type" text, -- STATIC or DYNAMIC
536 "min" float, -- Default minimum value
537 "max" float, -- Default maximum value
538 "notify" boolean, -- Notify if something changes ?
540 primary key (name, property_name)
543 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
544 VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
549 CREATE TABLE "parameter_notification"
551 "notificationid" bigint,
554 primary key (notificationid, paramid)
560 CREATE TABLE "priority"
566 "repeat_notification" boolean,
567 "interval_for_repeat" time without time zone
571 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
576 CREATE TABLE "property"
581 type text, -- STATIC or DYNAMIC
585 primary key (paramid, name)
591 CREATE TABLE "service"
595 "default_priority" integer,
596 "max_priority" integer
599 COPY "service" FROM stdin;
607 CROND Cron Daemon 1 5
608 gnucomo Gnucomo Daemon 1 5
609 sendmail Mail Transport Agent 1 5
610 dhcpd DHCP Daemon 1 5
612 named DNS Services 1 5
613 xinetd Internet Daemon 1 5
614 ipop Post Office Protocol 1 5
615 mgetty Serial port login and fax 1 5
617 pam Authentication modules 1 5
618 modprobe Kernel modules 1 5
621 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
623 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
625 CREATE TABLE service_pattern
633 primary key (service, rank)
636 INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log');
637 INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected');
638 INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected');
639 INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected');
643 CREATE TABLE "status"
645 "statuscode" character varying(3),
647 "open_notification" boolean,
652 COPY "status" FROM stdin;
653 new new entry t Just detected, but nothing has been done yet
654 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
655 pen pending t The notification is currently being worked on.
656 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
657 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
658 cls closed f The notification has been closed
659 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
662 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
664 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
666 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
671 CREATE TABLE supported_os
677 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
682 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
684 CREATE TABLE "type_of_issue"
686 "type_of_issueid" bigint DEFAULT
687 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
689 "suggested_priority" text,
692 automated_check boolean,
695 recheck_interval timestamp
699 COPY "type_of_issue" FROM stdin;
700 1 manual entry 4 A manual entry of a notification t t 1 \N \N
701 2 parameter created 3 A new parameter was created t t 1 \N \N
702 3 property modified 3 The STATIC property of a parameter was modified t t 1 \N \N
703 4 parameter removed 3 A parameter was removed t t 1 \N \N
704 5 service unknown 5 Service in log entry is unknown t t 1 \N \N
705 6 service not used 5 Service in log entry is not used t t 1 \N \N
706 7 abuses exceeded 5 Abuse treshold exceeded from an IP address t t 1 \N \N
707 8 unmatched log 5 Log entries could not be matched t t 1 \N \N
708 9 Error detected 5 An Error is reported in the log t t 1 \N \N
709 9 Failure detected 5 A Failure is reported in the log t t 1 \N \N
710 10 Warning detected 3 A Warning is reported in the log t t 1 \N \N
713 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
715 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
717 CREATE INDEX toi_active ON type_of_issue USING btree (active);
719 SELECT setval ('"type_of_issue_type_of_issue_seq"', 6, true);
726 "username" text NOT NULL,
727 "active_sessionid" bigint,
728 "account_active" boolean,
729 "security_level" integer,
734 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
736 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
738 CREATE INDEX usr_account_active ON usr USING btree (account_active);
740 CREATE INDEX usr_security_level ON usr USING btree (security_level);
743 -- Set up user groups and grant permissions in the proper places.
749 GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
750 GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
751 GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
752 GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
753 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
754 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
755 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
756 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
757 GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin;
758 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
759 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
760 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
761 GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin;
762 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
763 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
764 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
765 GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
766 GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
767 GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
768 GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
769 GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
770 GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
771 GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
772 GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
774 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
775 GRANT UPDATE ON action_user_actionstepid_seq TO GROUP ops, GROUP admin;
776 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
777 GRANT INSERT ON object TO GROUP admin;
778 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
779 GRANT DELETE ON object TO GROUP admin;
780 GRANT INSERT ON object_abuse TO GROUP admin;
781 GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin;
782 GRANT DELETE ON object_abuse TO GROUP admin;
783 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
784 GRANT INSERT ON usr TO GROUP admin;
785 GRANT DELETE ON usr TO GROUP admin;
786 GRANT SELECT ON service_pattern TO GROUP view, GROUP ops, GROUP admin;
787 GRANT INSERT ON service_pattern TO GROUP admin;
788 GRANT UPDATE ON service_pattern TO GROUP ops, GROUP admin;
789 GRANT DELETE ON service_pattern TO GROUP admin;
790 GRANT SELECT ON log_abuse TO GROUP view, GROUP ops, GROUP admin;