-- $Id: snort_view 503 2012-12-09 01:48:29Z bhockney $
--
-- Based on version 107 of snort data layout.
-- SELECT vseq FROM schema;
--
-- If your version of the snort schema is older or newer than this you may
-- need to adjust the following CREATE VIEW statement.
--
CREATE VIEW $SNORT_VIEW AS
SELECT
	e.cid      AS "id",               -- event ID
	extract('epoch' FROM timestamp)::bigint
	           AS "oob_time_sec",
	hostname   AS "local_hostname",
	sig_class_name
	           AS "oob_prefix",
	interface  AS "oob_in",
	ip_ver     AS "ip_ver",
	ip_hlen    AS "ip_ihl",
	ip_tos     AS "ip_tos",
	ip_len     AS "ip_totlen",
	ip_id      AS "ip_id",
	ip_off     AS "ip_fragoff",
	ip_ttl     AS "ip_ttl",
	ip_proto   AS "ip_protocol",
	ip_csum    AS "ip_csum",
	ip_src     AS "ip_saddr",
	ip_dst     AS "ip_daddr",
	tcp_sport  AS "tcp_sport",
	tcp_dport  AS "tcp_dport",
	tcp_seq    AS "tcp_seq",
	tcp_ack    AS "tcp_ackseq",
	tcp_off    AS "tcp_off",          -- not in ulog
--
--   ** tcp_flags bit map **
--
--   This is based on what version 2.8.1 of snort actually does, but at the
--   bottom of create_postgresql there is a reference to snortdb-extra, which
--   is no longer included in the snort sources but which in older versions
--   appears to reverse the mapping of the flags.  If you are running an old
--   version of snort and get strange results for tcp flags, try using the
--   mapping from snortdb-extra, below.
--
--   FROM: decode.h in snort sources
--   --------------------
--   #define TH_FIN  0x01
--   #define TH_SYN  0x02
--   #define TH_RST  0x04
--   #define TH_PUSH 0x08
--   #define TH_ACK  0x10
--   #define TH_URG  0x20
--   #define TH_RES2 0x40
--   #define TH_RES1 0x80
--   --------------------
--
	CASE WHEN (tcp_flags::int4 &   1 =   1) THEN TRUE ELSE FALSE END AS "tcp_fin",
	CASE WHEN (tcp_flags::int4 &   2 =   2) THEN TRUE ELSE FALSE END AS "tcp_syn",
	CASE WHEN (tcp_flags::int4 &   4 =   4) THEN TRUE ELSE FALSE END AS "tcp_rst",
	CASE WHEN (tcp_flags::int4 &   8 =   8) THEN TRUE ELSE FALSE END AS "tcp_psh",
	CASE WHEN (tcp_flags::int4 &  16 =  16) THEN TRUE ELSE FALSE END AS "tcp_ack",
	CASE WHEN (tcp_flags::int4 &  32 =  32) THEN TRUE ELSE FALSE END AS "tcp_urg",
	CASE WHEN (tcp_flags::int4 &  64 =  64) THEN TRUE ELSE FALSE END AS "tcp_res2",
	CASE WHEN (tcp_flags::int4 & 128 = 128) THEN TRUE ELSE FALSE END AS "tcp_res1",
--
--   FROM: snortdb-extra
--   +------------------+
--   | RES1 |     |   1 |
--   | RES2 |     |   2 |
--   | URG  | URG |   4 |
--   | ACK  | ACK |   8 |
--   | PSH  | PSH |  16 |
--   | RST  | RST |  32 |
--   | SYN  | SYN |  64 |
--   | FIN  | FIN | 128 |
--   +------------------+
--
--	CASE WHEN (tcp_flags::int4 &   1 =   1) THEN TRUE ELSE FALSE END AS "tcp_res1",
--	CASE WHEN (tcp_flags::int4 &   2 =   2) THEN TRUE ELSE FALSE END AS "tcp_res2",
--	CASE WHEN (tcp_flags::int4 &   4 =   4) THEN TRUE ELSE FALSE END AS "tcp_urg",
--	CASE WHEN (tcp_flags::int4 &   8 =   8) THEN TRUE ELSE FALSE END AS "tcp_ack",
--	CASE WHEN (tcp_flags::int4 &  16 =  16) THEN TRUE ELSE FALSE END AS "tcp_psh",
--	CASE WHEN (tcp_flags::int4 &  32 =  32) THEN TRUE ELSE FALSE END AS "tcp_rst",
--	CASE WHEN (tcp_flags::int4 &  64 =  64) THEN TRUE ELSE FALSE END AS "tcp_syn",
--	CASE WHEN (tcp_flags::int4 & 128 = 128) THEN TRUE ELSE FALSE END AS "tcp_fin",
--
	tcp_win    AS "tcp_window",
	tcp_urp    AS "tcp_urgp",
	tcp_csum   AS "tcp_csum",         -- not in ulog
	udp_sport  AS "udp_sport",
	udp_dport  AS "udp_dport",
	udp_len    AS "udp_len",
	udp_csum   AS "udp_csum",         -- not in ulog
	icmp_type  AS "icmp_type",
	icmp_code  AS "icmp_code",
	icmp_id    AS "icmp_echoid",
	icmp_seq   AS "icmp_echoseq"

FROM event e 
	LEFT JOIN sensor    s ON e.sid = s.sid
	LEFT JOIN iphdr     p ON e.cid = p.cid AND e.sid = p.sid
	LEFT JOIN tcphdr    t ON e.cid = t.cid AND e.sid = t.sid
	LEFT JOIN udphdr    u ON e.cid = u.cid AND e.sid = u.sid
	LEFT JOIN icmphdr   i ON e.cid = i.cid AND e.sid = i.sid
	LEFT JOIN signature g ON e.signature = g.sig_id
	LEFT JOIN sig_class l USING (sig_class_id)
;
