This is a good idea, however, I immediately have two objections, maybe you can convince me that they are minor. This is not at all to play down your idea.
- Performance
The new field has no index on it. That mean that such a scan means reading the entire table from disk in a full-text search. This can easily take minutes. You may argue that we can add an index on the newly added column, however, this is also not an optimal idea as there is no way the database knows that the column is "new" (as in all rows have aNULLcolumn) so at least the index creation would again ask for an entire full-text database operation. - Reliability
Such a lookup will only show things which happened in the past. Queries which would be blocked by CNAME inspection but we haven't seen them so far would not be shown.
I prepared a new branch new/cname_inspection_logging for this feature request.
The CNAME details are now, in addition to the already existing display in the Query Log:
also stored in the database:
sqlite> SELECT * FROM queries WHERE additional_info NOT NULL;
id |timestamp |type |status |domain |client |forward |additional_info
8747483|1594668331|1 |11 |fonts.gstatic.com|192.168.2.223| |gstaticadssl.l.google.com
and also in pihole.log:
Jul 13 21:25:31 dnsmasq[23773]: query[A] fonts.gstatic.com from 192.168.2.223
Jul 13 21:25:31 dnsmasq[23773]: forwarded fonts.gstatic.com to 127.0.0.1
Jul 13 21:25:31 dnsmasq[23773]: reply fonts.gstatic.com is <CNAME>
Jul 13 21:25:31 dnsmasq[23773]: reply gstaticadssl.l.google.com is blocked during CNAME inspection
What is not done so far is restoring CNAME information from the database into Pi-hole's memory after an FTL restart. I will have to think a bit about how to do this (it may be that the domain initially causing the block is not known to FTL during history parsing because it was seen more than 24 hours ago).