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 Query database - Pi-hole documentation 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
https://www.sqlite.org/lang_datefunc.html

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".

1 Like
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 ...
1 Like
15

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

21

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.