Last active
October 5, 2022 10:24
-
-
Save TaoK/31d198c05b9e1bdb0b29e743168199cb to your computer and use it in GitHub Desktop.
Query pihole DB for recent domains by a client IP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Prerequisites: raspberry pi with static IP, pihole, and sqlite3, set up with dhcp, and router dhcp disabled | |
| # look up the client IP in the admin interface | |
| INTERESTING_IP=192.168.1.242 | |
| # raw domains in 7 days, ordered by frequency | |
| sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain, count(1) FROM queries WHERE timestamp > (SELECT strftime('%s', datetime('now', '-1000 day'))) AND status IN (2, 3, 12, 13, 14) AND client='$INTERESTING_IP' GROUP BY client, domain ORDER BY count(1) DESC" | |
| # second level domains in 7 days, ordered by frequency | |
| sqlite3 /etc/pihole/pihole-FTL.db "SELECT substr(domain, length(rtrim(substr(domain, 0, length(rtrim(domain, replace(domain, '.', '')))), replace(domain, '.', ''))) + 1), count(1) FROM queries WHERE timestamp > (SELECT strftime('%s', datetime('now', '-1000 day'))) AND status IN (2, 3, 12, 13, 14) AND client='$INTERESTING_IP' GROUP BY substr(domain, length(rtrim(substr(domain, 0, length(rtrim(domain, replace(domain, '.', '')))), replace(domain, '.', ''))) + 1) ORDER BY count(1) DESC" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment