# database: snort

# 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 LONGTEXT NOT NULL,
	PRIMARY KEY (sid)
);

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

# dm_general_alerts table needs to be added to allow
# demarcd clients to communicate errors back to the
# main client/ web interface


CREATE TABLE dm_general_alerts (
    sid INT(10),
    aid INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    reporting_sid INT(10),
    timestamp_reported datetime NOT NULL DEFAULT "0",
    alert_level varchar(6) NOT NULL DEFAULT "RED",
    alert_message varchar(255) NOT NULL DEFAULT "EMPTY",
    required_admin_to_dismiss char(3)
);


INSERT INTO dm_sessions VALUES (
'admin','myTaxdrg53/9A','','','','',1,'','','','');


