Cómo obtener información adicional de log del servidor DNS Pi-hole. Usaremos la consola del servidor Linux y sqlite3, que es la base de datos que usa Pi-hole. Obtendremos los 15 dominios (sitios web) más bloqueados, todos los dominios sin bloquear ordenados por número de accesos (peticiones), etc.
- Instalar sqlite3 en Linux Debian, bd SQLite de Pi-hole.
- Consultas SQLite para obtener información del registro de dominios de Pi-hole.
Instalar sqlite3 en Linux Debian, bd SQLite de Pi-hole
En caso de no tener instalado sqlite3, lo instalaremos con el comando:
|
1 |
apt-get install sqlite3 |
La base de datos SQLite de Pi-hole se encuentra en:
/etc/pihole/pihole-FTL.db
Para mostrar las tablas disponibles en la base de datos de Pi-hole ejecutaremos:
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "SELECT name FROM sqlite_master WHERE type='table'" |
Que nos mostrará:
query_storage
sqlite_sequence
ftl
counters
message
network_addresses
network
aliasclient
domain_by_id
client_by_id
forward_by_id
addinfo_by_id
Y las vistas (view):
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "SELECT name FROM sqlite_master WHERE type='view'" |
Que nos devolverá la vista: queries, esta será la que usemos principalmente para obtener la información de Pi-hole.
Consultas SQLite para obtener información del registro de dominios de Pi-hole
A continuación mostramos algunas consultas SQLite interesantes para obtener información de los log registrados en el servidor DNS Pi-hole.
- Para obtener los dominios bloqueados en los últimos 7 días. Se agrupará el resultado por dominio, mostrando el número de veces que se ha bloqueado cada dominio. Limitaremos el resultado a los 15 dominios más bloqueados:
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (1,4,5,9,10,11) and timestamp>=strftime('%s','now')-604800 group by domain order by count(domain) desc Limit 15" |
Nos devolverá algo así:
En nuestro ejemplo, el dominio más bloqueado es incoming.telemetry.mozilla.org, seguido de googleads.g.doubleclick.net.
- Listar todos los dominios bloqueados desde siempre, mostrando el número de veces que se ha bloqueado cada dominio, ordenados de menor a mayor número de veces:
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (1,4,5,9,10,11) group by domain order by count(domain)" |
Esta consulta es útil almacenarla en un fichero de texto para su posterior revisión, ejecutando la consulta de esta forma:
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (1,4,5,9,10,11) group by domain order by count(domain)" >> /tmp/registros_bloqueados.txt |
- Mostrar dominios con más peticiones (de más a menos) que no han sido bloqueados:
|
1 2 |
sqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (2,3) and domain like 'www.%' group by domain order by count(domain) desc" |
- Mostrar registros bloqueados por listas negras exactas (exact blacklist):
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (5) and domain like 'www.%' group by domain order by count(domain) desc" |
- Mostrar registros bloqueados por listas negras con patrones (regex blacklist):
|
1 |
sqlisqlite3 /etc/pihole/pihole-FTL.db "select printf('% 6d',count(domain)), domain from queries where status in (4) and domain like 'www.%' group by domain order by count(domain) desc" |
- Para mostrar los 5 dominios más visitados (consultados) por un cliente (por una IP):
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select count(domain), domain from queries where client == '192.168.1.12' and domain like 'www.%' group by domain order by count(domain) desc LIMIT 5" |
- Equipo con más consultas DNS bloqueadas de las últimas 24 horas:
|
1 |
sqlite3 /etc/pihole/pihole-FTL.db "select client from queries where status in (1,4,5,9,10,11) and timestamp>=strftime('%s','now')-86400 group by client order by count(client) desc Limit 1" |
