Adding super-clients to Pi-hole FTL

Many devices have multiple addresses (e.g., IPv4 and IPv6) and we've been asked often if they can be grouped together in the statistics. Well, grouping them has a number of drawbacks (discussed elsewhere), but I think I found a way how to achieve what everyone wants without having to rip apart everything inside FTL.

Here, I introduce super clients. Super-clients are (in some way) ordinary clients but are only virtual. They appear everywhere on the dashboard just like the other clients, but they don't really exist (physically). They never ask the DNS server any questions but they represent a number of other devices which do.

Super clients are identified by their MAC address, whereas ordinary clients are defined by their IP addresses. This allows them to represent a number of other devices (which all share the same property: The same MAC address).

IMPORTANT INFORMATION

Most of the content below is obsolete by now. Check below for the updated superclient definition.

Let me give an example of what I have in mind:

In this example, we have the following "clients" (in the sense of IP addresses):

ID IP address MAC address (simplified) host name total queries blocked queries
0 192.168.1.25 AA linux-box-1 111 34
1 fe80::ca9c:1203:5365:010a AA some-very-long-name-isp.com 200 44
2 2a02:3443:3307:5968::1 AA other-very-long-name.isp.com 968 455
3 192.168.1.22 BB linux-box-2 134 22
4 192.168.1.20 CC android-phone 334 23

We define a super-client like

ID MAC address (simplified) host name
0 AA linux-box-1

The super-client will take over all the MAC address AA clients, so the output on the dashboard would look like

ID IP address MAC address (simplified) host name total queries blocked queries
-- --- AA linux-box-1 1279 533
3 192.168.1.22 BB linux-box-2 134 22
4 192.168.1.20 CC android-phone 334 23

Now we come to the part where you can try what we already have. Note that there is not user-interface, currently, but I will give an example of how to easily configure this, nonetheless.

  1. Checking out the FTL branch

    pihole checkout ftl new/super-clients
    
  2. You will get a new table superclient in /etc/pihole/pihole-FTL.db.

  3. Add your super clients identified by their MAC addresses like this:

    sudo sqlite3 /etc/pihole/pihole-FTL.db "INSERT INTO superclient (hwaddr,name) VALUES ('d0:50:99:33:78:33','desktop-machine');"
    
  4. Finally, run

    pihole restartdns
    

    to have FTL import your new super client(s).

    See my post below for updated instructions.

The effect will be immediate and the super-client should directly become visible on the dashboard (even when other host names are still unknown):
Screenshot at 2020-08-11 21-33-48

The new super-client (desktop-machine) summarizes three ordinary clients, all on the same hardware interface. You will notice that the link to the selective Query Log now contains a MAC address rather than an IP address to ensure you will still get all the queries from the three clients (as in identified by three individual IP addresses).

Everything should work just out of the box. I'm waiting for your feedback. Note that this is only a half-an-hour attempt at this new feature, there may very well be a lot of room for improvements.

2 Likes

Would love to test this, but have no IPv6 and use case for this :wink:

One question comes straight to my mind: does the super-client replace all ordinary clients in the stats/dashboard it includes or does it show up in addition to the clients?

Is it safe to go from the mac_clients branch to this super_clients branch? Or are there database items that could cause an incompatibility?

The corresponding ordinary clients are hidden, there are no duplicates.

Yes. Changing between these two is safe as new/super-clients is built upon new/mac-clients. Going back to master or development is not possible from new/super-clients because it is also no possible from new/mac-clients.

Do i add all my client whit the sql cmd?

Only those which you want grouped - together with the name you like to see. There is no need to add clients which only have one IP address in the first place.

My server give each clients 3 IPv6 adresse.

4 posts were merged into an existing topic: Force update of IP / hostname / clientname relation ship

Can I suggest psuedoclients then instead? Super clients seems like they have powers and capabilities above normal clients.

My original post was moved away so we lost some of the conversation. I can see the moderator's reason why they did this , let me re-post the relevant part below:

As always, just opinions: I disagree with pseudoclients . It somehow seems to suggest they are not really there. While one might argue that this is the case, they actually physically exist as devices and are no pseudo . In my understanding the term pseudo would cause more confusion. super may not be optimal, either, but right now at least it seems more descriptive for the "it is more than one ordinary client".

Otherwise this is -- once again -- a really cool new thing. I just tried it in my network and it seems to work just as expected. I even configured all my devices now and have not seen anything odd.

A single post is the smallest unit for splitting, there's no way to just move parts of it - thank you for reposting relevant parts back here.

1 Like

When i do:

pihole checkout new/super-clients

i get this msg [i] Requested option "new/super-clients" is not available

@Tntdruid It should be

pihole checkout ftl new/super-clients
1 Like

The tabel for

superclients

Does not get created after i did the checkout.

The table is called superclient without the trailing s, but watch my next post for further details before moving on.

Having seen @jpgpi250's interpretation of what this can be used for, I thought it may be a good idea to generalize the super-clients. So far, super-clients were set by using a MAC address as identifier. This is a limitation as you may have a device with:

  • more than one MAC address (e.g., Ethernet and WiFi interface)
  • devices without a MAC address being available (e.g., connected through VPN or on another VLAN)

So back to the drawing board!

Please first reset your database back to how it was before checking out the new/super-clients branch using:

sudo sqlite3 /etc/pihole/pihole-FTL.db "DROP TABLE superclient;"
sudo sqlite3 /etc/pihole/pihole-FTL.db "UPDATE ftl SET value = 8 WHERE id = 0;"

Then check out the latest version of this branch.

The superclient table now looks like this (hwaddr gone, id added):

ID host name comment
0 linux-box-1 (optional)

The network table - listing all devices - gets an extra column at the end: superclient_id (referencing the superclient table's IDs)

You can add a super-client ID to as many MAC addresses (may be real or mock-MAC addresses) as you like. This will give you a lot more flexibility and should work equally well.

Example:

  1. Check out the FTL branch

    pihole checkout ftl new/super-clients
    
  2. Add a new super-client

    sudo sqlite3 /etc/pihole/pihole-FTL.db
    
    INSERT INTO superclient (id,name,comment) VALUES (0,'something',NULL);
    
  3. Assign two MAC addresses to this super-client (ID = 0)

    sudo sqlite3 /etc/pihole/pihole-FTL.db
    
    UPDATE network SET superclient_id = 0 WHERE hwaddr = 'd0:50:99:33:78:33';
    
  4. Ask FTL to re-import super-clients

    sudo pkill -RTMIN+3 pihole-FTL
    
  5. See that the said client is now inside the super-client something
    Screenshot at 2020-08-12 19-59-43

  6. For the sake of demonstration, let's add the Top Client (25010 queries) to the same super-client:

    sudo sqlite3 /etc/pihole/pihole-FTL.db
    
    UPDATE network SET superclient_id = 0 WHERE hwaddr = 'd0:50:19:31:28:45';
    

    followed by a

    sudo pkill -RTMIN+3 pihole-FTL
    
  7. Enjoy.
    All statistics from all addresses of these two devices are summarized under one name:
    Screenshot at 2020-08-12 20-00-24

1 Like

This also works. It is even easier to configure.

It would be good if you could add a foreign-key constraint to the additional network table column:

superclient_id INTEGER REFERENCES superclient(id) ON DELETE SET NULL

to ensure superclient_id is reset to NULL when a super-client gets removed.

The superclient does not work whit client group, or my setup is buggy.

Yes, so far the supeclients only affect the computed statistics. I'm going forth and back on this, but should superclients even be used for group configuration? Given that - with the next version of Pi-hole - you can also specify per-client rules by MAC addresses, etc. It would be quite complex to bring them into this concept - and it would be working across databases (superclient and network in pihole-FTL.db, all per-group settings in gravity.db)

1 Like

Better wait for next version :+1: