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')
