Just from playing about with this.. reading the log file line by line with fopen/fgets is slow as hell...

from web.

It is definitely slower than file or file_get_contents, yes. Because you will be working with the file on disk rather than in memory. Picking up a file and dumping it in memory is pretty fast, but with big log files probably not what you want.

The bigger question might be: do you even need the entire log file in memory in the first place? If you are parsing per line you can possibly ignore lines that do not match what you are looking for. You could also stop parsing the log when you come across timestamps of a certain age. I haven't played around with that yet.

Another possibility might be to use file_get_contents in a batch-like way. This might be faster than fopen/fgets, from the PHP documentation:

file_get_contents() is the preferred way to read the contents of a file into a string. It will use memory mapping techniques if supported by your OS to enhance performance.

You can specify an offset and a length for file_get_contents to read, so you might be able to read the log file in intervals of x kb.

from web.

can you move whole file to different language? like java or python?

i don't need pretty graphs what interests me is only output. it could be even in txt format.

from web.

The next iteration will have a full API, but for now there is https://github.com/pi-hole/pi-hole#api that will provide you with some basic information. Does that help?

from web.

The current API (api.php) gets all its data from the functions in data.php, data.php will try to read the entire log file into memory at once. So probably not.

Is there some recommended way for running a development instance of Pi-Hole outside of an actual RPi? E.g. a virtual box? I would love to give memory efficient log reading a go.

from web.

It will run on any Debian or CentOS/RHEL environment. So if you'd like, just spin up a virtual Debian or Ubuntu and run the script. There isn't anything left that is truly Raspberry Pi dependent, you can run it on x86/64. Just need apt or yum/dnf to grab the dnsmasq and lighttpd packages along with PHP.

from web.

I do all my dev work on my live pi, despite having two or three spares lying around..! But then I'm a cowboy like that.

Also handy is having an IDE like Idea (with php plugin) with sftp folder mapping set up for easy deployment/testing! For this though, you'll either need to chown the /var/www/html/admin directory, or log in as root.

from web.

how do i catch @Mcat12 here so he can help me with php log?

from web.

Hey, @xxcriticxx Looking at the size of your log, I think it's most likely an issue our end, which we're going to work on getting fixed with @Zegnat 's kind help!

from web.

@PromoFaux I would report that i have few nest thermostats and they generate about 15000 dns queries each unit per day

from web.

Ah! Yeah, that's going to build up a lot of log entries!

from web.

Yep, currently a large log file will do that sort of thing, since we haven't quite optimized how we read it yet.

from web.

i dont have much to show because i just did flush but in about 10 min i got this
log-rts14-iad01.devices.nest.com 412
transport03.rts14.iad01.production.nest.com 403

from web.

Suggestion...If you don't get value out of those nest lookups being in the logs I'd suggest removing them from the log. A filter for incoming log lines isn't easy as far as I know but removing the logs afterwards is simple: sed -i '/.production.nest.com/d' /var/log/pihole.log in a cron running every 5 minutes should purge all those super noisy domains. so something like:

$ cat /etc/cron.d/nest_dns_cleanup
MAILTO=/dev/null
*/5 * * * * root /bin/sed -i '/.production.nest.com/d' /var/log/pihole.log

note: $ prefix is a command, no prefix is the output of the example file

from web.

2h running and i have 65722 connection only about 5000 is from nest. i think there is still something more behind this.

from web.

and i have to flush log full again ...

from web.

Lets stop using the web interface to diagnose why your log is so big...we can use the command line easily.

To get the top Type of query you can do this:

cat /var/log/pihole.log | awk '{ print $5 }' | sort | uniq -c | sort -n

# output:
      4 /etc/hosts
    172 query[PTR]
    242 /etc/pihole/gravity.list
    275 cached
    686 query[AAAA]
   2422 query[A]
   4197 forwarded
  10956 reply

Forwards and replies are probably the top ones if you're like me, replies outnumber forwards by almost double.

Next check what domains are the top 10 replies:

grep reply /var/log/pihole.log | awk '{ print $6 }' | sort | uniq -c | sort -n | tail

# output (some redacted)
     80 d.v.dropbox.com
     87 redact.ed
    102 redact.ed
    137 plex.tv
    156 daisy.ubuntu.com
    238 redact.ed
    375 oauth.reddit.com
    405 ssl.reddit.com
    755 googleapis.l.google.com
   5262 lots.of.redact.ed

(i'm kind of curious how quick that runs when your log is really large too...)

Next you might want to see how many forwards are happening for each reply too: grep -c 'nest.com' /var/log/pihole.log will tell you how often the request to nest if forwarded instead of directly replied from dnsmasq's cache. If grep -c for a certain domain in your top 10 returns a lot higher number than the 2nd command did then you're not caching very much and instead forwarding requests constantly for that domain.

Hope this helps. It is still my opinion if preventing a device (nest) on your network from spamming DNS requests is not possible then finding out what domains it targets and just removing them from your log seems like an easy solution.

from web.

I looked into properly filtering incoming dnsmasq log-query messages and found this thread, which pointed out what I figured it would - the job should be done by your syslog facility instead of the app.

http://lists.thekelleys.org.uk/pipermail/dnsmasq-discuss/2015q2/thread.html#9664 ->
http://unix.stackexchange.com/questions/52860/how-to-exclude-given-lines-in-syslog-ng

from web.

cat /var/log/pihole.log | awk '{ print $5 }' | sort | uniq -c | sort -n
2 query[SRV]
6 config
25 query[DNSKEY]
61 query[DS]
219 Maximum
313 query[PTR]
815 query[SOA]
1091 /etc/pihole/gravity.list
15210 cached
36894 query[AAAA]
113435 query[A]
155530 forwarded
363881 reply

grep reply /var/log/pihole.log | awk '{ print $6 }' | sort | uniq -c | sort -n | tail
5513 time-ios.g.aaplimg.com
5730 ec2-54-162-184-226.compute-1.amazonaws.com
6344 kodi.filmkodi.com
7130 e6858.dscc.akamaiedge.net
7559 log-rts14-iad01.devices.nest.com
7699 gsp10-ssl.ls-apple.com.akadns.net
8328 freedisc.pl
9027 bob-fed.gameloft.com
14526 pop-namer-se-courier.push-apple.com.akadns.net
15118 devices-rts14-production-937225294.us-east-1.elb.amazonaws.com

grep -c 'nest.com' /var/log/pihole.log
43489

from web.

Ah, DNSSEC is enabled. That will account for some of the increased overhead due to the nature of how DNSSEC follows the chain up to the certificate roots nameserver. See 1.5. How Does DNSSEC Change DNS Lookup? May have some increased performance from increasing the timeout variable to a value greater than 300 as the more DNSSEC TXT records that are in cache means less traversing the chain to find the root.

Try doubling the value and see if that cuts down the number of forwarded queries? (I do run DNSSEC, but only on my Unbound edge server, I have Pi-hole just doing regular lookups using the Unbound install as the upstream, so the validation role doesn't pollute the Pi-hole records and all the heavy lifting is done by the Unbound process that I access via a secured channel.)

from web.

@dschaper now you lost me

what command do i use to change DNSSEC value

from web.

Are you running stock Pi-hole, without any changes to the dnsmasq configs? I don't think we enable DNSSEC validation by default, it's a rather overhead intensive process and is something mostly paranoid security folks like myself enable. It's also possible that one of your clients (192.168.1.1) has DNSSEC validation enabled, and that is the station that is requesting the DS and DNSKEY records, which would also possibly account for the high number of forwards in the query by type breakdowns.

@diginc Beauty log parsing there!

from web.

It will run on any Debian or CentOS/RHEL environment. So if you'd like, just spin up a virtual Debian or Ubuntu and run the script.

I'll give that a try. Maybe tomorrow, else during the weekend.

from web.

@dschaper yes i am running stock pi-hole on ubuntu 16.04 lts. my router is netgear r7000 running Firmware: DD-WRT v3.0-r29155M kongac (02/25/16)

this is the setting in my router

from web.

can upstream DNS force DNSSec on clients?

from web.

If the client is a DNSSEC aware resolver (not just a normal client in the regular sense that we use it, but an actual resolver like dnsmasq) then it could internally traverse the security chain, following the certificate up until it gets a CA that it trusts and then walk back down to the actual authoritative DNS server for the domain requested. So really, most clients aren't DNSSEC aware, it's the resolver that will either return a NXDOMAIN / SERVFAIL for domains that fail the chain of trust, or otherwise flag the response as untrusted.

The test I always use, as goofy as it is, is http://dnssec.vs.uni-due.de/. Upstream can't "Force" DNSSEC on clients, it can only return a record with either the AD bit set or unset, indicating trust or no-trust. It's up to the clients (and to some extent the resolver queried by the client) to determine what to do next.

As for the DD-WRT, I personally use OpenWRT (building LEDE) and the build environment I use enforces DNSSEC at the router level, and will give a NXDOMAIN if any of the certificates fail validation. What causes a lot of the extra lookups is when domains don't have any SEC records and you sometimes end up going up the chain to the Root TLD server and back down to the Auth NS server for no really good reason. That's why I offload all the validation to another process and just let Pi-hole handle resolution tasks, then none of the validation traffic shows in the Pi-hole logs. And Unbound is set to such a low log level that only errors show. (If you go for even medium level logging with DNSSEC enforced you get very large logs, so it's not really recommended unless you are trying to troubleshoot a bad trust-anchor or cert errors along the validation path.)

from web.

my log is about 72 mb

from web.

For how many hours of logging?

from web.

about 2 or 3 h and i had 75000 dns queries in that same time and then ajax error

from web.

Okay, try the old fashioned approach, sudo tail -f /var/log/pihole.log and watch the traffic as it screams by and see if you can find any patterns or repeated queries and look for things being forwarded.

from web.

here is output from your test

Yes, your DNS resolver validates DNSSEC signatures.

from web.

Okay, and what is the DNS server for the client that you ran that test on. Is it pointed directly at the Pi-hole, or is it pointed to the Router?

from web.

router ip 192.168.1.1

from web.

Do you have a desktop environment on the Pi-hole? Can you run the same test on the Pi-hole and see if comes back as a Validator? I think your router may be configured for DNSSEC validation.

from web.

i ran the test on the same machine pi-hole is installed ubuntu 16.04 lts

from web.

right now log is about 152 mb because i did not flush yet. is this what you looking for?

Aug 31 23:58:11 dnsmasq[2736]: query[A] log-rts14-iad01.devices.nest.com from 192.168.1.1
Aug 31 23:58:11 dnsmasq[2736]: forwarded log-rts14-iad01.devices.nest.com to 192.168.1.1
Aug 31 23:58:11 dnsmasq[2736]: query[A] log-rts14-iad01.devices.nest.com from 192.168.1.1
Aug 31 23:58:11 dnsmasq[2736]: forwarded log-rts14-iad01.devices.nest.com to 192.168.1.1

from web.

If you're running that on the same machine as the Pi-hole, it shouldn't be pointed to the router, the Ubuntu install should be using the dnsmasq process from the Pi-hole directly. What does your /etc/resolv.conf show as your resolver for the Ubuntu/Pi-hole?

from web.

You somehow have a loop, it's getting a query from 192.168.1.1 and sending a query back to 192.168.1.1 which then will loop back to itself again and spike your logs and traffic.

from web.

maybe i dont understand your question correctly

i am forcing my router to give out static dns of 192.168.1.131 just like picture couple posts up

from web.

/etc/resolv.conf

Dynamic resolv.conf(5) file for glibc resolver(3) generated by resolvconf(8)

DO NOT EDIT THIS FILE BY HAND -- YOUR CHANGES WILL BE OVERWRITTEN

nameserver 127.0.1.1

from web.

What OS is installed on 192.168.1.1, is that the Ubuntu/Pi-hole install? You screengrab of the admin panel shows that there have been no queries from 192.168.1.131. If you are browsing on the same machine that the Pi-hole is installed on, it should not be set to use the router for it's DNS.

from web.

And that should be 127.0.0.1, but that may not be an issue.

from web.

Can you do a fresh pihole -d so we can look and see how things are set up?

from web.

192.168.1.1 dd-wrt os my netgear r7000 router
192.168.1.131 pi-hole

and my teamviwer crashed so i cant connect to ubuntu box no more. i should be home in hour we can try then.

from web.

@dschaper i can give you access to the box because its only test box Ubuntu and pi-hole nothing else. you can check logs your self

from web.

If we can't get some headway, that may be an option. I have some appointments and meetings here in a bit that will take out most of the rest of my day, but we might be able to get something set up a bit later.

from web.

For my DD-WRT I had trouble setting pi-hole as the primary and only DNS so I ended up just plugging it into the dnsmasq config under the services like:

server=192.168.1.131
server=8.8.8.8

This also provides a nice fail over for when I take the pi-hole down. It hides what the actual client IPs in the query log because everything comes from the router but you could try that as an alternative configuration and see if it calms the logs down. You'd want to make absolutely sure your pihole is not pointed to your router for upstream sources though.

from web.

@diginc and what do i do with static dns? just 0.0.0.0?

from web.

Here is how I did it, it isn't pretty but it worked for me.

This config made DHCP give out ONLY my router as DNS and it masks the PI and one google server. The 3 DNS servers in DHCP don't seem to matter since dnsmasq is forced for DHCP.

And I confirmed my pi-hole is NOT pointed at my router either - preventing a DNS loop - could you run this along with a pihole -d ?

grep -r ^server /etc/dnsmasq.d/
/etc/dnsmasq.d/01-pihole.conf:server=8.8.8.8
/etc/dnsmasq.d/01-pihole.conf:server=8.8.4.4

I tried all sorts of combinations to (no avail) try and force DHCP to give out the primary DNS as the pi before i settled on this with a whimper... It maybe my router's dd-wrt version just stinks.

from web.

what do i set Query DNS in strict order enable or disable?
add requestor MAC to DNS Query enable or disable?

from web.

I don't seem to have those options. Router only supports up to a 2013 build of dd-wrt :(

Go for defaults, play with them if it doesn't seem to reduce log size increase speed.

from web.

i will try this out tomorrow

from web.

@dschaper let me know when you around maybe we can try something today

from web.

anything else i can try i set limit in php.ini to 256M admin panel dies when report is only 85mb

from web.

To continue from pi-hole/pi-hole#723...

You could keep a monitor in real time by running tail -f /var/log/pihole.log but that's probably not what you're looking for.

You want the same information as the query-log page?

There is nothing like that, but you'll find that parsing the logs on the command line to get the same kind of results will be just as resource intensive.

from web.

i just need something i can turn on to see what was blocked so i can whitelist if i need to

if i whitelist something is it per client or for everyone on the network?

from web.

In which case, your best bet is going to be cat /var/log/pihole.log | grep gravity to see all domains blocked today.

Whitelisting is for the whole network, or at least for whichever devices are set to use the Pi-hole as their DNS server.

from web.

@PromoFaux thx for help i will give it try

from web.

@xxcriticxx What size is your /var/log/pihole.log right now? A) is it massive? B) Would you mind uploading it somewhere for me to test out a possible fix for the ajax error?

from web.

on avg around 80 mb before it dies. i just flushed so i get you something in few hours

from web.

Thanks <3

from web.

what site will take file this big? pastebin?

from web.

Um, might be best to zip it up and dropbox it.

from web.

yes give me 5 min am flooding my dns :)

from web.

see when you need it to die it wont die 108k dns queries and still running strong

from web.

Ha! No rush!

from web.

see if this works if not i need your email address

<link removed> - Promofaux

from web.

Downloading fine. Have edited your post to remove the link, thanks!

from web.

from web.

OK, the good news is, the changes work, and all your data loads. The bad news is, it's still slow as hell, but I think that may be a hardware limitation. Possibly even to do with the SD card...

from web.

i have this on reg computer

from web.

OK.. you can be the first beta tester! run the following commands on your pi-hole device:

cd /var/www/html/admin
sudo git fetch
sudo git checkout dbIntegrate

cd /etc/.pihole
sudo git fetch
sudo git checkout db
./automated\ install/basic-install.sh

When you run the final command, choose the update option. Once it's done, give it a few mins to import your log into the database, and then check the web admin page again...

Also, probably worth noting, look at the difference in ads blocked between your screenshot and mine! By running a cut down adlist.list, look at how many ads your're missing!

from web.

i am also using ublock origin on my main pc rest goes to tv + iphone + ipads

from web.

One thing to note, also, the query log page is still crashing as there are just so many lookups in your log... working on that one next

from web.

http://pastebin.com/NVS0zXR1

i dont think it worked all still spinning on admin console

i also have this msg There's an update available for this Web Interface! do i update?

my version:
Pi-hole Version V2.9.1 Web Interface Version v1.4

from web.

Yeah, PHP isn't installed.. that's.. weird. Can you run pihole -d please? Something has gone wrong here.

No need to update, that's a false flag

from web.

Actually, looks like we also have another issue in there, the database is locked when the admin page is loading from it. Which is why you're getting those database locked errors.

from web.

Debug log can be found at : http://termbin.com/brm8

yes i think i installed lighttpd php7 because this ubuntu 16.04 LTS

from web.

There is a letter missing from that termbin link!

OK, that's odd, if you're on 16.0.4 it should have detected that and installed the package php not php5

If you want to revert at any time just:

cd /var/www/html/admin
sudo git fetch
sudo git checkout master

cd /etc/.pihole
sudo git fetch
sudo git checkout master
./automated\ install/basic-install.sh

from web.

i fixed debug log link

should i reboot pc?

from web.

can you run
apt-cache show php5 please?

from web.

critic@critic:~$ apt-cache show php5
N: Can't select versions from package 'php5' as it is purely virtual
N: No packages found

from web.

critic@critic:~$ whereis php
php: /usr/bin/php7.0 /usr/bin/php /usr/lib/php /etc/php /usr/share/php7.0-json /usr/share/php7.0-common /usr/share/php7.0-opcache /usr/share/php7.0-readline /usr/share/man/man1/php.1.gz

from web.

and apt-cache show php?

from web.

critic@critic:~$ apt-cache show php
Package: php
Priority: optional
Section: php
Installed-Size: 11
Maintainer: Ubuntu Developers [email protected]
Original-Maintainer: Debian PHP Maintainers [email protected]
Architecture: all
Source: php-defaults (35ubuntu6)
Version: 1:7.0+35ubuntu6
Depends: php7.0
Filename: pool/main/p/php-defaults/php_7.0+35ubuntu6_all.deb
Size: 2832
MD5sum: 31c1cc09b02d168095e42ffe3557c580
SHA1: ae10f4c6426c05829d2afbe68e0fb750364ab3e3
SHA256: cd20ffd6877a367a86514a87082d3e979fec8922474f9246cdc398df0807eded
Description-en: server-side, HTML-embedded scripting language (default)
PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used
open source general-purpose scripting language that is especially suited
for web development and can be embedded into HTML.
.
This package is a dependency package, which depends on Debian's default
PHP version (currently 7.0).
Description-md5: b955c03ceec2872c327e77278c943d6a
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Origin: Ubuntu
Supported: 5y

from web.

should i restart lighttpd?

from web.

It's not loading because the install didn't detect the correct version of php to download. namely php-sqlite

can you save this to a file (e.g test.sh):

apt-cache show php5 &> /dev/null
if [ $? == 0 ]; then
    echo "PHP5!"
else
    echo "PHP!"
fi

and then run it please?

from web.

i had same problem when i install pihole i had to install php7 in order admin panel run

from web.

Yeah, that's why I thought we'd put a fix in to catch that... (the above script)

install php-sqlite and reboot

from web.

critic@critic:~$ ./test.sh
PHP5!

from web.

yep, that should have output PHP!.... something broken here.. currently downloading a 16.04 image...

from web.

Checking for sqlite3... Not found! Installing.... done!

i will restart this computer

from web.

nope same after reboot

from web.

OK, working on a fix for this now, you may want to go back to the master branches

from web.

i wait for your next beta ver

from web.

Thanks, sorry for messing things up :)

from web.

i never worked correctly. you have TeamViewer i can give you access to the box for test

from web.

@xxcriticxx can you:
cd /etc/.pihole
git checkout db (if you're not already on that branch)
git pull
./automated\ install/basic-install.sh

ta

from web.

critic@critic:/etc/.pihole$ git checkout db
fatal: Unable to create '/etc/.pihole/.git/index.lock': Permission denied
critic@critic:/etc/.pihole$ git pull
error: cannot open .git/FETCH_HEAD: Permission denied

from web.

sudo

from web.