# Schema Principles: # Initially a normalised data model # No hyphens in MySQL DB/table/column names # Verbose names use underscores for easy replacement with spaces # $string = str_replace('_', ' ', $string); # IP addresses stored as unsigned ints for performance # http://www.mybelovedphp.com/2007/05/20/storing-ip-numbers-in-mysql/ # Times stored as seconds since epoch # http://www.epochconverter.com/epoch/mysql-from-unixtime.php # http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/ # so microsecond accounting is not an issue with MySQL # Triggers used to update summary counters and last seen times to avoid # the need to perform large full table counts for summary reporting DROP DATABASE IF EXISTS honeeeboxtest; CREATE DATABASE IF NOT EXISTS honeeeboxtest; USE honeeeboxtest; CREATE TABLE IF NOT EXISTS `attacks` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `time_epoch` INT NOT NULL, `sensor_ipid` BIGINT NOT NULL, `attacker_ipid` BIGINT NOT NULL, `victim_ipid` BIGINT NOT NULL, `url` VARCHAR(100) NOT NULL, `trigger` VARCHAR(100) NOT NULL, `filename` VARCHAR(100) NOT NULL, `binaryid` BIGINT NOT NULL ); CREATE TABLE IF NOT EXISTS `binaries` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `firstseen_epoch` INT NOT NULL, `lastseen_epoch` INT NOT NULL, `countseen` BIGINT NOT NULL, `md5` VARCHAR(32) NOT NULL, `sha512` VARCHAR(64) NOT NULL, `filetype` VARCHAR(100) NOT NULL, `diskpath` VARCHAR(100) NOT NULL ); CREATE TABLE IF NOT EXISTS `geoips` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `ipn` INT UNSIGNED NOT NULL, `type` INT(1) NOT NULL, `country_short` CHAR(2), `country_long` VARCHAR(64), `region` VARCHAR(128), `city` VARCHAR(128), `isp` VARCHAR(255), `latitude` DOUBLE, `longitude` DOUBLE, `domain` VARCHAR(100), `asn` INT, `asn_prefix` VARCHAR(18), `asn_name` VARCHAR(128), `asn_country_short` CHAR(2), `asn_domain` VARCHAR(64), `asn_isp` VARCHAR(64), `firstseen_epoch` INT NOT NULL, `lastseen_epoch` INT NOT NULL, `countseen` BIGINT NOT NULL, UNIQUE KEY `uidx_ipn_type` (`ipn`, `type`) ); CREATE TABLE IF NOT EXISTS `summary` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `attacks` BIGINT NOT NULL, `geoips` BIGINT NOT NULL, `sensorips` BIGINT NOT NULL, `attackerips` BIGINT NOT NULL, `victimips` BIGINT NOT NULL, `binaries` BIGINT NOT NULL, `virustotal_info` BIGINT NOT NULL, `virustotal_results` BIGINT NOT NULL ); CREATE TABLE IF NOT EXISTS `virustotal_info` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `md5` VARCHAR(32) NOT NULL, `sha512` VARCHAR(64) NOT NULL, `filesize` BIGINT NOT NULL, `time_epoch` INT NOT NULL, `peid` VARCHAR(64) ); CREATE TABLE IF NOT EXISTS `virustotal_results` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `vtid` INT NOT NULL, `avengine` VARCHAR(32) NOT NULL, `avsigversion` VARCHAR(32), `avresult` VARCHAR(32) ); # Initialise summary tracking table insert into summary (`attacks`, `geoips`, `sensorips`, `attackerips`, `victimips`, `binaries`) values ('0', '0', '0', '0', '0', '0'); ## Triggers for tracking last seen time, counts and summaries DELIMITER $$ CREATE TRIGGER summary_geoips_trigger AFTER INSERT ON geoips FOR EACH ROW BEGIN UPDATE summary SET `geoips` = `geoips` + 1; IF NEW.type = 1 THEN UPDATE summary SET `sensorips` = `sensorips` +1; END IF; IF NEW.type = 2 THEN UPDATE summary SET `attackerips` = `attackerips` +1; END IF; IF NEW.type = 3 THEN UPDATE summary SET `victimips` = `victimips` +1; END IF; END$$ DELIMITER ; CREATE TRIGGER summary_binaries_trigger AFTER INSERT ON binaries FOR EACH ROW UPDATE summary SET `binaries` = `binaries` + 1; DELIMITER $$ CREATE TRIGGER latest_binary_trigger AFTER INSERT ON attacks FOR EACH ROW BEGIN UPDATE summary SET `attacks` = `attacks` + 1; UPDATE binaries SET `countseen` = `countseen` + 1, `lastseen_epoch` = NEW.time_epoch WHERE binaries.id = NEW.binaryid; UPDATE geoips SET `countseen` = `countseen` + 1, `lastseen_epoch` = NEW.time_epoch WHERE (geoips.id = NEW.sensor_ipid and geoips.type='1'); UPDATE geoips SET `countseen` = `countseen` + 1, `lastseen_epoch` = NEW.time_epoch WHERE (geoips.id = NEW.attacker_ipid and geoips.type='2'); UPDATE geoips SET `countseen` = `countseen` + 1, `lastseen_epoch` = NEW.time_epoch WHERE (geoips.id = NEW.victim_ipid and geoips.type='3'); END$$ DELIMITER ; CREATE TRIGGER summary_virustotal_info AFTER INSERT ON virustotal_info FOR EACH ROW UPDATE summary SET `virustotal_info` = `virustotal_info` + 1; CREATE TRIGGER summary_virustotal_results AFTER INSERT ON virustotal_results FOR EACH ROW UPDATE summary SET `virustotal_results` = `virustotal_results` + 1; GRANT ALL ON honeeeboxtest.* TO 'honeeeboxuser'@'localhost' IDENTIFIED BY 'honeeeboxpass';