YouTube script seems to be working very well

[cmgeek]

My son the Roblox/Terraria gamer says it's working. 91 on the list now..

May  4 10:41:02 raspberrypi pihole-youtube: /tmp/tmp.ujySzNA9IO /etc/hosts.youtube differ: byte 3117, line 66
May  4 10:41:02 raspberrypi pihole-youtube: Added 1 domains, new total 91.

[jpgpi250]

The idea is great, so is the script. I was looking at the script and asked myself if it is really necessary to parse the log file (thus eliminating unnecessary system load), we do have this information in the database (/etc/pihole/pihole-FTL.db).

To get the information, already in the database (first run):

#!/bin/bash
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT "domain" FROM "queries" WHERE domain LIKE 'r_%---sn-%.googlevideo.com';" | sed 's/---/./g' > /home/pi/result.txt

assuming a five minute interval (cron) to pick up new entries:

#!/bin/bash
start="$(date  "+%R" -d "5 min ago")"
begintm=$(TZ=CET date --date="$start" +"%s")
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT "domain" FROM "queries" WHERE domain LIKE 'r_%---sn-%.googlevideo.com' AND timestamp > $begintm;" | sed 's/---/./g' > /home/pi/result.txt

This may return an empty result (no matches in the last 5 minutes), so further processing may be skipped, if the query result is empty.
It's possible the LIKE string in the sqlite3 query may need to be improved

Be aware the response isn't immediate, by default the sqlite3 database (/etc/pihole/pihole-FTL.db) is written every minute, this can be modified by adding a setting (example DBINTERVAL=2) in /etc/pihole/pihole-FTL.conf

[grublets]

Interesting idea. Had me wondering how much time was used taking that route through the sqlite engine.
Not real profiling, but using time on the sqlite query on my VM:

real	0m0.005s
user	0m0.000s
sys	0m0.000s

Bash script:

real	0m0.001s
user	0m0.000s
sys	0m0.000s

[Eyerex]

Can i just double check i'm doing it right as bit of a noob

Logged into Pi via SSH
opened "cd /usr/local/bin"
then opened nano text editor "sudo nano youtube.update.sh"
And pasted text youtube.update.sh from 1 to 49 with updated forceIP with ip address from nslookup then saved plus did pihole restartdns

Seems to be working

[cmgeek]

Did you also set up the script to run every minute using the 'crontab'?
sudo crontab -e to edit the file. Add this line
* * * * * /usr/local/bin/youtube.update.sh
to the bottom of the file. Exit and save. It will now run every minute.
Check the syslog for cron output to verify sudo cat /var/log/syslog

[Eyerex]

Big thanks as forget that bit

Think everything looks right

[grublets]

Hi all,

Please update the script and keep on testing, there's been a couple of bugs fixed (including a dumb one I added in the previous edit) and a potential permissions issue.

Gitlab link

Thanks @ jpgpi250.

[cmgeek]

Crank'in away here, may05 version... The new version found 9 domains

May  5 15:18:01 raspberrypi pihole-youtube: /tmp/tmp.whbIsMrvrE /etc/hosts.youtube differ: byte 1030, line 22
May  5 15:18:02 raspberrypi pihole-youtube: Added 1 domains, new total 110.
May  5 15:47:01 raspberrypi pihole-youtube: /tmp/tmp.SEKBad40SQ /etc/hosts.youtube differ: byte 4668, line 99
May  5 15:47:01 raspberrypi pihole-youtube: Added 1 domains, new total 111.
May  5 15:49:01 raspberrypi pihole-youtube: /tmp/tmp.mpF6FgJHky /etc/hosts.youtube differ: byte 4997, line 106
May  5 15:49:02 raspberrypi pihole-youtube: Added 1 domains, new total 112.
May  5 15:57:01 raspberrypi pihole-youtube: /tmp/tmp.Ahrg3fpIMy /etc/hosts.youtube differ: byte 13, line 1
May  5 15:57:01 raspberrypi pihole-youtube: Added 9 domains, new total 121.
May  5 15:59:01 raspberrypi pihole-youtube: /tmp/tmp.24K5WvtZAV /etc/hosts.youtube differ: byte 175, line 4
May  5 15:59:01 raspberrypi pihole-youtube: Added 1 domains, new total 122.

[apveening]

I added a minor contribution, allowing the IP-address to be passed in as a parameter. I hope it meets with approval.

[grublets]

Hi! You're a mind reader. I'll merge it in later, thanks. There's no sanity checking on any of the input as it's a proof of concept. It's apparently working well for everyone who tries it, so a safer/saner version will come.

[Perflyst]

Nice script! Could you please explain why it seems to be working?
If I look up hostnames similar to r1---sn-5hne6nsr.googlevideo.com I always get different IP addresses. If I execute your awk manually with these different IP addresses I also get always results back.

So what changes if we block one of them? There are, according to my log, hundreds of different IP addresses available - and we choose only one of them.

[grublets]

This doesn't block hosts, it forces them to an IP of our choosing. Also note there was some ambiguity between the various pihole restart* options that had me off on the wrong direction for reloading.
If you update the script now it should be good.

[Deepunder]

Great work by the way. Wouldn’t reload-lists be a better option than reload to avoid the cache getting dumped?

Thanks

[SomeWhereOutThere]

Here is an Example for setting up ipv6 for the host file. (I run a dual stack -diverting ads 100% of the time will not work without ipv6 host additions.)

#!/bin/bash

# crappy hack that seems to keep YouTube ads to a minumum.
# over two hours of Peppa Pig and no ads. Taking one for the team...
# grub@grub.net v0.11

# Change forceIP to the real IP from an nslookup of a
# googlevideo hostname so you get something in your
# geographical region. You can find one in your
# Pi-hole's query logs.
# They will look something like this:
#     r6---sn-ni5f-tfbl.googlevideo.com

# as root: run this once then run "pihole restartdns"
# You can cron this for auto-updating of the host file.
# Mine fires every minute:
# * * * * * /home/grub/bin/youtube.update.sh 2>&1

forceIP="24.244.14.177"
#added for ipv6
#forceIP6="2001:4e8:0:b043::11"

# nothing below here should need changing

piLogs="/var/log/pihole.log"
ytHosts="/etc/hosts.youtube"

workFile=$(mktemp)
dnsmasqFile="/etc/dnsmasq.d/99-youtube.grublets.conf"

if [ ! -f $dnsmasqFile ]; then
    echo "addn-hosts=$ytHosts" > $dnsmasqFile
    touch $ytHosts
    piLogs="$piLogs*" # preload with results from all logs
    echo "Setup complete! Execute 'pihole restartdns' as root."
    echo "cron the script to run every minute or so for updates."
fi

cp $ytHosts $workFile
zgrep -e "reply.*-.*\.googlevideo.*\..*\..*\..*" $piLogs \
    | awk -v fIP=$forceIP '{ print fIP, $6 }' >> $workFile
#this section added for ipv6
#zgrep -e "$forceIP" $workFile \
#    | awk -v fIP6=$forceIP6 '{ print fIP6, $2 }' >> $workFile

sort -u $workFile -o $workFile

if ! cmp $workFile $ytHosts; then
    mv $workFile $ytHosts
    chmod 644 $ytHosts
    /usr/local/bin/pihole restartdns reload
else
    rm $workFile
fi


exit

You must uncomment the ipv6 sections and add an appropriate ipv6 address based on the same NSlookup you did for the ipv4 nslookup.

also uncomment the zgrep section for ip6

edit
updated the ipv6 section.

[yubiuser]

@Deepunder

Wouldn’t reload-lists be a better option than reload to avoid the cache getting dumped?

No. See here

pihole-FTL (NOT sure there is a problem - requesting clarification)

You are right, the description is somewhat inexact, we will change this (however, it will probably go into v5.1 now): restartdns = restarts the entire FTL, incl. config file parsing and history re-reading restartdns reload = reload the DNS cache (this implies reloading the HOSTS files as you've seen) + reload database tables (white-/blacklist, gravity, groups, clients) restartdns reload-lists = only reload database tables (white-/blacklist, gravity, groups, clients), the embedded dnsmasq c…

[SomeWhereOutThere]

So far all is good, I have captured 198 domains.

[Deepunder]

Thank you. I’ve updated the script

[Eyerex]

Bit of a noob how do you check your logs ?

[Perflyst]

@grublets

This doesn't block hosts, it forces them to an IP of our choosing.

Ah, right, now I understand it.
Do you also know why it works?

[Eyerex]

Cheers