-- $Id: pgsql_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_str, ip_daddr_str, ip_protocol, tcp_sport, tcp_dport
--    udp_sport, udp_dport, icmp_type, icmp_code
--     

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

--  "oob_hook" smallint DEFAULT NULL,         -- numeric netfilter hook -- NOT IMPLEMENTED
--  "raw_type" bigint 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.

CREATE INDEX "log_table_oob_time_sec" ON "log_table" (oob_time_sec);
CREATE INDEX "log_table_oob_family" ON "log_table" (oob_family);
-- CREATE INDEX "log_table_ip_saddr" ON "log_table" (ip_saddr);
-- CREATE INDEX "log_table_ip_daddr" ON "log_table" (ip_daddr);
CREATE INDEX "log_table_ip_saddr_str" ON "log_table" (ip_saddr_str);
CREATE INDEX "log_table_ip_daddr_str" ON "log_table" (ip_daddr_str);
CREATE INDEX "log_table_ip_protocol" ON "log_table" (ip_protocol);
CREATE INDEX "log_table_tcp_sport" ON "log_table" (tcp_sport);
CREATE INDEX "log_table_tcp_dport" ON "log_table" (tcp_dport);
CREATE INDEX "log_table_udp_sport" ON "log_table" (udp_sport);
CREATE INDEX "log_table_udp_dport" ON "log_table" (udp_dport);

-- vim: set syntax=sql:
