Process monitoring with Osquery

Zercurity
Zercurity
May 29, 2019 · 6 min read

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.

Image for post
Image for post
Osquery command line showing several processes related queries.

Before we start

Let us begin!

The processes table

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

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

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

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

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

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

Process events on MacOSX

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

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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store