PiHole databases and SQLite

The PiHole data databases are make and
managed by SQLite.

PiHole databases files are located in /etc/pihole:

$ ls -f /etc/pihole
.
..
list.5.s3.amazonaws.com.domains
black.list
list.2.mirror1.malwaredomains.com.domains
GitHubVersions
local.list
migration_backup
gravity.db
list.1.raw.githubusercontent.com.domains
custom.list
logrotate
dns-servers.conf
list.3.sysctl.org.domains
list.4.zeustracker.abuse.ch.domains
setupVars.conf.update.bak
install.log
gravity.list
macvendor.db
setupVars.conf
pihole-FTL.conf
list.6.s3.amazonaws.com.domains
localbranches
localversions
adlists.list
pihole-FTL.db
dhcp.leases

Databases are available from SQLite3 commands in bash console or SQLliteBrowser.

SQL code to get the know MACs order by recent DNS queries in PiHole from /etc/pihole/pihole-FTL.db:

SELECT hwaddr , strftime('%Y-%m-%d %H:%M:%S', lastQuery, 'unixepoch') AS date FROM `network` ORDER BY lastQuery DESC;

"hwaddr" "date"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:26:58"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:26:28"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:23:35"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:26:58"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:26:28"
"xx:xx:xx:xx:xx:xx"    "2021-01-16 18:23:35"
 ...

 It is good source of data for monitoring the net.

 

Search queries by domain name:

sqlite> .open pihole-FTL.db

sqlite> select * from queries where domain like "%KeyWord%" limit 50;

 .