Process monitoring with Osquery

Following on from our blog post on What is Osquery we thought it’d be helpful to provide some more examples and common use cases for Osquery’s most common table schemas.

Osquery command line showing several processes related queries.

Before we start

The examples below will make use of the osqueryi command. Which can be accessed via your terminal. For more information check our blog on What is Osquery to get it installed. Or you can use our virtual terminal over at www.zercurity.com/osquery which will let you run all the examples below against a snapshot.

Zercurity | Osquery

Let us begin!

The processes table

Of Osquery’s 250+ “virtual tables” the processes table provides a virtual interface into your systems currently running processes.

It’s important to note that the processes table only provides a point in time snapshot of the processes currently running on the system. It doesn't provide historical information about all the processes that have run since the system booted. For this, Osquery provides the process_events table which we’ll touch on later.

Please use the commandosqueryi . To enter the interactive Osquery terminal.

SELECT pid, name FROM processes ORDER BY start_time DESC LIMIT 5;

The command above will retrieve the first 5 most recently executed processes, returning just the process id (pid), user id (uid) and the process name (name).

+-------+-----+----------------------+
| pid   | uid | name                 |
+-------+-----+----------------------+
| 28508 | 501 | syncdefaultsd        |
| 28501 | 501 | Google Chrome Helper |
| 28476 | 501 | Google Chrome Helper |
| 28470 | 501 | osqueryd             |
| 28458 | 501 | Google Chrome Helper |
+-------+-----+----------------------+

Processes with usernames

As Osquery provides an SQLite interface you can also join additional tables to enrich results. For example, we can fetch the username, instead of the uid for a given process.

SELECT pid, username, name FROM processes p JOIN users u ON u.uid = p.uid ORDER BY start_time DESC LIMIT 5;

This will yield:

+-------+-----------+----------------------+
| pid   | username  | name                 |
+-------+-----------+----------------------+
| 28527 | hugh      | Google Chrome Helper |
| 28526 | hugh      | Google Chrome Helper |
| 28512 | hugh      | syncdefaultsd        |
| 28476 | hugh      | Google Chrome Helper |
| 28470 | hugh      | osqueryd             |
+-------+-----------+----------------------+

Most CPU intensive processes since boot

You can also figure out what processes are eating away at your valuable CPU time. Osquery won’t be able to give you a percentage for a given moment in time as in order to do this you’d have to continually poll the processes table and compare the last snapshot to the most recent snapshot. In order to see what process was busy in the previous period. However, using the query below you can ascertain which processes have consumed the most amount of CPU time. To work this out as a percentage we can actually query the cpu_time table.

SELECT (SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb, SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb FROM cpu_time;

This query will yield tsb (Time since the system booted) and itsb (Idle time since boot). These two values are important as the Osquery process table provides both the user_time and system_time columns which we can add together and divide by the tsb minus the itsb to work out the process’s usage as a percentage.

SELECT pid, uid, name, ROUND((
  (user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)
) * 100, 2) AS percentage
FROM processes, (
SELECT (
  SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb,
  SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
  FROM cpu_time
) AS cpu_time
ORDER BY user_time+system_time DESC
LIMIT 5;

Which will yield:

+-------+-----+----------------------+------------+
| pid   | uid | name                 | percentage |
+-------+-----+----------------------+------------+
| 1198  | 501 | com.docker.hyperkit  | 76.96      |
| 15376 | 501 | Google Chrome        | 29.2       |
| 7799  | 501 | Steam Helper         | 27.73      |
| 1034  | 501 | Atom Helper          | 23.68      |
| 15383 | 501 | Google Chrome Helper | 17.67      |
+-------+-----+----------------------+------------+

Most memory intensive processes

Or perhaps you need to know which processes are eating up the most amount of memory on your system. The following query will return the process id (pid), process name (name) and the amount of memory currently allocated in MB (used). You can replace 10e-7 with 10e-10 to get GB rather than MB.

SELECT pid, name, ROUND((total_size * '10e-7'), 2) AS used FROM processes ORDER BY total_size DESC LIMIT 5;

Which will yield:

+-------+----------------------+---------+
| pid   | name                 | used    |
+-------+----------------------+---------+
| 1198  | com.docker.hyperkit  | 4303.76 |
| 7799  | Steam Helper         | 1281.14 |
| 15383 | Google Chrome Helper | 1022.64 |
| 15376 | Google Chrome        | 683.43  |
| 1034  | Atom Helper          | 557.53  |
+-------+----------------------+---------+

Generating hashes for running processes

Now, this is a really useful query for commuting the sha256 for running processes on a given machine. It’ll let you uniquely identify processes and let you check public binary database such as Virus Total to determine if a running binary is known, unknown, malicious or even outdated. Running these queries hundreds of system can help you spot outliers or interesting processes that are unique to systems.

To compute the hash for a process we first need to join the processes table and the hash table.

SELECT DISTINCT h.sha256, p.name, u.username
FROM processes AS p
INNER JOIN hash AS h ON h.path = p.path
INNER JOIN users AS u ON u.uid = p.uid
ORDER BY start_time DESC
LIMIT 5;

You may notice that for some of your results a hash isn’t computed. The reason being is that by default and to ensure Osquery is performant Osquery will only hash files less than 50MB in size. This limitation can be increased by setting the following command line flag. This will increase the limit to 500MB.

--read_max=524288000

You can simply exclude processes without a computed hash by using the WHERE grammar.

Working out a processes execution time

With the processes table, Osquery provides a start_time (which is the number of seconds since the system booted) but not the actual time in UTC of when the process was actually expected. We can calculate this using both the uptime and time table together.

SELECT p.pid, p.name, DATETIME(time.unix_time-(uptime.total_seconds-p.start_time), 'unixepoch') AS execution_time
FROM processes AS p, uptime, time
ORDER BY execution_time DESC
LIMIT 5;

The process_events table

Sometimes, grabbing a snapshot isn’t enough especially if you’re planning on trying map process hierarchies. Querying the processes table only provides a snapshot in time and does not record new and terminated processes. This leads to missing process ids.

Process events on MacOSX

For collecting process events on MacOSX their changes need to be made to the OpenBSM audit_contol file.

sudo vim /etc/security/audit_control

Please ensure the file looks the same. I have highlighted the differences from the default MacOSX audit_control configuration.

#
# $P4: //depot/projects/trustedbsd/openbsm/etc/audit_control#8 $
#
dir:/var/audit
flags:ex,pc,ap,aa,lo,ad
minfree:5
naflags:no
policy:cnt,argv,arge
filesz:2M
expire-after:10M
superuser-set-sflags-mask:has_authenticated,has_console_access
superuser-clear-sflags-mask:has_authenticated,has_console_access
member-set-sflags-mask:
member-clear-sflags-mask:has_authenticated

Lastly, run audit -s to reload the audit daemon. A reboot may also be required.

Enabling process_events in Osquery

The process_events table is not enabled by default. You’ll need to set a few command line flags.

sudo osqueryi --disable_audit=false --verbose --disable_events=false

You can check whether process events are enabled with the following query.

SELECT active FROM osquery_events WHERE name = 'process_events';

If the value of active is 1, then process events are enabled. Start up a few new processes. These should now be added to the process_events table.

SELECT pid, uid, path FROM process_events ORDER BY time DESC LIMIT 5;

Which will yield:

+------+-----+-------------------+
| pid  | uid | path              |
+------+-----+-------------------+
| 2871 | 501 | ..eng.ChessEngine |
| 562  | 501 | /bin/sh           |
| 2816 | 501 | ..rces/bin/docker |
| 562  | 501 | /bin/sh           |
| 2817 | 501 | ..rces/bin/docker |
+------+-----+-------------------+

You can, of course, continue to join other tables to enrich the process_events table.

SELECT pid, uid, CASE WHEN LENGTH(path) > 15 THEN SUBSTR(path, -15) ELSE path END AS path 
FROM process_events 
WHERE path != '/dev/null' 
ORDER BY time 
DESC LIMIT 5;

Thank you for reading, please drop a comment if you’d like us to add any more examples. Happy to provide support as well if anyone is struggling to get the process_events table working.