Feature request

General Customizing Pi-hole
1

I used below sh script, for looking cumulative block and allow requests for all clients.
this statistics feature would be good to available in long term data

scripts, are,
Allow requests
sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain FROM queries WHERE
status=0 OR
status=2 OR
status=3 OR
status=12 OR
status=13 OR
status=14" | sort | uniq -c | sort -n -r | head -250000 > /home/pi/PIHOLE_LOG_EXPORT/cumulative_allow.txt

Block requests
sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain FROM queries WHERE
status=1 OR
status=4 OR
status=5 OR
status=6 OR
status=7 OR
status=8 OR
status=9 OR
status=10 OR
status=11 OR
status=15 OR
status=16" | sort | uniq -c | sort -n -r | head -250000 > /home/pi/PIHOLE_LOG_EXPORT/cumulative_block.txt

Example output

4706 mediaservices.cdn-apple.com
3982 reverselookup.quicksetcloud.com
2820 https://app-measurement.com/sdk-exp
2808 ssl.gstatic.com
2740 google.com
2620 gateway.icloud.com
2618 m.hotmail.com
2546 cloudgz-ecs.gravityzone.bitdefender.com
2521 bag.itunes.apple.com
2484 play.google.com
2362 login.microsoftonline.com
2285 www.google.com
2230 ncc.avast.com
2060 googleapis.com
2006 www.googleapis.com
1994 apple.com
1884 gstatic.com
1737 microsoft.com
1737 iphone-ld.apple.com
1710 avast.com
1652 oauthaccountmanager.googleapis.com
1620 docs.google.com
1608 yandex.net
1579 bitdefender.com
1561 play.googleapis.com

2

a better? version of this query would be:

pihole-FTL sqlite3 "/etc/pihole/pihole-FTL.db" \
".timeout = 5000" ".headers on" ".mode column" \
"SELECT count(domain), domain FROM queries \
WHERE status IN (0, 2, 3, 12,13, 14) \
GROUP by domain ORDER BY count(domain) DESC LIMIT 10;"

change the limit value to get more / less entries.

edit
change your MAXDBDAYS value to limit the database size, thus speeding up the query, see here.
/edit

3

Mant thanks for your tips,