Reporting - Insight - "No Data Available"

[thomasnilsen]

Jun 27 00:51:23 OPNsense root: /usr/local/etc/rc.d/flowd_aggregate: WARNING: failed to start flowd_aggregate Jun 27 00:51:24 OPNsense flowd_aggregate.py: flowd aggregate died with message Traceback (most recent call last): File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 148, in run aggregate_flowd(do_vacuum) File "/usr/local/opnsense/scripts/netflow/flowd_aggregate.py", line 79, in aggregate_flowd stream_agg_object.add(flow_record_cpy) File "/usr/local/opnsense/scripts/netflow/lib/aggregates/interface.py", line 70, in add super(FlowInterfaceTotals, self).add(flow) File "/usr/local/opnsense/scripts/netflow/lib/aggregate.py", line 258, in add self._update_cur.execute(self._update_stmt, flow) DatabaseError: database disk image is malformed

Going long time back works.. so its only newest data that is missing.

I have tried reinstalling flowd package without any luck. Rebooting, restarting etc does not help.

best regards
Thomas

[AdSchellevis]

You might have a corruption in one (or more) of the sqlite database at /var/netflow/, normally it will automatically try to repair them when damaged, but sqlite might in rare circumstances not notice it's corruption.
Another options is that your disk is full (try df -h).

[thomasnilsen]

@AdSchellevis

Filesystem Size Used Avail Capacity Mounted on
/dev/ufs/OPNsense 23G 2.9G 18G 14% /
devfs 1.0K 1.0K 0B 100% /dev
devfs 1.0K 1.0K 0B 100% /var/dhcpd/dev

Is it possible to force a repair?

[AdSchellevis]

you can try to copy the files and rebuild them, if it can't repair, you know which one is permanently broken and only remove that file (it will re-create missing files).

sqlite3 mydata.db ".dump" | sqlite3 new.db

The last time I've had a similar issue, only the detailed data was lost (which isn't kept very long).
If flowd_aggregate starts again, it might take quite some time to gain up to speed with all unprocessed logs in /var/log/flowd.log*

[thomasnilsen]

I just deleted all the sqlite databses and rebooted.

After reboot I still have data in my graphs, but not for last 24 hrs.. So the issue is same as before.

total 1238836
drwxr-x--- 2 root wheel 512B Jun 30 13:05 .
drwxr-xr-x 28 root wheel 512B May 6 21:15 ..
-rw-r----- 1 root wheel 10M Jun 24 23:59 dst_port_000300.sqlite
-rw-r----- 1 root wheel 7.4M Jun 24 23:59 dst_port_003600.sqlite
-rw-r----- 1 root wheel 277M Jun 24 23:59 dst_port_086400.sqlite
-rw-r----- 1 root wheel 2.6M Jun 25 17:01 interface_000030.sqlite
-rw-r----- 1 root wheel 1.8M Jun 24 23:59 interface_000300.sqlite
-rw-r----- 1 root wheel 728K Jun 24 23:59 interface_003600.sqlite
-rw-r----- 1 root wheel 132K Jun 24 23:59 interface_086400.sqlite
-rw-r----- 1 root wheel 8.0K Jun 24 23:59 metadata.sqlite
-rw-r----- 1 root wheel 6.2M Jun 24 23:59 src_addr_000300.sqlite
-rw-r----- 1 root wheel 5.9M Jun 24 23:59 src_addr_003600.sqlite
-rw-r----- 1 root wheel 213M Jun 24 23:59 src_addr_086400.sqlite
-rw-r----- 1 root wheel 684M Jun 24 23:59 src_addr_details_086400.sqlite
root@OPNsense:/var/netflow # date
Fri Jun 30 13:19:00 CEST 2017

I then also noticed the timestamp on the files.. 24th is the last date I have data in my grahps as well. The files re-appeared after delete and reboot.. So there is obviously a process here I do not know of.

[AdSchellevis]

I don't know why your files are back, this doesn't sound familiar.
If you want to start clean, you could easily trash all from the gui using Reporting -> Settings -> "Reset Netflow data".

If you removed only a part of the files and want to restart collection manually, you can restart the aggregation service using:

service flowd_aggregate restart

[giovino]

I recently observed this too. Don't recall what version it started. Current version (OPNsense 17.1.8-amd64)

• Last 2 hours, 30 second average -> No Data Available
• Last 8 hours, 5 minute average -> No Data Available
• Last 24 hours, 5 minute average -> No Data Available
• 7 days, 1 hour average -> (chart exists)
• 14 days, 1 hour average -> (chart exists)
• 30 days, 24 hour average -> (chart exists)
• 60 days, 24 hour average -> (chart exists)
• 90 days, 24 hour average -> (chart exists)
• 182 days, 24 hour average -> (chart exists)
• Last year, 24 hour average -> (chart exists)

[thomasnilsen]

"Reporting -> Settings -> Reset Netflow data"
and
service flowd_aggregate restart

solved my problems... @AdSchellevis @giovino , but the cause of the problem I dont know.

[AdSchellevis]

It could be a crash or improper shutdown at some point, flushing always works, but you loose history unfortunately.

[crazy-max]

I had to reset netflow data too after upgrading from 19.1.10 to 19.7.
flowd_aggregate service couldn't start and flowd.log rotation didn't work :

-rw-------   1 root             wheel              11M Jul 25 02:01 flowd.log.000010
-rw-------   1 root             wheel              11M Jul 25 04:01 flowd.log.000009
-rw-------   1 root             wheel              11M Jul 25 06:00 flowd.log.000008
-rw-------   1 root             wheel              11M Jul 25 07:37 flowd.log.000007
-rw-------   1 root             wheel              11M Jul 25 09:06 flowd.log.000006
-rw-------   1 root             wheel              11M Jul 25 11:05 flowd.log.000005
-rw-------   1 root             wheel              11M Jul 25 13:05 flowd.log.000004
-rw-------   1 root             wheel              11M Jul 25 15:05 flowd.log.000003
-rw-------   1 root             wheel              11M Jul 25 17:08 flowd.log.000002
-rw-------   1 root             wheel              11M Jul 25 19:04 flowd.log.000001
-rw-------   1 root             wheel             215M Jul 27 07:04 flowd.log