# Thanks to Craig Barraclough for this
# updated version of the script which
# now correctly adds some needed initial
# data!

# database: snort
# CREATE DATABASE snort;
use snort;

# data
DROP TABLE IF EXISTS data;

CREATE TABLE data(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	data_payload TEXT NULL,
	PRIMARY KEY (sid, cid)
);

# detail
DROP TABLE IF EXISTS detail;

CREATE TABLE detail(
	detail_type TINYINT(3) UNSIGNED NOT NULL,
	detail_text TEXT NOT NULL,
	PRIMARY KEY (detail_type)
);
INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');


# dm_cache
DROP TABLE IF EXISTS dm_cache;

CREATE TABLE dm_cache(
	sid INT(10) UNSIGNED NOT NULL,
	dm_key VARCHAR(20) NOT NULL,
	dm_content TEXT NULL,
	last_updated DATETIME NOT NULL,
	INDEX sid (sid)
);

# dm_conf
DROP TABLE IF EXISTS dm_conf;

CREATE TABLE dm_conf(
	sid INT(10) UNSIGNED NOT NULL,
	last_updated DATETIME NOT NULL,
	last_implemented DATETIME NOT NULL,
	snort_conf TEXT NOT NULL,
	PRIMARY KEY (sid)
);

# dm_firewall_rules
DROP TABLE IF EXISTS dm_firewall_rules;

CREATE TABLE dm_firewall_rules(
	sid INT(10) UNSIGNED NULL,
	ip_addr INT(10) UNSIGNED NULL,
	proto VARCHAR(6) NULL,
	port INT(5) UNSIGNED NULL,
	timestamp DATETIME NOT NULL,
	manual_remove INT(1) NOT NULL
);

# dm_firewall_violators
DROP TABLE IF EXISTS dm_firewall_violators;

CREATE TABLE dm_firewall_violators(
	ip_addr INT(10) UNSIGNED NULL,
	violations INT(11) NULL,
	last_violation DATETIME NOT NULL
);

# dm_general_config
DROP TABLE IF EXISTS dm_general_config;

CREATE TABLE dm_general_config(
	dm_key VARCHAR(20) NOT NULL,
	dm_value VARCHAR(255) NULL,
	PRIMARY KEY (dm_key)
);

# dm_ids_alert_rules
DROP TABLE IF EXISTS dm_ids_alert_rules;

CREATE TABLE dm_ids_alert_rules(
	sid INT(10) NULL,
	alert_uid INT(6) NOT NULL AUTO_INCREMENT,
	signature_is VARCHAR(255) NULL,
	signature_contains VARCHAR(255) NULL,
	priority_level INT(2) NULL,
	email_address VARCHAR(30) NULL,
	only_alert_from_hour INT(2) NOT NULL,
	only_alert_to_hour INT(2) NOT NULL,
	detail_level INT(1) NOT NULL,
	PRIMARY KEY (alert_uid)
);

# dm_log
DROP TABLE IF EXISTS dm_log;

CREATE TABLE dm_log(
	log_uid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	username VARCHAR(30) NULL,
	action VARCHAR(100) NULL,
	target VARCHAR(30) NULL,
	timestamp DATETIME NOT NULL,
	ip_address INT(10) UNSIGNED NULL,
	PRIMARY KEY (log_uid)
);

# dm_md5_alert_rules
DROP TABLE IF EXISTS dm_md5_alert_rules;

CREATE TABLE dm_md5_alert_rules(
	sid INT(10) NULL,
	monitored_sid INT(10) NULL,
	alert_uid INT(6) NOT NULL AUTO_INCREMENT,
	alert_level INT(2) NULL,
	email_address VARCHAR(60) NULL,
	only_alert_from_hour INT(2) NOT NULL,
	only_alert_to_hour INT(2) NOT NULL,
	suspend_until DATETIME NOT NULL,
	detail_level INT(1) NOT NULL,
	PRIMARY KEY (alert_uid)
);

# dm_md5_data
DROP TABLE IF EXISTS dm_md5_data;

CREATE TABLE dm_md5_data(
	md5_uid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	rule_uid INT(10) UNSIGNED NOT NULL,
	path VARCHAR(255) BINARY NOT NULL,
	k_inode INT(10) UNSIGNED NOT NULL,
	k_perms VARCHAR(4) NOT NULL,
	k_uid VARCHAR(8) NOT NULL,
	k_gid VARCHAR(8) NOT NULL,
	k_size INT(20) UNSIGNED NOT NULL,
	k_mtime INT(11) UNSIGNED NOT NULL,
	k_ctime INT(11) UNSIGNED NOT NULL,
	k_md5sum VARCHAR(60) NOT NULL,
	c_inode INT(10) UNSIGNED NOT NULL,
	c_perms VARCHAR(4) NOT NULL,
	c_uid VARCHAR(8) NOT NULL,
	c_gid VARCHAR(8) NOT NULL,
	c_size INT(20) UNSIGNED NOT NULL,
	c_mtime INT(11) UNSIGNED NOT NULL,
	c_ctime INT(11) UNSIGNED NOT NULL,
	c_md5sum VARCHAR(60) NOT NULL,
	last_confirmed DATETIME NOT NULL,
	last_checked DATETIME NOT NULL,
	last_changed DATETIME NOT NULL,
	out_of_sync INT(1) NOT NULL,
	added_flag INT(1) NOT NULL,
	modified_flag INT(1) NOT NULL,
	deleted_flag INT(1) NOT NULL,
	session_uid VARCHAR(4) NULL,
	sid INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (md5_uid)
);

# dm_md5_rules
DROP TABLE IF EXISTS dm_md5_rules;

CREATE TABLE dm_md5_rules(
	sid INT(10) UNSIGNED NOT NULL,
	rule_uid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	priority VARCHAR(6) NOT NULL,
	path VARCHAR(255) NOT NULL,
	recursive INT(1) NOT NULL,
	description VARCHAR(255) NULL,
	record_created DATETIME NOT NULL,
	session_uid VARCHAR(4) NULL,
	new_record INT(1) NOT NULL,
	PRIMARY KEY (rule_uid)
);

# dm_monitor_alert_rules
DROP TABLE IF EXISTS dm_monitor_alert_rules;

CREATE TABLE dm_monitor_alert_rules(
	sid INT(10) NULL,
	grouping VARCHAR(30) NULL,
	ip_addr INT(10) UNSIGNED NULL,
	service VARCHAR(20) NULL,
	limit_alerts INT(3) NOT NULL,
	email_address VARCHAR(30) NULL,
	continual_reds INT(1) NOT NULL,
	continual_yellows INT(1) NOT NULL,
	only_alert_from_hour INT(2) NOT NULL,
	only_alert_to_hour INT(2) NOT NULL,
	alert_uid INT(6) NOT NULL AUTO_INCREMENT,
	status_level VARCHAR(11) NULL,
	detail_level INT(1) NOT NULL,
	PRIMARY KEY (alert_uid)
);

# dm_monitor_alerts_raw
DROP TABLE IF EXISTS dm_monitor_alerts_raw;

CREATE TABLE dm_monitor_alerts_raw(
	auid VARCHAR(30) NOT NULL,
	alert_timestamp DATETIME NOT NULL,
	ip_addr INT(10) UNSIGNED NOT NULL,
	host_name VARCHAR(30) NOT NULL,
	service VARCHAR(30) NOT NULL,
	grouping VARCHAR(30) NOT NULL,
	duration INT(14) UNSIGNED NOT NULL,
	old_status VARCHAR(10) NOT NULL,
	new_status VARCHAR(10) NOT NULL
);

# dm_monitor_current
DROP TABLE IF EXISTS dm_monitor_current;

CREATE TABLE dm_monitor_current(
	sid INT(10) UNSIGNED NOT NULL,
	service VARCHAR(20) NOT NULL,
	ip_addr INT(10) UNSIGNED NOT NULL,
	host_name VARCHAR(30) NOT NULL,
	grouping VARCHAR(30) NOT NULL,
	first_checked DATETIME NOT NULL,
	last_checked DATETIME NOT NULL,
	current_status VARCHAR(10) NOT NULL,
	current_detail TEXT NOT NULL,
	client_sid INT(10) UNSIGNED NULL,
	port INT(5) UNSIGNED NULL,
	last_changed DATETIME NOT NULL,
	ext1 VARCHAR(255) NULL,
	ext2 VARCHAR(255) NULL,
	ext3 TEXT NULL,
	INDEX sid_index (sid)
);

# dm_monitor_events
DROP TABLE IF EXISTS dm_monitor_events;

CREATE TABLE dm_monitor_events(
	sid INT(10) UNSIGNED NOT NULL,
	eid INT(10) NOT NULL AUTO_INCREMENT,
	service VARCHAR(20) NULL,
	ip_addr INT(10) UNSIGNED NULL,
	status VARCHAR(111) NULL,
	detail TEXT NULL,
	event_timestamp DATETIME NOT NULL,
	INDEX sid (sid),
	INDEX eid (eid)
);

# dm_rules
DROP TABLE IF EXISTS dm_rules;

CREATE TABLE dm_rules(
	sid INT(10) UNSIGNED NOT NULL,
	rules_type VARCHAR(100) NOT NULL,
	snort_rules TEXT NOT NULL
);

# dm_sessions
DROP TABLE IF EXISTS dm_sessions;

CREATE TABLE dm_sessions(
	username VARCHAR(30) NOT NULL,
	password VARCHAR(21) NOT NULL,
	current_session_id VARCHAR(30) NOT NULL,
	current_ip INT(10) UNSIGNED NOT NULL,
	current_login_timedate DATETIME NOT NULL,
	email_address VARCHAR(60) NULL,
	admin INT(1) NOT NULL,
	ip_restrict VARCHAR(60) NULL,
	f1 INT(1) NOT NULL,
	f2 INT(1) NOT NULL,
	PRIMARY KEY (username),
	INDEX dm_sessions (current_session_id)
);
INSERT INTO dm_sessions VALUES (
'admin','myTaxdrg53/9A','','','','',1,'','','');

# encoding
DROP TABLE IF EXISTS encoding;

CREATE TABLE encoding(
	encoding_type TINYINT(3) UNSIGNED NOT NULL,
	encoding_text TEXT NOT NULL,
	PRIMARY KEY (encoding_type)
);
INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');

# event
DROP TABLE IF EXISTS event;

CREATE TABLE event(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	signature INT(10) UNSIGNED NOT NULL,
	timestamp DATETIME NOT NULL,
	PRIMARY KEY (sid, cid),
	INDEX time (timestamp),
	INDEX sid_cid_index (sid, cid),
	INDEX timestamp_index (timestamp),
	INDEX signature (signature)
);

# icmphdr
DROP TABLE IF EXISTS icmphdr;

CREATE TABLE icmphdr(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	icmp_type TINYINT(3) UNSIGNED NOT NULL,
	icmp_code TINYINT(3) UNSIGNED NOT NULL,
	icmp_csum SMALLINT(5) UNSIGNED NULL,
	icmp_id SMALLINT(5) UNSIGNED NULL,
	icmp_seq SMALLINT(5) UNSIGNED NULL,
	PRIMARY KEY (sid, cid),
	INDEX icmp_type (icmp_type),
	INDEX sid_cid_index (sid, cid)
);

# iphdr
DROP TABLE IF EXISTS iphdr;

CREATE TABLE iphdr(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	ip_src INT(10) UNSIGNED NOT NULL,
	ip_dst INT(10) UNSIGNED NOT NULL,
	ip_ver TINYINT(3) UNSIGNED NULL,
	ip_hlen TINYINT(3) UNSIGNED NULL,
	ip_tos TINYINT(3) UNSIGNED NULL,
	ip_len SMALLINT(5) UNSIGNED NULL,
	ip_id SMALLINT(5) UNSIGNED NULL,
	ip_flags TINYINT(3) UNSIGNED NULL,
	ip_off SMALLINT(5) UNSIGNED NULL,
	ip_ttl TINYINT(3) UNSIGNED NULL,
	ip_proto TINYINT(3) UNSIGNED NOT NULL,
	ip_csum SMALLINT(5) UNSIGNED NULL,
	PRIMARY KEY (sid, cid),
	INDEX ip_src (ip_src),
	INDEX ip_dst (ip_dst),
	INDEX sid_cid_index (sid, cid)
);

# opt
DROP TABLE IF EXISTS opt;

CREATE TABLE opt(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	optid INT(10) UNSIGNED NOT NULL,
	opt_proto TINYINT(3) UNSIGNED NOT NULL,
	opt_code TINYINT(3) UNSIGNED NOT NULL,
	opt_len SMALLINT(6) NULL,
	opt_data TEXT NULL,
	PRIMARY KEY (sid, cid, optid)
);

# reference
DROP TABLE IF EXISTS reference;

CREATE TABLE reference(
	ref_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	ref_system_id INT(10) UNSIGNED NOT NULL,
	ref_tag VARCHAR(20) NOT NULL,
	PRIMARY KEY (ref_id)
);

# reference_system
DROP TABLE IF EXISTS reference_system;

CREATE TABLE reference_system(
	ref_system_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	ref_system_name VARCHAR(20) NULL,
	PRIMARY KEY (ref_system_id)
);

# schema
DROP TABLE IF EXISTS schema;

CREATE TABLE schema(
	vseq INT(10) UNSIGNED NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (vseq)
);

INSERT INTO schema  (vseq, ctime) VALUES ('103', now());       

# sensor
DROP TABLE IF EXISTS sensor;

CREATE TABLE sensor(
	sid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	hostname TEXT NULL,
	interface TEXT NULL,
	filter TEXT NULL,
	detail TINYINT(4) NULL,
	encoding TINYINT(4) NULL,
	PRIMARY KEY (sid)
);

# sig_class
DROP TABLE IF EXISTS sig_class;

CREATE TABLE sig_class(
	sig_class_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	sig_class_name VARCHAR(60) NOT NULL,
	PRIMARY KEY (sig_class_id),
	INDEX sig_class_id (sig_class_id),
	INDEX sig_class_name (sig_class_name)
);

# sig_reference
DROP TABLE IF EXISTS sig_reference;

CREATE TABLE sig_reference(
	sig_id INT(10) UNSIGNED NOT NULL,
	ref_seq INT(10) UNSIGNED NOT NULL,
	ref_id INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (sig_id, ref_seq)
);

# signature
DROP TABLE IF EXISTS signature;

CREATE TABLE signature(
	sig_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	sig_name VARCHAR(255) NOT NULL,
	sig_class_id INT(10) UNSIGNED NULL,
	sig_priority INT(10) UNSIGNED NULL,
	sig_rev INT(10) UNSIGNED NULL,
	sig_sid INT(10) UNSIGNED NULL,
	PRIMARY KEY (sig_id),
	INDEX sign_idx (sig_name(20)),
	INDEX sig_class_id_idx (sig_class_id)
);

# tcphdr
DROP TABLE IF EXISTS tcphdr;

CREATE TABLE tcphdr(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	tcp_sport SMALLINT(5) UNSIGNED NOT NULL,
	tcp_dport SMALLINT(5) UNSIGNED NOT NULL,
	tcp_seq INT(10) UNSIGNED NULL,
	tcp_ack INT(10) UNSIGNED NULL,
	tcp_off TINYINT(3) UNSIGNED NULL,
	tcp_res TINYINT(3) UNSIGNED NULL,
	tcp_flags TINYINT(3) UNSIGNED NOT NULL,
	tcp_win SMALLINT(5) UNSIGNED NULL,
	tcp_csum SMALLINT(5) UNSIGNED NULL,
	tcp_urp SMALLINT(5) UNSIGNED NULL,
	PRIMARY KEY (sid, cid),
	INDEX tcp_sport (tcp_sport),
	INDEX tcp_dport (tcp_dport),
	INDEX tcp_flags (tcp_flags),
	INDEX sid_cid_index (sid, cid)
);

# udphdr
DROP TABLE IF EXISTS udphdr;

CREATE TABLE udphdr(
	sid INT(10) UNSIGNED NOT NULL,
	cid INT(10) UNSIGNED NOT NULL,
	udp_sport SMALLINT(5) UNSIGNED NOT NULL,
	udp_dport SMALLINT(5) UNSIGNED NOT NULL,
	udp_len SMALLINT(5) UNSIGNED NULL,
	udp_csum SMALLINT(5) UNSIGNED NULL,
	PRIMARY KEY (sid, cid),
	INDEX udp_sport (udp_sport),
	INDEX udp_dport (udp_dport),
	INDEX sid_cid_index (sid, cid)
);



