# Top queries by client

**URL:** <https://discourse.pi-hole.net/t/top-queries-by-client/27970>\
**Category:** Partially-implemented\
**Created:** [February 6, 2020, 3:04pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970 "2020-02-06T15:04:24Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Arno](https://discourse-cdn.pi-hole.net/letter_avatar_proxy/v4/letter/a/a5b964/32.png) [@Arno](https://discourse.pi-hole.net/u/Arno)\
**Post date:** [February 6, 2020, 3:04pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/1 "2020-02-06T15:04:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 6, 2020, 4:29pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/2 "2020-02-06T16:29:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 6, 2020, 6:04pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/3 "2020-02-06T18:04:36Z")

</div>

By popular demand 😉

`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

---

<div class="post-metadata">

**Author:** ![yubiuser](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/yubiuser/32/12100_2.png) [@yubiuser](https://discourse.pi-hole.net/u/yubiuser)\
**Post date:** [February 6, 2020, 7:29pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/4 "2020-02-06T19:29:04Z")

</div>

I've [requested the same](https://discourse.pi-hole.net/t/add-more-statistics-on-per-client-basis/27640) (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

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 6, 2020, 9:04pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/5 "2020-02-06T21:04:06Z")

</div>

I knew Android is chatty:

```auto
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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![jfb](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jfb/32/4332_2.png) [@jfb](https://discourse.pi-hole.net/u/jfb)\
**Post date:** [February 6, 2020, 9:22pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/6 "2020-02-06T21:22:15Z")

</div>

> [@deHakkelaar](#):
>
> didnt know the Ipad creates more noise:

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

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 6, 2020, 9:27pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/7 "2020-02-06T21:27:21Z")

</div>

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 😉

But its got my vote 👍

---

<div class="post-metadata">

**Author:** ![deHakkelaar](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dehakkelaar/32/674_2.png) [@deHakkelaar](https://discourse.pi-hole.net/u/deHakkelaar)\
**Post date:** [February 6, 2020, 9:40pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/8 "2020-02-06T21:40:48Z")

</div>

Below one seems to have a period of 30 seconds ish 😉

```auto
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

```

---

<div class="post-metadata">

**Author:** ![yubiuser](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/yubiuser/32/12100_2.png) [@yubiuser](https://discourse.pi-hole.net/u/yubiuser)\
**Post date:** [April 11, 2020, 8:22pm UTC](https://discourse.pi-hole.net/t/top-queries-by-client/27970/9 "2020-04-11T20:22:13Z")

</div>

Just cross post here for those of you who are on beta v5.0 and [want to try it out](https://discourse.pi-hole.net/t/add-more-statistics-on-per-client-basis/27640/3). Top list per client is implemented as well.