Customize amount of Netflow aggregator history kept

[n-thumann]

This PR introduces the ability for a user to customize the amount of history that is kept by the Netflow aggregator instead of using hardcoded values. For the source address details there's always 62 days of history kept, even though a user might want to keep more / less.

Why is this needed? I regularly ran into the same issue as https://github.com/opnsense/core/issues/4831, https://forum.opnsense.org/index.php?topic=30071.msg145214 or https://forum.opnsense.org/index.php?topic=20489.msg95251. In these cases the SQLite DBs of the aggregator take up a lot of space and cause the vacuum to fail.

I therefore moved the hardcoded values to a config file, which is written by configd. I kept the old history values as defaults. The newly added fields can be access via Reporting -> Netflow after enabling the advanced settings.

[AdSchellevis]

I'll try to take a look, but at a first glance I would rather try to focus on less toggles facing the user.

[n-thumann]

focus on less toggles facing the user

Yeah, i was thinking about this as well, but am struggling to come up with a good idea. Any ideas and how to incorporate them in the UI?

[AdSchellevis]

I'm not sure yet either to be honest, we could try to focus on the most problematic files in size first and uniform it a bit, the more aggregated ones are likely less problematic. How large are the files on your installation?

[n-thumann]

root@OPNsense:~ # ls -lh /var/netflow/
total 8889260
-rw-r-----  1 root  wheel   1.9M Oct  7 01:17 dst_port_000300.sqlite
-rw-r-----  1 root  wheel    21M Oct  7 01:17 dst_port_003600.sqlite
-rw-r-----  1 root  wheel   419M Oct  7 01:17 dst_port_086400.sqlite
-rw-r-----  1 root  wheel   2.3M Oct  7 01:17 interface_000030.sqlite
-rw-r-----  1 root  wheel   1.5M Oct  7 01:17 interface_000300.sqlite
-rw-r-----  1 root  wheel   216K Oct  7 01:17 interface_003600.sqlite
-rw-r-----  1 root  wheel   200K Oct  7 01:17 interface_086400.sqlite
-rw-r-----  1 root  wheel    12K Oct  7 01:17 metadata.sqlite
-rw-r-----  1 root  wheel   8.0M Oct  7 01:17 src_addr_000300.sqlite
-rw-r-----  1 root  wheel    85M Oct  7 01:17 src_addr_003600.sqlite
-rw-r-----  1 root  wheel   3.1G Oct  7 01:17 src_addr_086400.sqlite
-rw-r-----  1 root  wheel   4.9G Oct  7 01:20 src_addr_details_086400.sqlite

src_addr_details_086400.sqlite being the largest aligns with what other people reported (see links above).

Or would it be an option to move Capture local and my newly added fields to a new tab called Collect? While this wouldn't solve the issue of too many fields facing the user, it'd at least spreads the problem to another page.

[AdSchellevis]

Maybe it's enough to just offer the maximum numbers of days to keep given the daily summary sizes are the problematic ones.

If we choose to configure the src_addr_details_086400.sqlite to a relative number, we only need a single setting (1/6 th of specified days for example).

[n-thumann]

Good point! Based on this, we could only add one field for each resolution available (i.e. 30, 300, ...), instead of adding one for each aggregator. This would reduce the number of fields added from eleven down to four. But only having a single setting per resolution (or only one at all, if we only want the 86400 sec. resolution) has one big issue:

• interface, port and source keep 365 days per default
• source details keeps 61 days per default

If we'd join their history setting, we would either truncate a lot of history (by using 61 days for all) or make the problem even worst (by using 365 days for all) :/

[AdSchellevis]

I was more thinking in the direction of configuring the max and stick source details to a relative factor e.g 1/6 x configured would be roughly the same as it is now.