Source

hbbackend / schema / hbbackend.sql

Full commit

/* Drop Indexes */

DROP INDEX IF EXISTS ASN_SHADOWSERVER_IP_IDX;
DROP INDEX IF EXISTS GEOIP_SHADOWSERVER_IP_IDX;
DROP INDEX IF EXISTS VIRUSTOTAL_REPORTS_BINARY_ID_IDX;



/* Drop Tables */

DROP TABLE IF EXISTS ASN_SHADOWSERVER;
DROP TABLE IF EXISTS ATTACKS;
DROP TABLE IF EXISTS VIRUSTOTAL_RESULTS;
DROP TABLE IF EXISTS VIRUSTOTAL_REPORTS;
DROP TABLE IF EXISTS BINARIES;
DROP TABLE IF EXISTS GEOIP_SHADOWSERVER;
DROP TABLE IF EXISTS IDENTS;
DROP TABLE IF EXISTS IPS_SOURCE;
DROP TABLE IF EXISTS IPS_TARGET;




/* Create Tables */

CREATE TABLE ASN_SHADOWSERVER
(
	ID BIGSERIAL NOT NULL UNIQUE,
	IP INET NOT NULL,
	ASN BIGINT,
	AS_NAME VARCHAR,
	CC CHAR(2),
	DOM VARCHAR,
	ISP VARCHAR,
	BGP_PREFIX INET,
	TS TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE ATTACKS
(
	ID BIGSERIAL NOT NULL UNIQUE,
	TS TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	IDENT_ID BIGINT NOT NULL,
	BINARY_ID BIGINT NOT NULL,
	SOURCE_IP INET,
	SOURCE_PORT INT,
	TARGET_IP INET,
	TARGET_PORT INT,
	URL VARCHAR(128),
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE BINARIES
(
	ID BIGSERIAL NOT NULL UNIQUE,
	MD5 CHAR(32) NOT NULL UNIQUE,
	SHA512 CHAR(128),
	FILETYPE VARCHAR(128),
	FILESIZE INT,
	SEEN_COUNT BIGINT DEFAULT 0,
	SEEN_FIRST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	SEEN_LAST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	STORED BOOLEAN DEFAULT 'FALSE' NOT NULL,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE GEOIP_SHADOWSERVER
(
	ID BIGSERIAL NOT NULL UNIQUE,
	IP INET NOT NULL,
	CC CHAR(2),
	CITY VARCHAR,
	LATITUDE FLOAT,
	LONGITUDE FLOAT,
	TS TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE IDENTS
(
	ID BIGSERIAL NOT NULL UNIQUE,
	IDENT VARCHAR(16) NOT NULL UNIQUE,
	SEEN_COUNT BIGINT DEFAULT 0,
	SEEN_FIRST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	SEEN_LAST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE IPS_SOURCE
(
	IP INET NOT NULL UNIQUE,
	SEEN_COUNT BIGINT DEFAULT 0,
	SEEN_FIRST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	SEEN_LAST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	PRIMARY KEY (IP)
) WITHOUT OIDS;


CREATE TABLE IPS_TARGET
(
	IP INET NOT NULL UNIQUE,
	SEEN_COUNT BIGINT DEFAULT 0,
	SEEN_FIRST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	SEEN_LAST TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	PRIMARY KEY (IP)
) WITHOUT OIDS;


CREATE TABLE VIRUSTOTAL_REPORTS
(
	ID BIGSERIAL NOT NULL UNIQUE,
	BINARY_ID BIGINT NOT NULL,
	TS TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
	RESPONSE_CODE INT,
	SCAN_ID VARCHAR,
	SCAN_DATE TIMESTAMP WITH TIME ZONE,
	PERMALINK VARCHAR,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE VIRUSTOTAL_RESULTS
(
	REPORT_ID BIGINT NOT NULL,
	LABEL VARCHAR,
	ENGINE VARCHAR,
	E_VERSION VARCHAR,
	E_UPDATE VARCHAR
) WITHOUT OIDS;



/* Create Foreign Keys */

ALTER TABLE ATTACKS
	ADD FOREIGN KEY (BINARY_ID)
	REFERENCES BINARIES (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE VIRUSTOTAL_REPORTS
	ADD FOREIGN KEY (BINARY_ID)
	REFERENCES BINARIES (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE ATTACKS
	ADD FOREIGN KEY (IDENT_ID)
	REFERENCES IDENTS (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE VIRUSTOTAL_RESULTS
	ADD FOREIGN KEY (REPORT_ID)
	REFERENCES VIRUSTOTAL_REPORTS (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;



/* Create Indexes */

CREATE INDEX ASN_SHADOWSERVER_IP_IDX ON ASN_SHADOWSERVER (IP);
CREATE INDEX GEOIP_SHADOWSERVER_IP_IDX ON GEOIP_SHADOWSERVER (IP);
CREATE INDEX VIRUSTOTAL_REPORTS_BINARY_ID_IDX ON VIRUSTOTAL_REPORTS (BINARY_ID);