Application security

Osquery Tour

April 17, 2018 by Security Ninja

Since the sophistication of cyber-attacks is increasing every day, it is vital for organizations and individuals to keep track of their systems activity and ever-changing state. Attackers will try to keep their traffic under the hood and can blend their activities inside a huge Operating System(OS) profile easily. How about a tool that can query the OS objects and attributes just like a SQL query? Well, that is what Osquery is all about. It was developed by Facebook and introduced in 2014.However I have not seen much of its usage organization-wide, and even if the organizations deploy it, they are not using it to its full extent. In this article, we will review how Osquery can be beneficial in forensics, anomalies, and detection.

Before we investigate the specific cases of Osquery concerning security, it is essential to understand the components that make up Osquery

Osquery components

  • Osqueryi: It is an interactive shell and used for ad-hoc queries. It is located inside the main Osqueryi.exe. It does not need to run as admin to query the end user. In this article, we will focus on Osquery to see what all we can get from this tool. Below is a snippet of the Osqueryi help command.

  • Osqueryd: This is the Osquery daemon, which runs in the background to support scheduled queries and record the system changes over time.
  • Osqueryctl: It helps in deployment and testing of configurations.

As I said above, it resembles the syntax of SQL (it is a superset of SQLite). So, let’s look at the tables it supports:

There is a reason why I have introduced tables right away. Osquery will interact with tables to get the current state of the system. It is beneficial for persistent processes, but it can lose some artifacts when queried against the OS level objects. For example, when a process starts and disappears, it cannot be caught with the synchronous approach. To address this, Osquery exposes an asynchronous framework (pubsub framework) for aggregating the operating system information at event time and can be retrieved via a lookup during query time. More Details about eventing framework can be found here.

Now let’s see how we can use Osquery in daily security operations such as forensics. First, we will obtain individual artifacts; then we will see how we can join these artifacts to get more information.

Viewing OS basic information

In the beginning, we can view the basic OS profile like below

Viewing the kernel version

Osquery allows us to view the kernel info as well. Below, we can see the kernel_info table and review a snippet of the kernel information from the system.

Listing the patches installed on the system

With Osquery we can quickly view the patches installed on the system. Osquery gives a table ‘patches,’ to query same. Below, we can see the attributes fetched by Osquery.

Logged on users

We can view the logged-on users to the system with Osquery using the table logged_in_users. Below is a snippet from the same.

Viewing the listening ports

It is imperative to keep monitoring the listening ports on the system against a known good set and flag if a suspicious connection appears. With Osquery we can get this information in the listening_ports table.

Below we can see how to join this information with the process names from another table

This can be easily combined similarly to see the outgoing connections as well.

Retrieving detail about the process

With Osquery we can retrieve the details around processes. Following is the schema of processes table.

Some of the interesting columns to look out for are:

  • Path: It can be used to verify whether a known process is running from an approved location or not.
  • On_disk: It can be used to check whether the process has a file present on the disk or not.

Reviewing the scheduled tasks

Osquery also provides a table for querying around the scheduled tasks. The table name is schedules_tasks. Below is a snippet of the “running” scheduled tasks on the system.

Registry Information

Osquery also allows querying the registry information. The table name is registry. Many use cases can be built around registry like looking for values of Persistent areas (often used by malware) like Run, RunOnce. Some registry values need to be installed manually by users for some patches to be effective, whose existence can be easily queried by Osquery.

Certificate Information

With Osquery we can also evaluate the certificate information from the system. The table name is certificates and below is the snippet of its schema.

Use cases can be like to look out for some specific weak signing algorithm or key strength or their validity etc.

The use cases mentioned above can be combined easily with each other and with other tables to generate a perfect anomaly chain. Also, when the same set of data is combined over large systems, some more interesting artifacts can be revealed.


Posted: April 17, 2018
Security Ninja
View Profile