Title
#sql
z

Zach Zeid

05/15/2020, 5:32 PM
If I'm doing
select * from shell_history
on a schedule, does that read the whole
.bash_history
every time, or does it diff it in some way?
sundsta

sundsta

05/15/2020, 5:35 PM
It would be the whole thing unless you filter on the time column
z

Zach Zeid

05/15/2020, 5:39 PM
filter?
sundsta

sundsta

05/15/2020, 5:39 PM
Use a
WHERE
clause
z

Zach Zeid

05/15/2020, 5:41 PM
how would you be able to diff time in a sql statement?
sundsta

sundsta

05/15/2020, 5:46 PM
For example, to get the events for the past hour:
select * from shell_history where time > strftime('%s', 'now', '-1 hour');
5:46 PM
See here for SQLite’s date functions https://www.sqlite.org/lang_datefunc.html
z

Zach Zeid

05/15/2020, 5:53 PM
thank you, I'll look into this
5:39 PM
looks like this is a non-starter because
time
in
shell_history
is reliant on history timestamps enabled
osquery> select * from users cross join shell_history on shell_history.uid = users.uid where time > strftime('%s', 'now', '-1 minutes');
doesn't return any results (when there should be.
sundsta

sundsta

05/20/2020, 5:55 PM
Didn’t realize bash didn’t include a timestamp by default. Too used to zsh I guess 🙂
z

Zach Zeid

05/20/2020, 6:27 PM
yeah, I turned it on in my bash shell, so it seemingly only works a little bit.