Fix insight < 7 Days and keep data

[Roger@Opnsense]

I'b de grateful for any tips of fixing or troubleshooting the most granular Insight graphs.

Insight worked fine for a while, but broke for me a while back after an upgrade through the UI. I am now running 18.7.8 and had hoped my problem would go away with subsequent updates, but it has persisted. I have read numerous articles, but everything I have seen that may help looked like it would loose my current history. I am hoping to be able to fix the granular graphs and preserve my history.

My Insight works partially as follows

Interface totals (bits/sec) - Last 2 hours - No Data Available
Interface totals (bits/sec) - Last 8 hours - No Data Available
Interface totals (bits/sec) - Last 24 hours - No Data Available
Interface totals (bits/sec) - All the rest seem to work fine.

Top usage ports / sources (bytes) - Last 2 hours - No Data Available
Top usage ports / sources (bytes) - Last 8 hours - No Data Available
Top usage ports / sources (bytes) - All the rest seem fine.

I have rebooted the system.
I have reinstalled the package flowd.
I ran Reporting --> Settings --> Repair Netflow Data
I checked /var/log/flowd.log and see current data using flowd-reader flowd.log

The flow logs seem to be updating fine and rotating.

root@OPNsense:/var/log # ls -lh flowd.log*
-rw-------  1 root  wheel   1.4M Nov 23 09:34 flowd.log
-rw-------  1 root  wheel    11M Nov 23 08:17 flowd.log.000001
-rw-------  1 root  wheel    11M Nov 22 23:09 flowd.log.000002
-rw-------  1 root  wheel    11M Nov 22 18:45 flowd.log.000003
-rw-------  1 root  wheel    11M Nov 22 08:45 flowd.log.000004
-rw-------  1 root  wheel    11M Nov 21 22:08 flowd.log.000005
-rw-------  1 root  wheel    11M Nov 21 13:38 flowd.log.000006
-rw-------  1 root  wheel    11M Nov 20 22:34 flowd.log.000007
-rw-------  1 root  wheel    11M Nov 20 11:28 flowd.log.000008
-rw-------  1 root  wheel    11M Nov 19 22:27 flowd.log.000009
-rw-------  1 root  wheel    11M Nov 19 07:06 flowd.log.000010

I checked unique flowd type messages in system.log (One issue starting flowd_aggregate a while ago)

root@OPNsense:/var/log # grep flowd system.log | cut -d' ' -f 5- | sort -u
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/metadata.sqlite
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/metadata.sqlite [done]
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_000300.sqlite
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_000300.sqlite [done]
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_003600.sqlite
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_003600.sqlite [done]
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_086400.sqlite
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_details_086400.sqlite
OPNsense flowd_aggregate.py: sqlite3 repair /var/netflow/src_addr_details_086400.sqlite [done]
OPNsense flowd_aggregate.py: start watching flowd
OPNsense flowd_aggregate.py: startup, check database.
OPNsense flowd_aggregate.py: vacuum /var/netflow/dst_port_000300.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/dst_port_003600.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/dst_port_086400.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/interface_000030.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/interface_000300.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/interface_003600.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/interface_086400.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/src_addr_000300.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/src_addr_003600.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/src_addr_086400.sqlite
OPNsense flowd_aggregate.py: vacuum /var/netflow/src_addr_details_086400.sqlite
OPNsense flowd_aggregate.py: vacuum done
OPNsense pkg-static: flowd reinstalled: 0.9.1_3 -> 0.9.1_3
OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate
flowd_aggregate.py: start watching flowd
flowd_aggregate.py: startup, check database.
flowd_aggregate.py: vacuum /var/netflow/dst_port_000300.sqlite
flowd_aggregate.py: vacuum /var/netflow/dst_port_003600.sqlite
flowd_aggregate.py: vacuum /var/netflow/dst_port_086400.sqlite
flowd_aggregate.py: vacuum /var/netflow/interface_000030.sqlite
flowd_aggregate.py: vacuum /var/netflow/interface_000300.sqlite
flowd_aggregate.py: vacuum /var/netflow/interface_003600.sqlite
flowd_aggregate.py: vacuum /var/netflow/interface_086400.sqlite
flowd_aggregate.py: vacuum /var/netflow/src_addr_000300.sqlite
flowd_aggregate.py: vacuum /var/netflow/src_addr_003600.sqlite
flowd_aggregate.py: vacuum /var/netflow/src_addr_086400.sqlite
flowd_aggregate.py: vacuum /var/netflow/src_addr_details_086400.sqlite
flowd_aggregate.py: vacuum done
root@OPNsense:/var/log #
root@OPNsense:/var/log # grep 'failed to start flowd_aggregate' system.log
Nov  4 09:01:15 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate
Nov  4 09:01:24 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate
Nov  4 09:01:31 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate
Nov  4 09:06:46 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate
Nov  4 09:15:22 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate

I took a shot at making sure databases were valid.

# pwd                                                                                                                                                                             
/var/netflow
# for sqlite in `ls *sqlite`
do
echo $sqlite; sqlite3 ${sqlite} 'pragma integrity_check;' '.exit'
done
dst_port_000300.sqlite
ok
dst_port_003600.sqlite
ok
dst_port_086400.sqlite
ok
interface_000030.sqlite
ok
interface_000300.sqlite
ok
interface_003600.sqlite
ok
interface_086400.sqlite
ok
metadata.sqlite
ok
src_addr_000300.sqlite
ok
src_addr_003600.sqlite
ok
src_addr_086400.sqlite
ok
src_addr_details_086400.sqlite
ok


I did not do I ran Reporting --> Settings --> Reset Netflow Data
I have been avoiding this as I did not want to loose what it has collected.

[franco]

There's a ticket here describing the odd behaviour:

https://github.com/opnsense/core/issues/2895

The gist is that "no data available" doesn't mean there is something wrong, it just means there was no data captured during the selected period.

Not entirely sure how we will address this, but it's not a real issue.


Cheers,
Franco