February 03, 2022

Leveling Up with osquery for Workloads: Identifying and Contextualizing Windows Logon Failures - Part 1

This blog series is intended for readers that have a basic understanding of SQLite and have an osquery test environment. If neither of these things are true for you, please take a moment to read the Audit and Remediation Best Practices Guide before reading the rest of the blog. 

Audit and Remediation provides direct access to osquery functionality within the VMware Carbon Black Cloud console to enable security, compliance, and IT teams to query over 2,000 individual attributes across endpoints and workloads. osquery can help teams with gathering information at scale across environments for IT and help desk operations, compliance and M&A reporting, incident response, and security investigations. 

Audit and Remediation allows administrators to ask questions about the environment across hardware, software, and network variables at scale. VMware Carbon Black Cloud Workload customers have access to the full Audit and Remediation capabilities. In this blog series, we’ll be laying out relevant queries for VMware Carbon Black Cloud Workload customers to use to achieve a variety of use cases. All queries are available in the VMware Carbon Black Cloud console, or in the VMware Carbon Black User Exchange. 

Requirements

This blog series is intended for readers that have a basic understanding of SQLite and have an osquery test environment. If neither of these things are true for you, please take a moment to read the Audit and Remediation Best Practices Guide before reading the rest of the blog. 

Resources 

System Logon Events (Mac/Linux) 

System logon events can assist help desk analysts, security teams, system administrators, and auditors gather data at scale for a multitude of use cases. Help desk analysts or system administrators may want to know who is currently logged in before they suggest a reboot when troubleshooting an issue. Security teams, auditors, and system administrators need to know who is logging in to their systems - and from where. In this blog, we will start from a very basic query and then add to it to get the exact details these teams will need to meet their use cases.  

In this blog, we’ll walk through the queries and modifications that are needed to deliver the most relevant context for a specific team or use case. 

In looking for login and logout events in the Mac and Linux operating systems, admins will typically use the last command. This command produces output like: 

jnelson  pts/2        172.16.116.1     Thu Nov  4 10:49   still logged in 
jnelson  :0           :0               Thu Nov  4 10:43   still logged in 
reboot   system boot  5.4.0-73-generic Thu Nov  4 10:42   still running 
reboot   system boot  5.4.0-73-generic Thu Oct 21 17:04 - 17:44  (00:39) 
jnelson  :0           :0               Thu Oct 21 17:00 - down   (00:03) 
reboot   system boot  5.4.0-73-generic Thu Oct 21 17:00 - 17:03  (00:03) 
reboot   system boot  5.4.0-73-generic Thu Oct 21 16:56 - 16:58  (00:02) 
jnelson  :0           :0               Wed Feb  3 09:16 - down  (98+03:12) 
reboot   system boot  5.4.0-65-generic Tue Feb  2 15:46 - 13:28 (98+20:42) 
jnelson  :0           :0               Tue Feb  2 14:58 - down   (00:47) 
reboot   system boot  5.4.0-65-generic Tue Feb  2 14:57 - 15:46  (00:48) 
jnelson  :0           :0               Tue Feb  2 14:53 - down   (00:04) 
reboot   system boot  5.4.0-65-generic Tue Feb  2 14:52 - 14:57  (00:05) 
jnelson  :0           :0               Tue Feb  2 14:37 - down   (00:12) 
reboot   system boot  5.4.0-65-generic Tue Feb  2 14:36 - 14:50  (00:14) 
jnelson  pts/0        172.16.194.1     Tue Feb  2 09:59 - 14:35  (04:36) 
jnelson  tty1                          Tue Feb  2 09:58 - 09:59  (00:01) 
reboot   system boot  5.4.0-65-generic Tue Feb  2 09:57 - 14:36  (04:38) 


wtmp begins Tue Feb  2 09:57:27 2021

osqueryi will display some similar information when the last table is queried: 

osquery> select * from last; 
+----------+-------+--------+------+------------+--------------+ 
| username | tty   | pid    | type | time       | host         | 
+----------+-------+--------+------+------------+--------------+ 
| jnelson  | tty1  | 1010   | 7    | 1612288689 |              | 
| jnelson  | pts/0 | 3713   | 7    | 1612288769 | 172.16.194.1 | 
|          | tty1  | 1010   | 8    | 1612288779 |              | 
|          | pts/0 | 3713   | 8    | 1612305330 |              | 
| jnelson  | :0    | 5007   | 7    | 1612305454 | :0           | 
| jnelson  | :0    | 4422   | 7    | 1612306385 | :0           | 
| jnelson  | :0    | 5440   | 7    | 1612306724 | :0           | 
| jnelson  | :0    | 141383 | 7    | 1612372607 | :0           | 
| jnelson  | :0    | 2690   | 7    | 1634860849 | :0           | 
| jnelson  | :0    | 4880   | 7    | 1636047815 | :0           | 
| jnelson  | pts/2 | 16506  | 7    | 1636048195 | 172.16.116.1 | 
+----------+-------+--------+------+------------+--------------+

There are some rows missing in the above data such as the booting events and the duration of the connections (Note: the current uptime for the system can be obtained by querying the uptime table). There is also some additional information such as the pid and process status type. 

For the tty column, the following should be noted: 

  • tty# - this is virtual terminal and is present when a user opens an app like the terminal 

  • pts/# - This value is present when a user has logged in over the network (e.g. a ssh connection) 

  • :# - This is the display the user is connected to and indicates that they are using a GUI 

While this is great information, a lot of conversion needs to take place to fully understand what the data is showing. Luckily osqueryi is very good at making such conversions!  

First, we will convert the date (represented in Unix epoch format: the number of seconds from January 1, 1970) with the SQLite datetime() function, which is pretty trivial to do. 

osquery> select username, 
    ...>        tty, 
    ...>        pid, 
    ...>        type, 
    ...>        datetime(time,'unixepoch','localtime') as time, 
    ...>        host  
    ...> from last; 
+----------+-------+--------+------+---------------------+--------------+ 
| username | tty   | pid    | type | time                | host         | 
+----------+-------+--------+------+---------------------+--------------+ 
| jnelson  | tty1  | 1010   | 7    | 2021-02-02 09:58:09 |              | 
| jnelson  | pts/0 | 3713   | 7    | 2021-02-02 09:59:29 | 172.16.194.1 | 
|          | tty1  | 1010   | 8    | 2021-02-02 09:59:39 |              | 
|          | pts/0 | 3713   | 8    | 2021-02-02 14:35:30 |              | 
| jnelson  | :0    | 5007   | 7    | 2021-02-02 14:37:34 | :0           | 
| jnelson  | :0    | 4422   | 7    | 2021-02-02 14:53:05 | :0           | 
| jnelson  | :0    | 5440   | 7    | 2021-02-02 14:58:44 | :0           | 
| jnelson  | :0    | 141383 | 7    | 2021-02-03 09:16:47 | :0           | 
| jnelson  | :0    | 2690   | 7    | 2021-10-21 17:00:49 | :0           | 
| jnelson  | :0    | 4880   | 7    | 2021-11-04 10:43:35 | :0           | 
| jnelson  | pts/2 | 16506  | 7    | 2021-11-04 10:49:55 | 172.16.116.1 | 
+----------+-------+--------+------+---------------------+--------------+

Next, we have type which can be converted using the data on the top of the utmp man page, which has been reformatted here: 

UT_UNKNOWN            0 
RUN_LVL               1 
BOOT_TIME             2 
NEW_TIME              3 
OLD_TIME              4 
INIT_PROCESS          5 
LOGIN_PROCESS         6 
USER_PROCESS          7 
DEAD_PROCESS          8 
ACCOUNTING            9

By using a case statement, we can make this conversion on the fly: 

osquery> select username, 
    ...>        tty, 
    ...>        pid, 
    ...>        type, 
    ...>        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_text, 
    ...>        datetime(time,'unixepoch','localtime') as time, 
    ...>        host  
    ...> from last; 
+----------+-------+--------+------+--------------+---------------------+--------------+ 
| username | tty   | pid    | type | type_text    | time                | host         | 
+----------+-------+--------+------+--------------+---------------------+--------------+ 
| jnelson  | tty1  | 1010   | 7    | USER_PROCESS | 2021-02-02 09:58:09 |              | 
| jnelson  | pts/0 | 3713   | 7    | USER_PROCESS | 2021-02-02 09:59:29 | 172.16.194.1 | 
|          | tty1  | 1010   | 8    | DEAD_PROCESS | 2021-02-02 09:59:39 |              | 
|          | pts/0 | 3713   | 8    | DEAD_PROCESS | 2021-02-02 14:35:30 |              | 
| jnelson  | :0    | 5007   | 7    | USER_PROCESS | 2021-02-02 14:37:34 | :0           | 
| jnelson  | :0    | 4422   | 7    | USER_PROCESS | 2021-02-02 14:53:05 | :0           | 
| jnelson  | :0    | 5440   | 7    | USER_PROCESS | 2021-02-02 14:58:44 | :0           | 
| jnelson  | :0    | 141383 | 7    | USER_PROCESS | 2021-02-03 09:16:47 | :0           | 
| jnelson  | :0    | 2690   | 7    | USER_PROCESS | 2021-10-21 17:00:49 | :0           | 
| jnelson  | :0    | 4880   | 7    | USER_PROCESS | 2021-11-04 10:43:35 | :0           |
| jnelson  | pts/2 | 16506  | 7    | USER_PROCESS | 2021-11-04 10:49:55 | 172.16.116.1 | 
+----------+-------+--------+------+--------------+---------------------+--------------+

This amount of detail can be helpful to auditors and security teams when trying to identify all the users that have logged into a system and when they did. However, we can refine this dataset further to add more context to active connections.  

The next conversion will be the pid, but there is a caveat with this one. As can be seen in the data above, there are some really old logon events that are no longer active. pids can also be reused, so if there is a currently running process with the same pid then it will be reported as the process associated with that logon event, which it is not. 

To convert the pid a JOIN will be used on the processes table. Here is the resultant query: 

osquery> select username, 
    ...>        tty, 
    ...>        pid, 
    ...>        name, 
    ...>        type, 
    ...>        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_text, 
    ...>        datetime(time,'unixepoch','localtime') as time, 
    ...>        host  
    ...> from last 
    ...> join processes using(pid); 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+ 
| username | tty   | pid   | name          | type | type_text    | time                | host         | 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+ 
| jnelson  | tty1  | 1010  | bluetoothd    | 7    | USER_PROCESS | 2021-02-02 09:58:09 |              | 
|          | tty1  | 1010  | bluetoothd    | 8    | DEAD_PROCESS | 2021-02-02 09:59:39 |              | 
| jnelson  | :0    | 4880  | gdm-x-session | 7    | USER_PROCESS | 2021-11-04 10:43:35 | :0           | 
| jnelson  | pts/2 | 16506 | sshd          | 7    | USER_PROCESS | 2021-11-04 10:49:55 | 172.16.116.1 | 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+

Using this JOIN reduces the number of results from 11 to four. This change is due to the PID for the missing lines no longer existing in the list of running processes. The first two lines are due to the PID having been reused. 

In the last two lines, we can see that the user is in fact using a GUI by the process name (gdm-x-session), and that someone has logged on as that user over SSH. 

Now let’s get rid of the lines that are using PIDs that have been recycled. We can do this by comparing the time column from the last table and the start_time from the processes table. If the time column is less than the start_time column then the connection happened during the life of the process. Adding a simple WHERE clause will handle this comparison: 

osquery> select username, 
    ...>        tty, 
    ...>        pid, 
    ...>        name, 
    ...>        type, 
    ...>        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_text, 
    ...>        datetime(time,'unixepoch','localtime') as time, 
    ...>        host  
    ...> from last 
    ...> join processes using(pid) 
    ...> where time < start_time; 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+ 
| username | tty   | pid   | name          | type | type_text    | time                | host         | 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+ 
| jnelson  | :0    | 4880  | gdm-x-session | 7    | USER_PROCESS | 2021-11-04 10:43:35 | :0           | 
| jnelson  | pts/2 | 16506 | sshd          | 7    | USER_PROCESS | 2021-11-04 10:49:55 | 172.16.116.1 | 
+----------+-------+-------+---------------+------+--------------+---------------------+--------------+

This query (minus the time constraint) is available as a recommended query in the Live Query tab in the VMware Carbon Black Cloud console. 

In this blog post we have shown the power of osquery to not only look at system logon events but to add context to them from other tables. Help desk analysts, security teams, system administrators, and auditors can then use the queries in this post to gain a better understanding of these event types in their Linux and Mac environments. 

To learn more about how Audit and Remediation capabilities can help you gather data at scale across your environments, please visit: 

  1. Carbon Black Cloud Workload TestDrive 

  1. VMware Carbon Black Tech Zone: Mastering Audit & Remediation 

  1. Query Exchange (Carbon Black User Exchange) 

Filter Tags

Carbon Black Cloud Audit and Remediation Blog Linux macOS

Jon Nelson

Read More from the Author

Jon is a seasoned computer security professional with over 25 years of experience. He has worked for VMware Carbon Black for 5 years in New York City and the San Francisco Bay Area focusing on large enterprise accounts and security strategy as a Principal Solution Engineer. Jon had come from RSA where he was a Senior Security Systems Engineer in a pre-sales role whose clients included the top tier financials and pharmaceuticals in the New York Metro area. Prior to RSA Jon worked at the Defense Cyber Investigations Training Academy in Linthicum, MD where he taught computer forensics, network investigations, and malware analysis. Before that, Jon was a Corporal in the Pennsylvania State Police where he conducted proactive and reactive computer crime investigations for over 13 years before retiring in 2010.