anybody has an idea of how i can query users that are logged_in for more than 8 hours?

Look at

logged_in_users

and do something with

time

the something with time is the problem. i dont even understand how to make the time readable

here is a starting point, the

datetime

function is useful!

select *, datetime(time, 'unixepoch', 'localtime') as logged_in_time from logged_in_users;

@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 11424 -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;

this is awesome. specially with all the comments for explanations. thank you so much

That's interesting, it works as expected on my device:

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.

Ah, it is likely due to the omission of the

'local_time'

modifier in my

datetime

argument.

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.