Basic SQL query question

Help Community Help
1

Hello,

I need to know every day a specific client queried a specific domain.

I know from a search in the UI that the client performed multiple queries every day that it queried the domain, but I only want one result per day. I need the output in a format I can put in a spreadsheet, so had been trying to find a good sqllite3 query but I'm struggling. Could someone please help?

Thank you!

PS: I've read the documentation at Redirecting... If you're strong on this stuff it would be awesome if you could please add some examples to that page please!

2

Try something like

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "Select domain, count(domain) from queries where client='IP' and domain='DOMAIN' and timestamp > strftime('%s','now','-1 days');"

Adjust the time/date modifier to your needs

3

To export to cvs adjust like shown here: Long Term Query Log -> Excel Pivot Chart - #7 by yubiuser

4

Thank you @yubiuser !

That gets me a count of the queries, but sorry I didn't ask my question clearly.

I need to know the date every time this DNS query was made. It will have occurred multiple times on many dates, but I can deduplicate those if needed.

Basically I need to know when I was working from home for tax purposes! I don't visit the specific site unless doing work.

6

Make a cron to run this query every day at 2359. If the result is not blank, you worked from home that day.

7

Thanks @jfb but I asked for a query as I need historic data

8

How about I explain the original question clearer.

Which SQL query could I use to return the date/time of every query for one specific domain please,

Thank you!

9

There is no field with formatted Date/Time output, but there is a timestamp field.

Just add the field to your query:

SELECT timestamp, domain, count(domain) FROM queries ...
10

Thank you @rdwebdesign!

I'll give that a try when I can in a few hours.

11

Thanks so much for your answers everyone!

I got what I needed with this query:

nice pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT timestamp, domain FROM queries where client='10.10.10.10' and domain='domain.i.am.looking.for.com' and timestamp > strftime('%s','now','-9999 days');"

...which gives me an output like

1660884263|domain.i.am.looking.for.com
1660894170|domain.i.am.looking.for.com
1660894480|domain.i.am.looking.for.com
1660895720|domain.i.am.looking.for.com

...and I can then use a simple shell script with date -d @<date string> to convert those in to time and date strings. I'll then dedupe them so I get a list of each day the domain was queried at least once.

12

Try

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "SELECT date(timestamp,'unixepoch', 'localtime'), domain FROM queries where client='IP' and domain='DOMAIN' and timestamp > strftime('%s','now',' -999 days') group by date(timestamp,'unixepoch','localtime');"

I think you could even remove the timestamp selection as it seems you try to just get "all".

13

Thanks, but I'm afraid that particular query gives me no results.

I've resolved this for now with the previous post, even if I had to do some manipulation of the output in a shell, it's all good thank you!

14

You can avoid a script with date if you change your SQL query to use the SQLite3 datetime() function or strftime() if you need a different format:

-- Old query:
SELECT timestamp, domain FROM queries WHERE ...

-- New query using datetime() function
SELECT datetime(timestamp,'unixepoch', 'localtime'), domain FROM queries WHERE ...
15

That's unexpected as I only formatted the timestamp output to be human-readable and grouped by date...