Top queries by client

Feature Requests Partially-implemented
1

At the dashboard I see some peaks in number of queries over the last 24 hours.
In the ' Client activity over last 24 hour' graph I see which client does those queries.

Sure I can query the db.
Would be nice if there was a view in the UI to see top queries by client.

2

For now:

grep 'query.*11.11.11.11' /var/log/pihole.log | awk '{print $6}' | sort | uniq -c | sort -n -r | head -10

*Replace 11.11.11.11 with actual client IP.

3 Likes
3

By popular demand :wink:

sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain FROM queries WHERE client='11.11.11.11' AND timestamp>='$(($(date +%s) - 86400))'" | sort | uniq -c | sort -n -r | head -10

*Last 24 hours this time

2 Likes
4

I've requested the same (graphic and table and in addition:

  1. Graphics of all queries over selected time period for a selected client (cumulated blocked/permitted)
  2. Graphics of a single selected domain over selected time period for a selected client
5

I knew Android is chatty:

pi@noads:~ $ sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain FROM queries WHERE client='10.0.0.105' AND timestamp>='$(($(date +%s) - 86400))'" | sort | uniq -c | sort -n -r | head -10
    216 ssl.google-analytics.com
    180 googleads.g.doubleclick.net
    153 app-measurement.com
     94 pagead2.googlesyndication.com
     89 www.googletagmanager.com
     82 www.googleadservices.com
     58 scomcluster.cxense.com
     55 graph.facebook.com
     54 services.meteored.com
     46 firebaseremoteconfig.googleapis.com

But didnt know the Ipad creates more noise:

pi@noads:~ $ sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain FROM queries WHERE client='10.0.0.70' AND timestamp>='$(($(date +%s) - 86400))'" | sort | uniq -c | sort -n -r | head -10
    902 app-measurement.com
    476 e.crashlytics.com
    260 reports.crashlytics.com
     69 www.google-analytics.com
     63 keyvalueservice.fe.apple-dns.net
     62 star.c10r.facebook.com
     61 p53-buy.itunes-apple.com.akadns.net
     55 s.youtube.com
     53 sb.scorecardresearch.com
     53 mesu-cdn.origin-apple.com.akadns.net
6

This solely depends on the apps that you are running. A busy iPad will have more DNS queries than an idle Android device.

7

I dont believe its solely.
Some queries are just scheduled similar as running it in cron.
Both devices are too fubar with apps to figure out/compare :wink:

But its got my vote :+1:

8

Below one seems to have a period of 30 seconds ish :wink:

pi@noads:~ $ sqlite3 /etc/pihole/pihole-FTL.db "SELECT domain,timestamp FROM queries WHERE client='10.0.0.70' AND timestamp>='$(($(date +%s) - 86400))' AND domain='app-measurement.com'" | tail -10
app-measurement.com|1581020708
app-measurement.com|1581020727
app-measurement.com|1581020743
app-measurement.com|1581020762
app-measurement.com|1581020788
app-measurement.com|1581020805
app-measurement.com|1581020822
app-measurement.com|1581020839
app-measurement.com|1581020866
app-measurement.com|1581020896
9

Just cross post here for those of you who are on beta v5.0 and want to try it out. Top list per client is implemented as well.