To keep this tutorial concise, we will not dive deep into the “what” and “how” of the ELK stack. Instead, we’ll quickly and straightforwardly discuss how to use it with Osquery. We will also assume you have a working knowledge of SQL—the provided guide notwithstanding).
What is Osquery?
Developed by Facebook, Osquery is a cross-platform, open-source tool used to query and monitor systems using SQL based queries.
Osquery can interact with the system and gather detailed information such as memory usage, running processes, loaded kernel modules, hardware events, network connections, etc. The tool runs on all systems, including Windows, Linux, Mac, and BSD.
Using Osquery, you can create SQL queries that display information about the system and use this information to monitor and analyze the data collected.
How to Install Osquery on Debian Systems
Installing Osquery on Debian systems is very easy, and although it is not available in the main Debian repos, adding it is pretty straightforward.
Let’s look at the first method you can use to install Osquery on Debian:
The first and simplest step is to download the deb installer from the main page:
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 the deb packages have very few dependencies on most Debian distributions. However, if you wish to add to apt, use the next method.
Enter the following commands to install Osquery from the repositories.
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 deep into building automated scripts and working with the ELK stack, let us discuss some simple Osquery usage on the local system.
Osquery has three main components you can use to interact with the API.
Osquery: The first component is osqueryi, an interactive shell session. The osqueryi mode is entirely standalone and does not require interaction with the Osquery—Osquery daemon. Using the osqueryi mode, you can interactively execute SQL queries and explore the current system similar to a SQL shell.
NOTE: Osquery respects user spaces, and if you run the shell as a regular user mode, you will not have access to privileged tables.
Osqueryd: The other component is osqueryd, the Osquery daemon used to schedule queries and record state changes in the background. The daemon works by aggregating query results executed over a specific time frame and generates logs used to compare every query’s state changes.
Osqueryctl: The third component is Osqueryctl, a helper script used to test deployment configuration. You can also use it as an Osquery service manager, allowing you to start and stop the service.
Out of the box, Osquery is nothing more than a simple tool to query information about the system. However, when you combine the queries to build well-sorted and aggregated data, it becomes more than a query tool.
To get rollin’, let’s start with the basics to understand how it works:
The first step is to get help with the command:
This command will display the Osquery daemon help, with a list of arguments you can use in the shell.
The next, and the easiest way to interact with Osquery, is to use the osqueryi session. For example, if you execute the command osqueryi without an argument, you will drop into an SQL-like shell:
Inside the osqueryi shell, you can execute commands and SQL syntax to select specific information about the system.
To view the help mode inside the osqueryi shell, use the command:
Executing this command should display help regarding the Osquery session.
Since Osquery is a relational database mapper for your system, it has a list of tables you can use to select information from using SQLite Queries.
NOTE: Osquery queries are SQLite-based. You can refer to its documentation if Osquery does not provide enough information:
https://www.sqlite.org/index.html
Inside the osqueryi shell, use the command:
This command lists the available tables containing system information.
From there, you can select information from the available schemas. For example, view the information about DNS resolvers.
Depending on the schema you query, you will get a boatload of information and may need to use a combination of SQL queries to make sense of it.
You can learn more about Osquery tables and schemas from the following resource:
https://osquery.io/schema/4.6.0/
A Basic SQL Guide
Osquery works by using SQLite syntax queries to gather information about a system. I have no idea why Facebook chose this route, but it works.
This simple tutorial will discuss SQLite basics to explain how you can use it to interact with Osquery.
NOTE: This is in no way meant to be a guide for SQL or related languages. For more language-specific guides, refer to the primary documentation.
Selecting specific entries from a table
Using basic SQLite syntax, we can select specific information from a table using the SELECT statement as shown:
Adding SQL functions
Osquery also supports SQL functions, allowing you to perform various actions with data gathered from the queries.
For example, the count function can allow you to view the number of users in your system.
This command will return the total number of users in the system.
The ability for Osquery to use SQL syntax is a huge advantage that can help you build complex datasets that can give you a more in-depth analysis of a system. It also creates a bridge that SQL developers using engines such as PostgreSQL, MySQL, and others can use to adapt with ease.
https://osquery.readthedocs.io/en/stable/introduction/sql/
A Fun, Side Project
When you explore Osquery further and experiment with it, you will discover it’s a comprehensive and powerful tool that makes it easy to create projects specifically tuned to monitor your systems.
Because of this tutorial’s scope, and to avoid confusing beginners, we will not delve into complex projects. That mentioned, here are some tools you can build using Osquery:
- Collect logs with Logstash
- Build a system monitor dashboard with Elasticsearch, Logstash, and Kibana.
- Build Osquery fleet with Kolide
https://osquery.readthedocs.io/en/stable/deployment/log-aggregation/
https://www.elastic.co/guide/en/beats/filebeat/7.10/filebeat-module-osquery.html
https://github.com/fleetdm/fleet
Conclusion
In this tutorial, we looked at the basics of Osquery, including how to use it to collect system information.
Although not comprehensive, this guide purposed to provide you with a quick and straightforward introduction to Osquery; by no means was it a reference guide.
Feel free to use other resources to gain a deeper understanding of the various concepts we’ve discussed in this tutorial.
