Pi-Hole: Difference between revisions

From Omnia
Jump to navigation Jump to search
 
(One intermediate revision by the same user not shown)
Line 63: Line 63:


Ref: https://discourse.pi-hole.net/t/how-can-i-reset-all-pihole-stats/7749
Ref: https://discourse.pi-hole.net/t/how-can-i-reset-all-pihole-stats/7749
== Query History ==
Show tables:
sqlite3 pihole-FTL.db ".tables"
<pre>
addinfo_by_id      counters          ftl                network_addresses
aliasclient        domain_by_id      message            queries
client_by_id      forward_by_id      network            query_storage
</pre>
Show queries tables schema:
<pre>
$ sqlite3 pihole-FTL.db ".schema queries"
CREATE VIEW queries AS
SELECT id, timestamp, type, status, CASE typeof(domain)
WHEN 'integer' THEN (SELECT domain
FROM domain_by_id d WHERE d.id = q.domain)
ELSE domain END domain,CASE typeof(client)
WHEN 'integer' THEN (SELECT ip FROM client_by_id c
WHERE c.id = q.client) ELSE client END client,CASE typeof(forward)
WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward)
ELSE forward END forward,CASE typeof(additional_info) WHEN 'integer' THEN (SELECT content
FROM addinfo_by_id a WHERE a.id = q.additional_info)
ELSE additional_info END additional_info, reply_type, reply_time, dnssec
FROM query_storage q
/* queries(id,timestamp,type,status,domain,client,forward,additional_info,reply_type,reply_time,dnssec) */;
</pre>
Show last 10 entries queries table:
<pre>
$ sqlite3 pihole-FTL.db "select * from queries limit 10"
1|1705181665|4|2|wpad.mydomain.com|10.0.0.5|8.8.8.8#53||2|0.0357|0
2|1705181665|4|3|google.com|10.0.0.5|||2|0.0|0
...
</pre>
Search for domains with GOOGLE in them:
$ sqlite3 pihole-FTL.db "SELECT * from queries WHERE domain like '%GOOGLE%' ORDER by timestamp desc"
Show last 100 records with date (formatted), domain, and client
$ sqlite3 pihole.db "select date(timestamp, 'unixepoch'), domain, client from queries limit 100"
2024-01-13|wpad.mydomain.com|10.0.0.5
...


== Clear Logs ==
== Clear Logs ==

Latest revision as of 06:10, 29 July 2024

Pi-Hole

Network Wide Ad Blocker

httsp://pi-hole.net/

Install

Install Pi-Hole [1]

curl -sSL https://install.pi-hole.net | bash
  • StevenBlack's Unified Hosts List

Admin web interface:

http://10.x.x.x/admin/
or
http://pi.hole/admin

Good List with Auto Updater

JavanXD/ya-pihole-list: A bunch of useful pi-hole adlists and a auto updater.
https://github.com/JavanXD/ya-pihole-list

Commands

https://kb.adamsdesk.com/application/pi-hole-quick-reference-commands/

Wild card black list:

pihole --wild somedomain.com

Old

Switch lighttpd port

https://discourse.pi-hole.net/t/migrating-pi-hole-from-lighttpd-to-apache/152/11

#!/bin/bash

if (whiptail --title "Change Pi-hole port" --yesno "Do you want to change the port pi-hole uses?" 8 78) then
  PORT_PIHOLE="89"
  PORT_PIHOLE=$(whiptail --inputbox "Which port you do you want to use for Pi-hole?" 8 78 $PORT_PIHOLE --title "Pi-hole Port" 3>&1 1>&2 2>&3)
  sed -i "s/server.port                 = 80/server.port                 = $PORT_PIHOLE/" /etc/lighttpd/lighttpd.conf
  service lighttpd restart
fi
http://10.0.0.10:89/admin/

Reset Password

pihole -a -p

ref: https://discourse.pi-hole.net/t/how-do-i-set-or-reset-the-web-interface-password/1328

Reset Stats

cd /etc/pihole
sudo service pihole-FTL stop
sudo mv pihole-FTL.db pihole-FTL.db.old
sudo service pihole-FTL start

Ref: https://discourse.pi-hole.net/t/how-can-i-reset-all-pihole-stats/7749

Query History

Show tables:

sqlite3 pihole-FTL.db ".tables"
addinfo_by_id      counters           ftl                network_addresses
aliasclient        domain_by_id       message            queries
client_by_id       forward_by_id      network            query_storage

Show queries tables schema:

$ sqlite3 pihole-FTL.db ".schema queries"
CREATE VIEW queries AS
 SELECT id, timestamp, type, status, CASE typeof(domain)
 WHEN 'integer' THEN (SELECT domain
 FROM domain_by_id d WHERE d.id = q.domain)
 ELSE domain END domain,CASE typeof(client)
 WHEN 'integer' THEN (SELECT ip FROM client_by_id c
 WHERE c.id = q.client) ELSE client END client,CASE typeof(forward)
 WHEN 'integer' THEN (SELECT forward FROM forward_by_id f WHERE f.id = q.forward)
 ELSE forward END forward,CASE typeof(additional_info) WHEN 'integer' THEN (SELECT content
 FROM addinfo_by_id a WHERE a.id = q.additional_info)
 ELSE additional_info END additional_info, reply_type, reply_time, dnssec
 FROM query_storage q
/* queries(id,timestamp,type,status,domain,client,forward,additional_info,reply_type,reply_time,dnssec) */;

Show last 10 entries queries table:

$ sqlite3 pihole-FTL.db "select * from queries limit 10"
1|1705181665|4|2|wpad.mydomain.com|10.0.0.5|8.8.8.8#53||2|0.0357|0
2|1705181665|4|3|google.com|10.0.0.5|||2|0.0|0
...

Search for domains with GOOGLE in them:

$ sqlite3 pihole-FTL.db "SELECT * from queries WHERE domain like '%GOOGLE%' ORDER by timestamp desc"

Show last 100 records with date (formatted), domain, and client

$ sqlite3 pihole.db "select date(timestamp, 'unixepoch'), domain, client from queries limit 100"
2024-01-13|wpad.mydomain.com|10.0.0.5
...

Clear Logs

pihole -f

Whitelist Only

Set a regex ".*" black list, then whitelist what you want.

Ref: https://discourse.pi-hole.net/t/block-everything-allow-whitelisted-only/15782

keywords