Recent query log does not show data of some top blocked domains. Hit number not limited to 24 hours.

[shizunge]

In raising this issue, I confirm the following: {please fill the checkboxes, e.g: [X]}

• I have read and understood the contributors guide.
• The issue I am reporting can be replicated.
• The issue I am reporting isn't a duplicate (see FAQs, closed issues, and open issues).

How familiar are you with the the source code relevant to this issue?:

1

---

Expected behaviour:

1. Click one of sites in "top blocked domains" in the dashboard, goes to the resent queries page. The page should show which client sent the queries to this site.
2. The "top blocked domains" in the dashboard, should show the number of hits within 24 hours, not an accumulated number of multiple days.

Actual behaviour:

settingsfd-geo.trafficmanager.net is one of my top blocked domain.

I want to see which client sent the queries.
However the recent queries shows no information about settingsfd-geo.trafficmanager.net

Moreover the "hits" of settingsfd-geo.trafficmanager.net accumulated, instead of showing the hits within 24 hours. I saw the number kept increasing, never decreased.

This problem occured not only to this domain, my second top blocked domain "rs.fullstory.com" had the same problem.

Another interesting found, I believe it is related, the settings.data.microsoft.com seems blocked settingsfd-geo.trafficmanager.net.
(I cannot find which domain blocked rs.fullstory.com, it is not in the top blocked domain list)

Steps to reproduce:

I think if you add both settingsfd-geo.trafficmanager.net and settings.data.microsoft.com to the block list and send enough queries to settings.data.microsoft.com to make it one of the top blocked domains, you could reproduce the problem.

Repeat above step multiple days to observe that the hits of settingsfd-geo.trafficmanager.net accumulated.

Debug token provided by uploading pihole -d log:

https://tricorder.pi-hole.net/zmhfg3it0a

Troubleshooting undertaken, and/or other relevant information:

N/A

[yubiuser]

The problem might be that settings.data.microsoft.com is CNAME for settingsfd-geo.trafficmanager.net. Latter is blocked due to the deep CNAME inspection. While the dashboard toplist will show settingsfd-geo.trafficmanager.net, it seems that FTL doesn't save/returns the information when queried directly for the final blocked domain.
I know this information is not stored in the long-term database (only settings.data.microsoft.com with status "blocked (CNAME)" is stored) but it might be worth adding this information to FTL's memory (usually stored for the last 24h) as it is confusing for users to see a domain on the toplist and not be able to see which client did the request.

[Bugbavka]

Can confirm the same behaviour on CNAME blocked domains.
They are also not counted towards the number of all blocked domain.

Restarting DNS will purge them from the "Top Domains" list.

[pralor-bot]

This issue has been mentioned on Pi-hole Userspace. There might be relevant details there:

https://discourse.pi-hole.net/t/display-blocked-deep-cname-domains-in-pi-hole-tail-query/32297/40

[DL6ER]

This should be fixed by #832

@shizunge Please try whether

pihole checkout ftl new/cname_inspection_logging

fixes the issue you observed.

[DL6ER]

This should be fixed by the release of Pi-hole FTL 5.2. Feel free to re-open of create a new ticket with new information if the issue persists.

[yubiuser]

Please reopen, because the issue persists on 5.2.

  Pi-hole version is new/adlist_date_updated v5.0-119-gdcafdc2 (Latest: v5.1.2)
  AdminLTE version is devel v5.1.1-69-g3384690b (Latest: v5.1.1)
  FTL version is development vDev-95608cf (Latest: v5.2)

nanopi@nanopi:~$ sqlite3 /etc/pihole/pihole-FTL.db "Select * from queries where additional_info is 'aax-eu-retail-direct.amazon-adsystem.com';"
952192|1596229786|1|9|aax-eu.amazon.de|10.0.10.84||aax-eu-retail-direct.amazon-adsystem.com
960102|1596283861|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
960104|1596283863|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
960105|1596283863|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
991054|1596449947|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
991055|1596449948|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
991056|1596449948|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
991062|1596449953|1|9|aax-eu.amazon.de|10.0.10.136||aax-eu-retail-direct.amazon-adsystem.com
[...]

[DL6ER]

@yubiuser This is another issue, maybe something I have overlooked when reading this discussion again, please check out

pihole checkout ftl fix/getAllQueries_CNAME_filtering

for testing and use #878 for comments.

[pralor-bot]

This issue has been mentioned on Pi-hole Userspace. There might be relevant details there:

https://discourse.pi-hole.net/t/top-blocked-domain-not-showing-who-was-blocked/39217/21