Report of blocked domains to be sent to you by email or download report

I thought of an idea where PiHole could potentially integrate a reporting feature that will email you a scheduled report (daily, weekly, monthly, whatever) with all blocked queries or you can download the report from the PiHole admin page.

They don't have to be in a very long giant list. It could be, as example:

ipv4.microsoft.com 60 queries blocked
ipv6.microsoft.com 100 queries blocked

Something like that. I know that some people use PiHole in a corporate environment and I feel that this could help serve IT staff in helping diagnose any potential malware issues or bad employee behavior.

Maybe this will suit your needs.
I've already explained here how to send yesterdays log (log rotation is available now, so use the instructions for pihole.log.1) to yourself.
By processing this log with some simple grep and sed commands, you can get the detailed information, redirect the output to a different file and mail it to yourself.
example for ipv4 wildcards (change the ip address to match your own):

grep 192.168.2.250 /var/log/pihole.log.1 | grep config | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sort | uniq

example for for ipv4 gravity list (change the ip address to match your own):

grep 192.168.2.250 /var/log/pihole.log.1 | grep gravity.list | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sort | uniq

I'm quite sure some people can come up with better grep/sed combinations, I'm not an expert.

2 Likes

This is incredibly useful, thank you!

I am nowhere near as advanced as you, but I could make this a cron job, right?

The method used to send the mail requires a script (sendlog.sh) and a cron job (/etc/cron.d/sendlog)

You could easely modify the script so that it:

  • redirects the wildcard output to a file (just add > /home/pi/wildcard.log)
  • redirects the ipv4 gravity list output to a file (just add > /home/pi/exact.log)
  • now modify the send command into:
echo -e "to: <your_account_name>@gmail.com\nsubject: pihole wildcard log\n"| (cat - && uuencode /home/pi/wildcard.log wildcard.log) | /usr/sbin/ssmtp <your_account_name>@gmail.com

duplicate the line (still changing sendlog.sh)

echo -e "to: <your_account_name>@gmail.com\nsubject: pihole exact log\n"| (cat - && uuencode /home/pi/exact.log exact.log) | /usr/sbin/ssmtp <your_account_name>@gmail.com

and save the file. You'll be getting to mails.

If you only want a single mail:

grep 192.168.2.250 /var/log/pihole.log.1 | grep config | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sort | uniq > /home/pi/blocked.log
grep 192.168.2.250 /var/log/pihole.log.1 | grep gravity.list | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sort | uniq >>  /home/pi/blocked.log
echo -e "to: <your_account_name>@gmail.com\nsubject: pihole exact log\n"| (cat - && uuencode /home/pi/blocked.log blocked.log) | /usr/sbin/ssmtp <your_account_name>@gmail.com

notice the >> on the second line, this means append.

Now create the crontab file as indicated, but change the time (it should be after midnight):
The command should be:

00 05 * * * root PATH="$PATH:/home/pi" sendlog.sh

that will (hopefully - i haven't tested this) do the trick.

1 Like

Wow thank you for all of this. My apologies in a late reply.

I will try this out tonight and will let you know how it worked out.

Cheers!

This may be partially solved by the new Audit Log feature in 3.2.

 grep 192.168.1.3 /var/log/pihole.log.1 | grep gravity.list | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sed 's/[^ ]* //' | sed 's/[^ ]* //' | sed -e 's/\/etc\/pihole\/gravity.list//g' | sed -e 's/is 192.168.1.3//g' | sed 's/\/[^/\]*  / /g' | sort | uniq -c

Of course the IP needs to be changed to your host.

Output:

this should be what @sycnewton needs :slight_smile:


  1. ^: ↩︎

You can even use the bellow code to give the count for blocked hits only (no IPs)

grep 192.168.1.3 /var/log/pihole.log.1 | grep gravity.list | sed 's/^.*]:/]:/' | sed 's/^[^:]*://g' | sed 's/[^ ]* //' | sed 's/[^ ]* //' | sed -e 's/\/etc\/pihole\/gravity.list/ blocked hits for host/g' | sed -e 's/is 192.168.1.3//g' | sed 's/\/[^/\]*  / /g' | cut --complement -d " " -f 1 | sort | uniq -c

Output:

image