Osquery is an open-source security threat monitoring tool developed by Facebook. It is used for querying system information including system version, kernel information, running processes, memory information, listening ports, login users, and more. Osquery helps system administrators to write SQL queries to identify, probe, and eliminate various types of threats. It supports several operating systems including Windows, Linux, FreeBSD, and macOS.
In this post, we will explain how to install and use Osquery on Ubuntu 20.04.
Step 1 – Install Osquery
By default, the Osquery package is not included in the Ubuntu default repository, so you will need to add the Osquery repository to your system.
First, install required dependencies using the following command:
apt-get install gnupg2 software-properties-common wget unzip -y
Next, add the Osquery GPG key with the following command:
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY
Next, add the Osquery repository to APT using the following command:
add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
Once the repository is added, update the repository and install Osquery with the following command:
apt-get update -y apt-get install osquery -y
Step 2 – Connect to Osquery Console
Osquery provides an interactive shell to execute queries and explore the current state of your operating system.
You can connect to the Osquery shell using the following command:
osqueryi
Once you are connected, you should get the following output:
Using a virtual database. Need help, type '.help' osquery>
Next, display the default settings of Osquery using the following command:
osquery> .show
You should see the following output:
osquery - being built, with love.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
osquery 4.9.0
using SQLite 3.35.5
General settings:
Flagfile:
Config: filesystem (/etc/osquery/osquery.conf)
Logger: filesystem (/var/log/osquery/)
Distributed: tls
Database: ephemeral
Extensions: core
Socket: /root/.osquery/shell.em
Shell settings:
echo: off
headers: on
mode: pretty
nullvalue: ""
output: stdout
separator: "|"
width:
Non-default flags/options:
database_path: /root/.osquery/shell.db
disable_database: true
disable_events: true
disable_logging: true
disable_watchdog: true
extensions_socket: /root/.osquery/shell.em
hash_delay: 0
logtostderr: true
stderrthreshold: 0
Osquery has a lot of tables available for query. You can list them with the following command:
osquery> .tables
Sample output:
=> acpi_tables => apparmor_events => apparmor_profiles => apt_sources => arp_cache => atom_packages => augeas => authorized_keys => azure_instance_metadata => azure_instance_tags => block_devices => bpf_process_events => bpf_socket_events => carbon_black_info => carves => chrome_extension_content_scripts => chrome_extensions => cpu_time => cpuid => crontab => curl => curl_certificate => deb_packages => device_file => device_hash => device_partitions => disk_encryption => dns_resolvers => docker_container_fs_changes => docker_container_labels => docker_container_mounts => docker_container_networks => docker_container_ports
Osquery has several modes to display the query output. Here, we will use line mode to display the output.
To set line mode, run the following command:
osquery> .mode line
Step 3 – How to Use Osquery
To get information about your running operating system, run:
osquery> SELECT * FROM system_info;
Sample output:
hostname = ubuntu2004
uuid = a83cffe2-50f4-4fea-9ef4-423853fdc122
cpu_type = x86_64
cpu_subtype = 6
cpu_brand = QEMU Virtual CPU version 2.5+
cpu_physical_cores = 1
cpu_logical_cores = 1
cpu_microcode = 0x1
physical_memory = 2084278272
hardware_vendor = QEMU
hardware_model = Standard PC (i440FX + PIIX, 1996)
hardware_version = pc-i440fx-bionic
hardware_serial =
board_vendor =
board_model =
board_version =
board_serial =
computer_name = ubuntu2004
local_hostname = ubuntu2004
To filter the system information output, run:
osquery> SELECT hostname, cpu_type, physical_memory, hardware_vendor, hardware_model FROM system_info;
Sample output:
hostname = ubuntu2004
cpu_type = x86_64
physical_memory = 2084278272
hardware_vendor = QEMU
hardware_model = Standard PC (i440FX + PIIX, 1996)
To display your operating system version, run:
osquery> SELECT * FROM os_version;
Sample output:
name = Ubuntu
version = 20.04 LTS (Focal Fossa)
major = 20
minor = 4
patch = 0
build =
platform = ubuntu
platform_like = debian
codename = focal
arch = x86_64
To display your kernel information, run:
osquery> SELECT * FROM kernel_info;
Sample output:
version = 5.4.0-29-generic
arguments = ro net.ifnames=0 biosdevname=0 console=ttyS0 console=tty0
path = /boot/vmlinuz-5.4.0-29-generic
device = UUID=29a0b164-1ba1-45a7-b23a-cdb98f23edbc
To display all listening ports with the service name and PID, run:
osquery> SELECT DISTINCT processes.name, listening_ports.port, processes.pid FROM listening_ports JOIN processes USING (pid) WHERE listening_ports.address = '0.0.0.0';
Sample output:
name = unbound port = 53 pid = 3407 name = sshd port = 22 pid = 649 name = unbound port = 953 pid = 3407 name = darkstat port = 666 pid = 6100 name = darkstat port = 667 pid = 6109 name = apt-cacher-ng port = 3142 pid = 4071 name = ntpd port = 123 pid = 483
To display information for the Apache package, run:
osquery> SELECT name, version FROM deb_packages WHERE name="apache2";
Sample output:
+---------+-------------------+ | name | version | +---------+-------------------+ | apache2 | 2.4.41-4ubuntu3.4 | +---------+-------------------+
To display system memory information, run:
osquery> SELECT * FROM memory_info;
Sample output:
+--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+ | memory_total | memory_free | buffers | cached | swap_cached | active | inactive | swap_total | swap_free | +--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+ | 2084278272 | 1358233600 | 44519424 | 520896512 | 0 | 406622208 | 222449664 | 495411200 | 495411200 | +--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+
To display information of all network interfaces, run:
osquery> SELECT * FROM interface_addresses;
Sample output:
+-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+ | interface | address | mask | broadcast | point_to_point | type | +-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+ | lo | 127.0.0.1 | 255.0.0.0 | | 127.0.0.1 | unknown | | eth0 | 69.87.221.220 | 255.255.255.0 | 69.87.221.255 | | unknown | | lo | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | | | unknown | | eth0 | fe80::200:45ff:fe57:dddc%eth0 | ffff:ffff:ffff:ffff:: | | | unknown | +-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+
To check system uptime, run:
osquery> SELECT * FROM uptime;
Sample output:
+------+-------+---------+---------+---------------+ | days | hours | minutes | seconds | total_seconds | +------+-------+---------+---------+---------------+ | 0 | 1 | 55 | 5 | 6905 | +------+-------+---------+---------+---------------+
To list all users whose UID is greater than 1000, run:
osquery> SELECT * FROM users WHERE uid>=1000;
Sample output:
+-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+ | uid | gid | uid_signed | gid_signed | username | description | directory | shell | uuid | +-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+ | 65534 | 65534 | 65534 | 65534 | nobody | nobody | /nonexistent | /usr/sbin/nologin | | | 65534 | 65534 | 65534 | 65534 | nobody | nobody | / | /usr/sbin/nologin | | +-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+
To check last logged in users, run:
osquery> SELECT * FROM last;
Sample output:
+----------+-------+------+------+------------+-----------------+ | username | tty | pid | type | time | host | +----------+-------+------+------+------------+-----------------+ | root | pts/0 | 1013 | 7 | 1629008887 | 106.213.193.155 | | root | pts/1 | 3372 | 7 | 1629010656 | 106.213.193.155 | | root | pts/2 | 4158 | 7 | 1629013021 | 106.213.193.155 | +----------+-------+------+------+------------+-----------------+
To display all logged in users, run:
osquery> SELECT * FROM logged_in_users;
Sample output:
+-----------+----------+------------+------------------+------------+------+ | type | user | tty | host | time | pid | +-----------+----------+------------+------------------+------------+------+ | boot_time | reboot | ~ | 5.4.0-29-generic | 1629008369 | 0 | | init | | /dev/tty1 | | 1629008378 | 491 | | init | | /dev/ttyS0 | | 1629008378 | 484 | | login | LOGIN | ttyS0 | | 1629008378 | 484 | | login | LOGIN | tty1 | | 1629008378 | 491 | | runlevel | runlevel | ~ | 5.4.0-29-generic | 1629008383 | 53 | | user | root | pts/0 | 106.213.193.155 | 1629008887 | 1013 | | user | root | pts/1 | 106.213.193.155 | 1629010656 | 3372 | | user | root | pts/2 | 106.213.193.155 | 1629013021 | 4158 | +-----------+----------+------------+------------------+------------+------+
Conclusion
In the above guide, we explained how to install and use Osquery to get data from the operating system by running SQL-based queries. It is a very useful and easy-to-use tool to find backdoors, malware, zombie processes, and more. Get started with Osquery on your VPS from Atlantic.Net today!
