Channels
  • l

    LM

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

    seph

    8 months ago
    Look at
    logged_in_users
    and do something with
    time
  • l

    LM

    8 months ago
    the something with time is the problem. i dont even understand how to make the time readable
  • b

    blaedj

    8 months ago
    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

    8 months ago
    @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

    8 months ago
    this is awesome. specially with all the comments for explanations. thank you so much
    it works really good except for the ORDER BY login_time ASC. it doesnt order it.
  • f

    fritz

    8 months ago
    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

    8 months ago
    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

    8 months ago
    Ah, it is likely due to the omission of the
    'local_time'
    modifier in my
    datetime
    argument.
    let's see...
    @User can you give me an example of your output?
    where the
    session_age_hours
    is longer?