https://github.com/osquery/osquery logo
Title
l

LM

09/28/2021, 1:40 PM
anybody has an idea of how i can query users that are logged_in for more than 8 hours?
s

seph

09/28/2021, 1:46 PM
Look at
logged_in_users
and do something with
time
l

LM

09/28/2021, 1:49 PM
the something with time is the problem. i dont even understand how to make the time readable
b

blaedj

09/28/2021, 2:05 PM
here is a starting point, the
datetime
function is useful!
select *, datetime(time, 'unixepoch', 'localtime') as logged_in_time from logged_in_users;
with sessions as (
  select *,
         (strftime('%s', 'now') -  time) as raw_elapsed_time
         from logged_in_users
  )
select type, user, (raw_elapsed_time / 3600) as hours_logged_in
  from sessions
         where hours_logged_in > 8 ;
f

fritz

09/28/2021, 2:22 PM
@LM From a blogpost I am working on: Most datetime values in osquery are stored in what is called unixepoch. This means that the value you see for a given item for example file.mtime (when a file was last modified) will be represented not as it’s standard calendar format (eg. 09/28/2021 1:14:24 -5 GMT), but instead as the number of seconds between the start of the epoch (January 1, 1970) and that time. Let's take a look at our current unix time by running the following query:
osquery> SELECT unix_time FROM time;
+------------+
| unix_time  |
+------------+
| 1632838244 |
+------------+
SQLite syntax allows you to perform various manipulations based on a known timestamp:
SELECT name, datetime(last_opened_time,'unixepoch') AS last_opened_at FROM apps WHERE last_opened_at >= '2021-02-24';
In the case of your
logged_in_users
example, you could create a
WHERE
condition based on hours:
SELECT 
-- Get all of the columns from logged_in_users
*, 
-- Convert time from unixepoch to datetime format
datetime(time,'unixepoch') AS login_time,
-- Calculate the difference in time between login_time and now in hours
ROUND(((JULIANDAY('now') - JULIANDAY(time,'unixepoch'))*60),1) AS session_age_hours
FROM logged_in_users
-- Scope to only sessions which are older than 8 hours 
WHERE datetime(time,'unixepoch') <= datetime('now','-8 hours') 
-- Order by oldest sessions
ORDER BY login_time ASC;
^^ slight correction: it’s its standard calendar format
l

LM

09/28/2021, 2:34 PM
this is awesome. specially with all the comments for explanations. thank you so much
🙌 1
it works really good except for the ORDER BY login_time ASC. it doesnt order it.
f

fritz

09/28/2021, 3:01 PM
That's interesting, it works as expected on my device:
What happens if you order by
session_age_hours
instead?
SELECT 
-- Get all of the columns from logged_in_users
*, 
-- Convert time from unixepoch to datetime format
datetime(time,'unixepoch') AS login_time,
-- Calculate the difference in time between login_time and now in hours
ROUND(((JULIANDAY('now') - JULIANDAY(time,'unixepoch'))*60),1) AS session_age_hours
FROM logged_in_users
-- Scope to only sessions which are older than 8 hours 
WHERE datetime(time,'unixepoch') <= datetime('now','-8 hours') 
-- Order by oldest sessions
ORDER BY session_age_hours DESC;
l

LM

09/28/2021, 3:31 PM
if you dont mind me asking. why is the session_age_hours longer than the time that has elapsed from the login time to the current time.
f

fritz

09/28/2021, 4:12 PM
Ah, it is likely due to the omission of the
'local_time'
modifier in my
datetime
argument.
let's see...
@LM can you give me an example of your output?
where the
session_age_hours
is longer?
l

LM

09/28/2021, 5:16 PM
i cannot provide an output. its on a classified airgap network. im also using fleet UI to do the osquery so maybe that has something to do with it? your query is exactly what i needed. im just trying to understand all the components of it so i can learn. thank you for all your help.