-- $Id: snort_view 683 2018-11-04 19:24:33Z bhockney $
CREATE
SQL SECURITY INVOKER
VIEW $SNORT_VIEW AS
SELECT
	e.cid      AS "id",               -- event ID
	unix_timestamp(timestamp)
	           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_mysql 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 &   1) THEN 1 ELSE 0 END AS "tcp_fin",
	CASE WHEN (tcp_flags &   2) THEN 1 ELSE 0 END AS "tcp_syn",
	CASE WHEN (tcp_flags &   4) THEN 1 ELSE 0 END AS "tcp_rst",
	CASE WHEN (tcp_flags &   8) THEN 1 ELSE 0 END AS "tcp_psh",
	CASE WHEN (tcp_flags &  16) THEN 1 ELSE 0 END AS "tcp_ack",
	CASE WHEN (tcp_flags &  32) THEN 1 ELSE 0 END AS "tcp_urg",
	CASE WHEN (tcp_flags &  64) THEN 1 ELSE 0 END AS "tcp_res2",
	CASE WHEN (tcp_flags & 128) THEN 1 ELSE 0 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 &   1) THEN 1 ELSE 0 END AS "tcp_res1",
--	CASE WHEN (tcp_flags &   2) THEN 1 ELSE 0 END AS "tcp_res2",
--	CASE WHEN (tcp_flags &   4) THEN 1 ELSE 0 END AS "tcp_urg",
--	CASE WHEN (tcp_flags &   8) THEN 1 ELSE 0 END AS "tcp_ack",
--	CASE WHEN (tcp_flags &  16) THEN 1 ELSE 0 END AS "tcp_psh",
--	CASE WHEN (tcp_flags &  32) THEN 1 ELSE 0 END AS "tcp_rst",
--	CASE WHEN (tcp_flags &  64) THEN 1 ELSE 0 END AS "tcp_syn",
--	CASE WHEN (tcp_flags & 128) THEN 1 ELSE 0 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 $SNORT_DB.$SNORT_TABLE e 
	LEFT JOIN $SNORT_DB.sensor    s ON e.sid = s.sid
	LEFT JOIN $SNORT_DB.iphdr     p ON e.cid = p.cid AND e.sid = p.sid
	LEFT JOIN $SNORT_DB.tcphdr    t ON e.cid = t.cid AND e.sid = t.sid
	LEFT JOIN $SNORT_DB.udphdr    u ON e.cid = u.cid AND e.sid = u.sid
	LEFT JOIN $SNORT_DB.icmphdr   i ON e.cid = i.cid AND e.sid = i.sid
	LEFT JOIN $SNORT_DB.signature g ON e.signature = g.sig_id
	LEFT JOIN $SNORT_DB.sig_class l USING (sig_class_id)
;
-- vim:syntax=mysql:
