The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

Linux and macOS X login information

Description: Linux and macOS X login information

Source: https://medium.com/@zercurity/building-atop-osquery-compliance-monitoring-threat-hunting-and-auditin...

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,
                p.name AS process,
                DATETIME(time, 'unixepoch') AS datetime,
                host
FROM last AS l
LEFT JOIN processes AS p
    ON p.pid = l.pid
WHERE host <> ''
    AND host NOT LIKE ':pts%'
ORDER BY time DESC;
 
 
 

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

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

 

3 Comments
jnelson
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,
                p.name AS process,
                DATETIME(time, 'unixepoch') AS datetime,
                host
FROM last AS l
LEFT JOIN processes AS p
    ON p.pid = l.pid
WHERE host <> ''
    AND host NOT LIKE ':pts%'
ORDER BY time DESC;
jnelson
Carbon Black Employee
Status changed to: Approved
 
da878t
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?