Simple Netfilter MySQL logging via ulogd

simple-netfilter-ulogd-mysql.md

In the netfilter configuration

    chain forward {
            type filter hook forward priority filter; policy accept;
            log prefix "FORWARD" group 0
    }

The Group ID is important and is used in ulogd2 config below

Ulogd2 configuration

plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_NFLOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IFINDEX.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2STR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_PRINTPKT.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_HWHDR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_LOGEMU.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_SYSLOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_MYSQL.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_raw2packet_BASE.so"

stack=log1:NFLOG,base1:BASE,ifi1:IFINDEX,ip2str1:IP2STR,mysql1:MYSQL

Stack dedines the chain of filters used when processing the event. In this case we have the following chain (in sequence) :

• NFLOG : gets data from Netfilter event
• BASE : parse the raw data into comprehensive elements
• IFINDEX : translate numeric interface id into symbolic ones
• IP2STR : translate binary format IP addresses into readable strings
• MYSQL : output to MySQL

Each filter can be prefixed with a configuration block :

[log1]
group=0
[mysql1]
db="ulogd"
host="localhost"
user="ulogd"
table="fwevent"
pass="***mysupersecurepassword***"
procedure="INSERT"

The procedure defined a SQL stored procedure to call with the data, so that extra logic processing can be perfomed by the database engine. In our case, we are using "INSERT" and define a table to do a simple INSERT in a table (without pre-processing by the database engine).

In order to know the possible fields avaible to insert, you need to query the ulogd2 modules present in yur stack :

  ulogd -i /usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_NFLOG.so
  ulogd -i /usr/lib/x86_64-linux-gnu/ulogd/ulogd_raw2packet_BASE.so
  ulogd -i /usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IFINDEX.so
  ulogd -i /usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2STR.so

All dots in the names need to be changed to underscores for MySQL.

The resulting table structure is :

CREATE TABLE `fwevent` (
  `id` bigint(20) NOT NULL,
  `oob_prefix` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `oob_time_sec` int(32) UNSIGNED DEFAULT NULL,
  `oob_time_usec` int(32) UNSIGNED DEFAULT NULL,
  `oob_uid` int(32) UNSIGNED DEFAULT NULL,
  `ip_protocol` int(8) UNSIGNED DEFAULT NULL,
  `ip_tos` int(8) UNSIGNED DEFAULT NULL,
  `ip_ttl` int(8) UNSIGNED DEFAULT NULL,
  `ip_totlen` int(16) UNSIGNED DEFAULT NULL,
  `ip_ihl` int(8) UNSIGNED DEFAULT NULL,
  `ip_csum` int(16) UNSIGNED DEFAULT NULL,
  `ip_id` int(16) UNSIGNED DEFAULT NULL,
  `ip_fragoff` int(16) UNSIGNED DEFAULT NULL,
  `ip6_payloadlen` int(16) UNSIGNED DEFAULT NULL,
  `ip6_priority` int(8) UNSIGNED DEFAULT NULL,
  `ip6_flowlabel` int(32) UNSIGNED DEFAULT NULL,
  `ip6_hoplimit` int(8) UNSIGNED DEFAULT NULL,
  `ip6_nexthdr` int(8) UNSIGNED DEFAULT NULL,
  `ip6_fragoff` int(16) UNSIGNED DEFAULT NULL,
  `ip6_fragid` int(32) UNSIGNED DEFAULT NULL,
  `tcp_sport` int(16) UNSIGNED DEFAULT NULL,
  `tcp_dport` int(16) UNSIGNED DEFAULT NULL,
  `tcp_seq` int(32) UNSIGNED DEFAULT NULL,
  `tcp_ackseq` int(32) UNSIGNED DEFAULT NULL,
  `tcp_window` int(16) UNSIGNED DEFAULT NULL,
  `tcp_offset` int(8) UNSIGNED DEFAULT NULL,
  `tcp_reserved` int(8) UNSIGNED DEFAULT NULL,
  `tcp_urg` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_urgp` int(16) UNSIGNED DEFAULT NULL,
  `tcp_ack` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_psh` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_rst` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_syn` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_fin` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_res1` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_res2` tinyint(4) UNSIGNED DEFAULT NULL,
  `tcp_csum` int(16) UNSIGNED DEFAULT NULL,
  `udp_sport` int(16) UNSIGNED DEFAULT NULL,
  `udp_dport` int(16) UNSIGNED DEFAULT NULL,
  `udp_len` int(16) UNSIGNED DEFAULT NULL,
  `udp_csum` int(16) UNSIGNED DEFAULT NULL,
  `icmp_type` int(8) UNSIGNED DEFAULT NULL,
  `icmp_code` int(8) UNSIGNED DEFAULT NULL,
  `icmp_echoid` int(16) UNSIGNED DEFAULT NULL,
  `icmp_echoseq` int(16) UNSIGNED DEFAULT NULL,
  `icmp_fragmtu` int(16) UNSIGNED DEFAULT NULL,
  `icmp_csum` int(16) UNSIGNED DEFAULT NULL,
  `icmpv6_type` int(8) UNSIGNED DEFAULT NULL,
  `icmpv6_code` int(8) UNSIGNED DEFAULT NULL,
  `icmpv6_echoid` int(16) UNSIGNED DEFAULT NULL,
  `icmpv6_echoseq` int(16) UNSIGNED DEFAULT NULL,
  `icmpv6_csum` int(16) UNSIGNED DEFAULT NULL,
  `ahesp_spi` int(32) UNSIGNED DEFAULT NULL,
  `arp_hwtype` int(16) UNSIGNED DEFAULT NULL,
  `arp_protocoltype` int(16) UNSIGNED DEFAULT NULL,
  `arp_operation` int(16) UNSIGNED DEFAULT NULL,
  `sctp_sport` int(16) UNSIGNED DEFAULT NULL,
  `sctp_dport` int(16) UNSIGNED DEFAULT NULL,
  `sctp_csum` int(32) UNSIGNED DEFAULT NULL,
  `oob_in` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `oob_out` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `ip_saddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `ip_daddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `orig_ip_saddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `orig_ip_daddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `reply_ip_saddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `reply_ip_daddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `arp_saddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL,
  `arp_daddr_str` varchar(32) COLLATE ascii_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;