OS : Raspbian Linux raspberrypi 5.4.51-v7l+
Pi-hole version is v5.3.1 (Latest: v5.3.1)
AdminLTE version is v5.5 (Latest: v5.5)
FTL version is v5.8.1 (Latest: v5.8.1)
ls -lh /etc/pihole/pihole-FTL.db
-rw-r--r-- 1 pihole pihole 223M Apr 24 14:50 /etc/pihole/pihole-FTL.db
cat /etc/pihole/pihole-FTL.conf
PRIVACYLEVEL=0
Restating pihole service, restarting Pi has not helped.
The size of your pihole-FTL.db database file would suggest that long term data is available.
If your UI isn't displaying any data, it could be because of time frame differences between Pi-hole and your client.
Besides checking your times and time zones on Pi-hole's host and your client, you could try to verify that data is available for the current period, e.g. by running the following SQL commands:
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,datetime(max(timestamp), 'unixepoch', 'localtime') FROM queries;"
This will return the domain for most recently stored query with the time of request.
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain,count(domain) FROM queries \
WHERE timestamp BETWEEN strftime('%s','now','-7 day') AND strftime('%s','now') \
GROUP BY domain ORDER BY count(domain) DESC LIMIT 10;"
This will return the top queried domains and how often they've been queried during the last week.
Thank you for the response, I have checked with your command and both are returning results, for some reason, after running commands you provided and opening the UI again, the UI started to give long term data.
There is one problem though, I can see data for the past 7 days on http://pi.hole/admin/db_queries.php but when I select a greater time range such as 1 month, the query times out with an error popup :
An unknown error occurred while loading the data.
Check the server's log files (/var/log/lighttpd/error.log when you're using the default Pi-hole web server) for details. You may need to increase the memory available for Pi-hole in case you requested a lot of data.
I checked the memory and CPU usage while running the 1 month query fetch from UI and both are normal (60% CPU utilization on single core and 100 MB RAM).
However, the file /var/log/lighttpd/error.log is reporting this :
2021-04-25 13:51:17: (mod_fastcgi.c.421) FastCGI-stderr: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in /var/www/html/admin/api_db.php on line 121
Please upload a debug log and post just the token that is generated after the log is uploaded by running the following command from the Pi-hole host terminal:
Before you do so, please consider to address the underlying cause.
Memory is exhausted by huge volumes of queries, and I wouldn't expect that to be the case for a single month, unless you have a very active network with more than just a few clients.
Also, closing a full or partial DNS loop may cause queries to be repeated excessively.
Your debug log shows that you are using Conditional Fowarding, so a partial DNS loop may contribute to a higher volume of DNS requests.
Run from your Pi-hole machine, what's the output of:
Those numbers don't look too bad, though they are not exactly low either.
It's hardly representative, but my own network produces roughly about 1,000 to 2,000 requests per client in a day. Your first three clients exceed that mark.
Let's have look what's being queried the most for such an active client:
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT client,domain,count(domain) FROM queries \
WHERE client == '192.168.0.101' \
GROUP BY domain ORDER BY count(domain) DESC LIMIT 10;"
pi@raspberrypi:~ $ sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT client,domain,count(domain) FROM queries \
> WHERE client == '192.168.0.101' \
> GROUP BY domain ORDER BY count(domain) DESC LIMIT 10;"
192.168.0.101|data.mistat.india.xiaomi.com|6279
192.168.0.101|www.googleadservices.com|3883
192.168.0.101|googleads.g.doubleclick.net|3871
192.168.0.101|data.media-lab.ai|2981
192.168.0.101|app-measurement.com|2049
192.168.0.101|youtubei.googleapis.com|1893
192.168.0.101|ssl.google-analytics.com|1515
192.168.0.101|www.google.com|1408
192.168.0.101|connectivitycheck.gstatic.com|1248
192.168.0.101|data.mistat.intl.xiaomi.com|1083
I should tell here that if top clients is an historical list (and not just top client in the past day) the results are probably justified in the sense that a recent router restart gave the IP 192.168.0.101 to another of my device while it was allocated to some other device for about 4 months now.
The number 10943 before it can signify the sum of queries from this IP address 192.168.0.101 which was allocated to 2 devices (once before then another device with high usage after router restart).