Deleting a client(s) from the database


#1

Have you ever wondered how to delete a client from Pi-hole?
Just follow these simple steps:

FOR EXPERTS

  1. sqlite3 /etc/pihole/pihole-FTL.db
  2. sqlite> DELETE from queries WHERE client = 'clientIP';
  3. Verify that you deleted the client
    sqlite> SELECT * FROM queries WHERE client = 'clientIP';
    There should be little to no queries as a result of the above command.
  4. Exit SQLite: sqlite> .exit

PLEASE NOTE:
If you are logged in as the root user, such as with sudo -su, then you do not need to enter sudo before every command. If you are a regular user, then you must use the sudo before every command and enter your password (if set).

  1. Using a text editor of your choice, create a new file in /usr/local/bin. Give it a name, such as rmclient
cd /usr/local/bin
sudo nano rmclient
  1. Copy and paste the following code below into your file:
#!/bin/bash
# rmclient
## Set Global Variables
db="/etc/pihole/pihole-FTL.db"
log="/var/log/pihole.log"

# Pre Run Checks
if [[ $(id -u) -ne 0 ]]; then 
    echo "Please run as root"
    exit 1
elif [ "$#" -lt 1 ]; then
    exit 1
elif [ "$#" -gt 1 ]; then
    echo "ONLY ONE ARGUMENT PERMITTED"
    exit 1
elif [ ! -f "$db" ]; then
    echo "NO DATABASE"
    exit 1
fi

# Verify the IP Address
ip="$1"
if [[ "$ip" =~ ^(([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))\.){3}([1-9]?[0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))$ ]]; then
    # Delete the client from the database
    echo "DELETING CLIENT FROM DATABASE"
    sqlite3 "$db" "DELETE from queries WHERE client = '$ip';" && sed -i '/"$ip"/d' "$log"
    if [ $? = 0 ]; then
        echo "CLIENT SUCCESSFULLY DELETED"
        #service pihole-FTL reload
    fi
else
    echo "INVALID IP ADDRESS"
    exit 1
fi
  1. Save the file (using nano):
[CTL]+[X]
[Y]
[ENTER]
  1. Give the file execute permissions
    sudo chmod a+x rmclient
  2. You’re All Done! You can now delete clients from the Pi-hole database. Look at the example below:
    sudo rmclient 127.0.0.1
    5B. If you want to see instant results, you will have to reload FTL. Enter the command below:
    sudo service pihole-FTL reload

EXTRAS

  • PRAGMA table_info('queries')
  • Get domains for a specific client: SELECT domain FROM queries WHERE client = '192.168.1.7';

SOURCE CODE: Click Here


Browsing the FTL database
#2

This deletes the client from the long term database, but client transactions will still exist in the pihole logs (unless the logs are disabled).

In /var/log

pihole.log
pihole.log.1
pihole.log.3.gz
pihole.log.4.gz
pihole.log.5.gz

#4

I added support for removing client queries from the main log. I don’t know what pihole.log.1 is but I might need to edit it to remove queries from that one too. The other ones are compressed, and it would be hard on the SD card (Pi only) uncompressing and re-compressing those logs, so they can just be left alone.

UPDATE:
pihole.log.1 only includes information from the previous day, as at the bottom, it stops at 00:00 the next day. So it does not matter. As stated before, the logs are rotated, so eventually they will disappear altogether as time passes if I’m correct.


#5

The pihole.log is rotated nightly at midnight. The previous 5 days are stored, with the oldest being removed each night. So, if you delete a client, in 6 days it will not appear in the pihole logs.

If you delete a client and want to get rid of it immediately from the pihole logs, then wait until the next day and delete all the pihole logs 1 through 5. No record of the client remains in the logs.