Query Database shows details of all queries
Query Database seems to be missing lots of data. This data was available earlier today, but seems to have been cleared. This has led to Total Queries and Client Activity graphs displaying large gaps. When I now look at the figures, the number of queries appears to be reducing, not increasing.
Something is seriously not right at the moment!
The screen shots don't necessarily indicate a problem with the database. It could be that your Pi-hole was offline for that period of time. As Pi-hole runs, the query log is populated from memory and written to the query database.
You can confirm this by looking in the dnsmasq logs for today and yesterday and see if there is any activity recorded in that log during the period in question.
Today's log is at /var/log/pihole.log
Yesterday's log is at /var/log/pihole.log.1
This is not unexpected, since the totals shown in the dashboard are for the most recent 24 hours. As the period of high activity on the left slides off the graph and is replaced with a lower activity period entering from the right, the numbers will decrease.
Edit:
Did you make any changes to your query database just prior to this? It is not showing the previous 24 hour history you would expect on a Pi-hole restart (the 24 hours are read from the query database).
My Pi-hole definitely wasn't offline - the data was showing earlier, but not showing now. The only downtime has been upon a reboot earlier, which would have been downtime of approx 1min.
I didn't make any changes to the query database...consciously anyway!
What is the output of the following from the Pi terminal
ls -lha /etc/pihole/pihole*
So, I've checked the dnsmasq log for today...
Both of those times are showing as empty on the graphs 
-rw-rw-r-- 1 pihole root 15 Oct 4 14:36 /etc/pihole/pihole-FTL.conf
-rw-rw-r-- 1 pihole pihole 2.1G Oct 5 14:00 /etc/pihole/pihole-FTL.db
It appears that something caused a problem with your query database. It may be damaged, or just missing some information.
You saw a full query log on the dashboard (which is normal since the query log is populated from memory while Pi-hole is running). The query information is also written to the long term database every minute (by default).
When you rebooted, the memory goes away and when Pi-hole starts again it reads the most recent 24 hours from the query database to populate the dashboard. That's where the missing information showed itself.
That's a pretty hefty database size. Almost 10x the size of mine on a Pi that serves about 35 clients.
I would do the following steps to start with a new database (while saving your existing). This will clear your query log on the dashboard, but it will fully populate over the next 24 hours.
sudo service pihole-FTL stop
sudo mv /etc/pihole/pihole-FTL.db /etc/pihole/pihole-FTL-old.db
sudo service pihole-FTL start
I have 43 clients (if the Total Queries figure is to be trusted)
Will give that database rebuild a go now, thanks!
[2021-10-05 14:00:00.521 553/T557] SQLite3 message: statement aborts at 10: [DELETE FROM queries WHERE timestamp <= 1601902800] database disk image is malformed (11)
[2021-10-05 14:00:00.523 553/T557] ERROR: SQL query "DELETE FROM queries WHERE timestamp <= 1601902800" failed: database disk image is malformed
[2021-10-05 14:00:00.523 553/T557] WARN: Database /etc/pihole/pihole-FTL.db is damaged and cannot be used.
[2021-10-05 14:00:00.524 553/T557] delete_old_queries_in_DB(): Deleting queries due to age of entries failed!
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.