https://github.com/osquery/osquery logo
p

Priyanshu Jindal

04/29/2019, 12:14 PM
Does anybody have an idea on how to use where along with time Eg
Copy code
SELECT atime FROM file WHERE path LIKE '/usr/bin/%' WHERE atime > date('now', '-30 days');
Above will work only if atime is a sql datetime field, but it seems like it is not
p

Pat Haley

04/29/2019, 1:24 PM
atime is the epoch time stored as a bigint data type so you need to convert it to use it date functions like that. Try this:
Copy code
SELECT path, datetime(atime,'unixepoch') FROM file WHERE path LIKE '/usr/bin/%' AND  datetime(atime,'unixepoch') > date('now', '-30 days');
p

Priyanshu Jindal

04/29/2019, 1:26 PM
@Pat Haley thank you
p

Pat Haley

04/29/2019, 1:26 PM
you’re welcome
2 Views