#!/bin/bash PIHOLE_ADLIST_TOOL_VERSION="2.6.6" # define path to pihole's databases and temporary database TEMP_DB="/tmp/temp.db" PIHOLE_ROOT="/etc/pihole" PIHOLE_FTL="file:$PIHOLE_ROOT/pihole-FTL.db?mode=ro" GRAVITY="file:$PIHOLE_ROOT/gravity.db" ## define and initialize variables ## # variables for various timestamps and dates DAYS_REQUESTED=30 declare -i TIMESTAMP_REQUESTED declare -i TIMESTAMP_FIRST_QUERY declare -i TIMESTAMP_LAST_QUERY declare -i TIMESTAMP_FIRST_ANALYZED DATE_REQUESTED= DATE_FIRST_QUERY= DATE_FIRST_ANALYZED= DATE_LAST_QUERY= declare -i FTL_ID # variables defining what to show in output TOP=0 SORT= SORT_ORDER= UNIQUE=0 REGEX_MODE=0 # variables for adlist management declare -a adlist_conf_old_enabled declare -a adlist_conf_unique_enabled declare -a adlist_enabled_in_gravity declare -a adlist_conf_minimal_enabled declare -i LEFT_DOMAINS # variables for stats NUM_DOMAINS_BLOCKED= HITS_TOTAL= NUM_ADLISTS= NUM_ADLISTS_ENABLED= NUM_GRAVITY_UNIQUE_DOMAINS= NUM_DOMAINS_BLOCKED_CURRENT= HITS_TOTAL_CURRENT= BLACKLIST_GRAVITY= NUM_TOTAL_UNIQUE_DOMAINS= BLACKLIST_CNAME= NUM_DOMAINS_BLOCKED_FUTURE= NUM_ENABLED_REGEX= NUM_REGEX= NUM_ALL_DOMAINS= NUM_DOMAINS_BLOCKED_BY_REGEX= # variables for general info PIHOLE_DNSMASQ_VERSION= SQLITE_VERSION= # Docker variables PIHOLE_DOCKER= CONTAINER_ID= # variables for menu selection AUTOMATIC_MODE=0 declare -i ENABLE_ALL_ADLISTS_FOR_ANALYSIS declare -i RUN_GRAVITY_NOW declare -i FURTHER_ACTION #for text formatting bold=$(tput bold) normal=$(tput sgr0) # variables for RegEx analysis declare -a all_domains declare -a all_exact_domains CURRENT_DOMAIN= REGEX_ID= # version message print_version() { echo "Pihole Adlist Tool v${PIHOLE_ADLIST_TOOL_VERSION}" } # fetches latest version fetch_remote_version() { local remote_version remote_version="$( curl --silent https://api.github.com/repos/yubiuser/pihole_adlist_tool/releases/latest | grep '"tag_name":' | awk -F \" '{print $4}' )" # returns the latest version tag(eg: 2.4.0). echo "$remote_version" } # help message print_help() { echo echo " Usage: pihole_adlist_tool [options] Options: -d [Num] Consider the last [Num] days (Default: 30). Enter 0 for all-time analysis. -t [Num] Show top blocked domains. [Num] defines the number to show. -s [total/covered/hits/unique] Set sorting order to total (total domains), covered (domains covered), hits (hits covered) or unique (covered unique domains) DESC. (Default sorting: id ASC). -u Show covered unique domains. -a Run in 'automatic mode'. No user input is required at all, assuming default choice would be to leave everything untouched. -r Analyse RegEx as well. Depending on the amount of domains and RegEx this might take a while. Please note: Can only be used, if Pi-hole is NOT running in a Docker Container! -v Display pihole_adlist_tool's version. -h Show this help dialog. " } #convert timestamp to date timestamp2date () { local DATE DATE=$(date -d @"$1" +%d.%m.%Y%t%k:%M:%S) echo "$DATE" } # calculate timestamps set_timestamps () { TIMESTAMP_FIRST_QUERY=$(sqlite "$PIHOLE_FTL" "SELECT CAST(MIN(timestamp) AS INTEGER) FROM queries;") TIMESTAMP_LAST_QUERY=$(sqlite "$PIHOLE_FTL" "SELECT CAST(MAX(timestamp) AS INTEGR) FROM queries;") if [ "$DAYS_REQUESTED" = 0 ]; then TIMESTAMP_REQUESTED=$TIMESTAMP_FIRST_QUERY else TIMESTAMP_REQUESTED=$(date +%s) TIMESTAMP_REQUESTED="$TIMESTAMP_REQUESTED-86400*${DAYS_REQUESTED}" fi TIMESTAMP_FIRST_ANALYZED=$(sqlite "$PIHOLE_FTL" "SELECT CAST(min(timestamp) AS INTEGER) FROM queries WHERE timestamp>=$TIMESTAMP_REQUESTED;") } # converts dates set_dates () { DATE_FIRST_QUERY="$(timestamp2date "$TIMESTAMP_FIRST_QUERY")" DATE_LAST_QUERY="$(timestamp2date "$TIMESTAMP_LAST_QUERY")" DATE_REQUESTED="$(timestamp2date "$TIMESTAMP_REQUESTED")" DATE_FIRST_ANALYZED="$(timestamp2date "$TIMESTAMP_FIRST_ANALYZED")" } # calculates how many domains from gravity_strip are contained in all enabled adlists in gravity # this is useful in case users select to enable all adlist initially, but return to their previous adlist configuration # which might not contain all domains that could have been blocked or only enable adlists with unique covered domains domains_blocked_future () { sqlite $TEMP_DB << EOF ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db; INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_FUTURE', count(distinct gravity_strip.domain) from gravity_strip join gravity_db.gravity on gravity_strip.domain=gravity_db.gravity.domain Join gravity_db.adlist on gravity_db.gravity.adlist_id=gravity_db.adlist.id where enabled=1; DETACH DATABASE gravity_db; .exit EOF NUM_DOMAINS_BLOCKED_FUTURE=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_FUTURE';") echo echo " [i] Of the ${bold}$NUM_DOMAINS_BLOCKED_CURRENT${normal} domains that would have been blocked by the adlist configuration you chose to analyze," echo " ${bold}$NUM_DOMAINS_BLOCKED_FUTURE${normal} domains are covered by your current adlist configuration." } # removes temporary database remove_temp_database () { echo echo -e " [i] Removing temporary database..." if [ "$PIHOLE_DOCKER" = 0 ]; then rm -f $TEMP_DB else docker exec "$CONTAINER_ID" rm -f $TEMP_DB fi echo -e " [✓] Temporary database removed\n" } # cleanup on trap cleanup_on_trap () { echo -e "\n\n [✗] ${bold}User-abort detected!${normal}" remove_temp_database exit 1 } # use pihole-FTL's built-in sql shell # putting this in a function allows to easily change to another sqlite engine one day if necessary sqlite () { if [ "$PIHOLE_DOCKER" = 0 ]; then pihole-FTL sqlite3 "$@" else docker exec -i "$CONTAINER_ID" pihole-FTL sqlite3 "$@" fi } # use declare to save the sqlite function, which will be passed by bash -c to the sudo commands # https://unix.stackexchange.com/questions/269078/executing-a-bash-script-function-with-sudo SUDO_SQLITE=$(declare -f sqlite) trap cleanup_on_trap INT # getopts flags and assign arguments to variables while getopts 'd:t:s:uarvh' flag; do case "${flag}" in d) DAYS_REQUESTED="${OPTARG}" ;; t) TOP="${OPTARG}" ;; s) SORT="${OPTARG}" ;; u) UNIQUE=1 ;; a) AUTOMATIC_MODE=1 ;; r) REGEX_MODE=1 ;; v) print_version exit 0 ;; h) print_help exit 0 ;; *) print_help exit 1 ;; esac done ### warm-up ###### warm_up() { # figure out if Pi-hole runs in docker if command -v pihole &> /dev/null then PIHOLE_DOCKER=0 else if (command -v docker &> /dev/null) && [ "$(docker ps -q -f status="running" -f name="pihole")" ] then PIHOLE_DOCKER=1 CONTAINER_ID=$(docker ps -f "name=pihole" -q) else echo -e "\n\n [✗] ${bold}No Pi-hole installation detected. Exiting.${normal}" exit 1 fi fi # get Pi-hole's dnsmasq version if [ "$PIHOLE_DOCKER" = 0 ]; then PIHOLE_DNSMASQ_VERSION=$(pihole-FTL -vv |awk '/dnsmasq/{getline; print substr($2,9)}') else PIHOLE_DNSMASQ_VERSION=$(docker exec "$CONTAINER_ID" pihole-FTL -vv |awk '/dnsmasq/{getline; print substr($2,9)}') fi # Enforce an up-to-date pihole version. This also guarantees that this script gets all necessary information if [ "$(printf '%s\n' "2.87" "$PIHOLE_DNSMASQ_VERSION" | sort -V | head -n1)" = "2.87" ]; then : else echo -e "\n\n [✗] ${bold}You're running an old Pi-hole version which is missing important security updates. Please upgrade.${normal}" exit 1 fi # exit if $DAYS_REQUESTED is no digit case "$DAYS_REQUESTED" in ''|*[0-9]*) ;; *) print_help exit 1 ;; esac # exit if $TOP is no digit case "$TOP" in ''|*[0-9]*) ;; *) print_help exit 1 ;; esac echo -e "\n ++++++++ Info ++++++++\n" # running Pi-hole in Docker? if [ "$PIHOLE_DOCKER" = 0 ]; then echo -e " [i] PIHOLE_DOCKER: No" else echo -e " [i] PIHOLE_DOCKER: Yes" fi # print Pi-hole FTL version and SQLite version echo -e " [i] PIHOLE_DNSMASQ_VERSION: $PIHOLE_DNSMASQ_VERSION" if [ "$PIHOLE_DOCKER" = 0 ]; then SQLITE_VERSION=$(pihole-FTL sqlite3 --version|awk '{print $1}') else SQLITE_VERSION=$(docker exec "$CONTAINER_ID" pihole-FTL sqlite3 --version|awk '{print $1}') fi echo -e " [i] SQLITE_VERSION: $SQLITE_VERSION" # print number of requested days if [ "$DAYS_REQUESTED" = 0 ]; then echo -e " [i] DAYS_REQUESTED: all time" else echo -e " [i] DAYS_REQUESTED: $DAYS_REQUESTED" fi # print number of requested top blocked domains if [ "$TOP" = 0 ]; then echo -e " [i] TOP: Not shown" else echo -e " [i] TOP: $TOP top blocked domains" fi # set sort order for adlist table based on $SORT case "$SORT" in total) SORT_ORDER="total_domains DESC" echo -e " [i] SORT_ORDER: total_domains DESC";; domains) SORT_ORDER="domains_covered DESC" echo -e " [i] SORT_ORDER: domains_covered DESC";; hits) SORT_ORDER="hits_covered DESC" echo -e " [i] SORT_ORDER: hits_covered DESC";; unique) SORT_ORDER="unique_domains_covered DESC" echo -e " [i] SORT_ORDER: unique_domains_covered DESC";; *) SORT_ORDER="id ASC" echo -e " [i] SORT_ORDER: id ASC";; esac # print if unique covered domains should be shown if [ "$UNIQUE" -eq 1 ]; then echo -e " [i] UNIQUE: Shown" else echo -e " [i] UNIQUE: Not shown" fi # print if RegEx should be analysed as well if [ "$REGEX_MODE" -eq 1 ]; then # do not RegEx when running in docker container: if [ "$PIHOLE_DOCKER" = 1 ]; then REGEX_MODE=0 echo -e " [i] REGEX_MODE: Disabled due to running Pi-hole in Docker Container" else echo -e " [i] REGEX_MODE: Enabled" fi else echo -e " [i] REGEX_MODE: Disabled" fi # verify if there is an update available remote_version=$(fetch_remote_version) if [ -n "$remote_version" ] && [ "$remote_version" != "$PIHOLE_ADLIST_TOOL_VERSION" ]; then echo -e " [i] There is an update available: $remote_version" fi echo -e "\n ++++++++++++++++++++++\n\n" } #### Program starts ##### echo echo echo " ${bold}*** Pihole Adlist Tool $PIHOLE_ADLIST_TOOL_VERSION ***${normal}" echo warm_up # calculate various timestamps, converts them to dates set_timestamps set_dates # get FTL_ID based on $TIMESTAMP_REQUESTED FTL_ID=$(sqlite "$PIHOLE_FTL" "SELECT MIN(id) FROM queries WHERE timestamp>=$TIMESTAMP_REQUESTED;") # Print timestamp warnings if [ "$TIMESTAMP_REQUESTED" -gt "$TIMESTAMP_LAST_QUERY" ]; then echo echo " [i] ${bold}Warning:${normal} You requested to analyze the last ${DAYS_REQUESTED} day(s) (starting from $DATE_REQUESTED)," echo " but last query is from $DATE_LAST_QUERY" echo " [i] Nothing to do here. Exiting " echo exit 0 fi if [ "$TIMESTAMP_REQUESTED" -lt "$TIMESTAMP_FIRST_QUERY" ]; then echo echo -e " [i] ${bold}Warning:${normal} You requested to analyze the last ${DAYS_REQUESTED} days (starting from $DATE_REQUESTED)," echo -e " but oldest query is from $DATE_FIRST_QUERY. Using this instead\n" echo fi # save old adlist_configuration mapfile -t adlist_conf_old_enabled < <(sqlite $GRAVITY "select id from adlist where enabled=1;") echo " Would you like to analyze your current adlist configuration or first enable all adlists (current can be restored later)?" echo echo " 1) Current adlist configuration" echo " 2) Enable all adlists (runs pihole -g)" echo if [ "$AUTOMATIC_MODE" -eq 1 ]; then ENABLE_ALL_ADLISTS_FOR_ANALYSIS=1 echo " [i] Running in automatic mode, keeping current adlist configuration." fi while [[ $ENABLE_ALL_ADLISTS_FOR_ANALYSIS != [12] ]]; do read -r -p " Please select: " ENABLE_ALL_ADLISTS_FOR_ANALYSIS done echo echo if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 1 ]; then if [ "$AUTOMATIC_MODE" -eq 0 ]; then echo " [i] Keeping current adlist configuration" fi # check if a mismatch between enabled adlists and data in gravity exists, offer to run gravity # https://stackoverflow.com/a/28161520 mapfile -t adlist_enabled_in_gravity < <(sqlite $GRAVITY "select distinct adlist_id from gravity;") if [ -n "$(echo "${adlist_conf_old_enabled[@]}" "${adlist_enabled_in_gravity[@]}" | tr ' ' '\n' |sort |uniq -u)" ]; then echo echo " [i] There is a mismatch between your enabled adlists and the data found in the gravity database." echo " You have ${bold}${#adlist_conf_old_enabled[@]}${normal} adlists enabled, but data from ${bold}${#adlist_enabled_in_gravity[@]}${normal} (partially different) adlists in your gravity database." echo " You're likely disabled/enabled adlist without running gravity afterwards." echo " It's highly recommended to run gravity now to solve the differences, otherwise this tool will analyze the available data." echo echo echo " Would you like to run gravity now?" echo echo " 1) Yes" echo " 2) No" echo if [ "$AUTOMATIC_MODE" -eq 1 ]; then RUN_GRAVITY_NOW=2 echo " [i] Running in automatic mode, not running gravity." fi while [[ $RUN_GRAVITY_NOW != [12] ]]; do read -r -p " Please select: " RUN_GRAVITY_NOW done if [ "$RUN_GRAVITY_NOW" -eq 1 ]; then echo echo echo " [i] Starting gravity" echo if [ "$PIHOLE_DOCKER" = 0 ]; then pihole -g else docker exec "$CONTAINER_ID" pihole -g fi echo echo " [✓] Gravity update finished" echo echo echo else echo echo if [ "$AUTOMATIC_MODE" -eq 0 ]; then echo " [i] Not running gravity, keeping mismatch between enabled adlists and the data found in the gravity database. " fi fi fi fi if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 2 ]; then echo " [i] Enabling all adlists...." if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=1;" fi echo echo echo " [i] Starting gravity" echo if [ "$PIHOLE_DOCKER" = 0 ]; then pihole -g else docker exec "$CONTAINER_ID" pihole -g fi echo echo " [✓] Gravity update finished" echo echo echo fi echo echo echo " [i] Calculating....." echo " [i] This might take some time - please be patient." # # # # Database manipulation # To analyze the data this tool creates a temporary database using data provided by pihole-ftl.db and gravity.db # timeout is set to 5000 ms in which sqlite tries to open an locked database # # # table blocked_domains contains all domains that would have been blocked # table adlist contauns the info about all adlists and how many domains, hits, unique domains it contains # table gravity_strip is a stripped version of the gravity database's gravity table which contains only domains that have been visited # table blacklist_gravity contains all domains that are on the personal blacklist and also in gravity_strip # table unique_domains contains all domains from gravity_strip that are found just on one adlist # table cname contains all domains that have been blocked during deep CNAME inspection (not the requested domain, but the actual blocked domain) # table blacklist_cname contains all domains that are on the personal blacklist and also in cname # table info contains various pre-calculated information # create $TEMP_DB sqlite $TEMP_DB << EOF create table blocked_domains (domain TEXT UNIQUE,hits INTEGER); create table adlist (id INTEGER, enabled INTEGER, address TEXT, total_domains INTEGER, domains_covered INTEGER, hits_covered INTEGER, unique_domains_covered INTEGER); create table gravity_strip (domain TEXT,adlist_id INTEGER); create table blacklist_gravity(domain TEXT, hits INTEGER); create table unique_domains(domain TEXT, adlist_id INTEGER); create table cname(additional_info TEXT,hits INTEGER); create table blacklist_cname(domain TEXT, hits INTEGER); create table info (property TEXT, value INTEGER); .exit EOF # get all data from $PIHOLE_FTL and $GRAVITY # 1.) select all domains from pihole-ftl.db that that are also found in gravity.db. Depending on -d n this is limited to the last n days # 2.) copies id, enable, address, number from gravity.adlist to table adlist # 3.) strip gravity's gravity table to domains that have been visited (are in blocked_domains table) # 4.) select all domains that are on the blacklist and also found in gravity_strip # 5.) update blacklist_gravity with the number of hits for each domain (must be done before CNAME handling, as this adds hits to domains found during CNAME instection) # CNAME handling # 6.) table cname selects all domains from pihole-ftl.db (additional_info) that that are also found in gravity.db and have status=9. # (status=9 == "Domain contained in gravity database & Blocked during deep CNAME inspection". This is just being cautious, because "additional_info" might contain other domains in the future for purposes different than CNAME inspection) # 7.) add blocked domains (found by deep CNAME inspection) to gravity_strip # 8.) add domain and hits found during cname analysis to blocked_domains; if domain is already on the list, only update the hit counter # (this is the critical step - "upsert" function was introduced first in sqlite with 3.24) # 9.) select all domains that are on the blacklist and also found during deep CNAME inspection # 10.) update blacklist_cname with the number of hits for each domain sqlite -cmd ".timeout 5000" $TEMP_DB << EOF ATTACH DATABASE "${PIHOLE_FTL}" AS pihole_ftl_db; ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db; INSERT INTO blocked_domains(domain, hits) SELECT domain, COUNT(domain) FROM pihole_ftl_db.queries WHERE EXISTS (select 1 from gravity_db.gravity where gravity.domain=queries.domain) AND id>=${FTL_ID} GROUP BY domain ORDER BY COUNT(domain) DESC; INSERT INTO adlist (id, enabled, address, total_domains) SELECT id, enabled, address, number FROM gravity_db.adlist ORDER BY adlist.id; INSERT INTO gravity_strip(domain,adlist_id) SELECT gravity_db.gravity.domain, gravity_db.gravity.adlist_id FROM gravity JOIN blocked_domains ON blocked_domains.domain = gravity.domain; INSERT INTO blacklist_gravity(domain) SELECT gravity_strip.domain FROM gravity_strip JOIN gravity_db.domainlist on gravity_strip.domain=gravity_db.domainlist.domain WHERE type==1 GROUP BY gravity_strip.domain; UPDATE blacklist_gravity SET hits=(SELECT blocked_domains.hits FROM blocked_domains WHERE blocked_domains.domain=blacklist_gravity.domain); INSERT INTO cname(additional_info, hits) SELECT additional_info, COUNT(domain) FROM pihole_ftl_db.queries WHERE EXISTS (select 1 from gravity_db.gravity where gravity.domain=queries.additional_info) AND id>=${FTL_ID} AND status=9 GROUP BY additional_info ORDER BY COUNT(additional_info) DESC; INSERT OR IGNORE INTO gravity_strip(domain,adlist_id) SELECT gravity_db.gravity.domain, gravity_db.gravity.adlist_id FROM gravity JOIN cname ON cname.additional_info = gravity.domain; INSERT INTO blocked_domains (domain, hits) SELECT additional_info,hits FROM cname WHERE true ON CONFLICT(domain) DO UPDATE SET hits=hits+(SELECT hits FROM cname); INSERT INTO blacklist_cname(domain) SELECT cname.additional_info FROM cname JOIN gravity_db.domainlist on cname.additional_info=gravity_db.domainlist.domain WHERE type==1 GROUP BY cname.additional_info; UPDATE blacklist_cname SET hits=(SELECT cname.hits FROM cname WHERE cname.additional_info=blacklist_cname.domain); DETACH DATABASE gravity_db; DETACH DATABASE pihole_ftl_db; .exit EOF # finish database work in $TEMP_DB # # # 1.) select all domains that are only once in gravity_strip (covered unique domains) # 2.) counts how many domains an adlist would have covered if used alone # 3.) counts how many hits an adlist would have covered if used alone # 4.) counts the number of unique domains covered by each adlist # 5-11.) Calculate some statistics sqlite $TEMP_DB << EOF INSERT INTO unique_domains(domain, adlist_id) SELECT domain, adlist_id FROM gravity_strip GROUP BY domain HAVING COUNT(domain)==1 order by adlist_id asc; UPDATE adlist SET domains_covered=(select count(domain) FROM gravity_strip WHERE id== adlist_id GROUP BY adlist_id); UPDATE adlist SET hits_covered=(SELECT SUM(blocked_domains.hits) FROM gravity_strip JOIN blocked_domains ON gravity_strip.domain == blocked_domains.domain WHERE id== adlist_id Group by adlist_id); UPDATE adlist SET unique_domains_covered=(SELECT COUNT(domain) FROM unique_domains WHERE adlist_id==id GROUP BY adlist_id); INSERT INTO info (property, value) Select 'NUM_ADLISTS', COUNT(id) FROM adlist; INSERT INTO info (property, value) Select 'NUM_ADLISTS_ENABLED', COUNT(id) FROM adlist WHERE enabled==1; INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_CURRENT', COUNT(domain) FROM blocked_domains; INSERT INTO info (property, value) Select 'HITS_TOTAL_CURRENT', SUM(hits) FROM blocked_domains; INSERT INTO info (property, value) Select 'BLACKLIST_GRAVITY', COUNT(*) FROM blacklist_gravity; INSERT INTO info (property, value) Select 'NUM_TOTAL_UNIQUE_DOMAINS', COUNT(*) FROM unique_domains; INSERT INTO info (property, value) Select 'BLACKLIST_CNAME', COUNT(*) FROM blacklist_cname; .exit EOF # get some statistics # the number of domains blocked and hits is the sum of enties with status 1 or 9 read -r NUM_DOMAINS_BLOCKED HITS_TOTAL <<<"$(sqlite -separator " " "$PIHOLE_FTL" "SELECT COUNT(DISTINCT domain),count(domain) FROM queries WHERE id>=${FTL_ID} AND status in (1,9);")" NUM_ADLISTS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS';") NUM_ADLISTS_ENABLED=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS_ENABLED';") NUM_DOMAINS_BLOCKED_CURRENT=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_CURRENT';") HITS_TOTAL_CURRENT=$(sqlite $TEMP_DB "SELECT value FROM info where property='HITS_TOTAL_CURRENT';") BLACKLIST_GRAVITY=$(sqlite $TEMP_DB "SELECT value FROM info where property='BLACKLIST_GRAVITY';") NUM_TOTAL_UNIQUE_DOMAINS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_TOTAL_UNIQUE_DOMAINS';") BLACKLIST_CNAME=$(sqlite $TEMP_DB "SELECT value FROM info where property='BLACKLIST_CNAME';") NUM_GRAVITY_UNIQUE_DOMAINS=$(sqlite $GRAVITY "SELECT value FROM info WHERE property == 'gravity_count';") echo echo " [i] You have ${bold}$NUM_ADLISTS adlists${normal} configured ($NUM_ADLISTS_ENABLED enabled)" echo " [i] Your gravity.db contains ${bold}$NUM_GRAVITY_UNIQUE_DOMAINS unique domains${normal}" echo " [i] Since $DATE_FIRST_ANALYZED ${bold}$NUM_DOMAINS_BLOCKED different domains${normal} from your adlists have been blocked ${bold}$HITS_TOTAL times${normal} in total" echo " (blocked directly by gravity or during deep CNAME inspection)" echo " [i] Using your current adlist configuration ${bold}$NUM_DOMAINS_BLOCKED_CURRENT domains${normal} would have been blocked ${bold}$HITS_TOTAL_CURRENT times${normal}" echo echo echo if [ "$BLACKLIST_GRAVITY" -ne 0 ]; then echo echo echo " [i] ${bold}You hit a special case${normal}" echo " Your personal blacklist contains at least one domain that is also on an adlist" echo " and has been requested in the selected time period. If it was blocked by gravity," echo " it got a special status ('blocked by blacklist' instead of 'blocked by gravity')" echo " and is NOT counted on the above number of blocked domains/hits. As the domain is on an" echo " adlist, the number of potentially blocked domains/hits is therefore higher." echo echo sqlite -column -header $TEMP_DB "SELECT * FROM blacklist_gravity" echo echo " [i] Use 'pihole -q DOMAIN' to see which adlist(s) contains the requested domain" echo echo echo fi if [ "$BLACKLIST_CNAME" -ne 0 ]; then echo echo echo " [i] ${bold}You hit a special case${normal}" echo " Your personal blacklist contains at least one domain that is also on an adlist" echo " and has been blocked in the selected time period by deep CNAME inspection. It got a special" echo " status ('blocked by blacklist during deep CNAME inspection' instead of 'blocked by gravity')" echo " and is NOT counted on the above number of blocked domains/hits. As the domain is on an adlist," echo " the number of potentially blocked domains/hits is therefore higher." echo echo sqlite -column -header $TEMP_DB "SELECT * FROM blacklist_cname" echo echo echo fi echo echo # prints n top potentially blocked domains based on -t argument if [ "$TOP" = 0 ]; then : else echo echo " [i] ${bold}Top blocked adlist domains${normal}" echo " Those would have been the ${bold}$TOP top blocked adlist domains${normal} since $DATE_FIRST_ANALYZED" echo " using your current adlist configuration" echo sqlite -column -header $TEMP_DB "SELECT domain, hits FROM blocked_domains LIMIT ${TOP};" echo echo echo echo fi echo echo echo " [i] ${bold}Adlist coverage${normal}" echo echo # prints the adlist table, sorting depends on -s argument sqlite -column -header $TEMP_DB "SELECT id, enabled, total_domains, domains_covered, hits_covered, unique_domains_covered, address FROM adlist ORDER BY ${SORT_ORDER};" echo echo echo echo " [i] Domains from disabled adlists are not stored in gravity's database." echo " If you want to include them in the analysis, run this script again and select 'Enable all adlists'" echo " As the same domains usually appears on more than one adlist the sum of covered domains from this table is greater " echo " than the number of calculated blocked domains shown above" echo echo echo " [i] In total your adlists contain ${bold}$NUM_TOTAL_UNIQUE_DOMAINS visited (covered) unique domains${normal} - meaning those domains are contained only in a single adlist. " echo echo if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 1 ]; then echo " Would you like to ... " echo echo " 1) Keep your current adlist configuration" echo " 2) Enable only adlists with covered unique domains" echo " 3) Enable the minimal number of adlists that cover all domains that would have been blocked" echo if [ "$AUTOMATIC_MODE" -eq 1 ]; then FURTHER_ACTION=1 echo " [i] Running in automatic mode, keeping current adlist configuration." fi while [[ $FURTHER_ACTION != [123] ]]; do read -r -p " Please select: " FURTHER_ACTION done if [ "$FURTHER_ACTION" -eq 1 ] && [ "$AUTOMATIC_MODE" -eq 0 ]; then echo echo " [i] Keeping current adlist configuration" fi else echo " Would you like to ..." echo echo " 1) Keep all adlists enabled" echo " 2) Enable only adlists with covered unique domains" echo " 3) Enable the minimal number of adlists that cover all domains that would have been blocked" echo " 4) Restore previous adlist configuration" echo while [[ $FURTHER_ACTION != [1234] ]]; do read -r -p " Please select: " FURTHER_ACTION done if [ "$FURTHER_ACTION" -eq 1 ]; then echo echo " [i] Keeping all adlists enabled" fi fi if [ "$FURTHER_ACTION" -eq 2 ]; then echo echo " [i] Enabling adlists with covered unique domains...." if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=0;" fi mapfile -t adlist_conf_unique_enabled < <(sqlite $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;") for adlist_id in "${adlist_conf_unique_enabled[@]}"; do if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;" fi done if [ "$PIHOLE_DOCKER" = 0 ]; then pihole restartdns reload-lists else docker exec "$CONTAINER_ID" pihole restartdns reload-lists fi echo echo " [✓] Adlists with covered unique domains enabled" echo domains_blocked_future fi if [ "$FURTHER_ACTION" -eq 3 ]; then echo echo " [i] Enabling minimum number of adlists that cover all domains that would have been blocked...." if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=0;" fi # get all adlist_ids with unique domains (same as $adlist_conf_unique_enabled) # create a copy of gravity_strip where domains can be removed from (gravity_strip is used later again) # delete all domains from gravity_dup that are also found on an adlist in the array with the unique domains # repeat until gravity_dup is empty # get the adlist_id for which there are the most remaining domains on gravity_dup # add this adlist_id to the array # remove all domains from gravity_dup that are also contained in that adlist # count how many domains are still on gravity_dup mapfile -t adlist_conf_minimal_enabled < <(sqlite $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;") sqlite $TEMP_DB "CREATE TABLE gravity_dup AS SELECT * FROM gravity_strip" for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do sqlite $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$adlist_id);" done LEFT_DOMAINS=$(sqlite $TEMP_DB "SELECT COUNT (domain) from gravity_dup;") while [[ ${LEFT_DOMAINS} != 0 ]]; do current_id=$(sqlite $TEMP_DB "Select adlist_id from gravity_dup group by adlist_id order by count (domain) desc, adlist_id asc limit 1;"); adlist_conf_minimal_enabled[${#adlist_conf_minimal_enabled[@]}]="$current_id" sqlite $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$current_id);" LEFT_DOMAINS=$(sqlite $TEMP_DB "SELECT COUNT (domain) from gravity_dup;") done echo echo " [i] Enabling adlists with id" "${adlist_conf_minimal_enabled[@]}" for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;" fi done if [ "$PIHOLE_DOCKER" = 0 ]; then pihole restartdns reload-lists else docker exec "$CONTAINER_ID" pihole restartdns reload-lists fi echo echo " [✓] Minimal number of adlists that cover all domains (that would have been blocked) enabled" echo domains_blocked_future fi if [ "$FURTHER_ACTION" -eq 4 ]; then echo echo " [i] Restoring previous adlist configuration...." if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=0;" fi for adlist_id in "${adlist_conf_old_enabled[@]}"; do if [ "$PIHOLE_DOCKER" = 0 ]; then sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'" else sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;" fi done if [ "$PIHOLE_DOCKER" = 0 ]; then pihole restartdns reload-lists else docker exec "$CONTAINER_ID" pihole restartdns reload-lists fi echo echo " [✓] Previous adlist configuration restored" echo domains_blocked_future fi if [ "$UNIQUE" = 1 ]; then echo echo echo " [i] ${bold}Covered unique domains${normal}" echo sqlite -column -header $TEMP_DB "SELECT domain, adlist_id, address FROM unique_domains JOIN adlist WHERE adlist_id=id;" echo echo fi # analyse RegEx if [ "$REGEX_MODE" -eq 1 ]; then echo echo echo " [i] Analysing RegEx ....." echo " [i] This might take some time (minutes!) - please be patient." echo echo # table all_regex contains all RegEx from gravity.db # table regex_black contains all blacklisted RegEx from gravity.db # table all_domains contains all domains (in the selected time period) from the pihole-FTL.db (including domains from CNAME inspection) # table domain_by_regex contains all domains and the blocking RegEx # table domainlist_regex contains all exact domains that are on the personal black/whitelist and covered by RegEx as well # 1.) copy blacklisted RegEx info from gravity database # 2.) copy distinct domains from pihole-FTL.db # 3.) add distinct domains from pihole-FTL.db found in additional_info columen coming from CNAME inspection (status 9,10,11) # 4.) Save some statistics sqlite -cmd ".timeout 5000" $TEMP_DB << EOF ATTACH DATABASE "${PIHOLE_FTL}" AS pihole_ftl_db; ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db; CREATE table all_regex (id TEXT UNIQUE, regex TEXT); CREATE table regex_black (id TEXT UNIQUE, regex TEXT, enabled INTEGER, domains_covered INTEGER); CREATE table all_domains(domain TEXT UNIQUE); CREATE table domain_by_regex(domain TEXT, regex_id INTEGER); CREATE table domainlist_regex(domain TEXT, regex_id INTEGER, regex TEXT); INSERT INTO all_regex(id, regex) SELECT id, domain FROM gravity_db.domainlist WHERE type in (2,3); INSERT INTO regex_black(id, regex,enabled) SELECT id, domain, enabled FROM gravity_db.domainlist WHERE type =3; INSERT INTO all_domains(domain) SELECT distinct domain FROM pihole_ftl_db.queries WHERE id>=${FTL_ID}; INSERT OR IGNORE INTO all_domains(domain) SELECT distinct additional_info FROM pihole_ftl_db.queries WHERE status in (9,10,11) AND id>=${FTL_ID}; INSERT INTO info (property, value) Select 'NUM_ALL_DOMAINS', COUNT(*) FROM all_domains; INSERT INTO info (property, value) Select 'NUM_REGEX', COUNT(*) FROM regex_black; INSERT INTO info (property, value) Select 'NUM_ENABLED_REGEX', COUNT(id) FROM regex_black WHERE enabled=1; DETACH DATABASE gravity_db; DETACH DATABASE pihole_ftl_db; .exit EOF # Regex vs. GRAVITY # copy all domains from table all_domais in array all_domains # iterate over each domain in all_domains # for each domain check if it is covered by a RegEx (using pihole-FTL regex-test) # if the test returns regex_ids, save them in domain_by_regex table # NOTE: pihole-FTL regex-test will test also against RegEx whitelist BUT this is still much faster than to create a second loop to check against each RegEx blacklist individually mapfile -t all_domains < <(sqlite $TEMP_DB "SELECT domain FROM all_domains") for CURRENT_DOMAIN in "${all_domains[@]}"; do pihole-FTL regex-test "$CURRENT_DOMAIN" |grep -E -o "DB ID [0-9]*"|awk '{print $3}' | while read -r REGEX_ID; do sqlite $TEMP_DB "INSERT INTO domain_by_regex(domain, regex_id) VALUES ('$CURRENT_DOMAIN',$REGEX_ID);" done done # count for each RegEx_id how many domains are in domain_by_regex and store it in table regex_black sqlite $TEMP_DB "UPDATE regex_black SET domains_covered=(SELECT COUNT(regex_id) from domain_by_regex WHERE id=regex_id GROUP BY regex_id );" # Regex vs domainlist # copy all exact black/whitelisted domains from gravity's domainlist (type=1 or type=0) # iterate over each domain # for each domain check if it is covered by a RegEx (using pihole-FTL regex-test) # if the test returns regex_ids, save them in domainlist_regex mapfile -t all_exact_domains < <(sqlite $GRAVITY "SELECT domain FROM domainlist WHERE type in (0,1);") for CURRENT_DOMAIN in "${all_exact_domains[@]}"; do pihole-FTL regex-test "$CURRENT_DOMAIN" |grep -E -o "DB ID [0-9]*"|awk '{print $3}' | while read -r REGEX_ID; do sqlite $TEMP_DB "INSERT INTO domainlist_regex(domain, regex_id) VALUES ('$CURRENT_DOMAIN',$REGEX_ID);" done done sqlite $TEMP_DB "UPDATE domainlist_regex SET regex=(SELECT regex from all_regex WHERE id=regex_id);" # get stats # the number of different domains that would have been blocked by RegEx with the current RegEx configuration sqlite $TEMP_DB "INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_BY_REGEX', COUNT (distinct domain) FROM domain_by_regex JOIN regex_black ON regex_id=id where enabled=1 ;" # the number of domainlist domains covered by RegEx sqlite $TEMP_DB "INSERT INTO info (property, value) Select 'DOMAINLIST_REGEX', COUNT(distinct domain) FROM domainlist_regex;" NUM_ALL_DOMAINS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ALL_DOMAINS';") NUM_REGEX=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_REGEX';") NUM_ENABLED_REGEX=$(sqlite $TEMP_DB "SELECT value FROM info WHERE property ='NUM_ENABLED_REGEX';") NUM_DOMAINS_BLOCKED_BY_REGEX=$(sqlite $TEMP_DB "SELECT value FROM info WHERE property ='NUM_DOMAINS_BLOCKED_BY_REGEX';") NUM_DOMAINLIST_REGEX=$(sqlite $TEMP_DB "SELECT value FROM info WHERE property ='DOMAINLIST_REGEX';") echo echo echo echo " [i] ${bold}Blacklist RegEx coverage${normal}" # prints the RegEx table echo echo sqlite -column -header $TEMP_DB "SELECT id, enabled, domains_covered, regex FROM regex_black;" echo echo echo " [i] Since $DATE_FIRST_ANALYZED you have been visiting ${bold}$NUM_ALL_DOMAINS different domains${normal}." echo " You have ${bold}$NUM_REGEX blacklist RegEx${normal} configured ($NUM_ENABLED_REGEX enabled)" echo " With your enabled blacklist RegEx you would have covered ${bold}$NUM_DOMAINS_BLOCKED_BY_REGEX different domains${normal}." echo echo " [i] Please note: the internal Pi-hole RegEx test used here only checks domains against ${bold}enabled RegEx${normal}." echo " Therefore, currently disabled RegEx will always have 0 domains covered." if [ "$NUM_DOMAINLIST_REGEX" -ne 0 ]; then echo echo echo " [i] ${bold}You hit a special case${normal}" echo " Your personal domainlist (black- and whitelist) contains at least one domain that is also covered by at least" echo " one of your RegEx (black- and whitelist)" echo echo sqlite -column -header $TEMP_DB "SELECT * FROM domainlist_regex" echo echo echo " ${bold}$NUM_DOMAINLIST_REGEX distinct domain(s)${normal} from your domainlist are covered by your RegEx." echo " Please note that this tool does not take into account if your domains and RegEx do match" echo " regarding their black/whitelist status. E.g. a whitelisted domain that is covered by a blacklist RegEx" echo " is counted and reported here as well." echo echo " [i] Use 'pihole-FTL regex-test DOMAIN' to get more information about that specific domain vs the RegEx filters" echo echo fi fi remove_temp_database