# database: snort



# 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
CREATE TABLE detail(
	detail_type TINYINT(3) UNSIGNED NOT NULL,
	detail_text TEXT NOT NULL,
	PRIMARY KEY (detail_type)
);

# 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
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
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
CREATE TABLE dm_firewall_violators(
	ip_addr INT(10) UNSIGNED NULL,
	violations INT(11) NULL,
	last_violation DATETIME NOT NULL
);

# 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
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
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
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
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
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
CREATE TABLE dm_monitor_alert_rules(
	sid INT(10) NULL,
	grouping VARCHAR(30) 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,
	host_name VARCHAR(100) NULL,
	PRIMARY KEY (alert_uid)
);

# 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(100) 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
CREATE TABLE dm_monitor_current(
	sid INT(10) UNSIGNED NOT NULL,
	service VARCHAR(20) NOT NULL,
	ip_addr INT(10) UNSIGNED NULL,
	host_name VARCHAR(100) 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,
	check_dns INT(1) NULL,
	INDEX sid_index (sid)
);

# 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,
	host_name VARCHAR(100) NOT NULL,
	INDEX sid (sid),
	INDEX eid (eid)
);

# 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
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,
	f3 INT(1) NOT NULL,
	PRIMARY KEY (username),
	INDEX dm_sessions (current_session_id)
);

# encoding
CREATE TABLE encoding(
	encoding_type TINYINT(3) UNSIGNED NOT NULL,
	encoding_text TEXT NOT NULL,
	PRIMARY KEY (encoding_type)
);

# 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
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
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
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
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
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
CREATE TABLE schema(
	vseq INT(10) UNSIGNED NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (vseq)
);

# 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
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
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
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))
);

# 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
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)
);

INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');
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');
INSERT INTO schema  (vseq, ctime) VALUES ('103', now());
INSERT INTO dm_sessions VALUES (
'admin','myTaxdrg53/9A','','','','',1,'','','','');


