ARP: Main & Guest network

General
1

@DL6ER - Using this method also creates a blind spot in the network section if you have a guest wifi on another subnet that is restricted by design from the primary one. For me, pi-hole runs on the primary but none of the guest wifi connections show up in the section. Wondering if you have other options to populate this new feature area that can capture more clients?

2

I wonder how you want to combine these two? If they are hard separated, this is not possible.

3

@DL6ER - Many routers offer this setup out-of-the-box: primary LAN+Wifi and a Guest Wifi. Firewall rules to keep primary separated from Guest. Just pointing this out and asking if the network overview section in pi-hole could be adjusted to match this use case.

4

If the separation happens outside of Pi-hole (e.g. on your router), then there is no way to do this. You could setup a second Pi-hole in the guest network and fuse the two network tables. This is not integrated or planned for Pi-hole, however, it is also not too much work.

5

Can you describe how to accomplish this? A 2nd pi-hole would be trivial to setup. Thanks.

6

SQLite3 is very flexible. What you would want is to merge the content of two identical tables in two separated files, see, e.g., How to use sqlite3 to merge two sqlite databases with each other - Quora
This synchronization can be automated using, e.g., a cron job.

The only thing you need to ensure is there is one common spot in your network where both databases can be accessed from.

1 Like
7

Nice. So in principal, run 2x pi-holes, one on the LAN (piholelan) and one on the guestzone (piholeguest).

I have never used sqlite3 before. Can you propose some code suitable for a bash script to execute?

#!/bin/bash
DB1=/path/to/guestzone.db
DB2=/path/to/lanzone.db

# assume guestzone.db is accessible on the LAN zone

sqlite3 ...
8

Trial and error :smiley:

First backup the database file:

cd ~

sqlite3 /etc/pihole/pihole-FTL.db ".backup main pihole-FTL.db.bak"

Create a copy to play with:

cp pihole-FTL.db.bak play.db

Open it to start playing:

sqlite3 play.db

Check what commands/queries are available besides the regular SQL ones:

.help

For example its nice to know what columns are available in a particular database table:

sqlite> .databases
main: /home/pi/tmp/play.db

sqlite> .tables
counters  ftl       network   queries

sqlite> .schema network
CREATE TABLE network ( id INTEGER PRIMARY KEY NOT NULL, ip TEXT NOT NULL, hwaddr TEXT NOT NULL, interface TEXT NOT NULL, name TEXT, firstSeen INTEGER NOT NULL, lastQuery INTEGER NOT NULL, numQueries INTEGER NOT NULL,macVendor TEXT);

Those same queries can be run from bash command line directly:

pi@noads:~/tmp $ sqlite3 play.db ".schema network"
CREATE TABLE network ( id INTEGER PRIMARY KEY NOT NULL, ip TEXT NOT NULL, hwaddr TEXT NOT NULL, interface TEXT NOT NULL, name TEXT, firstSeen INTEGER NOT NULL, lastQuery INTEGER NOT NULL, numQueries INTEGER NOT NULL,macVendor TEXT);

Or an actual SQL query:

pi@noads:~/tmp $ sqlite3 play.db "SELECT * FROM network"
1|10.0.0.11|00:1e:0b:xx:xx:xx|eth0|hakpc|1558466162|1558818290|25623|Hewlett Packard
2|10.0.0.1|50:46:5d:xx:xx:xx|eth0||1558466221|0|0|ASUSTek COMPUTER INC.
[..]