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.
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.
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
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.
The osquery Interactive Shell
To start osquery in interactive mode, use the
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.
To close the osquery session you can use either
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.
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.
We know enough now to be able to issue our first SQL statement. We’re going to select the data from the
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;
Selecting Data From Tables
The users Table
To see a list of all usernames we can query the
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
select name, major, minor, codename from os_version;
The interface_addresses Table
interface_addresses table holds information about your network interfaces.
select interface, address from interface_addresses order by interface;
The memory_info Table
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
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
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.