قالب وردپرس درنا توس
Home / Tips and Tricks / Use SQL and osquery to query your hardware on Linux – CloudSavvy IT

Use SQL and osquery to query your hardware on Linux – CloudSavvy IT



Bash Shell

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̵

7;s not a problem, we can download installation packages from the osquery download page.

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

sudo dpkg -i osquery_4.6.0-1.linux_amd64.deb in a terminal window

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

sudo rpm -U osquery-4.6.0-1.linux.x86_64.rpm in a terminal window

On Manjaro, osquery is located in the standard repositories. we can install it with pacman.

sudo pacman -Sy osquery

sudo pacman -Sy osquery in a terminal window

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

osqueryi --version in a terminal window

The osquery Interactive Shell

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

osqueryi

osqueryi in a terminal window

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

.help in an osquery interactive session

You can use both to close the osquery session .exit or .quit.

.quit

v in an osquery interactive session

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

.tables in an osquery interactive session

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

schedule uptime in an osquery interactive session

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;

select days, hours, minutes of uptime;  in an osquery interactive session

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 * from uptime;  in an osquery interactive session

Select data from tables

The user list

To see a list of all usernames, we can do it users table.

select username from users;

select username of users;  in an osquery interactive session

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;

select order user username by username;  in an osquery interactive session

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;

select username, folder of users where length (username) = 4 sort by username;  in an osquery interactive session

Maybe we are only interested in one username.

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

select username, directory, shell of users where username =

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";

select pid, name, state, start_time of processes where name =

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;

select user, host, time, from logged in_users;  in an osquery interactive session

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 "~";

select user, host, login_in_users time 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;

select name, major, minor, codename from os_version;  in an osquery interactive session

The interface_addresses

The interface_addresses table contains information about your network interfaces.

select interface, address from interface_addresses order by interface;

select interface, address from interface_addresses sequence per interface;  in an osquery interactive session

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;

select memory_total, memory_free, cached from memory_info;  in an osquery interactive session

The group table

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

.all groups

.  all groups in an osquery interactive session

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;

select * from groups where gid> = 1000;  in an osquery interactive session “width =” 646 “height =” 197 “onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);  “onerror =” this.onerror = null;  pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon /> (this);  “</p>
<h3>The usb_devices table</h3>
<p>If a Universal Serial Bus device is not recognized, check this table to see if the operating system detects the device.</p>
<pre>select usb_address, usb_port, version, model from usb_devices;</pre>
<p><img class=

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;

select name, version, source from deb_packages limit 10;  in an osquery interactive session

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;

select cpu_brand from system_info;  in an osquery interactive session

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