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

En caso de no tener instalado sqlite3, lo instalaremos con el comando:

Instalar sqlite3 en Linux Debian, bd SQLite de Pi-hole

La base de datos SQLite de Pi-hole se encuentra en:

/etc/pihole/pihole-FTL.db

Instalar sqlite3 en Linux Debian, bd SQLite de Pi-hole

Para mostrar las tablas disponibles en la base de datos de Pi-hole ejecutaremos:

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):

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:

Nos devolverá algo así:

Consultas SQLite para obtener información del registro de dominios de Pi-hole

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:

Esta consulta es útil almacenarla en un fichero de texto para su posterior revisión, ejecutando la consulta de esta forma:

Consultas SQLite para obtener información del registro de dominios de Pi-hole
  • Mostrar dominios con más peticiones (de más a menos) que no han sido bloqueados:
Consultas SQLite para obtener información del registro de dominios de Pi-hole
  • Mostrar registros bloqueados por listas negras exactas (exact blacklist):
  • Mostrar registros bloqueados por listas negras con patrones (regex blacklist):
  • Para mostrar los 5 dominios más visitados (consultados) por un cliente (por una IP):
lang:sh decode:true
  • Equipo con más consultas DNS bloqueadas de las últimas 24 horas: