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-release

Look 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
save

Step 3: Download the correct OSQuery File

If wget is not installed use the following command:

sudo yum install wget

Go 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.rpm

The 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.rpm

Delete 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
save

Alternative 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 osquery

If 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 repo

OSQuery Integration With Wazuh:

Step 1: Build the osquery.conf file

sudo nano /etc/osquery/osquery.conf

Input 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 osqueryd

Step 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-agent

Step 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:

https://youtu.be/BWFDSwwlmG0

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.