In this Osquery article, we’ll start by discussing what Osquery is, how it works, how to install it on Debian, a quick introduction to SQL, and finally, create a project detailing how to integrate Osquery with the ELK stack.
To keep this tutorial short, we will not go into the “what” and “how” of the ELK stack. Instead, we’ll quickly and directly discuss how to use it with Osquery. We also assume you have a working knowledge of SQL despite the tutorial provided).
What is Osquery?
Osquery, developed by Facebook, is an open source, cross-platform tool used to query and monitor systems using SQL-based queries.
Osquery can interact with the system and collect detailed information such as memory usage, running processes, loaded kernel modules, hardware events, network connections, etc. The tool works on all systems including Windows, Linux, Mac and BSD.
Using Osquery, you can create SQL queries that display system information and use this information to track and analyze the collected data.
How to install Osquery on Debian systems
It is very easy to install Osquery on Debian systems, and although it is not available in the main Debian repositories, adding it is quite simple.
Let’s take a look at the first method you can use to install Osquery on Debian:
The first and easiest step is to download the deb installer from the main page:
wget https://pkg.osquery.io/deb/osquery_4.6.0-1.linux_amd64.deb sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb
We recommend the above method as deb packages depend very little on most Debian distributions. However, if you want to add to apt use the following method.
Enter the following commands to install Osquery from the repositories.
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys $OSQUERY_KEY sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main' sudo apt-get update sudo apt-get install osquery
How to use Osquery on Debian 10
Before diving into automated scripting and working with the ELK stack, let’s discuss some simple ways to use Osquery on a local system.
Osquery has three main components that you can use to interact with the API.
Osquery: The first component is osqueryi, an interactive shell session. Osqueryi mode is completely self-contained and does not require interaction with the Osquery daemon – Osquery. Using osqueryi mode, you can interactively execute SQL queries and explore the current system just like in a SQL shell.
Note Osquery respects user spaces, and if you run the shell in normal user mode, you will not have access to privileged tables.
Osqueryd: Another component is osqueryd, the Osquery daemon used to schedule queries and record state changes in the background. The daemon works by aggregating the results of queries executed over a period of time and generates logs that are used to compare changes in the state of each query.
Osqueryctl: The third component is Osqueryctl, a helper script used to test the deployment configuration. You can also use it as the Osquery service manager, allowing you to start and stop a service.
By default, Osquery is nothing more than a simple tool to query system information. However, when you combine queries to create well-sorted and aggregated data, it becomes more than a query tool.
To get started, let’s start with the basics to understand how it works:
The first step is to get help with the command:
sudo osqueryd --help
This command will display the Osquery daemon help with a list of arguments that you can use in the shell.
The next and easiest way to interact with Osquery is using the osqueryi session. For example, if you run the osqueryi command without an argument, you will end up in a SQL-like shell:
Within the osqueryi shell, you can execute commands and SQL syntax to select specific system information.
To view the help mode inside the osqueryi shell, use the command:
osquery > .help
Running this command should display help for the Osquery session.
Since Osquery is a relational database mapper for your system, it has a list of tables that you can use to select information using SQLite Queries.
Note Osquery is based on SQLite. You can refer to its documentation if Osquery doesn’t provide enough information:
Inside the osqueryi shell, use the command:
osquery > .tables
This command lists the available tables containing system information.
From there, you can select information from the available diagrams. For example, review the information on DNS resolvers.
SELECT * FROM dns_resolvers;
Depending on the schema you are querying, you will get a tremendous amount of information, and you may need to use a combination of SQL queries to understand it.
You can read more about Osquery tables and schemas from the following resource:
Basic SQL Guide
Osquery works by using SQLite syntax queries to gather information about the system. I have no idea why Facebook chose this route, but it works.
This simple tutorial discusses the basics of SQLite to explain how you can use it to interact with Osquery.
Note: This is in no way intended as a guide to SQL or related languages. For additional language-specific guides, see the main documentation.
Selecting specific records from a table
Using basic SQLite syntax, we can select specific information from a table using a SELECT statement as shown:
ВSELECT pid, name, path FROM processes;
Adding SQL functions
Osquery also supports SQL functions, allowing you to perform various actions on data collected from queries.
For example, a counting feature might allow you to view the number of users on your system.
SELECT COUNT(*) FROM users;
This command will return the total number of users on the system.
Osquery’s ability to use SQL syntax is a huge benefit that can help you create complex datasets that can give you deeper insights into your system. It also creates a bridge that SQL developers using engines like PostgreSQL, MySQL, and others can use for easy adaptation.
A fun side project
As you explore Osquery further and experiment with it, you will find that it is a comprehensive and powerful tool that makes it easy to create projects specifically configured to monitor your systems.
Due to the length of this tutorial and to avoid newbie confusion, we will not go into complex projects. As mentioned, here are a few tools you can build with Osquery:
- Collect logs with Logstash
- Create a system dashboard using Elasticsearch, Logstash and Kibana.
- Build an Osquery Fleet with Kolide
In this article, we covered the basics of Osquery, including how to use it to collect system information.
Although this article is not exhaustive, it is intended to provide you with a quick and easy to understand introduction to Osquery; it was by no means a reference.
Feel free to use other resources to better understand the various concepts we’ve discussed in this article.