In this guide, we are going to learn how to install osquery on Ubuntu 18.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 18.04
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 18.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.list
Import the repository signing keys
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
Update your system packages
sudo apt update
Once the update is done, install osquery.
sudo apt install osquery
Components 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 -h
Usage: /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 osqueryd osqueryctl stop osqueryd osqueryctl restart osqueryd
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 ... osquery>
Various OS attributes have been converted into tabular like database concepts. Hence, to list tables from which various system information is stored, run the .tables command.
osquery> .tables
=> 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;
osquery> select * from os_version; +--------+-----------------------------+-------+-------+-------+-------+----------+---------------+----------+ | name | version | major | minor | patch | build | platform | platform_like | codename | +--------+-----------------------------+-------+-------+-------+-------+----------+---------------+----------+ | Ubuntu | 18.04.1 LTS (Bionic Beaver) | 18 | 4 | 0 | | ubuntu | debian | bionic | +--------+-----------------------------+-------+-------+-------+-------+----------+---------------+----------+ osquery>
To query non system users,
osquery> 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 | amos | amos,,, | /home/amos | /bin/bash | | +-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+ osquery>
To check logged in users;
osquery> select user,host,time from logged_in_users where tty not like '~'; +-------+----------------+------------+ | user | host | time | +-------+----------------+------------+ | root | 192.168.43.149 | 1547894367 | | amos | 192.168.43.149 | 1547902074 | +-------+----------------+------------+ osquery>
Check system uptime;
osquery> select * from uptime; +------+-------+---------+---------+---------------+ | days | hours | minutes | seconds | total_seconds | +------+-------+---------+---------+---------------+ | 0 | 4 | 35 | 32 | 16532 | +------+-------+---------+---------+---------------+ osquery>
To show network interfaces and IP addresses;
osquery> 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.56.160 | 255.255.255.0 | | enp0s3 | fe80::3760:84e7:7371:50a1%enp0s3 | ffff:ffff:ffff:ffff:: | | enp0s8 | fe80::ccd8:e5fe:851:c19c%enp0s8 | ffff:ffff:ffff:ffff:: | +-----------+----------------------------------+-----------------------+
The view mode can be changed by running the command, .mode MODE where MODE can be line, csv, pretty (default), column, list. For example to set the view to line mode;
osquery> .mode line
osquery> SELECT * FROM system_info;
hostname = u18svr.example.com
uuid = 9F23F1AC-B198-4EB6-8363-1ED87FB8B43E
cpu_type = 6
cpu_subtype = 69
cpu_brand = Intel(R) Core(TM) i5-4300U CPU @ 1.90GHz
cpu_physical_cores = 1
cpu_logical_cores = 1
cpu_microcode =
physical_memory = 2090295296
hardware_vendor = innotek GmbH
hardware_model = VirtualBox
hardware_version = 1.2
hardware_serial = 0
computer_name = u18svr
local_hostname = u18svr.example.com
List install packages;
osquery> select * from deb_packages top limit 3;
name = accountsservice
version = 0.6.45-1ubuntu1
source =
size = 440
arch = amd64
revision = 1ubuntu1
name = adduser
version = 3.116ubuntu1
source =
size = 624
arch = all
revision =
name = amd64-microcode
version = 3.20180524.1~ubuntu0.18.04.2
source =
size = 75
arch = amd64
revision =
osquery>
Beautiful, isn’t it?. Feel free to explore all the other tables.
You can read more about osquery here.
Related Tutorials
Install and Setup Kolide Fleet on Ubuntu 18.04
