If you’re like most people, you probably use a variety of different software to manage your hardware and keep your systems running smoothly. But what about the underlying technology that makes all of that possible? In this article, we’ll be taking a look at how to use SQL and osquery to check your hardware on Linux. First things first: if you’re not familiar with SQL or osquery, we recommend reading up on both before continuing. Both tools are powerful tools for investigating the inner workings of systems, and can be used for a variety of purposes. For example, SQL can be used to query databases and retrieve information about them, while osquery can be used to probe the operating system and network resources. Now that we’ve introduced the basics, let’s get started! To check our hardware on Linux, we’ll need to install both SQL and osquery. To do so, open up a terminal window (or command prompt on Windows) and type the following commands: sudo apt-get install sqlite3 libosquery-dev 1 sudo apt - get install sqlite3 libosquery - dev Once those packages have been installed, we can start using SQL by typing the following command: sqlite3 mydatabase.db 1 sqlite3 mydatabase . db ..

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.

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.

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

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 osqueryi command.

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 .exit or .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.

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 days, hours, and minutes columns from the uptime table. Remember this is SQL, so use a semicolon “;” to terminate your command.

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.)

Selecting Data From Tables

The users Table

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

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.

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.

Perhaps we are only interested in a single username.

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:

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.

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.

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.

The interface_addresses Table

The interface_addresses table holds information about your network interfaces.

The memory_info Table

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

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.

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.

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.

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.

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.