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. -- To unsubscribe from this list: send the line "unsubscribe netfilter-devel" in the body of a message to majordomo@xxxxxxxxxxxxxxx More majordomo info at http://vger.kernel.org/majordomo-info.html
