-- $Id: mysql_data_table.sql 554 2014-04-19 16:20:00Z bhockney $
--
-- For database logs, webfwlog expects data with these column names and types.
-- It can be an actual table containing your logs or a view mapping your logs
-- to these names and types.  Field widths for character types can be whatever
-- suits your installation, but except as noted below integer types are the
-- minimum needed to contain all valid values.
--
-- You only need to include the columns you want, but at a minimum you should
-- include one of the following sets of fields:
--
--    ip_saddr, ip_daddr, ip_protocol, tcp_sport, tcp_dport,
--    udp_sport, udp_dport, icmp_type, icmp_code
--
--    oob_family, ip_saddr_bin, ip_daddr_bin, ip_protocol, tcp_sport, tcp_dport
--    udp_sport, udp_dport, icmp_type, icmp_code
--     

CREATE TABLE `log_table` (
-- Type for id field can also be bigint, or any other valid numeric type
  `id` int(10) unsigned NOT NULL auto_increment,      -- Unique record number
  `local_hostname` varchar(32) DEFAULT NULL,          -- Logging host hostname
  `oob_time_sec` int(10) unsigned DEFAULT NULL,       -- Time in seconds since epoch (unixtime)
  `oob_time_usec` int(10) unsigned DEFAULT NULL,      -- Time microseconds
  `oob_prefix` varchar(32) DEFAULT NULL,              -- Logging prefix or label
  `oob_mark` int(10) unsigned DEFAULT NULL,           -- Firewall mark
  `oob_in` varchar(32) DEFAULT NULL,                  -- Input interface
  `oob_out` varchar(32) DEFAULT NULL,                 -- Output interface
  `oob_family` tinyint(3) unsigned DEFAULT NULL,      -- Address family, AF_INET(2) or AF_INET6(10)
-- you need either ip_saddr and ip_daddr as unsigned integers
-- or ip_saddr_bin and ip_saddr_bin as binary strings.
-- binary(16) type is required for ipv6
  `ip_saddr` int(10) unsigned DEFAULT NULL,           -- Source address as unsigned 32 bit integer (IPv4 only)
  `ip_daddr` int(10) unsigned DEFAULT NULL,           -- Destination address as unsigned 32 bit integer (IPv4 only)
--  `ip_saddr_bin` binary(16) DEFAULT NULL,             -- Source address as 16 byte (128 bit) binary string
--  `ip_daddr_bin` binary(16) DEFAULT NULL,             -- Destination address 16 byte (128 bit) binary string
  `ip_protocol` tinyint(3) unsigned DEFAULT NULL,     -- Numeric IP protocol
  `ip_tos` tinyint(3) unsigned DEFAULT NULL,          -- IPv4 tos header field
  `ip_ttl` tinyint(3) unsigned DEFAULT NULL,          -- IPv4 ttl header field
  `ip_totlen` smallint(5) unsigned DEFAULT NULL,      -- IPv4 total length header field
  `ip_ihl` tinyint(3) unsigned DEFAULT NULL,          -- IPv4 header length field
  `ip_id` smallint(5) unsigned DEFAULT NULL,          -- IPv4 ip id header field
  `ip_fragoff` smallint(5) unsigned DEFAULT NULL,     -- IPv4 fragmentation offset header field, including MF and DF
  `ip6_payloadlen` smallint(5) unsigned DEFAULT NULL, -- IPv6 payload length header field 
  `ip6_priority` tinyint(3) unsigned DEFAULT NULL,    -- IPv6 traffic class header field
  `ip6_hoplimit` tinyint(3) unsigned DEFAULT NULL,    -- IPv6 hoplimit header field
  `ip6_flowlabel` int(10) DEFAULT NULL,               -- IPv6 flowlabel header field
  `ip6_fragoff` smallint(5) DEFAULT NULL,             -- IPv6 fragmentation offset  option header field
  `ip6_fragid` int(10) unsigned DEFAULT NULL,         -- IPv6 fragmentation id option header field
  `tcp_sport` smallint(5) unsigned DEFAULT NULL,      -- TCP source port
  `tcp_dport` smallint(5) unsigned DEFAULT NULL,      -- TCP destination port
  `tcp_seq` int(10) unsigned DEFAULT NULL,            -- TCP sequence number
  `tcp_ackseq` int(10) unsigned DEFAULT NULL,         -- TCP ack sequence number
  `tcp_window` smallint(5) unsigned DEFAULT NULL,     -- TCP window
-- TCP flags below are boolean (1 or 0) and could
-- be bit(1) type for MySQL 5.0 or later
  `tcp_syn` tinyint(4) DEFAULT NULL,                  -- TCP flag SYN (boolean)
  `tcp_ack` tinyint(4) DEFAULT NULL,                  -- TCP flag ACK (boolean)
  `tcp_fin` tinyint(4) DEFAULT NULL,                  -- TCP flag FIN (boolean)
  `tcp_rst` tinyint(4) DEFAULT NULL,                  -- TCP flag RST (boolean)
  `tcp_psh` tinyint(4) DEFAULT NULL,                  -- TCP flag PSH (boolean)
  `tcp_urg` tinyint(4) DEFAULT NULL,                  -- TCP flag URG (boolean)
  `tcp_urgp` smallint(5) unsigned DEFAULT NULL,       -- TCP urgent pointer
  `udp_sport` smallint(5) unsigned DEFAULT NULL,      -- UDP source port
  `udp_dport` smallint(5) unsigned DEFAULT NULL,      -- UDP destination port
  `udp_len` smallint(5) unsigned DEFAULT NULL,        -- UDP length
  `icmp_type` tinyint(3) unsigned DEFAULT NULL,       -- ICMPv4 type
  `icmp_code` tinyint(3) unsigned DEFAULT NULL,       -- ICMPv4 code
  `icmp_echoid` smallint(5) unsigned DEFAULT NULL,    -- ICMPv4 echo id
  `icmp_echoseq` smallint(5) unsigned DEFAULT NULL,   -- ICMPv4 echo sequence number
  `icmp_gateway` int(10) unsigned DEFAULT NULL,       -- ICMPv4 redirect gateway
  `icmp_fragmtu` smallint(5) unsigned DEFAULT NULL,   -- ICMPv4 fragmentation MTU
  `icmpv6_type` tinyint(3) unsigned DEFAULT NULL,     -- ICMPv6 type
  `icmpv6_code` tinyint(3) unsigned DEFAULT NULL,     -- ICMPv6 code
  `icmpv6_echoid` smallint(5) unsigned DEFAULT NULL,  -- ICMPv6 echo id
  `icmpv6_echoseq` smallint(5) unsigned DEFAULT NULL, -- ICMPv6 echo sequence number
  `mac_str` varchar(256) DEFAULT NULL,                -- MAC address string
  `oob_protocol` smallint(5) unsigned DEFAULT NULL,   -- ethernet protocol
  `mac_saddr_str` varchar(32) DEFAULT NULL,           -- Source MAC address
  `mac_daddr_str` varchar(32) DEFAULT NULL,           -- Destination MAC address

--  `oob_hook` tinyint(3) unsigned DEFAULT NULL,      -- numeric netfilter hook -- NOT IMPLEMENTED
--  `raw_type` int(10) unsigned DEFAULT NULL,         -- arp type -- NOT IMPLEMENTED

  PRIMARY KEY (id),

-- Create indexes on columns frequently referenced in queries to improve performance.
-- You can create as many or few as you need.

  INDEX `oob_time_sec` (oob_time_sec),
  INDEX `ip_saddr`     (ip_saddr),
  INDEX `ip_daddr`     (ip_daddr),
-- INDEX `ip_saddr`      (ip_saddr_bin),
-- INDEX `ip_daddr`      (ip_daddr_bin),
  INDEX `ip_protocol`  (ip_protocol),
  INDEX `tcp_sport`    (tcp_sport),
  INDEX `tcp_dport`    (tcp_dport),
  INDEX `udp_sport`    (udp_sport),
  INDEX `udp_dport`    (udp_dport)
);

-- vim: set syntax=mysql:
