Browse Live Discover and Response Queries by Category
Under Review

Live Discover tip - how to select time limits in a human readable format - use STRFTIME

 REVIEWED by Sophos 

I've been dumping some tables recently to see what kind of data is in them, and noticed that many of the default queries limit the results to the last 15 minutes of data if a time constraint is not specified (and fair enough, given the potential size of reply)

In the Sophos Journals schema (https://community.sophos.com/products/intercept/early-access-program/m/files/9491) the examples given use 'time' in epoch-seconds.

eg:

SELECT
*
FROM
sophos_url_journal
WHERE
time > 1587146418

 

But as much fun as using https://www.epochconverter.com/ is, there is a better way; use STRFTIME to parse the time column (at least in windows queries).

A much easier to modify query (and example) alternative of the above would be:

SELECT
*
FROM
sophos_url_journal
where time > STRFTIME('%s','NOW','-30 minutes')

And you can use 'days', 'hours', and 'seconds' as well.

happy hunting!

AK
Parents
  • On a similar note, if you want times returned in responses in an easy readable format then use the datetime function, for example: SELECT    datetime(time, 'unixepoch', 'localtime'),    url FROM sophos_url_journal WHERE time > STRFTIME('%s','NOW','-30 minutes') In this example the returned field "time" will be in a easy readable format. 
Comment
  • On a similar note, if you want times returned in responses in an easy readable format then use the datetime function, for example: SELECT    datetime(time, 'unixepoch', 'localtime'),    url FROM sophos_url_journal WHERE time > STRFTIME('%s','NOW','-30 minutes') In this example the returned field "time" will be in a easy readable format. 
Children
No Data
Unfiltered HTML