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.
16 CREATE SEQUENCE "action_actionid_seq";
20 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
22 "statuscode" character varying(3),
27 COPY "action" FROM stdin;
28 1 Entry in the system NEW This indicates that a notification has been entered into the system.
29 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
30 3 Remarks added PEN Remarks have been added to the notification.
31 4 Priority changed manually PEN The priority of the notification has been changed by the user.
32 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.
33 6 Action taken PEN An action has been taken.
34 7 Assignment to user PEN The notification has been assigned to an user.
35 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.
36 9 Make output reference REF The automated output from an object was sent to gnucomo. The input has been identified as a valid reference for the future. For that reason the status is now REFERENCE
37 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.
38 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.
39 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.
40 13 Action verified CLS A check has been done and the results were approved. The notification has been closed.
41 15 SMS sent OPN An SMS has been sent.
42 14 E-mail sent OPN An e-mail has been sent.
43 16 Fax sent OPN An fax has been sent.
44 17 Log entries shown \N The log entries relevant to the notification have been shown.
45 18 Notification closed CLS The notification has been closed.
46 19 Notification reopend OPN The notification has been reopend.
49 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
51 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
53 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
55 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
57 SELECT setval ('"action_actionid_seq"', 19, true);
62 CREATE SEQUENCE "action_user_actionstepid_seq";
64 CREATE TABLE "action_user"
66 "actionstepid" bigint DEFAULT
67 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
70 "notificationid" bigint,
71 "timestamp" timestamp with time zone,
72 "statuscode" character varying(3),
76 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
78 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
80 CREATE INDEX anu_username ON action_user USING btree (username);
82 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
84 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
86 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
88 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
93 CREATE SEQUENCE "log_logid_seq";
97 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
99 "original_filename" text,
101 "object_timestamp" timestamp with time zone,
102 "timestamp" timestamp with time zone,
106 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
108 CREATE INDEX log_objectid ON log USING btree (objectid);
110 CREATE INDEX log_original_filename ON log USING btree (original_filename);
112 CREATE INDEX log_servicecode ON log USING btree (servicecode);
114 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
116 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
118 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
120 SELECT setval ('"log_logid_seq"', 1, false);
125 CREATE TABLE "log_adv"
129 "destination_ip" inet,
130 "mac_address" macaddr,
131 "packetlength" integer,
133 "source_port" integer,
134 "destination_port" integer,
136 "system_username" text,
140 CREATE INDEX loa_logid ON log_adv USING btree (logid);
142 CREATE INDEX loa_source_ip ON log_adv USING btree (source_ip);
144 CREATE INDEX loa_destination_ip ON log_adv USING btree (destination_ip);
146 CREATE INDEX loa_mac_address ON log_adv USING btree (mac_address);
148 CREATE INDEX loa_packetlength ON log_adv USING btree (packetlength);
150 CREATE INDEX loa_protocol ON log_adv USING btree (protocol);
152 CREATE INDEX loa_source_port ON log_adv USING btree (source_port);
154 CREATE INDEX loa_destination_port ON log_adv USING btree (destination_port);
156 CREATE INDEX loa_messageid ON log_adv USING btree (messageid);
158 CREATE INDEX loa_system_username ON log_adv USING btree (system_username);
160 CREATE INDEX loa_networkdevice ON log_adv USING btree (networkdevice);
165 CREATE TABLE "log_notification"
167 "notificationid" bigint,
171 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
173 CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);
175 CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);
180 CREATE SEQUENCE "notification_notificationid_seq";
182 CREATE TABLE "notification"
184 "notificationid" bigint DEFAULT
185 nextval('"notification_notificationid_seq"'::text) NOT NULL,
187 "type_of_notificationid" bigint,
188 "timestamp" timestamp with time zone,
189 "statuscode" character varying(3),
191 "escalation_count_timestamp" timestamp with time zone,
192 "repeat_notification_timestamp" timestamp with time zone,
193 "securitylevel_view" integer,
194 "securitylevel_add" integer,
195 "securitylevel_close" integer
198 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
200 CREATE INDEX not_objectid ON notification USING btree (objectid);
202 CREATE INDEX not_type_of_notificationid ON notification USING btree (type_of_notificationid);
204 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
206 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
208 CREATE INDEX not_priority ON notification USING btree (priority);
210 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
212 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
214 SELECT setval ('"notification_notificationid_seq"', 1, false);
219 CREATE SEQUENCE "object_objectid_seq";
221 CREATE TABLE "object"
223 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
226 "scp_enabled" boolean,
228 "mail_enabled" boolean,
230 "sms_enabled" boolean,
232 "fax_enabled" boolean,
234 "object_description" text,
236 "physical_location" text,
241 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
243 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
245 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
247 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
249 SELECT setval ('"object_objectid_seq"', 1, false);
254 CREATE TABLE "object_issue"
257 "type_of_notificationid" bigint,
258 "default_priority" integer,
259 "escalation" boolean,
260 "escalation_time" time without time zone,
261 "max_priority" integer,
262 "adjust_setting" text
265 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_notificationid);
267 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
269 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_notificationid);
274 CREATE TABLE "object_priority"
277 "priorityid" integer,
281 "repeat_notification" boolean,
282 "interval_for_repeat" time without time zone
285 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
287 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
289 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
294 CREATE TABLE "object_service"
298 "expected_interval" bigint,
299 "last_entry" timestamp with time zone,
300 "default_priority" integer,
301 "maximum_priority" integer,
305 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
307 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
309 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
311 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
316 CREATE TABLE "object_system_user"
319 "system_username" text,
321 "can_be_root" boolean
324 CREATE UNIQUE INDEX osu_pk ON object_system_user USING btree (objectid, system_username);
326 CREATE INDEX osu_objectid ON object_system_user USING btree (objectid);
328 CREATE INDEX osu_system_username ON object_system_user USING btree (system_username);
333 CREATE TABLE "object_user"
337 "security_level" integer
340 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
342 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
344 CREATE INDEX ous_username ON object_user USING btree (username);
346 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
351 CREATE TABLE "priority"
357 "repeat_notification" boolean,
358 "interval_for_repeat" time without time zone
362 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
367 CREATE TABLE "service"
371 "default_priority" integer,
372 "max_priority" integer
375 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
377 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
382 CREATE TABLE "status"
384 "statuscode" character varying(3),
386 "open_notification" boolean,
391 COPY "status" FROM stdin;
392 new new entry t Just detected, but nothing has been done yet
393 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
394 pen pending t The notification is currently being worked on.
395 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
396 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
397 cls closed f The notification has been closed
398 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
401 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
403 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
405 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
410 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
412 CREATE TABLE "type_of_issue"
414 "type_of_issueid" bigint DEFAULT
415 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
417 "suggested_priority" text,
423 COPY "type_of_issue" FROM stdin;
424 1 manual entry 4 A manual entry of a notification t
427 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
429 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
431 CREATE INDEX toi_active ON type_of_issue USING btree (active);
433 SELECT setval ('"type_of_issue_type_of_issue_seq"', 1, true);
438 CREATE SEQUENCE "unprocessed_l_unprocessedid_seq";
440 CREATE TABLE "unprocessed_log"
442 "unprocessedid" bigint DEFAULT
443 nextval('"unprocessed_l_unprocessedid_seq"'::text) NOT NULL,
448 CREATE UNIQUE INDEX unprocessed_l_unprocessedid_key ON unprocessed_log USING btree (unprocessedid);
450 SELECT setval ('"unprocessed_l_unprocessedid_seq"', 1, false);
455 CREATE TABLE "user_gnucomo"
459 "active_sessionid" bigint,
460 "account_active" boolean,
461 "security_level" integer
464 CREATE UNIQUE INDEX usr_pk ON user_gnucomo USING btree (username);
466 CREATE INDEX usr_active_sessionid ON user_gnucomo USING btree (active_sessionid);