-- Before running this script with 'psql -f', you should have your
-- DBA create the database and give access permissions.
--
--- $log$
+-- $Log: create.sql,v $
+-- Revision 1.5 2003-01-18 08:46:48 arjen
+-- Added new records to the 'type_of_issue' table.
+-- Changed semantics of actionid 9 in the 'action' table.
+--
--
CREATE SEQUENCE "action_actionid_seq";
6 Action taken PEN An action has been taken.
7 Assignment to user PEN The notification has been assigned to an user.
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.
-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
+9 Investigation completed PEN Investigation has been done. Information is available to fix the problem.
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.
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.
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.
COPY "db_value" FROM stdin;
-db_version 1
+db_version 20
+gcm_daemon_version 1
+log_processing 0
\.
--
"object_timestamp" timestamp with time zone,
"timestamp" timestamp with time zone,
"rawdata" text,
- "processed" boolean
+ "processed" boolean DEFAULT false,
+ "recognized" boolean DEFAULT false
);
CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
SELECT setval ('"log_logid_seq"', 1, false);
-CREATE RULE log_update AS ON UPDATE TO log DO INSTEAD NOTHING;
-
--
--
(
"log_advid" bigint DEFAULT
nextval('"log_advid_seq"'::text) NOT NULL,
- "logid" bigint NOT NULL
+ "logid" bigint NOT NULL,
+ "detailed_table" text
);
SELECT setval ('"log_advid_seq"', 1, false);
+CREATE TABLE log_adv_kernel_network
+(
+ device_in text,
+ device_out text,
+ hw_address text,
+ source_ip INET,
+ destination_ip INET,
+ packet_length bigint,
+ tos_bit text,
+ prec_bit text,
+ ttl int,
+ header_id bigint,
+ source_port int,
+ destination_port int,
+ body_length int,
+ protocol text,
+ body_len int,
+ window text,
+ urgp int,
+ syn boolean DEFAULT false,
+ type int,
+ code int,
+ sequence_number int,
+ res text,
+ rst boolean,
+ df boolean
+) INHERITS (log_adv);
+
/*
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
"object_owner" text,
"physical_location" text,
"timezone" text,
- "remark" text
+ "remark" text,
+ "os" text,
+ "os_version" text
);
CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
CREATE INDEX obj_mail_from ON object USING btree (mail_from);
+CREATE INDEX os ON object (os);
+
+CREATE INDEX os_version ON object (os, os_version);
+
SELECT setval ('"object_objectid_seq"', 1, false);
--
--
--
+CREATE TABLE supported_os
+(
+ os_name text,
+ remarks text
+);
+
+CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
+
+--
+--
+
CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
CREATE TABLE "type_of_issue"
"name" text,
"suggested_priority" text,
"description" text,
- "active" boolean
+ "active" boolean,
+ automated_check boolean,
+ alert_level int,
+ last_run timestamp,
+ recheck_interval timestamp
);
COPY "type_of_issue" FROM stdin;
1 manual entry 4 A manual entry of a notification t
+2 parameter created 3 A new parameter was created t
+3 property modified 3 The STATIC property of a parameter was modified t
+4 parameter removed 3 A parameter was removed t
\.
CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
CREATE INDEX toi_active ON type_of_issue USING btree (active);
-SELECT setval ('"type_of_issue_type_of_issue_seq"', 1, true);
+SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
--
--
case 1:
//DROP the rule that prohibits updates (change of spec).
$local_sql = "DROP RULE log_update";
- $dbms->query($local_sql);
+// $dbms->query($local_sql); DISABLED FOR NEW ROLE LOG-tABLE
case 2:
//In the log table processed should be false by default
$local_sql = "UPDATE log SET processed = false";
- $dbms->query($local_sql);
+ $dbms->query($local_sql);
case 3:
//In the log_adv a column is added that indicates where the detailed
$dbms->query($local_sql);
case 11:
+ //Recognize some more kernel_network fields
$local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN res varchar(5)";
$dbms->query($local_sql);
$dbms->query($local_sql);
case 12:
+ //Add the gcm_daemon_version to the system
$local_sql = "INSERT INTO db_value VALUES ('gcm_daemon_version', '1')";
$dbms->query($local_sql);
+
+ case 13:
+ //Create the table object_system_user
+ $local_sql = "CREATE TABLE object_system_user (objectid BIGINT, system_username TEXT, can_login BOOLEAN, can_be_root BOOLEAN)";
+ $dbms->query($local_sql);
+
+ $local_sql = "CREATE UNIQUE INDEX obsyus_id_name ON object_system_user (objectid, system_username)";
+ $dbms->query($local_sql);
+
+
+ $local_sql = "CREATE INDEX obsyus_id ON object_system_user (objectid)";
+ $dbms->query($local_sql);
+
+
+ $local_sql = "CREATE INDEX obsyus_name ON object_system_user (system_username)";
+ $dbms->query($local_sql);
+
+ case 14:
+ //Make type_of_issue ready for automatic detection
+ $local_sql = "ALTER TABLE type_of_issue ADD COLUMN automated_check BOOLEAN";
+ $dbms->query($local_sql);
+
+ $local_sql = "ALTER TABLE type_of_issue ADD COLUMN alert_level INT";
+ $dbms->query($local_sql);
+
+ $local_sql = "ALTER TABLE type_of_issue ADD COLUMN last_run TIMESTAMP";
+ $dbms->query($local_sql);
+
+ $local_sql = "ALTER TABLE type_of_issue ADD COLUMN recheck_interval TIMESTAMP";
+ $dbms->query($local_sql);
+
+ case 15:
+ $local_sql = "INSERT INTO db_value (setting, setting_value) VALUES ('log_processing', '0')";
+ $dbms->query($local_sql);
+
+ case 16:
+ $local_sql = "ALTER TABLE log_adv_kernel_network DROP COLUMN hw_address";
+ $dbms->query($local_sql);
+
+
+ $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN hw_address TEXT";
+ $dbms->query($local_sql);
+ case 17:
+ $local_sql = "DROP TABLE object_system_user";
+ $dbms->query($local_sql);
+
+ case 18:
+ $local_sql = "UPDATE action SET actionname = 'Investigation completed', statuscode = 'PEN', description = 'Investigation has been done.' ";
+ $local_sql .= "WHERE actionid = '9'";
+ $dbms->query($local_sql);
+
+ case 19:
+ $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
+ $local_sql .= " values ('parameter created',3,'A new parameter was created')";
+ $dbms->query($local_sql);
+
+ $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
+ $local_sql .= " values ('property modified',3,'The STATIC property of a parameter was modified')";
+ $dbms->query($local_sql);
+
+ $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
+ $local_sql .= " values ('parameter removed',3,'A parameter was removed') ";
+ $dbms->query($local_sql);
+
+/*
+ //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN
+ $local_sql = "ALTER TABLE log DROP COLUMN recognized";
+ $dbms->query($local_sql);
+
+ $local_sql = "ALTER TABLE logng DROP COLUMN recognized";
+ $dbms->query($local_sql);
+*/
}
- $active_version++;
- $local_sql = "UPDATE db_value SET setting_value = ".$active_version." WHERE setting = 'db_version'";
- $dbms->query($local_sql);
+ $local_sql = "UPDATE db_value SET setting_value = ".$db_version." WHERE setting = 'db_version'";
+ $dbms->query($local_sql);
+
?>
projects / gnucomo.git / commitdiff