On ulogd2, nfacct and sqlite3

[DEXTER <mydexterid@gmail.com>]

Hi Guys!

I was searching for a _simple_ way to account traffic per host and found
numerous methods just by googleing but none of them were simple.
Then I stumbled upon ulogd2 and this page:
https://home.regit.org/2012/07/flow-accounting-with-netfilter-and-ulogd2/

Which is almost something I want, but the output should be in sqlite3.
So I tried to configure ulogd2 with sqlite3 output on Ubuntu 16.04, and
it just did not want to work.

Here are the issues I found (after all my remaining hair have fallen out):

- First, this is what comes up in the logs after starting ulogd:
"type mismatch between SQLITE3 and NFACCT in stack"
This completely misguided me on where to look for the issue. (~40% hair
loss)

- Then I realized (after a couple of hours trying to find out what is
going on) Ubuntu starts ulogd2 with --uid ulog option. When I manually
start ulogd without the --uid option it was able to write to the sqlite
file but not with --uid. I'm thinking this is probably because it cannot
communicate with the kernel after the setgid/setuid, because it is
missing the appropriate capability (maybe CAP_NET_ADMIN? idk.). (~30%
hair loss)

- The docs containing the sqlite3.table file completely missing the
table where the nfacct data can be written. Looking at
pgsql-ulogd2.sql.gz I found that the table columns should be something
like this:
INSERT INTO nfacct
(sum_name,sum_pkts,sum_bytes,oob_time_sec,oob_time_usec) (~5% hair loss)

- Ulogd logs this every pollinterval: unknown type 32816 for sum
This is because in ulogd_output_SQLITE3.c ULOGD_RET_RAW is only handled
in the default: section in the sqlite3_interp function. I don't know why
the sum is RET_RAW, I also don't know what that sum even want to be.
(because there are already sum_pkts, and sum_bytes). (~5% hair loss)

- Data is written to the sqlite database even if the sum_pkts and
sum_bytes are 0. There should be at least on option either on the input
side (nfacct) or the output side(sqlite3) that if the sums are 0 do not
write it to the database. (It just uses up space and I'm only interested
in hourly/daily/monthly/yearly traffic sums). (~6% hair loss)

Now the only thing needed for me is to regularly consolidate the sqlite
database and also show the data on a somewhat pretty web page. This has
nothing to do with ulogd and sqlite or you guys but since I couldn't
find one, I probably need to write my own. (~14% hair loss).

I hope I managed to collect all the issues I found while trying to make
this work, and hoping that someone in the future don't have to go
through this and will have the _justworks_ magic feeling.