Halo Zusammen,
ich weiß nicht ob das hier schonmal ein Thema war. Es gibt ein cooles Script was Euch mal zeigt wie effizient Eure ausgewählten Adlists auf Eurem pihole laufen und wieviel Blocks durch die relevanten und aktiven Adlists ausgelöst werden. Wer relativ viel Adlists auf gut Glück eingebaut hat, könnte damit gegebenenfalls auch etwas an Performance gewinnen, sich von unnötigen Adlists zu trennen. Legt Euch ein Script an und lasst es einfach in der Console laufen. Ihr werdet entsprechend in der Laufzeit des Scripts gefragt, was ihr tun wollt.
Ich übernehme keine Gewähr, das Ihr Euch was zerschießt, sollte aber nicht sein, da die ADlists im Pihole nur deaktiviert werden und nicht gelöscht. Also erst richtig lesen bevor man was bestätigt!
Viel Erfolg. Ich konnte damit meine ursprünglich größere Anzahl der konfig. Adlists effizient zusammendampfen. ACHTUNG. Der Prüflauf braucht etwas, also geduldig sein!
Grüße
Hier der Content des Scripts:
#!/bin/bash
# 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
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
TOP=0
SORT=
NUM_DOMAINS_BLOCKED=
HITS_TOTAL=
SORT_ORDER=
NUM_ADLISTS=
NUM_ADLISTS_ENABLED=
NUM_GRAVITY_UNIQUE_DOMAINS=
NUM_DOMAINS_BLOCKED_CURRENT=
HITS_TOTAL_CURRENT=
BLACKLIST_GRAVITY=
UNIQUE=0
NUM_TOTAL_UNIQUE_DOMAINS=
declare -a adlist_conf_old_enabled
declare -a adlist_conf_unique_enabled
declare -i menu_selection
NEW_ADLIST_FILENAME_SCHEMA=
CNAME_AVAILABLE=
BLACKLIST_CNAME=
SQLITE_VERSION=
NUM_DOMAINS_BLOCKED_FUTURE=
declare -a adlist_enabled_in_gravity
declare -i run_gravity_now
declare -a adlist_conf_minimal_enabled
left_domains=
#for text formating
bold=$(tput bold)
normal=$(tput sgr0)
# 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
-h Show this help dialog
"
}
#convert timestamp to date
timestamp2date () {
local DATE=$(date -d @$1 +%d.%m.%Y%t%k:%M:%S)
echo "$DATE"
}
# calculate timestamps
set_timestamps () {
TIMESTAMP_FIRST_QUERY=$(sqlite3 $PIHOLE_FTL "SELECT MIN(timestamp) FROM queries;")
TIMESTAMP_LAST_QUERY=$(sqlite3 $PIHOLE_FTL "SELECT MAX(timestamp) 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=$(sqlite3 $PIHOLE_FTL "SELECT min(timestamp) 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 initally, 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 () {
sqlite3 $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=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_FUTURE';")
echo
echo " [✓] 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..."
rm -f $TEMP_DB
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
}
trap cleanup_on_trap INT
# getopts flags and assing arguments to variables
while getopts 'd:t:s:uh' flag; do
case "${flag}" in
d) DAYS_REQUESTED="${OPTARG}" ;;
t) TOP="${OPTARG}" ;;
s) SORT="${OPTARG}" ;;
u) UNIQUE=1 ;;
h) print_help
exit 0 ;;
*) print_help
exit 1 ;;
esac
done
### warm-up ######
warm_up() {
# 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"
# 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
# Is the new adlist filename schema used
if git -C /etc/.pihole/ log 2> /dev/null |grep -q 73963fecda6dc65b10d1dd3e43a5931dc531304a; then
NEW_ADLIST_FILENAME_SCHEMA=1
echo -e " [i] NEW_ADLIST_FILENAME_SCHEMA: yes"
else
NEW_ADLIST_FILENAME_SCHEMA=0
echo -e " [i] NEW_ADLIST_FILENAME_SCHEMA: no"
fi
# does the query database contain the additional info for deep CNAME inspection
if sqlite3 ${PIHOLE_FTL} "PRAGMA table_info(queries);" |grep -q additional_info ;then
CNAME_AVAILABLE=1
echo -e " [i] CNAME_AVAILABLE: deep CNAME info available"
else
CNAME_AVAILABLE=0
echo -e " [i] CNAME_AVAILABLE: deep CNAME info not available"
fi
# get and print SQLite version
SQLITE_VERSION=$(sqlite3 --version|awk '{print $1}')
echo -e " [i] SQLITE_VERSION: $SQLITE_VERSION"
echo -e "\n ++++++++++++++++++++++\n\n"
}
#### Program starts #####
echo
echo
echo " ${bold}*** Pihole Adlist Tool ***${normal}"
echo
warm_up
# calculate various timestamps, converts them to dates
set_timestamps
set_dates
# get FTL_ID based on $TIMESTAMP_REQUESTED
FTL_ID=$(sqlite3 $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
adlist_conf_old_enabled=(`sqlite3 $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
while [[ $menu_selection != [12] ]]; do
read -p " Please select: " menu_selection
done
echo
echo
if [ "$menu_selection" -eq 1 ]; then
echo " [i] Keeping current adlist configuration"
# check if a mismatch between enabled adlists and data in gravity exists, offer to run gravity
# https://stackoverflow.com/a/28161520
adlist_enabled_in_gravity=(`sqlite3 $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} (patially 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
while [[ $run_gravity_now != [12] ]]; do
read -p " Please select: " run_gravity_now
done
if [ "$run_gravity_now" -eq 1 ]; then
echo
echo
echo " [i] Starting gravity"
echo
pihole -g
echo
echo " [✓] Gravity update finished"
echo
echo
echo
else
echo
echo
echo " [i] Not running gravity, keeping mismatch between enabled adlists and the data found in the gravity database. "
fi
fi
fi
if [ "$menu_selection" -eq 2 ]; then
echo " [i] Enabling all adlists...."
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=1;"
echo
echo
echo " [i] Starting gravity"
echo
pihole -g
echo
echo " [✓] Gravity update finished"
echo
echo
echo
fi
# if sqlite3 version <3.24.0, deactivate CNAME analysis, as at least this version is needed for UPSERT sql syntax in CNAME analysis
if [ "$(printf '%s\n' "3.24.0" "$SQLITE_VERSION" | sort -V | head -n1)" = "3.24.0" ] && [ "$CNAME_AVAILABLE" = 1 ]; then :
else
echo -e "\n [i] CNAME info availabe but SQLite version < 3.24.0. Deactivating CNAME analysis\n"
CNAME_AVAILABLE=0
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 acutal 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
sqlite3 $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 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)
sqlite3 -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) SELECT id, enabled, address 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);
DETACH DATABASE gravity_db;
DETACH DATABASE pihole_ftl_db;
.exit
EOF
# CNAME handling
# onyl executed if CNAME_AVAILABE is still 1 (also after SQLite check)
# 1.) 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)
# 2.) add blocked domains (found by deep CNAME inspection) to gravity_strip
# 3.) add domain and hits found during cname analysis to blocked_domains; if domain is already on the list, onyl update the hit counter
# (this is the critical step - "upsert" function was introduced frist in sqlite with 3.24)
# 4.) select all domains that are on the blacklist and also found during deep CNAME inspection
# 5.) update blacklist_cname with the number of hits for each domain
if [ "$CNAME_AVAILABLE" = 1 ]; then
sqlite3 -cmd ".timeout 5000" $TEMP_DB << EOF
ATTACH DATABASE "${PIHOLE_FTL}" AS pihole_ftl_db;
ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db;
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
fi
# finsih 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
sqlite3 $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
# Table adlist is updated with total number of domains for each id (adlist)
# Since commit 73963fecda6dc65b10d1dd3e43a5931dc531304a to pihole's core, locally saved adlist copies contain the adlist_id in the filename.
# We can use that to count the lines in each file and use the adlist_id to attribute it to the corresponding adlist in TEMP_DB
# This is faster than to count the domains for each adlist from gravity_db
# We use the new method only if the commit is found in the local git log to ensure that the new filename schema is used
if [ "$NEW_ADLIST_FILENAME_SCHEMA" = 1 ]; then
grep -c . /etc/pihole/list* |awk -F '[.:]' '{print $2 " "$NF}' | while read adlist_id count; do
sqlite3 $TEMP_DB "UPDATE adlist SET total_domains="${count}" WHERE id="${adlist_id}";"
done
else
sqlite3 -separator " " $GRAVITY "SELECT adlist_id,count(domain) FROM gravity GROUP BY adlist_id;" | while read adlist_id count; do
sqlite3 $TEMP_DB "UPDATE adlist SET total_domains="${count}" WHERE id="${adlist_id}";"
done
fi
# get some statistics
# depending on CNAME_AVAILABLE, the number of domains blocked and hits is the sum of enties with status 1 or (1 and 9)
if [ "$CNAME_AVAILABLE" = 1 ]; then
read NUM_DOMAINS_BLOCKED HITS_TOTAL <<<$(sqlite3 -separator " " $PIHOLE_FTL "SELECT COUNT(DISTINCT domain),count(domain) FROM queries WHERE id>=${FTL_ID} AND status in (1,9);")
else
read NUM_DOMAINS_BLOCKED HITS_TOTAL <<<$(sqlite3 -separator " " $PIHOLE_FTL "SELECT COUNT(DISTINCT domain),count(domain) FROM queries WHERE id>=${FTL_ID} AND status == 1;")
fi
NUM_ADLISTS=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS';")
NUM_ADLISTS_ENABLED=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS_ENABLED';")
NUM_DOMAINS_BLOCKED_CURRENT=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_CURRENT';")
HITS_TOTAL_CURRENT=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='HITS_TOTAL_CURRENT';")
BLACKLIST_GRAVITY=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='BLACKLIST_GRAVITY';")
NUM_TOTAL_UNIQUE_DOMAINS=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='NUM_TOTAL_UNIQUE_DOMAINS';")
BLACKLIST_CNAME=$(sqlite3 $TEMP_DB "SELECT value FROM info where property='BLACKLIST_CNAME';")
NUM_GRAVITY_UNIQUE_DOMAINS=$(sqlite3 $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}"
if [ "$CNAME_AVAILABLE" = 1 ]; then
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 you current adlist configuration ${bold}"$NUM_DOMAINS_BLOCKED_CURRENT" domains${normal} would have been blocked ${bold}"$HITS_TOTAL_CURRENT" times${normal}"
else
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 by gravity only)"
echo " [i] Using you current adlist configuration ${bold}"$NUM_DOMAINS_BLOCKED_CURRENT" domains${normal} would have been blocked ${bold}"$HITS_TOTAL_CURRENT" times${normal}"
fi
echo
echo
echo
read -p " Press enter to continue..."
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
sqlite3 -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
read -p " Press enter to continue..."
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
sqlite3 -column -header $TEMP_DB "SELECT * FROM blacklist_cname"
echo
echo
echo
read -p " Press enter to continue..."
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
sqlite3 -column -header $TEMP_DB "SELECT domain, hits FROM blocked_domains LIMIT "${TOP}";"
echo
echo
echo
echo
read -p " Press enter to continue..."
fi
echo
echo
echo " [i] ${bold}Adlist coverage${normal}"
echo
echo
# prints the adlist table, sorting depends on -s argument
sqlite3 -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 [ "$menu_selection" -eq 1 ];
then
menu_selection=
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
while [[ $menu_selection != [123] ]]; do
read -p " Please select: " menu_selection
done
if [ "$menu_selection" -eq 1 ]; then
echo
echo " [i] Keeping current adlist configuration"
fi
else
menu_selection=
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 [[ $menu_selection != [1234] ]]; do
read -p " Please select: " menu_selection
done
if [ "$menu_selection" -eq 1 ]; then
echo
echo " [i] Keeping all adlists enabled"
fi
fi
if [ "$menu_selection" -eq 2 ]; then
echo
echo " [i] Enabling adlists with covered unique domains...."
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=0;"
adlist_conf_unique_enabled=(`sqlite3 $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;"`)
for adlist_id in "${adlist_conf_unique_enabled[@]}"; do
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
done
pihole restartdns reload-lists
echo
echo " [✓] Adlists with covered unique domains enabled"
echo
domains_blocked_future
fi
if [ "$menu_selection" -eq 3 ]; then
echo
echo " [i] Enabling minimum number of adlists that cover all domains that would have been blocked...."
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=0;"
# 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
adlist_conf_minimal_enabled=(`sqlite3 $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;"`)
sqlite3 $TEMP_DB "CREATE TABLE gravity_dup AS SELECT * FROM gravity_strip"
for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do
sqlite3 $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$adlist_id);"
done
left_domains=(`sqlite3 $TEMP_DB "SELECT COUNT (domain) from gravity_dup;"`)
while [[ $left_domains != [0] ]]; do
current_id=(`sqlite3 $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"
sqlite3 $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$current_id);"
left_domains=(`sqlite3 $TEMP_DB "SELECT COUNT (domain) from gravity_dup;"`)
done
echo " [i] Enabling adlists with id ${adlist_conf_minimal_enabled[@]}"
for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
done
pihole restartdns reload-lists
echo
echo " [✓] Minimal number of adlists that cover all domains (that would have been blocked) enabled"
echo
domains_blocked_future
fi
if [ "$menu_selection" -eq 4 ]; then
echo
echo " [i] Restoring previous adlist configuration...."
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=0;"
for adlist_id in "${adlist_conf_old_enabled[@]}"; do
sudo sqlite3 $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
done
pihole restartdns reload-lists
echo
echo " [✓] Previous adlist configuration restored"
echo
domains_blocked_future
fi
if [ "$UNIQUE" = 1 ];
then
echo
echo
read -p " Press enter to continue (show covered unique domains)..."
echo
echo
echo " [i] ${bold}Covered unique domains${normal}"
echo
sqlite3 -column -header $TEMP_DB "SELECT domain, adlist_id, address FROM unique_domains JOIN adlist WHERE adlist_id=id;"
echo
echo
fi
remove_temp_database
