Long term data / query log

My DNS domain is 'localdomain', because it's defined as such on pfsense and in the hosts file of the raspberry pi
´´´
127.0.1.1 raspberrypi.localdomain raspberrypi
´´´
result on the pi:

pi@raspberrypi:~ $ dnsdomainname
localdomain

result on a workstation (ipconfig /all):

Ethernet adapter Ethernet:

   Connection-specific DNS Suffix  . : localdomain

My hosts file (NOT using custom.list, because it doesn't allow multiple name entries on one line) looks like this (partial):

192.168.2.166	slate7-2.localdomain	slate7-2
192.168.2.227	hp7900.localdomain	hp7900
192.168.2.228	y50.localdomain		y50

Web interface, 'Long term data', 'query log'
Enter 'localdomain' in the search field, select 'today' as 'date and time range'

Apparently, don't really know if this is a v5 specific problem (may occur in v4), the result of the query shows all entries that match the search string 'localdomain' in both the domain and the client column. It is possible to find the entries that match 'localdomain' in the domain column, however, it's showing a lot of entries that only match the client name.

An SQL query would indicate the desired result would only list 29 entries, as opposed to the approximately 1250 (125 pages, 10 entries per page) entries listed in the web interface.

I don't know if the result the web interface shows, intentionally (developers choice?) includes the search string (localdomain) and matches it to both the domain and the client column. The result in the web interface however, doesn't really provide useful information.

Yes, this is an intentional decision, all (meaningful) columns are searched by Pi-hole. How should we have realized it otherwise? Having multiple search boxes or check boxes what/where to search would clutter the interface.
I think this is a very special situation and, more typically, users are searching for things like 192.168.2.10 or google.com where the result will be clear.

What about users with their devices in a (AD) domain. All their devices would have a name like 'device.company.com', a search would result in identical results as mine.
No worries, I'll learn to live with the limitation...

You misunderstood me, I was not asking for more examples, I can see why this may be a limitation, I am interested in ideas for improvement. If we can find something nice and functionational that is not too much work implementing, great!

1 Like

I've had a little play about, though not overly enamoured with the result. Needs a bit of tweaking...

But of course it is certainly possible

1 Like

You may need to limit the fields where you can search, I don't think searching in the time or status fields would work as they are numbers internally (timestamps / enum codes). The rendering to nice text happens on top of the raw that that would be searched in IIRC.

For sure. Just dipping my toes in for now.. :slight_smile:

I created a feature request which would cover this situation.
Unlike the idea of @PromoFaux where you add extra 'search fields' for each column change the headers of each column into a 'filter box'. I got the inspiration from Excel where you can select the head of a table and can select 'Auto filters' which provides a way to select for which items in this particular column you want to filter for.

Looks great, this will greatly enhance long term search. Stupid question, probably, does this imply you can combine search parameters, e.g. all 'A' records' for 'visual' by 'localhost'?

I don't expect a positive answer here, as this would be to much to ask for.

I think you need to remove the initial search field (above the page numbers in the screen shot), that is, if I understand what I see....

Great solution, it proves that it never hurts to ask, sometimes it even leads to a great improvement.

THANKS!

Isn't the time already covered by 'select date and time range' (NOT in the screen shot)?

Whilst this would be nice, I think it would be overly complex to implement.

I'll play further with this as I get time. I'm a big proponent of the KISS principle, I don't want to add any unnecessary complexity to the code.

2 Likes

Ah, I wasn't on the long term query log, rather the normal one. But yes, same principle applies

I wasn't expecting additional features on the regular query log, that would be overkill. I would always use the long term query (or SQL) to search for data patterns that I want to investigate (or eliminate).

Sure, but do remember you are not the only person that uses Pi-hole. We need to cover more than just one person's use case.

I'm all about consistency. Why implement it on one table, and not the other, when they are both the same thing at the base of it all?

1 Like

Don't want to argue here, I'm already gratefull you're trying to accommodate this request.
I just think the normal query log is 'loaded" enough, it already has the CNAME information and the regex link. As far as I know, that information is NOT available in the long term query log (and I don't expect it to be there).

Anyway, do as you please, I will be happy, if I don't have to use SQL anymore to find patterns I don't like (like what is going on between 1 and 3 AM very bad example, already possible).

If you have a test branch, I'll be happy to test it with my live data, I've been running beta5 as my primary pihole for over 4 weeks now, it got better along the way, nearing completion, as there are hardly any beta5 topics opened anymore.

In time. I'm just playing about with things at the moment, and as such have not actually committed anything anywhere. I don't foresee myself having a great deal of time to spend on this this week, but we shall see what happens. Sometimes inspiration hits and before you know it...

I'll update once I have something more solid.

Thanks for considering playing with it.
I added 3 code examples I found online to the feature request.

Maybe something nice for pihole v6.0 Web UI :wink: