osquery is a free and open-source tool developed by Facebook that can be used for querying operating system-related information including, memory usage, installed packages, process information, login users, listening ports and many more. It can be run on several operating systems including, Windows, Linux, FreeBSD and MacOS. It is a very useful tool for a variety of use cases to troubleshoot performance and operational issues. It comes with a lot of tools that help you to perform OS analytics and monitoring.
In this tutorial, we will learn how to install and use osquery on Debian 10.
Prerequisites
- A server running Debian 10.
- A root password is configured on your server.
Getting Started
Before starting, it is a good idea to update your system’s package to the latest version. You can update all the packages with the following command:
apt-get update -y
apt-get upgrade -yOnce all the packages are updated, restart your system to apply the changes.
Install osquery
By default, osquery is not available in the Debian 10 default repository. So you will need to add the osquery repository in your system.
First, download and add GPG key with the following command:
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80BNext, add the osquery repository with the following command:
apt-get install software-properties-common -y
add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'Next, update the repository and install osquery with the following command:
apt-get update -y
apt-get install osquery -yOnce the installation has been finished, start the osquery service with the following command:
osqueryctl start osquerydYou can also verify the status of osquery with the following command:
osqueryctl status osquerydYou should see the following output:
? osqueryd.service - The osquery Daemon
Loaded: loaded (/lib/systemd/system/osqueryd.service; disabled; vendor preset: enabled)
Active: active (running) since Sun 2020-04-19 15:21:57 UTC; 6s ago
Process: 25333 ExecStartPre=/bin/sh -c if [ ! -f $CONFIG_FILE ]; then echo {} > $CONFIG_FILE; fi (code=exited, status=0/SUCCESS)
Process: 25334 ExecStartPre=/bin/sh -c if [ ! -f $FLAG_FILE ]; then touch $FLAG_FILE; fi (code=exited, status=0/SUCCESS)
Process: 25336 ExecStartPre=/bin/sh -c if [ -f $LOCAL_PIDFILE ]; then mv $LOCAL_PIDFILE $PIDFILE; fi (code=exited, status=0/SUCCESS)
Main PID: 25337 (osqueryd)
Tasks: 13 (limit: 4701)
Memory: 6.4M
CGroup: /system.slice/osqueryd.service
??25337 /usr/bin/osqueryd --flagfile /etc/osquery/osquery.flags --config_path /etc/osquery/osquery.conf
??25339 /usr/bin/osqueryd
Apr 19 15:21:57 debian10 systemd[1]: Starting The osquery Daemon...
Apr 19 15:21:57 debian10 systemd[1]: Started The osquery Daemon.
Apr 19 15:21:57 debian10 osqueryd[25337]: osqueryd started [version=4.2.0]
Apr 19 15:21:57 debian10 osqueryd[25337]: I0419 15:21:57.261158 25339 events.cpp:863] Event publisher not enabled: auditeventpublisher: Publish
Apr 19 15:21:57 debian10 osqueryd[25337]: I0419 15:21:57.261485 25339 events.cpp:863] Event publisher not enabled: syslog: Publisher disabled v
Working with osquery
osquery comes with three useful components osqueryi, osqueryd and osqueryctl. The osqueryi is an osquery interactive shell and does not communicate with a daemon. You can use the shell to execute queries and explore the current state of your operating system. osqueryd is the host monitoring daemon that can be used to schedule queries and record OS state changes. osqueryctl is a helper script for testing configuration.
You can run the following command to connect to the osquery shell:
osqueryiYou should get the following output:
Using a virtual database. Need help, type '.help'
Next, run the .help command to see all option available with osquery:
osquery> .helpYou should get the following output:
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>
There are lots of tables available for query. You can list all tables with the following command:
osquery> .tableYou should get the following output:
=> acpi_tables => apparmor_profiles => apt_sources => arp_cache => atom_packages => augeas => authorized_keys => block_devices => carbon_black_info => carves => chrome_extensions => cpu_time => cpuid => crontab => curl => curl_certificate => deb_packages => device_file => device_hash => device_partitions => disk_encryption => dns_resolvers => docker_container_labels => docker_container_mounts
You can the find the various system information using the above table with osquery.
Monitor System with osquery
You can monitor memory usage, process information, disk space, login users, and many more with osquery.
First, launch the osquery shell with the following command:
osqueryiNext, you can get the information of your system hostname, cpu core and physical memory with the following command:
osquery> select hostname,cpu_physical_cores,physical_memory from system_info;You should get the following output:
------------ -------------------- ----------------- | hostname | cpu_physical_cores | physical_memory | ------------ -------------------- ----------------- | debian10 | 1 | 1032937472 | ------------ -------------------- -----------------
To get the information about ssh_config file run the following query:
osquery> select * from ssh_configs;You should get the following output:Advertisements
W0419 15:47:17.043509 25397 virtual_table.cpp:959] The ssh_configs table returns data based on the current user by default, consider JOINing against the users table W0419 15:47:17.043740 25397 virtual_table.cpp:974] Please see the table documentation: https://osquery.io/schema/#ssh_configs ----- -------- -------------------------- --------------------- | uid | block | option | ssh_config_file | ----- -------- -------------------------- --------------------- | 0 | host * | sendenv lang lc_* | /etc/ssh/ssh_config | | 0 | host * | hashknownhosts yes | /etc/ssh/ssh_config | | 0 | host * | gssapiauthentication yes | /etc/ssh/ssh_config | ----- -------- -------------------------- --------------------- osquery>
To get a list of all users in your system run the following query:
osquery> SELECT * FROM users;You should get the following output:
------- ------- ------------ ------------ ----------------- ------------------------------------ ---------------------- ------------------- ------ | 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 | | | 5 | 60 | 5 | 60 | games | games | /usr/games | /usr/sbin/nologin | | | 6 | 12 | 6 | 12 | man | man | /var/cache/man | /usr/sbin/nologin | | | 7 | 7 | 7 | 7 | lp | lp | /var/spool/lpd | /usr/sbin/nologin | |
If you want to list all non system users in your system run the following query:
osquery> select * from users where uid <= 1000 limit 3;You should get the following output:
----- ----- ------------ ------------ ---------- ------------- ----------- ------------------- ------ | 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 | | ----- ----- ------------ ------------ ---------- ------------- ----------- ------------------- ------
To get the list of currently log in users run the following query:
osquery> select * from logged_in_users where type = 'user';You should get the following output:
------ ------ ------- -------------- ------------ ------- | type | user | tty | host | time | pid | ------ ------ ------- -------------- ------------ ------- | user | root | pts/0 | 27.61.217.59 | 1587309538 | 19279 | | user | root | pts/1 | 27.61.217.59 | 1587310737 | 25378 | | user | root | pts/2 | 27.61.217.59 | 1587310997 | 25394 | ------ ------ ------- -------------- ------------ -------
To display the memory information of your system run the following query:
osquery> select * from memory_info;You should get the following output:
-------------- ------------- ---------- ------------ ------------- ----------- ----------- ------------ ----------- | memory_total | memory_free | buffers | cached | swap_cached | active | inactive | swap_total | swap_free | -------------- ------------- ---------- ------------ ------------- ----------- ----------- ------------ ----------- | 4138455040 | 2407211008 | 79745024 | 1384751104 | 0 | 556371968 | 954744832 | 0 | 0 | -------------- ------------- ---------- ------------ ------------- ----------- ----------- ------------ ----------- osquery>
To find the average load of your system run the following query:
osquery> select * from load_average;You should get the following output:Advertisements
-------- ---------- | period | average | -------- ---------- | 1m | 0.000000 | | 5m | 0.000000 | | 15m | 0.000000 | -------- ---------- osquery>
To get a list of first five packages in your system run the following query:
osquery> select * from deb_packages top limit 5;You should get the following output:
------------------- ------------ -------------- ------ ------- ---------- | name | version | source | size | arch | revision | ------------------- ------------ -------------- ------ ------- ---------- | acpi-support-base | 0.142-8 | acpi-support | 43 | all | 8 | | acpid | 1:2.0.31-1 | | 146 | amd64 | 1 | | adduser | 3.118 | | 849 | all | | | apparmor | 2.13.2-10 | | 1833 | amd64 | 10 | | apt | 1.8.2 | | 4064 | amd64 | | ------------------- ------------ -------------- ------ ------- ----------
To get information about running processes in your system run the following query:
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';You should get the following output:
------ ------ ----- | name | port | pid | ------ ------ ----- | sshd | 22 | 729 | ------ ------ -----
To find all the previous logins run the following query:
osquery> select * from last;You should get the following output:
---------- ------- ------- ------ ------------ -------------- | username | tty | pid | type | time | host | ---------- ------- ------- ------ ------------ -------------- | root | pts/0 | 1448 | 7 | 1587365277 | 27.61.217.41 | | root | pts/1 | 13392 | 7 | 1587368569 | 27.61.217.41 | | | pts/0 | 1004 | 8 | 1587376329 | | | | pts/1 | 13321 | 8 | 1587376821 | | | | ttyS0 | 748 | 8 | 1587465619 | | | | tty1 | 749 | 8 | 1587465619 | | | root | pts/0 | 1057 | 7 | 1587465664 | 27.61.217.9 | | root | pts/1 | 1375 | 7 | 1587465846 | 27.61.217.9 | ---------- ------- ------- ------ ------------ --------------
To list all the jobs scheduled by crontab run the following query:
osquery> select command, path from crontab ;You should get the following output:
---------------------------------------------------------------------------------------------------------------------------------------- ------------------- | command | path | ---------------------------------------------------------------------------------------------------------------------------------------- ------------------- | root cd / && run-parts --report /etc/cron.hourly | /etc/crontab | | root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily ) | /etc/crontab | | root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly ) | /etc/crontab | | root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly ) | /etc/crontab | | root if [ -x /usr/share/mdadm/checkarray ] && [ $(date %d) -le 7 ]; then /usr/share/mdadm/checkarray --cron --all --idle --quiet; fi | /etc/cron.d/mdadm |
To find all open ports in your system run the following query:
osquery> select * from listening_ports;You should get the following output:
------ ------ ---------- -------- ------------ ----- -------- ---------------------------------------- --------------- | pid | port | protocol | family | address | fd | socket | path | net_namespace | ------ ------ ---------- -------- ------------ ----- -------- ---------------------------------------- --------------- | 444 | 53 | 6 | 2 | 127.0.0.53 | 13 | 14910 | | 4026531993 | | 729 | 22 | 6 | 2 | 0.0.0.0 | 3 | 16940 | | 4026531993 | | 664 | 3306 | 6 | 2 | 127.0.0.1 | 69 | 15824 | | 4026531993 | | 544 | 6379 | 6 | 2 | 127.0.0.1 | 6 | 15472 | | 4026531993 | | 729 | 22 | 6 | 10 | :: | 4 | 16951 | | 4026531993 | | 544 | 6379 | 6 | 10 | ::1 | 7 | 15473 | | 4026531993 | | 759 | 80 | 6 | 10 | :: | 4 | 17009 | | 4026531993 | | 444 | 53 | 17 | 2 | 127.0.0.53 | 12 | 14909 | | 4026531993 | | 405 | 58 | 255 | 10 | :: | 15 | 16039 | | 4026531993 |
To list top 5 most active processes run the following query:
osquery> select count(pid) as total, name from processes group by name order by total desc limit 5;You should get the following output:
------- --------- | total | name | ------- --------- | 4 | sshd | | 3 | apache2 | | 2 | systemd | | 2 | bash | | 2 | agetty | ------- ---------
Conclusion
In the above tutorial, we learned how to install and use osquery in Debian 10. osquery is very useful tool to find any backdoors, malware, or any zombie process in your system. For more information about osquery, visit the osquery documentation page.
