In this guide, we are going to learn how to install osquery on Ubuntu 20.04. Osquery is an opensource tool that queries an operating system as if it were a relational database. It leverage SQL-like queries to gather Operating System information for performance, security, compliance audit analysis. It runs on multiple platforms such as Linux, FreeBSD, MacOS, Windows systems.
Installing Osquery on Ubuntu 20.04
Install Osquery APT Repository
The default Ubuntu repositories does not contain the osquery package. However, osquery publishes an apt repository for each stable release. To add osquery apt repository to Ubuntu 20.04, create the osquery source list;
echo "deb [arch=amd64] https://pkg.osquery.io/deb deb main" | sudo tee /etc/apt/sources.list.d/osquery.listImport the repository signing keys
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80BUpdate your system packages
sudo apt updateOnce the update is done, install osquery;
sudo apt install osqueryComponents of osquery
Osquery package installs three basic components;
osqueryctl– This is an osquery helper script for testing osquery configuration/deployment as well as managing the osqueryd service.osqueryd– is an osquery daemon for scheduling queries and recording the changes in the state of OS.osqueryi– is an osquery interactive shell. From the shell, you can run various queries to explore that state of your OS.
In order to learn the usage of the commands above, you can pass the -h/–help option.
osqueryctl -hUsage: /usr/bin/osqueryctl {clean|config-check|start|stop|status|restart}For example to start, stop and restart osqueryd using osqueryctl, run the commands;
osqueryctl start osquerydosqueryctl stop osqueryd
osqueryctl restart osqueryd
Running Osquery
Osquery can be run in standalone mode using the osqueryi or it can be run as service using osqueryd. In this guide, we are going to focus on how to use the osquery interactive shell to query various system activities.
Running osquery in standalone mode
When osqueryi is run without any arguments, it takes you to the interactive shell prompt;
osqueryi
Using a virtual database. Need help, type '.help'
osquery>
You can obtain help by typing .help on the shell prompt.
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
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.features List osquery's features and their statuses
.headers ON|OFF Turn display of headers on or off
.help Show this message
.mode MODE Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns see .width
line One value per line
list Values delimited by .separator string
pretty Pretty printed SQL results (default)
.nullvalue STR Use STRING in place of NULL values
.print STR... Print literal STRING
.quit Exit this program
.schema [TABLE] Show the CREATE statements
.separator STR Change separator used by output mode
.socket Show the osquery extensions socket path
.show Show the current values for various settings
.summary Alias for the show meta command
.tables [TABLE] List names of tables
.types [SQL] Show result of getQueryColumns for the given query
.width [NUM1]+ Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off
osquery>
System Information tables
Osquery converts various OS attributes into tabular like database concepts. Hence, to list tables from which various system information is stored, run the .tables command within the osqueryi prompt.
osqueryi
osquery> .tables
Sample output;
=> acpi_tables
=> apt_sources
=> arp_cache
=> augeas
=> authorized_keys
=> block_devices
=> carbon_black_info
=> carves
=> chrome_extensions
=> cpu_time
…
=> time
=> uptime
=> usb_devices
=> user_events
=> user_groups
=> user_ssh_keys
=> users
=> yara
=> yara_events
=> yum_sources
osquery>
For example purposes, let us see what is contained on some of the tables;
select * from os_version;
+--------+---------------------------+-------+-------+-------+-------+----------+---------------+----------+--------+
| name | version | major | minor | patch | build | platform | platform_like | codename | arch |
+--------+---------------------------+-------+-------+-------+-------+----------+---------------+----------+--------+
| Ubuntu | 20.04.1 LTS (Focal Fossa) | 20 | 4 | 0 | | ubuntu | debian | focal | x86_64 |
+--------+---------------------------+-------+-------+-------+-------+----------+---------------+----------+--------+
To query system users whose uid is greater than 1000,
select * from users where uid >=1000;
+-------+-------+------------+------------+-----------+-------------+-----------------+-------------------+------+
| uid | gid | uid_signed | gid_signed | username | description | directory | shell | uuid |
+-------+-------+------------+------------+-----------+-------------+-----------------+-------------------+------+
| 65534 | 65534 | 65534 | 65534 | nobody | nobody | /nonexistent | /usr/sbin/nologin | |
| 1000 | 1000 | 1000 | 1000 | koromicha | koromicha | /home/koromicha | /bin/bash | |
| 65534 | 65534 | 65534 | 65534 | nobody | nobody | / | /usr/sbin/nologin | |
+-------+-------+------------+------------+-----------+-------------+-----------------+-------------------+------+
To list all logged in users;
select user,tty,host,time from logged_in_users where tty not like '~';
+-----------+-------+--------------+------------+
| user | tty | host | time |
+-----------+-------+--------------+------------+
| koromicha | tty1 | | 1613887707 |
| koromicha | pts/0 | 192.168.57.1 | 1613888358 |
+-----------+-------+--------------+------------+
Check system uptime;
select * from uptime;
+------+-------+---------+---------+---------------+
| days | hours | minutes | seconds | total_seconds |
+------+-------+---------+---------+---------------+
| 0 | 1 | 21 | 49 | 4909 |
+------+-------+---------+---------+---------------+
To show network interfaces and IP addresses;
select interface,address,mask from interface_addresses where interface NOT LIKE '%lo%';
+-----------+---------------------------------+-----------------------+
| interface | address | mask |
+-----------+---------------------------------+-----------------------+
| enp0s3 | 10.0.2.15 | 255.255.255.0 |
| enp0s8 | 192.168.57.3 | 255.255.255.0 |
| enp0s3 | fe80::a00:27ff:fe5c:52a%enp0s3 | ffff:ffff:ffff:ffff:: |
| enp0s8 | fe80::a00:27ff:fe7f:8415%enp0s8 | ffff:ffff:ffff:ffff:: |
+-----------+---------------------------------+-----------------------+
Osquery command output view modes
The osquery command output view mode can be changed by running the command, .mode MODE from within the osqueryi shell prompt, where MODE can be line, csv, pretty (default), column, list.
For example to set the view to line mode;
osquery> .mode line
The when you run the queries, output is produced line by line;
SELECT * FROM system_info;
hostname = ubuntu20
uuid = 269c209d-fc67-ec4f-bf56-c759a8296e14
cpu_type = x86_64
cpu_subtype = 142
cpu_brand = Intel(R) Core(TM) i7-10510U CPU @ 1.80GHz
cpu_physical_cores = 1
cpu_logical_cores = 1
cpu_microcode =
physical_memory = 2084356096
hardware_vendor = innotek GmbH
hardware_model = VirtualBox
hardware_version = 1.2
hardware_serial = 0
board_vendor = Oracle Corporation
board_model = VirtualBox
board_version = 1.2
board_serial = 0
computer_name = ubuntu20
local_hostname = ubuntu20
List installed system packages;
select * from deb_packages top limit 3;
name = accountsservice
version = 0.6.55-0ubuntu12~20.04.4
source =
size = 452
arch = amd64
revision = 0ubuntu12~20.04.4
status = install ok installed
maintainer = Ubuntu Developers <[email protected]>
section = admin
priority = optional
name = adduser
version = 3.118ubuntu2
source =
size = 624
arch = all
revision =
status = install ok installed
maintainer = Ubuntu Core Developers <[email protected]>
section = admin
priority = important
Exit Osquery Interactive shell
To exit osqueri interactive shell, osquery>, use the command .exit or simply press Control+d keyboard combination keys.
osquery> .exit
Running Osquery as a service
osqueryd is an osquery daemon for scheduling queries and recording the changes in the state of OS. You can use this daemon to run Osquery a service.
For this to work, you need to copy the sample Osquery configuration to /etc/osquery directory as follows;
cp /usr/share/osquery/osquery.example.conf /etc/osquery/osquery.conf
Next, that the service;
systemctl start osqueryd
Checking the status;
systemctl status osqueryd
● osqueryd.service - The osquery Daemon
Loaded: loaded (/lib/systemd/system/osqueryd.service; disabled; vendor preset: enabled)
Active: active (running) since Sun 2021-02-21 07:42:48 UTC; 18s ago
Process: 66618 ExecStartPre=/bin/sh -c if [ ! -f $FLAG_FILE ]; then touch $FLAG_FILE; fi (code=exited, status=0/SUCCESS)
Process: 66633 ExecStartPre=/bin/sh -c if [ -f $LOCAL_PIDFILE ]; then mv $LOCAL_PIDFILE $PIDFILE; fi (code=exited, status=0/SUCCESS)
Main PID: 66634 (osqueryd)
Tasks: 14 (limit: 2282)
Memory: 7.6M
CGroup: /system.slice/osqueryd.service
├─66634 /usr/bin/osqueryd --flagfile /etc/osquery/osquery.flags --config_path /etc/osquery/osquery.conf
└─66637 /usr/bin/osqueryd
Feb 21 07:42:48 ubuntu20 systemd[1]: Starting The osquery Daemon...
Feb 21 07:42:48 ubuntu20 systemd[1]: Started The osquery Daemon.
Feb 21 07:42:48 ubuntu20 osqueryd[66634]: osqueryd started [version=4.6.0]
Well, that is it on how to install Osquery. You can continue to explore this awesome tool.
Further Reading
Other Tutorials
Install and Setup Kolide Fleet on Ubuntu 18.04
