Generate process trees with osquery

Rich process trees on macOS, Linux, and Windows

This article was originally written by Zach Wasserman

Using advanced SQL syntax, it is possible to generate process trees in osquery similar to those generated by the pstree utility. With osquery, the generated trees can be extended to include additional information that can aid analysis.

Below is the basic structure of the query:

Running this query in osqueryi will generate results like:

How it Works

This query makes use of SQLite Common Table Expressions (CTEs) to recursively generate the requested data. Below we will examine the components of the query:

  • Line 2 — Set the target process(es) that the query will generate trees for.
  • Line 10 — Stop the recursion when the process parent is the process itself. This prevents infinite recursion on macOS and Linux for the process 0.
  • Line 11 — Order the evaluation of recursive rows by the pid. This generally results in the process trees being output in the correct order (though we cannot guarantee that the ordering will be correct, we will get results for all processes in the trees). On Windows it can be better to ORDER BY start_time as pids are not increasing.
  • Line 13 — Choose the columns to retrieve from the results. In this example we select a limited set of columns to ease interpretation of the results.

Extend the Concept

There are a number of ways that this query can be extended to address different needs.

Retrieve More Details

Change the SELECT statement in line 13 to retrieve a different set of results. A simple case could be changing to SELECT * to get all the columns from the processes table.

A more complex scenario could be to generate the hashes of the running binaries:

Target Different Processes

Change the query on line 2 to generate trees for a different set of processes. One option would be SELECT * FROM processes WHERE pid = 1234 if we know the pid of the process we are interested in. Targeting processes by attributes that remain consistent on different machines (like name, unlike pid) is a useful technique to ensure that live queries across hosts are successful.

This can also be extended using the full power of osquery. For example, we might want the process tree of every process bound to a port:

Wrapping Up

With this query as a building block, osquery provides the capability to generate rich process trees. Consider using this with an osquery TLS server such as Fleet to examine this information on multiple machines at once.

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