Long term Data is not getting populated | PiHole v5.3.1

teraform · April 24, 2021, 9:21am

Expected Behaviour:

Pi Hole should display long term data including graphs and top charts at http://pi.hole/admin/db_graph.php and http://pi.hole/admin/db_lists.php

Actual Behaviour:

Long term data is blank on UI

Some more info :

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.

Bucking_Horn · April 24, 2021, 9:49am

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.

teraform · April 25, 2021, 8:33am

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

The same time range (7 days, 1 month, This year) are working fine on http://pi.hole/admin/db_lists.php

Do I need to allocate more memory to PHP or do some other config to mitigate FastCGI-stderr: PHP Fatal error

Bucking_Horn · April 25, 2021, 8:34am

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:

pihole -d

or do it through the Web interface:

Tools > Generate Debug Log

teraform · April 25, 2021, 8:50am

Debug Token is : a4ovgjk385

Bucking_Horn · April 25, 2021, 9:16am

You could try How do I increase PHP's memory? - #3 by deHakkelaar.
That may or may not mitigate your issue, and possibly only temporarily until the next limit is hit.

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:

echo ">stats >quit" | nc localhost 4711

echo ">top-clients >quit" | nc localhost 4711

teraform · April 25, 2021, 9:23am

pi@raspberrypi:~ $ echo ">stats >quit" | nc localhost 4711

domains_being_blocked 216757
dns_queries_today 19962
ads_blocked_today 10358
ads_percentage_today 51.888588
unique_domains 2050
queries_forwarded 8121
queries_cached 571
clients_ever_seen 7
unique_clients 7
dns_queries_all_types 19962
reply_NODATA 71
reply_NXDOMAIN 122
reply_CNAME 2839
reply_IP 14600
privacy_level 0
status enabled

pi@raspberrypi:~ $ echo ">top-clients >quit" | nc localhost 4711

0 10943 192.168.0.101
1 4972 192.168.0.104
2 3072 192.168.0.103
3 431 192.168.0.102
4 387 192.168.0.100
5 172 127.0.0.1 localhost
6 1 192.168.0.106 raspberrypi

Bucking_Horn · April 25, 2021, 9:39am

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;"

teraform · April 25, 2021, 9:52am

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).

Also, I tried your link : How do I increase PHP's memory? - #3 by deHakkelaar.

And increased the memory allocated to 512 MB, now the top query is also getting populated

I am highly thankful for such a short turn around time from your end and also the excellent support.

I checked that conditional forwarding is turned on in Pi settings, I use my router as DHCP, would you recommend turning Conditional Forwarding to off?

Bucking_Horn · April 25, 2021, 10:00am

No, as your results do not suggest that you have an issue with CF.
(Note that those echo commands only return results for the last 24 hour period.)

If they were showing local hostnames as the top domains, then that would have been a hint at CF closing a partial DNS loop.

Your top domains for the most active clients are all public, so that's maybe just the normal level of activity for a Xiaomi smart phone.

I'll be offline now, but that shouldn't stop you from posting any additional findings if your issue would persist.

teraform · April 25, 2021, 10:06am

Thank you, much appreciated

system · May 16, 2021, 10:07am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.