Do you keep forgetting the syntax for obscure hardware commands that you hardly use? The osquery application allows you to query 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 collects a huge amount of information about your Linux computer and makes it accessible as a pseudo database. The database contains many tables with different categories of information. The data in the tables can be retrieved using simple SQL (Structured Query Language) commands.
osquery can be used interactively, or it can be managed through a configuration file and run as a daemon. Running it as a daemon allows you to schedule queries. osquery is a very flexible, advanced application. There is much more to it than can be covered in an introductory article. The excellent documentation is the first place to go if you want to explore the many other options.
We are going to look at the interactive use of osquery.
Install Osquery
Most Linux distributions do not have osquery in their official repositories. That̵
For Ubuntu, download the “.deb” package. This will most likely be downloaded to your “Downloads” directory. Change the directory to the “Downloads” directory and then use dpkg command to install the “.deb” file. Replace the name of the file in our example with the name of the file you are downloading.
cd Downloads
sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb
For Fedora, download the “.rpm” file. Find the file on your hard drive and use the rpm package to install it. We use 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 located in the standard repositories. we can install it with pacman.
sudo pacman -Sy osquery
You can test if osquery is installed correctly and find out which version you are using by starting osquery with the --version option. Note that 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 order.
osqueryi
An osquery interactive shell starts and an “osquery>” prompt appears.
This is where you get the SQL and point assignments. Dot commands are commands that you issue to interact with the osquery shell. With one or two exceptions, point commands do not provide information about your computer. They get their name from the dot or dot “.” that appears at the beginning of all point 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 point commands and a brief description of each command, use the .help command at the “osquery>” prompt.
.help
You can use both to close the osquery session .exit or .quit.
.quit
Using SQL Commands
osquery presents the system information it can report on as tables in a database. In order to query that database, you must know the names of the tables and the fields each contains. The .tables command will list the tables for you.
.tables
There are many tables. You can scroll back and forth through the list and search for the ones that interest you. Let’s just say we want to know more about the uptime table. The .schema command lists the field names of a table and the type of data each field contains.
.schema uptime
We now know enough to issue our first SQL statement. We are 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 end your command.
select days, hours, minutes from uptime;
If we want to see all columns, we can use an asterisk “*As a shortcut that represents all column names. But there is a point command that is even faster. Type .all followed by the table name to get the same result. (Remember there is no semicolon “;” at the end of point commands.)
select * from uptime;
.all uptime
Select data from tables
The user list
To see a list of all usernames, we can do it users table.
select username from users;
You can sort the data by any column in the results. In this example, we only have one 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 certain length perhaps – we can add more clauses to our SQL statement.
select username, directory from users where length(username)=4 order by username;
Maybe we are only interested in one username.
select username, directory, shell from users where username="mary";
The processes Table
We can apply the same principles to the data in each table. If we want to know 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 it state column means interruptible sleep.
The registered_in_users table
Rather than looking at the list of configured users like we did before, we might be more interested in the users who are currently logged in.
select user, host, time from logged_in_users;
To exclude non-human users, use the “not likeSQL clause. The tty column contains the details of the tty or a tilde of the user ‘~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 the user must be physically on site. The other login session with that username is from a different IP address.
That probably needs to be investigated. How can they remotely access the computer when they are here in the building? One of these sessions may be fraudulent.
The os_version table
For more information about the Linux version, ask os_version table.
select name, major, minor, codename from os_version;
The interface_addresses
The interface_addresses table contains information about your network interfaces.
select interface, address from interface_addresses order by interface;
The memory_info table
The memory_info table provides information about the amount of memory in your computer and how it is used.
select memory_total, memory_free, cached from memory_info;
The group table
To quickly view the configured groups on your system, use the .all dot command on it groups table.
.all groups
To see the details of the groups associated with users, filter the groups with a group ID greater than or equal to 1000.
select * from groups where gid>=1000;
The deb_packages table
The deb_packages table contains details of all “.deb” software packages installed on your computer. There also is a rpm_packages table for Fedora and other Red Hat derivatives. No reports are made on other package management systems.
There are many packages on this test machine. The results are limited to 10 records.
select name, version, source from deb_packages limit 10;
The system_info table
A lot of information is packed in the system_info table. We are only going to verify the make of the central processing unit (CPU) installed on this computer.
select cpu_brand from system_info;
Tip of the iceberg
osquery puts information from over 150 tables at your fingertips. Even if you are not familiar with SQL, you will easily master what little you need to use this excellent application productively.
Source link
