Use SQL and osquery to Interrogate Your Hardware on Linux

Do you keep forgetting the syntax for obscure hardware commands you hardly use? The osquery application lets you interrogate the hardware, users, and performance of your Linux computer with standard SQL commands.

The osquery Application

The osquery application is a free and open-source program from the osquery Foundation. It gathers a tremendous amount of information about your Linux computer and makes it accessible as a pseudo-database. The database contains many tables holding different categories of information. The data in the tables can be retrieved using simple structured query language (SQL) commands.

osquery can be used interactively, or it can be controlled through a configuration file and run as a daemon. Running it as a daemon allows you to schedule queries. osquery is a very flexible, sophisticated application. It contains far more than can be covered in an introductory article. The excellent documentation is the first place to turn if you want to investigate its many other options.

We’re going to look at using osquery interactively.

Installing osquery

Most Linux distributions do not include osquery in their official repositories. That doesn’t present a problem, we can download installation packages from the osquery downloads page.

For Ubuntu, download the “.deb” package. This will most likely be downloaded to your “Downloads” directory. Change directory to the “Downloads” directory and then use dpkg command to install the “.deb” file. Substitute the name of the file in our example with the name of the one you download.

cd Downloads
sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb

For Fedora, download the “.rpm” file. Locate the file on your hard drive and use the rpm package to install it. We’re using the -U (update option. This installs an application if it is not already installed on your system, and upgrades it if it is.

sudo rpm -U osquery-4.6.0-1.linux.x86_64.rpm

On Manjaro, osquery is in the default repositories. we can install it using pacman.

sudo pacman -Sy osquery

You can test that osquery has installed correctly—and find out the version you’re using—by starting osquery with the --version option. Note there is an  “i” at the end of the command. It’s “osqueryi” not “osquery.” The “i” stands for interactive.

osqueryi --version

The osquery Interactive Shell

To start osquery in interactive mode, use the osqueryi command.

osqueryi

An osquery interactive shell starts and an “osquery>” prompt appears.

This is where you’ll type the SQL and dot commands. Dot commands are commands you issue to interact with the osquery shell. With one or two exceptions dot commands don’t return information about your computer. They get their name from the dot or period “.” that appears at the start of all dot commands.

You can use the “Up Arrow” key to scroll through previously used commands, and tab completion is available for table names.

To get a list of the dot commands and a quick description of each one, use the .help command at the “osquery>” prompt.

.help

To close the osquery session you can use either .exit or .quit.

.quit

Using SQL Commands

osquery presents the system information it can report on as tables in a database. To be able to interrogate that database you need to know the names of the tables and the fields they each contain. The .tables command will list the tables for you.

.tables

There’s a lot of tables. You can scroll forward and backward through the list and look for ones that interest you. Let’s say we want to know more about the uptime table. The .schema command will list a table’s field names and the type of data each field holds.

.schema uptime

We know enough now to be able to issue our first SQL statement. We’re going to select the data from the days, hours, and minutes columns from the uptime table. Remember this is SQL, so use a semicolon “;” to terminate your command.

select days, hours, minutes from uptime;

If we want to see all of the columns we can use an asterisk “*” as a shortcut representing all the column names. But there’s a dot command that is even quicker. Type .all followed by the table name to get the same result. (Remember, there’s no semicolon “;” at the end of dot commands.)

select * from uptime;
.all uptime

Selecting Data From Tables

The users Table

To see a list of all usernames we can query the users table.

select username from users;

You can sort the data by any column in the results. In this example, we have only a single column but we can still sort the data.

select username from users order by username;

If we want to see a subset of the usernames—those of a particular length, perhaps—we can add more clauses to our SQL statement.

select username, directory from users where length(username)=4 order by username;

Perhaps we are only interested in a single username.

select username, directory, shell from users where username="mary";

The processes Table

We can apply the same principles to the data in any table. If we want to find out the details of a gedit process, we can search for it like this:

select pid, name, state, start_time from processes where name="gedit";

The “S” in the state column means interruptible sleep.

The logged_in_users Table

Instead of looking at the list of configured users like we did before, perhaps we’re more interested in the users who are logged in right now.

select user, host, time from logged_in_users;

To exclude non-human users, use the “not like” SQL clause. The tty column holds the details of the user’s tty or a tilde “~” if no tty is in use.

select user, host, time from logged_in_users where tty not like "~";

We can see that the user with username “dave” is logged in twice.

The “:0” in the host column indicates the screen and keyboard connected to the computer. So that user ought to be physically on the premises. The other login session using that username is from another IP address.

That probably needs investigating. How can they be remotely accessing the computer if they’re here in the building? One of these sessions might be fraudulent.

The os_version Table

To find out details about the Linux version, query the os_version table.

select name, major, minor, codename from os_version;

The interface_addresses Table

The interface_addresses table holds information about your network interfaces.

select interface, address from interface_addresses order by interface;

The memory_info Table

The memory_info table holds information regarding the amount of memory in your computer and how it is being used.

select memory_total, memory_free, cached from memory_info;

The groups Table

To quickly see the configured groups on your system, use the .all dot command on the groups table.

.all groups

To see the details of the groups associated with users, filter out the groups with a group ID greater or equal to 1000.

select * from groups where gid>=1000;

The usb_devices Table

If a universal serial bus device is not being recognized, you can check this table to see if the operating system is even detecting it.

select usb_address, usb_port, version, model from usb_devices;

The deb_packages Table

The deb_packages table holds details of all of the “.deb” software packages that have been installed on your computer. There’s also an rpm_packages table for Fedora and other Red Hat derivatives. Other package management systems are not reported on.

There are a lot of packages on this test machine. The results have been limited to 10 records.

select name, version, source from deb_packages limit 10;

The system_info Table

There’s a lot of information packed into the system_info table. We’re just going to verify the brand of central processing unit (CPU) that is installed in this computer.

select cpu_brand from system_info;

Tip of the Iceberg

osquery places the information from over 150 tables at your fingertips. Even if you don’t know much SQL, you’ll find it easy to master the little that you need to make productive use of this excellent application.