-- $Id: create_sguildb.sql,v 1.18 2007/03/17 02:43:37 bamm Exp $
-- Users may want to use a different DB name.
-- CREATE DATABASE IF NOT EXISTS sguildb;
-- USE sguildb;
-- Depreciated for MRG_MyISAM tables
-- CREATE TABLE event
-- (
-- sid INT UNSIGNED NOT NULL,
-- cid INT UNSIGNED NOT NULL,
-- signature VARCHAR(255) NOT NULL,
-- signature_gen INT UNSIGNED NOT NULL,
-- signature_id INT UNSIGNED NOT NULL,
-- signature_rev INT UNSIGNED NOT NULL,
-- timestamp DATETIME NOT NULL,
-- unified_event_id INT UNSIGNED,
-- unified_event_ref INT UNSIGNED,
-- unified_ref_time DATETIME,
-- priority INT UNSIGNED,
-- class VARCHAR(20),
-- status SMALLINT UNSIGNED DEFAULT 0,
-- src_ip INT UNSIGNED,
-- dst_ip INT UNSIGNED,
-- src_port INT UNSIGNED,
-- dst_port INT UNSIGNED,
-- icmp_type TINYINT UNSIGNED,
-- icmp_code TINYINT UNSIGNED,
-- ip_proto TINYINT UNSIGNED,
-- ip_ver TINYINT UNSIGNED,
-- ip_hlen TINYINT UNSIGNED,
-- ip_tos TINYINT UNSIGNED,
-- ip_len SMALLINT UNSIGNED,
-- ip_id SMALLINT UNSIGNED,
-- ip_flags TINYINT UNSIGNED,
-- ip_off SMALLINT UNSIGNED,
-- ip_ttl TINYINT UNSIGNED,
-- ip_csum SMALLINT UNSIGNED,
-- last_modified DATETIME,
-- last_uid INT UNSIGNED,
-- abuse_queue enum('Y','N'),
-- abuse_sent enum('Y','N'),
-- PRIMARY KEY (sid,cid),
-- INDEX src_ip (src_ip),
-- INDEX dst_ip (dst_ip),
-- INDEX dst_port (dst_port),
-- INDEX src_port (src_port),
-- INDEX icmp_type (icmp_type),
-- INDEX icmp_code (icmp_code),
-- INDEX timestamp (timestamp),
-- INDEX last_modified (last_modified),
-- INDEX signature (signature),
-- INDEX status (status),
-- INDEX abuse_queue (abuse_queue),
-- INDEX abuse_sent (abuse_sent)
--);
--CREATE TABLE tcphdr
--(
-- sid INT UNSIGNED NOT NULL,
-- cid INT UNSIGNED NOT NULL,
-- tcp_seq INT UNSIGNED,
-- tcp_ack INT UNSIGNED,
-- tcp_off TINYINT UNSIGNED,
-- tcp_res TINYINT UNSIGNED,
-- tcp_flags TINYINT UNSIGNED,
-- tcp_win SMALLINT UNSIGNED,
-- tcp_csum SMALLINT UNSIGNED,
-- tcp_urp SMALLINT UNSIGNED,
-- PRIMARY KEY (sid,cid));
--
--CREATE TABLE udphdr
--(
-- sid INT UNSIGNED NOT NULL,
-- cid INT UNSIGNED NOT NULL,
-- udp_len SMALLINT UNSIGNED,
-- udp_csum SMALLINT UNSIGNED,
-- PRIMARY KEY (sid,cid));
--
--CREATE TABLE icmphdr
--(
-- sid INT UNSIGNED NOT NULL,
-- cid INT UNSIGNED NOT NULL,
-- icmp_csum SMALLINT UNSIGNED,
-- icmp_id SMALLINT UNSIGNED,
-- icmp_seq SMALLINT UNSIGNED,
-- PRIMARY KEY (sid,cid));
--
--CREATE TABLE data
--(
-- sid INT UNSIGNED NOT NULL,
-- cid INT UNSIGNED NOT NULL,
-- data_payload TEXT,
-- PRIMARY KEY (sid,cid));
CREATE TABLE sensor
(
sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
hostname VARCHAR(255) NOT NULL,
agent_type VARCHAR(40),
net_name VARCHAR(40),
interface VARCHAR(255),
description TEXT,
bpf_filter TEXT,
updated TIMESTAMP(14) NOT NULL,
active ENUM('Y','N') DEFAULT 'Y',
ip VARCHAR(15) DEFAULT NULL,
public_key VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (sid),
INDEX hostname_idx (hostname)
);
CREATE TABLE portscan
(
hostname VARCHAR(255),
timestamp DATETIME,
src_ip varchar(16),
src_port INT UNSIGNED,
dst_ip varchar(16),
dst_port INT UNSIGNED,
data TEXT,
INDEX ps_src_ip (src_ip),
INDEX ps_timestamp (timestamp));
-- Depreciated
-- CREATE TABLE sessions (
-- sid INT UNSIGNED NOT NULL,
-- xid BIGINT UNSIGNED NOT NULL,
-- start_time datetime NOT NULL,
-- end_time datetime NOT NULL,
-- src_ip INT UNSIGNED NOT NULL,
-- dst_ip INT UNSIGNED NOT NULL,
-- src_port INT UNSIGNED NOT NULL,
-- dst_port INT UNSIGNED NOT NULL,
-- ip_proto TINYINT UNSIGNED NOT NULL,
-- src_pckts BIGINT UNSIGNED NOT NULL,
-- dst_pckts BIGINT UNSIGNED NOT NULL,
-- src_bytes BIGINT UNSIGNED NOT NULL,
-- dst_bytes BIGINT UNSIGNED NOT NULL,
-- PRIMARY KEY (sid,xid),
-- INDEX begin (start_time),
-- INDEX end (end_time),
-- INDEX server (src_ip),
-- INDEX client (dst_ip),
-- INDEX sport (src_port),
-- INDEX cport (dst_port));
CREATE TABLE status
(
status_id SMALLINT UNSIGNED NOT NULL,
description VARCHAR(255) NOT NULL,
long_desc VARCHAR(255),
PRIMARY KEY (status_id)
);
CREATE TABLE history
(
sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
uid INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
status SMALLINT UNSIGNED NOT NULL,
comment VARCHAR(255),
INDEX log_time (timestamp)
);
CREATE TABLE user_info
(
uid INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(16) NOT NULL,
last_login DATETIME NOT NULL,
PRIMARY KEY (uid)
);
CREATE TABLE nessus_data
(
rid VARCHAR(40) NOT NULL,
port VARCHAR(40),
nessus_id INT UNSIGNED,
level VARCHAR(20),
description TEXT,
INDEX rid (rid));
CREATE TABLE nessus
(
uid INT NOT NULL,
rid VARCHAR(40) NOT NULL,
ip VARCHAR(15) NOT NULL,
timestart DATETIME,
timeend DATETIME,
PRIMARY KEY (rid),
INDEX ip (ip));
CREATE TABLE IF NOT EXISTS `pads`
(
hostname VARCHAR(255) NOT NULL,
sid INT UNSIGNED NOT NULL,
asset_id INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
ip INT UNSIGNED NOT NULL,
service VARCHAR(40) NOT NULL,
port INT UNSIGNED NOT NULL,
ip_proto TINYINT UNSIGNED NOT NULL,
application VARCHAR(255) NOT NULL,
hex_payload VARCHAR(255),
PRIMARY KEY (sid,asset_id)
);
--
-- Depreciated for MERGE tables
--CREATE TABLE sancp
--(
-- sid INT UNSIGNED NOT NULL,
-- sancpid BIGINT UNSIGNED NOT NULL,
-- start_time DATETIME NOT NULL,
-- end_time DATETIME NOT NULL,
-- duration INT UNSIGNED NOT NULL,
-- ip_proto TINYINT UNSIGNED NOT NULL,
-- src_ip INT UNSIGNED,
-- src_port SMALLINT UNSIGNED,
-- dst_ip INT UNSIGNED,
-- dst_port SMALLINT UNSIGNED,
-- src_pkts INT UNSIGNED NOT NULL,
-- src_bytes INT UNSIGNED NOT NULL,
-- dst_pkts INT UNSIGNED NOT NULL,
-- dst_bytes INT UNSIGNED NOT NULL,
-- src_flags TINYINT UNSIGNED NOT NULL,
-- dst_flags TINYINT UNSIGNED NOT NULL,
-- PRIMARY KEY (sid,sancpid),
-- INDEX src_ip (src_ip),
-- INDEX dst_ip (dst_ip),
-- INDEX dst_port (dst_port),
-- INDEX src_port (src_port),
-- INDEX start_time (start_time)
--);
--
INSERT INTO status (status_id, description, long_desc) VALUES (0, "New", "Real Time Event");
INSERT INTO status (status_id, description, long_desc) VALUES (1, "No Further Action Required", "No Further Action Required");
INSERT INTO status (status_id, description, long_desc) VALUES (2, "Escalated", "Escalated");
INSERT INTO status (status_id, description, long_desc) VALUES (11, "Category I", "Unauthorized Root Access");
INSERT INTO status (status_id, description, long_desc) VALUES (12, "Category II", "Unauthorized User Access");
INSERT INTO status (status_id, description, long_desc) VALUES (13, "Category III", "Attempted Unauthorized Access");
INSERT INTO status (status_id, description, long_desc) VALUES (14, "Category IV", "Successful Denial of Service Attack");
INSERT INTO status (status_id, description, long_desc) VALUES (15, "Category V", "Poor Security Practice or Policy Violation");
INSERT INTO status (status_id, description, long_desc) VALUES (16, "Category VI", "Reconnaissance/Probes/Scans");
INSERT INTO status (status_id, description, long_desc) VALUES (17, "Category VII", "Virus Infection");
CREATE TABLE version
(
version VARCHAR(32),
installed DATETIME
);
INSERT INTO version (version, installed) VALUES ("0.12", now());