Skip to content

Instantly share code, notes, and snippets.

@TaoK

TaoK/gist:31d198c05b9e1bdb0b29e743168199cb

Last active October 5, 2022 10:24
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save TaoK/31d198c05b9e1bdb0b29e743168199cb to your computer and use it in GitHub Desktop.
Save TaoK/31d198c05b9e1bdb0b29e743168199cb to your computer and use it in GitHub Desktop.
Download ZIP
Query pihole DB for recent domains by a client IP
Raw
gistfile1.txt
# 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