OSQuery Project
Project Partners: Elizabeth Chadbourne, Lily Pouliot, and Connor Schnackenberg
What is OSQuery?
OSQuery is an Open Source tool that you can use to query your device for information as if it were a database. It has installation guides for Windows, macOS, and Linux. At a high level OSQuery can be used to monitor devices and generate logs for state changes, which can easily be integrated into existing logging solutions. It uses SQL language and interface to collect information on your devices.
For our purposes, OSQuery can be integrated into the Wazuh logging solution we already have in place, making it easy to find all logs and alerts in a single dashboard.
Installation Instructions for Rocky Linux:
The following instructions detail how to install OSQuery on Rocky Linux.
Step 1: Check your Linux Distribution
cat /etc/os-releaseLook at this section:
We know from here that we need to use RPM (red hat) and x86 for the download in the next few steps.
Step 2: Create Firewall rule to allow for software Installation
Use the commands below on your firewall to allow traffic temporarily, so you are able to install software on your rocky machine.
# allow all traffic temporarily
configure
set firewall name DMZ-to-WAN default-action accept
set firewall name WAN-to-DMZ default-action accept
commit
saveStep 3: Download the correct OSQuery File
If wget is not installed use the following command:
sudo yum install wgetGo to this link: https://osquery.io/downloads/official/5.15.0
Copy the link address
Do the following commands in your Rocky machine
We are using the RPM(x86_64) version for this lab
wget https://pkg.osquery.io/rpm/osquery-5.15.0-1.linux.x86_64.rpmThe file should now be in your home directory. You will need to install it with the following command:
sudo yum install ./osquery-5.15.0-1.linux.x86_64.rpmDelete Firewall Rule used to Install Traffic:
configure
set firewall name DMZ-to-WAN default-action drop
set firewall name WAN-to-DMZ default-action drop
commit
saveAlternative Methods for Installation:
Follow the instructions for alternative installation for RPM distributions
$ curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
$ sudo yum-config-manager --add-repo
https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
# If you have issues with the above step, see instructions below
$ sudo yum-config-manager --enable osquery-s3-rpm-repo
$ sudo yum install osqueryIf the use of “yum-config-manager” comes back with a “not found” error, install it:
$ sudo yum install yum-utils
# Installs yum-config-manager for use in adding a repoOSQuery Integration With Wazuh:
Step 1: Build the osquery.conf file
sudo nano /etc/osquery/osquery.confInput the following into the file:
{
"options": {
"config_plugin": "filesystem",
"logger_plugin": "filesystem",
"utc": "true"
},
"schedule": {
"system_info": {
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"interval": 3600
},
"high_load_average": {
"query": "SELECT period, average, '70%' AS 'threshold' FROM load_average WHERE period = '15m' AND average > '0.7';",
"interval": 900,
"description": "Report if load charge is over 70 percent."
},
"low_free_memory": {
"query": "SELECT memory_total, memory_free, CAST(memory_free AS real) / memory_total AS memory_free_perc, '10%' AS threshold FROM memory_info WHERE memory_free_perc < 0.1;",
"interval": 1800,
"description": "Free RAM is under 10%."
}
},
"packs": {
"osquery-monitoring": "/opt/osquery/share/osquery/packs/osquery-monitoring.conf",
"incident-response": "/opt/osquery/share/osquery/packs/incident-response.conf",
"it-compliance": "/opt/osquery/share/osquery/packs/it-compliance.conf",
"vuln-management": "/opt/osquery/share/osquery/packs/vuln-management.conf",
"hardware-monitoring": "/opt/osquery/share/osquery/packs/hardware-monitoring.conf",
"ossec-rootkit": "/opt/osquery/share/osquery/packs/ossec-rootkit.conf"
}
}Step 2: Enable and start the OSQuery Daemon
systemctl enable osqueryd
systemctl start osquerydStep 3: Edit the default Wazuh configuration file
sudo nano /var/ossec/etc/ossec.conf
Add the following to the bottom of the Wazuh configuration file:
<wodle name="osquery"/>Your file should now look like this:
Step 4: Restart the Wazuh Agent
sudo systemctl restart wazuh-agentStep 5: Update Wazuh Dashboard
Go to Wazuh Interface
Click Wazuh Logo
>Settings
>Modules
> Scroll down to threat detection and response
> Enable Osquery
Step 6: Test Querying to Wazuh
Go to your Wazah interface
Click Wazuh Logo
> Modules
> Threat Detection and Response
> OSquery
Follow the steps below to find your logs:
Go to your Wazuh Interface
> Wazuh Logo
> Security Events
Additional Features of OSQuery Application Client:
SQL-Based Queries
One of the major features of osquery is the support of SQL syntax to fetch information from endpoint devices. They can be codified and reviewed to meet specific requirements, allowing the users to reuse queries and scale them to fetch data from multiple devices. This can be executed without the need to reach out to all operating systems and devices that are present.
Everything in SQL: When using SQL with osequery, select only ! All mutation-based verbs exist, like INSERT, UPDATE, DELETE, and ALTER, but they do nothing, unless creating run-time tables or VIEWs, or using an extension. Mutation-based verbs are allowed in extensions, if the extension supports them. When working with several tables, they require a predicate for one of the columns and will not work without it.
Instructions to add SQL-Based Queries
To begin using SQL with osequery, osqueryi must be powered up as the user or superuser.
osqueryi
# To Leave, do ^D
The following shows the output of running the help command after running osqueryi
$ osqueryi
Using a virtual database. Need help, type '.help'
osquery> .help
Welcome to the osquery shell. Please explore your OS!
You are connected to a transient 'in-memory' virtual database.
.all [TABLE] Select all from a table
.bail ON|OFF Stop after hitting an error
.connect PATH Connect to an osquery extension socket
.disconnect Disconnect from a connected extension socket
.echo ON|OFF Turn command echo on or off
[...]
osquery>
Meta Commands
.tables and .schema will list all of the tables and their schema. The schema meta-command takes an argument that helps limit the output to a partial string match. Below shows the output of the .schema process, where the complete schema can be found linked here, showing all supported platforms. To see schema in your shell for tables foreign to your OS, like kernel modules on macOS, use the --enable_foreign command line flag.
osquery> .schema process
[...]
CREATE TABLE process_memory_map(`pid` INTEGER, `start` TEXT, `end` TEXT, `permissions` TEXT, `offset` BIGINT, `device` TEXT, `inode` INTEGER, `path` TEXT, `pseudo` INTEGER, PRIMARY KEY (`pid`)) WITHOUT ROWID;
CREATE TABLE process_open_files(`pid` BIGINT, `fd` BIGINT, `path` TEXT, PRIMARY KEY (`pid`)) WITHOUT ROWID;
CREATE TABLE process_open_sockets(`pid` INTEGER, `fd` BIGINT, `socket` BIGINT, `family` INTEGER, `protocol` INTEGER, `local_address` TEXT, `remote_address` TEXT, `local_port` INTEGER, `remote_port` INTEGER, `path` TEXT, `state` TEXT, `net_namespace` TEXT HIDDEN, PRIMARY KEY (`pid`, `fd`, `socket`, `family`, `protocol`, `local_address`, `remote_address`, `local_port`, `remote_port`, `path`, `state`, `net_namespace`)) WITHOUT ROWID;
CREATE TABLE processes(`pid` BIGINT, `name` TEXT, `path` TEXT, `cmdline` TEXT, `state` TEXT, `cwd` TEXT, `root` TEXT, `uid` BIGINT, `gid` BIGINT, `euid` BIGINT, `egid` BIGINT, `suid` BIGINT, `sgid` BIGINT, `on_disk` INTEGER, `wired_size` BIGINT, `resident_size` BIGINT, `total_size` BIGINT, `user_time` BIGINT, `system_time` BIGINT, `disk_bytes_read` BIGINT, `disk_bytes_written` BIGINT, `start_time` BIGINT, `parent` BIGINT, `pgroup` BIGINT, `threads` INTEGER, `nice` INTEGER, `elevated_token` INTEGER HIDDEN, `secure_process` INTEGER HIDDEN, `protection_type` TEXT HIDDEN, `virtual_process` INTEGER HIDDEN, `elapsed_time` BIGINT HIDDEN, `handle_count` BIGINT HIDDEN, `percent_processor_time` BIGINT HIDDEN, `upid` BIGINT, `uppid` BIGINT, `cpu_type` INTEGER, `cpu_subtype` INTEGER, `translated` INTEGER, `cgroup_path` TEXT HIDDEN, PRIMARY KEY (`pid`)) WITHOUT ROWID;
Making a simple query:
SQL supports refinements to queries like select, which can be combined with OS-level data like browser plugins to give more insight.
osquery> select * from users;
osquery> select pid, name, uid, resident_size from processes order by resident_size desc limit 1;
+-------+-----------------+------+---------------+
| pid | name | uid | resident_size |
+-------+-----------------+------+---------------+
| 3122 | chromium-browse | 1000 | 649736000 |
+-----+---------+--------------------------------+
osquery> select * from listening_ports where port != 0;
+------+-------+----------+--------+---------------+-----+--------+------+---+
| pid | port | protocol | family | address | fd | socket | path | net_namespace |
+------+-------+----------+--------+---------------+-----+--------+------+---+
| -1 | 53 | 6 | 2 | 192.168.122.1 | -1 | 41060 | | 0 |
| -1 | 53 | 6 | 2 | 127.0.0.53 | -1 | 28923 | | 0 |
Meta table:
looking at the “meta” table that provides details to osquery about itself, which can be prefixed with osquery_ This will always show the current PID of the running osquery process.
Reminder: .mode will change the outlook of the display mode. There are five modes
osquery> .mode line #changes display mode
osquery> SELECT * FROM osquery_info;
pid = 15982
uuid = 4892E1C6-F800-5F8E-92B1-BC2216C29D4F
instance_id = 94c004b0-49e5-4ece-93e6-96c1939c0f83
version = 2.4.6
config_hash =
config_valid = 0
extensions = active
build_platform = darwin
build_distro = 10.12
start_time = 1496552549
watcher = -1
Pros and Cons of OSQuery:
Pros:
Relatively easy to install OSQuery on the machine
Many methods to use if one doesn’t work
Very simple to integrate with our existing Wazuh logging
Lots of documentation - Both for installation and Wazuh integration
Allows querying system information using SQL queries
Provides real-time monitoring
Supports extensions and plugins
Cons:
Resource intensive; depending on the query complexity and frequency OSQuery can consume lots of system resources.
Learning curve; takes time to research and get used to the commands and how it works, not necessarily intuitive.
Demonstration Video:
Sources:
Osqueryd (Daemon) - Osquery. https://osquery.readthedocs.io/en/stable/introduction/using-osqueryd/. Accessed 5 Mar. 2025.
Osqueryi (Shell) - Osquery. https://osquery.readthedocs.io/en/stable/introduction/using-osqueryi/. Accessed 5 Mar. 2025.
Welcome to Osquery - Osquery. https://osquery.readthedocs.io/en/stable/. Accessed 5 Mar. 2025.
Last updated