From 869f1640e807f1fda4c410fe517d1aa8d74af6cf Mon Sep 17 00:00:00 2001
From: Pablo Neira Ayuso <pablo@netfilter.org>
Date: Fri, 25 Feb 2011 13:06:20 +0100
Subject: sqlite3: add configuration examples and new tables layout

This patch adds two configuration examples for sqlite3 to log
flows and packets.

We use two tables, one for packet logging information, and
another for flow-based information.

Signed-off-by: Pablo Neira Ayuso <pablo@netfilter.org>
---
 doc/sqlite3.table | 49 ++++++++++++++++++++++++++++---------------------
 doc/sqlite3.txt   |  7 +++++++
 ulogd.conf.in     | 16 ++++++++++++++++
 3 files changed, 51 insertions(+), 21 deletions(-)
 create mode 100644 doc/sqlite3.txt

diff --git a/doc/sqlite3.table b/doc/sqlite3.table
index 7b5e99a..393b386 100644
--- a/doc/sqlite3.table
+++ b/doc/sqlite3.table
@@ -1,22 +1,29 @@
-CREATE TABLE ulog (
-			raw_mac		VARCHAR(80),
-			oob_time_sec	INT UNSIGNED,
-			oob_time_usec	INT UNSIGNED,
-			ip_saddr	INT UNSIGNED,
-			ip_daddr	INT UNSIGNED,
-			ip_protocol	TINYINT UNSIGNED,
-			ip_totlen       SMALLINT UNSIGNED,
-			tcp_sport	SMALLINT UNSIGNED,
-			tcp_dport	SMALLINT UNSIGNED,
-			udp_sport	SMALLINT UNSIGNED,
-			udp_dport	SMALLINT UNSIGNED,
-			udp_len		SMALLINT UNSIGNED,
-			icmp_type	TINYINT UNSIGNED,
-			icmp_code	TINYINT UNSIGNED,
-			icmp_echoid	SMALLINT UNSIGNED,
-			icmp_echoseq	SMALLINT UNSIGNED,
-			icmp_gateway	INT UNSIGNED,
-			icmp_fragmtu	SMALLINT UNSIGNED
+CREATE TABLE ulog_ct (
+			flow_start_sec		INT UNSIGNED,
+			flow_start_usec		INT UNSIGNED,
+			flow_end_sec		INT UNSIGNED,
+			flow_end_usec		INT UNSIGNED,
+			orig_ip_saddr		INT UNSIGNED,
+			orig_ip_daddr		INT UNSIGNED,
+			orig_l4_sport		SMALLINT UNSIGNED,
+			orig_l4_dport		SMALLINT UNSIGNED,
+			orig_ip_protocol	TINYINT UNSIGNED,
+			icmp_type		TINYINT UNSIGNED,
+			icmp_code		TINYINT UNSIGNED,
+			orig_raw_pktlen		INT UNSIGNED,
+			orig_raw_pktcount	INT UNSIGNED,
+			reply_raw_pktlen	INT UNSIGNED,
+			reply_raw_pktcount	INT UNSIGNED,
+			ct_mark			INT UNSIGNED
+		);
+CREATE TABLE ulog_pkt (
+			raw_pktlen		INT UNSIGNED,
+			raw_pktcount		INT UNSIGNED,
+			oob_prefix		VARCHAR(64),
+			oob_time_sec		INT UNSIGNED,
+			oob_time_usec		INT UNSIGNED,
+			oob_mark		INT UNSIGNED,
+			oob_hook		TINYINT UNSIGNED,
+			oob_uid			INT UNSIGNED,
+			oob_gid			INT UNSIGNED
 		);
-
-			
diff --git a/doc/sqlite3.txt b/doc/sqlite3.txt
new file mode 100644
index 0000000..97e8bc9
--- /dev/null
+++ b/doc/sqlite3.txt
@@ -0,0 +1,7 @@
+XXX: This has to go in ulogd.sgml, later.
+
+To create the database file, you have to:
+$ sqlite3 file.db < sqlite3.table
+
+To check that we are logging stuff into it correctly:
+sqlite3 ulogd.sqlite3db "SELECT * from ulog"
diff --git a/ulogd.conf.in b/ulogd.conf.in
index d944c92..b7b714b 100644
--- a/ulogd.conf.in
+++ b/ulogd.conf.in
@@ -39,6 +39,7 @@ plugin="@pkglibexecdir@/ulogd_filter_PRINTFLOW.so"
 plugin="@pkglibexecdir@/ulogd_output_LOGEMU.so"
 plugin="@pkglibexecdir@/ulogd_output_SYSLOG.so"
 plugin="@pkglibexecdir@/ulogd_output_XML.so"
+#plugin="@pkglibexecdir@/ulogd_output_SQLITE3.so"
 #plugin="@pkglibexecdir@/ulogd_output_OPRINT.so"
 #plugin="@pkglibexecdir@/ulogd_output_NACCT.so"
 #plugin="@pkglibexecdir@/ulogd_output_PCAP.so"
@@ -95,6 +96,11 @@ plugin="@pkglibexecdir@/ulogd_raw2packet_BASE.so"
 # this is a stack for flow-based logging to PGSQL without local hash
 #stack=ct1:NFCT,ip2str1:IP2STR,pgsql3:PGSQL
 
+# this is a stack for flow-based logging to SQLITE3
+#stack=ct1:NFCT,sqlite3_ct:SQLITE3
+
+# this is a stack for logging packet to SQLITE3
+#stack=log1:NFLOG,sqlite3_pkt:SQLITE3
 
 # this is a stack for flow-based logging in NACCT compatible format
 #stack=ct1:NFCT,ip2str1:IP2STR,nacct1:NACCT
@@ -216,6 +222,16 @@ table="ulog"
 pass="ulog2"
 procedure="INSERT_PACKET_FULL"
 
+[sqlite3_ct]
+table="ulog_ct"
+db="/var/log/ulogd.sqlite3db"
+buffer=200
+
+[sqlite3_pkt]
+table="ulog_pkt"
+db="/var/log/ulogd.sqlite3db"
+buffer=200
+
 [sys2]
 facility=LOG_LOCAL2
 
-- 
cgit v1.2.3