Pi-hole - DNS server

Purpose: Trusted and Filtering Feature Domain Name Resolution
Solution: Pi-hole (https://pi-hole.net/)
OS: Debian GNU/Linux 12 (bookworm) 
 preparations: 
 setup 
 # Pi-hole Automated Installer
This installer will transform your device into a network-wide ad blocker!
[OK]

# Open Source Software
The Pi-hole is free, but powered by your donations:
https://pi-hole.net/donate/
[OK]
 
 Decide and assign static IP address 
 # Static IP Needed
The Pi-hole is a SERVER so it needs a STATIC IP ADDRESS to function properly.

IMPORTANT: If you have not already done so, you must ensure that this device has a static IP.

Depending on your operating system, there are many ways to achieve this, through DHCP reservation, or by manually assigning one. 
Please continue when the static addressing has been configured.
[Continue]

# Select Upstream DNS Provider. To use your own, select Custom.
(*) Cloudflare (DNSSEC)

# Blocklists
Pi-hole relies on third party lists in order to block ads.
You can use the suggestion below, and/or add your own after installation.
Select 'Yes' to include:
StevenBlack's Unified Hosts List
[Yes]

# Enable Logging
Would you like to enable query logging?
[Yes]

# Select a privacy mode for FTL. https://docs.pi-hole.net/ftldns/privacylevels/
(*) 0 Show everything
[Continue]
 
 # Installation Complete!

Configure your devices to use the Pi-hole as their DNS server using:

IPv4: 10.166.0.2
IPv6: Not Configured
If you have not done so already, the above IP should be set to static.
View the web interface at http://pi.hole/admin:80 or
http://10.166.0.2:80/admin

Your Admin Webpage login password is (superpass)
 
 Configure admin dashboard listening port 
 vi /etc/pihole/pihole.toml
 
 Let's disable IPv6, disable HTTP and swap HTTPS port 
 # port = "80o,443os,[::]:80o,[::]:443os"
 port = "445os"
 
 Restart service and check port has been changed 
 systemctl restart pihole-FTL
ss -ntap | grep hole
 
 Advanced usage 
 Install database cient 
 apt install sqlite
 
 Set pattern 
 # export pattern="dox.installanduse.com"
# export pattern="dox.2dz.fi"
export pattern="ntp"
 
 List domains from gravity database (blocking list) 
 sqlite3 /etc/pihole/gravity.db \
 "SELECT domain FROM gravity \
 WHERE domain LIKE '%${pattern}%' \
 COLLATE NOCASE ORDER BY domain;"
 
 List schema 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db .schema
 
 List queries from pihole-FTL database (log) 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT DISTINCT domain
 FROM queries
 WHERE domain LIKE '%${pattern}%'
 ORDER BY domain;"
 
 Detail 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT DISTINCT d.domain
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 WHERE d.domain LIKE '%${pattern}%'
 ORDER BY d.domain;"
 
 Query and client 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT c.ip || ' ' || d.domain
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 JOIN client_by_id c ON c.id = q.client
 WHERE d.domain LIKE '%${pattern}%'
 ORDER BY c.ip, d.domain;"

# can be used by adding suffix " | uniq"
 
 Queries by specific client 
 export pattern="dox.2dz.fi"
export src="192.168.0.0"

sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT DISTINCT d.domain
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 JOIN client_by_id c ON c.id = q.client
 WHERE c.ip = '${src}'
 AND d.domain LIKE '%${pattern}%'
 ORDER BY d.domain;"
 
 With count, most frequent 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT d.domain, COUNT(*)
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 WHERE d.domain LIKE '%${pattern}%'
 GROUP BY d.domain
 ORDER BY COUNT(*) DESC, d.domain;"
 
 Only last 24 hours 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT DISTINCT d.domain
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 WHERE d.domain LIKE '%${pattern}%'
 AND q.timestamp > unixepoch('now','-1 day')
 ORDER BY d.domain;"
 
 Detail: timestamp + source IP + domain 
 sqlite3 -noheader /etc/pihole/pihole-FTL.db \
 "SELECT datetime(q.timestamp,'unixepoch','localtime') || ' ' || c.ip || ' ' || d.domain
 FROM query_storage q
 JOIN domain_by_id d ON d.id = q.domain
 JOIN client_by_id c ON c.id = q.client
 WHERE d.domain LIKE '%${pattern}%'
 ORDER BY q.timestamp;"