Before we investigate the specific cases of Osquery concerning security, it is essential to understand the components that make up Osquery
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. https://Osquery.readthedocs.io/en/stable/ https://Osquery.io/schema/2.11.2
