Showing results for 
Show  only  | Search instead for 
Did you mean: 
Follow the latest information and updates available on the FireEye and SolarWinds situations here.

Linux and macOS X login information

Description: Linux and macOS X login information


What the Data Shows: The last table in Osquery provides login and logout events for Linux and Mac OS X systems whilst providing the process id and the connecting host.

SQL: The following query joins the processes table to get the entry process name showing all sessions with a host identifier.

SELECT username,
                CASE type WHEN 0 THEN "EMPTY"
                    WHEN 1 THEN "RUN_LVL"
                    WHEN 2 THEN "BOOT_TIME"
                    WHEN 3 THEN "NEW_TIME"
                    WHEN 4 THEN "OLD_TIME"
                    WHEN 5 THEN "INIT_PROCESS"
                    WHEN 6 THEN "LOGIN_PROCESS"
                    WHEN 7 THEN "USER_PROCESS"
                    WHEN 8 THEN "DEAD_PROCESS"
                    WHEN 9 THEN "ACCOUNTING"
                END AS type,
       AS process,
                DATETIME(time, 'unixepoch') AS datetime,
FROM last AS l
LEFT JOIN processes AS p
    ON =
WHERE host <> ''
    AND host NOT LIKE ':pts%'

You can modify this query further to grab only external IP addresses.

AND host NOT IN (':1', '')


Carbon Black Employee
Status changed to: Under Review

Nice query @stympanick!

I would like to offer a suggestion that converts the type field to a human readable format. Would you consider modifying your query to:

SELECT username,
                CASE type WHEN 0 THEN "EMPTY"
                    WHEN 1 THEN "RUN_LVL"
                    WHEN 2 THEN "BOOT_TIME"
                    WHEN 3 THEN "NEW_TIME"
                    WHEN 4 THEN "OLD_TIME"
                    WHEN 5 THEN "INIT_PROCESS"
                    WHEN 6 THEN "LOGIN_PROCESS"
                    WHEN 7 THEN "USER_PROCESS"
                    WHEN 8 THEN "DEAD_PROCESS"
                    WHEN 9 THEN "ACCOUNTING"
                END AS type,
       AS process,
                DATETIME(time, 'unixepoch') AS datetime,
FROM last AS l
LEFT JOIN processes AS p
    ON =
WHERE host <> ''
    AND host NOT LIKE ':pts%'
Carbon Black Employee
Status changed to: Approved
New Contributor II

Hello @stympanick, I ran this query against our MacOS devices but it did not return any matches. Result = not_matched. Any idea why this would be the case?