In this guide, we are going to learn how to install osquery on Debian 10 Buster. 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.
Install Osquery on Debian 10 Buster
Install Osquery APT Repository
The default Debian 10 repositories does not contain the osquery package. However, osquery publishes an apt repository for each stable release.
Import and install the osquery repository signing keys.
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
Next install osquery APT repo on Debian 10 Buster.
sudo apt install software-properties-common sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
Update your system packages
sudo apt update
Install Osquery on Debian 10 Buster
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. For example, to obtain osqueryctl help;
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
Executing Osquery SQL queries
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 within the osquery shell prompt 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>
osqueryi accepts several meta-commands, prefixed with a dot (.).
With 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. For example;
osquery> .tables => acpi_tables => apt_sources => arp_cache => augeas ... => ssh_configs => sudoers => suid_bin => syslog_events => system_controls => system_info => time => ulimit_info => 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, say the sudoers table.
osquery> select * from sudoers;
+----------+----------------------------------------------------------------------------+
| header | rule_details |
+----------+----------------------------------------------------------------------------+
| Defaults | env_reset |
| Defaults | mail_badpass |
| Defaults | secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" |
| root | ALL=(ALL:ALL) ALL |
| %sudo | ALL=(ALL:ALL) ALL |
+----------+----------------------------------------------------------------------------+
osquery>
osquery> select * from sudoers where header like '%root';
+--------+-------------------+
| header | rule_details |
+--------+-------------------+
| root | ALL=(ALL:ALL) ALL |
+--------+-------------------+
osquery>
To query only top 5 system users,
osquery> select * from users where uid <= 1000 limit 5;
+-----+-------+------------+------------+----------+-------------+-----------+-------------------+------+
| uid | gid | uid_signed | gid_signed | username | description | directory | shell | uuid |
+-----+-------+------------+------------+----------+-------------+-----------+-------------------+------+
| 0 | 0 | 0 | 0 | root | root | /root | /bin/bash | |
| 1 | 1 | 1 | 1 | daemon | daemon | /usr/sbin | /usr/sbin/nologin | |
| 2 | 2 | 2 | 2 | bin | bin | /bin | /usr/sbin/nologin | |
| 3 | 3 | 3 | 3 | sys | sys | /dev | /usr/sbin/nologin | |
| 4 | 65534 | 4 | 65534 | sync | sync | /bin | /bin/sync | |
+-----+-------+------------+------------+----------+-------------+-----------+-------------------+------+
osquery>
To check logged in users;
osquery> select * from logged_in_users where type = 'user';
+------+------+-------+----------------+------------+------+
| type | user | tty | host | time | pid |
+------+------+-------+----------------+------------+------+
| user | root | tty1 | | 1565598621 | 729 |
| user | amos | pts/0 | 192.168.43.17 | 1565598768 | 851 |
| user | amos | pts/1 | 192.168.43.162 | 1565602356 | 7712 |
+------+------+-------+----------------+------------+------+
osquery>
Check system uptime;
osquery> select * from uptime;
+------+-------+---------+---------+---------------+
| days | hours | minutes | seconds | total_seconds |
+------+-------+---------+---------+---------------+
| 0 | 1 | 10 | 13 | 4213 |
+------+-------+---------+---------+---------------+
osquery>
The view mode can be changed by running the command, .mode MODE
where MODE can be line, csv, pretty (default), column, list. For exampl to set the view to line mode;
osquery> .mode line
osquery> select * from load_average;
period = 1m
average = 0.080000
period = 5m
average = 0.070000
period = 15m
average = 0.120000
osquery>
List install packages and display only top 3.
osquery> select * from deb_packages top limit 3;
name = adduser
version = 3.118
source =
size = 849
arch = all
revision =
name = adwaita-icon-theme
version = 3.30.1-1
source =
size = 26804
arch = all
revision = 1
name = anacron
version = 2.3-28
source =
size = 99
arch = amd64
revision = 28
osquery>
List system processes;
osquery> select pid,name,state,parent from processes order by start_time desc limit 10;
+------+-----------------------------+-------+--------+
| pid | name | state | parent |
+------+-----------------------------+-------+--------+
| 8405 | kworker/0:0-ata_sff | I | 2 |
| 8332 | osqueryi | R | 874 |
| 8329 | kworker/0:1-ata_sff | I | 2 |
| 8280 | kworker/u2:0-events_unbound | I | 2 |
| 7726 | bash | S | 7725 |
| 7725 | su | S | 7722 |
| 7721 | sshd | S | 7712 |
| 7722 | bash | S | 7721 |
| 7712 | sshd | S | 456 |
| 7599 | kworker/u2:1-events_unbound | I | 2 |
+------+-----------------------------+-------+--------+
osquery>
Get system information.
osquery> select hostname,cpu_physical_cores,physical_memory from system_info;
+----------------------+--------------------+-----------------+
| hostname | cpu_physical_cores | physical_memory |
+----------------------+--------------------+-----------------+
| debian10.example.com | 1 | 1035452416 |
+----------------------+--------------------+-----------------+
osquery>
Using Osquery Daemon
Just instead of having to run osquery in an interactive mode using the osqueryi, you can configure Osquery to read the queries from the configuration file and save the results on a log file.
osqueryd
makes it easy to schedule queries and record OS state changes. The daemon aggregates query results over time and generates logs, which indicate state change according to each query.
Osquery doesn’t installs a configuration file by default. Hence, copy the sample configuration to /etc/osquery directory.
cp /usr/share/osquery/osquery.example.conf /etc/osquery/osquery.conf
Our final osquery configuration file looks like;
{
"options": {
// Select the osquery config plugin.
"config_plugin": "filesystem",
// Select the osquery logging plugin.
"logger_plugin": "filesystem",
// Define osquery log directory
"logger_path": "/var/log/osquery",
// Enable logging
"disable_logging": "false",
// Splay the scheduled interval for queries.
// This is very helpful to prevent system performance impact when scheduling
// large numbers of queries that run a smaller or similar intervals.
"schedule_splay_percent": "10",
// Set the osquery PID file directory
"pidfile": "/var/osquery/osquery.pidfile",
// A filesystem path for disk-based backing storage used for events and
// query results differentials.
"database_path": "/var/osquery/osquery.db",
// Disable verbosity
"verbose": "false",
// Set number of threats for concurrent queries
"worker_threads": "2",
// enable schedule profiling
// if adding a query "select * from osquery_schedule" in schedule section,
// it's possible to record the performances
"enable_monitor": "true"
},
"schedule": {
"users": {
// query non-system logged in users
"query": "select username,directory,uid,gid,shell from users where uid >= 1000;",
// The interval in seconds to run this query, not an exact interval.
"interval": 10
},
"processes": {
// The exact query to run to check zombie processes
"query": "select pid,name,state,parent from processes where state = 'Z';",
// The interval in seconds to run this query
"interval": 10
},
"load_average": {
// The exact query to run to check zombie processes
"query": "select * from load_average;",
// The interval in seconds to run this query
"interval": 10
}
},
// Decorators are normal queries that append data to every query.
"decorators": {
"load": [
"SELECT uuid AS host_uuid FROM system_info;",
"SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
]
},
// Add default osquery packs
"packs": {
"osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf"
}
}
Save the configuration file and run the command below to validate it.
osqueryctl config-check
Running osqueryd
systemctl start osqueryd systemctl enable osqueryd
The query logs are not populated to /var/log/osquery/osqueryd.results.log and you can view them in real time using the tail command,
tail -f /var/log/osquery/osqueryd.results.log
That is just about it on our on installing osquery on Debian 10 Buster.
You can read more about osquery here.
Related Tutorials;
How to Install Osquery on Ubuntu 18.04