Pi-hole work

Contents

Displaying hostnames in the dashboard

The web-based dashboard for the Pi-hole has a tendency to display IP addresses instead of hostnames, especially if the IP address for a device was allocated out of the DHCP pool. The best way to get Pi-hole to allocate static IP addresses is two-fold:

  1. In the hosts file on penguin, set up fixed IP addresses for various hostnames. For example, 192.168.1.10 sparrow sparrow.hvdc.net
  2. In the Pi-hole DHCP Server settings page, set up MAC address and Hostname entries, but leave the IP address empty. When dnsmasq gets a request from a given MAC address, it gets a hostname by matching it with the MAC address, looks up the hostname in the hosts file, and if found does a DCHPOFFER of the corresponding IP address.

Displaying hostnames in long-term data queries

Another annoying thing about the Pi-hole web interface is it matches an IP address with a hostname only in the dashboard. In the long-term data part of the web interface it displays only the IP address.

To get around this problem, I can copy the pihole-FTL.db file from penguin to sparrow, then set up a few additional tables:

File pihole-FTL-extra-tables.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
#!/SQL
DROP TABLE IF EXISTS types;
CREATE TABLE types (id integer PRIMARY KEY, desc text);
INSERT INTO types VALUES (1, "IPv4"), (2, "IPv6");

DROP TABLE IF EXISTS statuses;
CREATE TABLE statuses (id integer PRIMARY KEY, desc text);
INSERT INTO statuses VALUES (1, "pi-holed"), (2, "OK (forwarded)"), (3, "OK (cached)");

DROP TABLE IF EXISTS ip_to_hostname;
CREATE TABLE ip_to_hostname (
        id integer PRIMARY KEY AUTOINCREMENT,
        address text(15),
        hostname text
);
INSERT INTO ip_to_hostname VALUES
  (NULL, "192.168.1.1", "penguin"),
  (NULL, "192.168.1.5", "aastra"),
  (NULL, "192.168.1.6", "spa1001"),
  (NULL, "192.168.1.10", "sparrow"),
  (NULL, "192.168.1.11", "sparrow-wifi"),
  (NULL, "192.168.1.12", "galaxy-s3"),
  (NULL, "192.168.1.13", "titan"),
  (NULL, "192.168.1.15", "galaxy-s4"),
  (NULL, "192.168.1.50", "buncha-auk"),
  (NULL, "192.168.1.51", "buncha-ipad"),
  (NULL, "192.168.1.52", "buncha-dell-wired"),
  (NULL, "192.168.1.53", "buncha-dell-wireless"),
  (NULL, "192.168.1.100", "rpi1"),
  (NULL, "192.168.1.101", "rpi1-wired"),
  (NULL, "192.168.1.102", "rpi3"),
  (NULL, "192.168.1.103", "rpi3-wired"),
  (NULL, "192.168.1.104", "netvista"),
  (NULL, "192.168.1.105", "proxmox"),
  (NULL, "192.168.1.106", "dell-97qdl72"),
  (NULL, "192.168.1.107", "dkb"),
  (NULL, "192.168.1.108", "dkb-wifi"),
  (NULL, "192.168.1.110", "win10"),
  (NULL, "192.168.1.111", "p200"),
  (NULL, "192.168.1.112", "piholec6"),
  (NULL, "192.168.1.206", "buncha-auk"),
  (NULL, "192.168.1.207", "rpi3"),
  (NULL, "192.168.1.212", "rpi3"),
  (NULL, "192.168.1.215", "buncha-ipad"),
  (NULL, "192.168.1.213", "galaxy-s4"),
  (NULL, "192.168.1.226", "sparrow-wifi"),
  (NULL, "192.168.1.248", "titan"),
  (NULL, "192.168.1.228", "buncha-iphone"),
  (NULL, "192.168.1.230", "spa1001");
CREATE INDEX addr_idx ON ip_to_hostname (address);

I can then perform an ad-hoc query on the database using sqlite3:

.headers on
.mode column
.nullvalue NULL
.width 0 0 40 20 15
SELECT
  datetime(q.timestamp, 'unixepoch', 'localtime') AS Timestamp,
  types.desc AS Type, q.domain,
  ifnull(ip_to_hostname.hostname, q.client) AS Client,
  statuses.desc AS Status
FROM queries AS q, types, statuses
LEFT JOIN ip_to_hostname ON ip_to_hostname.address=q.client
WHERE timestamp BETWEEN strftime('%s', '2018-05-21 14:12:00', 'UTC')
                    AND strftime('%s', '2018-05-30 15:51:00', 'UTC')
  AND q.type=1 AND types.id=q.type AND statuses.id=q.status;

(The dates in the above query are from my trip to Brandon last month, which means the results are primarily from Buncha’s devices.)

About

Ramblings about Linux and other things

Archives

Tags