A Linux installation has many tools to query different aspects of the system. Some tools, like top and ps, give a nice overview, whereas others, like ip, interface directly with the kernel. The number of tools at your disposal quickly multiplies if you manage a network with various operating systems, and, while having access to several utilities sounds like a good thing, juggling them and their respective syntax is quite bothersome.

If you crave a unified interface for querying the different aspects of the operating system, you need osquery. Osquery [1] is a cross-platform open source tool originally created by Facebook that, as its name suggests, is designed to query various details about the state of your machines.

The osquery tool works across Linux, Windows, and macOS and exposes operating system configuration data in the form of relational database tables. In other words, osquery turns a Linux installation into one giant database, with tables that you can query using SQL-like statements. With these queries, you can check on running processes, loaded kernel modules, and active user accounts, and you can even monitor file integrity, check the status and configuration of the firewall, perform security audits of the target server, and lots more. The tool uses a high level of the SQLite dialect, which isn't too difficult to grasp, even for those unfamiliar with SQL.

Loaded Question

Although osquery won't be available in your distribution's official repositories, installing it isn't much of an issue. The tool is available as a source tarball along with pre-packed binaries for RPM- and DEB-based distributions. You can also install it by adding its repository for your respective distribution. In this tutorial, I'll install osquery on top of a CentOS 7 installation.

If this is a pristine CentOS 7 installation, you'll have to update curl and a number of other packages with:

$ sudo yum update curl nss nss-util nss-sysinit nss-tools

Now grab the GPG key for the tool's repository with:

$ curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery

Now add and enable the repository with:

$ sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
$ sudo yum-config-manager --enable osquery-s3-rpm

Once the repository has been enabled, you can simply grab the tool with yum:

$ sudo yum install osquery

Installing osquery gives you access to three components: osqueryi, which is an interactive osquery shell and is useful as a test bed for performing ad hoc queries; osqueryd, which is a daemon that runs scheduled queries in the background; and osqueryctl, a helper script that will assist you by testing osquery's configuration. You can also use it to start, stop, and restart the daemon. It's important to note that osqueryi doesn't talk to osqueryd in any way, which is to say that osqueryi isn't a client to osqueryd. They are separate but related tools that come together in one package. Most of the flags and options needed to run both are the same, and you can launch osqueryi using the osqueryd configuration file, which is useful for customizing the interactive environment without using lots of command-line switches.

Get Curious

To get started, fire up a terminal and run

sudo osqueryi

to get into the osquery interactive console mode (Figure 1). Before pressing ahead, you should familiarize yourself with some basics. Osquery collects and aggregates a system's log and status information in a number of predefined tables. It is these tables that you query to get information about the state of your system. To get a list of all available tables in osquery, run the command:

osquery> .tables

Figure 1: Osquery only accepts SELECT statements. Others like UPDATE and DELETE will spit an error.

The list of tables isn't really useful, but you can look inside at each table's schema to identify the available columns, column types, and descriptive details. The schema of a table can be viewed with the .schema command:

osquery> .schema users
osquery> .schema processes
osquery> .schema os_version

Each of these commands will get the schema of the respective table, which will be in the form of CREATE TABLE commands.

Besides .tables and .schema, various other commands are at your disposal. You can use the .help command to see them all and .show to view the tool's current configuration. One useful option is .mode, with which you can change the display mode for the results (Figure 2).

Figure 2: By default, osquery uses the pretty mode, which displays the results in nicely formatted tables.

Riddle Me This

You're good to go now. Note that all the following commands are run in the osqueryi interactive shell. I am omitting the osquery> prompt from now on to save space. For starters, the command

SELECT * FROM processes;

is the equivalent of ps ax and produces a long bit of output that wouldn't make sense. If you replace the * with particular column names, the output becomes more manageable (Figure 3):

SELECT pid, name, path FROM processes;

Figure 3: With osquery, you can create some complex queries with arguments, such as JOIN and WHERE.

For more meaningful output, use

SELECT pid, name, uid, resident_size FROM processes ORDER BY resident_size desc limit 10;

to display the 10 largest processes arranged by size. Similarly, using

SELECT count(pid) as total, name FROM processes group by name ORDER BY total desc limit 10;

will display the process count and name of the top 10 most active processes. Finally,

SELECT name, path, pid FROM processes WHERE on_disk = 0;

displays processes with no associated binary – usually a red flag that means you should immediately terminate the suspicious process.

To keep an eye on the logged in users, use:

SELECT * FROM logged_in_users;
SELECT * FROM last;

This query lists previous logins so you can find logins from unknown IP addresses, especially if multiple users are logging in from an unfamiliar host.

Also, you can check the repositories available in your distribution. On CentOS, this information is retrieved from the yum_sources table with:

SELECT name, baseurl, enabled FROM yum_sources;

Use the WHERE operative to restrict the view to only the enabled repositories:

SELECT name, baseurl FROM yum_sources WHERE enabled=1;

You can bring up an alphabetized list of all installed packages with:

SELECT name, version FROM rpm_packages ORDER BY name;

To look for a specific package, you can append the name filter:

SELECT name, version, release, source, size FROM rpm_packages WHERE name="firefox";

You can also use osquery to keep an eye on network traffic. The

SELECT * FROM listening_ports;
SELECT * FROM suid_bin;

commands list all the listening ports and find the files that are setuid-enabled, to help you find backdoors on the server and detect backdoor binaries. Often attackers delete the malicious binary file after running it in the system. You can find such processes with:

SELECT name, path, pid FROM processes WHERE on_disk = 0;

The queries

SELECT * FROM kernel_info;
SELECT name, size, used_by, status FROM kernel_modules where status="Live" order by size;

help identify outdated kernels and list all loaded kernel modules. You'll also want to run these queries periodically and compare their output against older results for any changes.

These are just a handful of enquiries you can make with osquery. Read through its documentation, especially the schema for the tables [2], to gain its true potential.