System monitoring is crucial for maintaining the security and performance of any computer system. With the rise of cyber threats and attacks, having the right tools to monitor and protect your system is essential. OSQuery is an open-source tool that provides a simple and effective way to monitor various aspects of your Ubuntu system.
In this blog post, we will discuss how to install OSQuery on Ubuntu and how to use it to monitor your system. We will cover various queries that can be used to monitor processes, users, network connections, and more.
Requirements
While OSQuery works on most platforms, this tutorial will use an Ubuntu Server 22.04 machine.
You can visit the OSQuery downloads page to see all available options.
Installing OSQuery
The first step in using OSQuery is to install it on your Ubuntu system. OSQuery can be installed via the official OSQuery repository, which can be added to Ubuntu’s package manager using the following commands:
Add the OSQuery GPG key to your machine:
sudo apt-key adv --keyserver keyserver.ubuntu.com \ --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
Install the OSQuery repository:
sudo add-apt-repository \ 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
Now, install the osquery package:
sudo apt-get install -y osquery
Once OSQuery is installed, you’ll have access to three components or commands.
- osqueryd — The OSQuery daemon. This command can be used to schedule and run queries in the background.
- osqueryi — The OSQuery interactive shell. You can use this command when manually and interactively running queries.
- osqueryctl — The helper script to control the OSQuery daemon, such as stopping, starting, restarting, and checking the daemon status.
Using OSQuery Commands
There are two ways to use the osqueryi tool: as an interactive shell and as a standalone command line tool.
To enter the OSQuery interactive shell, run the below command.
sudo osquery*i*
When inside the OSQuery shell, you can get help by running the .help function.
To exit the shell, run .exit or .quit.
When used as a command-line tool, you can run osqueryi with options and flags. To get all available options and flags, run:
osqueryi --help
Listing All Tables
Think of OSQuery as a database that has multiple tables. Each table contains data about the system, including processes, users, etc. And in a database-like manner, you can run SQL queries against these tables. But which tables are available? To find out, run the below command to list them.
osqueryi -L
Or if in the interactive shell:
.tables
Listing Table Schema
Tables have fields, and to find out which fields a particular table has, run this command in the interactive shell.
.schema [TABLE]
Or in the command line.
osqueryi ".schema [TABLE]"
For example, to display the system_info table schema:
osqueryi ".schema system_info"
As you can see below, the result returned all fields in the table. Now you know which fields you can return when you run a query.
Display System Information
This query displays the computer name, memory size, and CPU brand of the local system.
select computer_name,physical_memory,cpu_brand from system_info;
Show Logged In Users
When you need to monitor who logs on to a system, perhaps to catch unauthorized users and lock down access, you can run the below query.
SELECT * FROM logged_in_users;
Calculate Free Disk Space
One of the crucial stats to monitor on a system is the storage space. The query below returns the free and used space on the root drive ‘/dev/root’ and all drives matching ‘/dev/sd%1’.
SELECT
path,
ROUND(('10e-10' * blocks_available * blocks_size), 1) AS 'free (GB)',
ROUND(
100 - (blocks_available * 1.0 / blocks * 1.0) * 100,
1
) AS 'used %',
device,
type
FROM
mounts
WHERE
device like '/dev/sd%1'
or path = '/';
Calculate File Hash
OSQuery can also calculate the file hash of a file by querying the hash table. For example, the below query returns the SHA256 hash of the /etc/sudoers and /etc/passwd files. This query would be helpful if you’d like to monitor if the files have changed.
SELECT path, sha256 FROM hash WHERE path = '/etc/sudoers' or path = '/etc/passwd';
Display Memory Intensive Processes
In cases where the system runs out of RAM, it would be good to check which processes may be experiencing high memory usage.
This query lists the top ten processes that use the most memory by querying the processes table. The result will be listed in descending order by the value of used memory.
SELECT pid as 'Process ID', name as 'Process Name', CAST (ROUND((resident_size * 0.0000010), 0) as int) AS 'Private Memory (M)', CAST (ROUND((total_size * 0.0000010), 0) as int) AS 'Virtual Memory (M)' FROM processes ORDER BY total_size DESC LIMIT 10;
Show Docker Containers Count
When you run Docker on the server and want to quickly see container count and per status count, query the docker_info table, as shown below.
SELECT containers, containers_running as running, containers_paused as paused, containers_stopped as stopped FROM docker_info;
List Running Docker Containers
How about listing all running containers? You can query the docker_containers table like so.
SELECT SUBSTRING(id, 1, 12) as id, name, pid, status, command FROM docker_containers;
There are two running Docker containers in this example, as you can see below.
Run Queries from Files
You can also use files as input besides running queries inside the interactive shell or passing the query to the osqueryi command. This way, you can store different SQL queries in separate files.
For example, copy the code below and save it in a file called processes_listening_ports_with_username.sql. This query will return the processes with listening ports, including the username that owns the process.
SELECT listening_ports.pid as processid, processes.name as processname, users.username, listening_ports.port, listening_ports.address FROM listening_ports JOIN processes ON listening_ports.pid = processes.pid JOIN users ON processes.uid = users.uid LIMIT 5;
Once the query file is saved, run the osqueryi command below in the terminal.
sudo osqueryi < processes_listening_ports_with_username.sql
And you should get a result similar to the screenshot below.
Note. You can find the sample SQL query files in this GitHub repository.
Exporting OSQuery Result to File
You can also export the OSQuery results to files when you run the command from the terminal. For example, let’s export the computer uptime to a text file.
osqueryi "select * from uptime;" > uptime.txt
The result is as follows.
As you can see, the default format is not easily readable or would be hard to parse using automation tools. The more common formats are CSV and JSON, and OSQuery can export to those formats.
To export in CSV format, use the –csv –separator [VALUE] flags.
osqueryi "select * from uptime;" --csv --separator , > uptime.csv
To export in JSON format, use the –json flag like so:
osqueryi "select * from uptime;" --json > uptime.json
You can also create a formatted JSON output using the –json_pretty flag.
osqueryi "select * from uptime;" --json_pretty > uptime.json
Conclusion
This tutorial showed you how to run queries against your system using the OSQuery tool. The OSQuery syntax uses the familiar SQL format to retrieve information from many tables.
There is more to OSQuery than just running queries interactively. It also allows you to configure scheduled queries with automation and build different configuration files for different monitoring sets.
It is up to you to take your OSQuery game up a notch. Good luck!
