How to install osquery on Debian 10

How to install osquery on Debian 10

osquery is a free and open source tool developed by Facebook that can be used to query information related to the operating system, including memory usage, installed software packages, process information, user login, listening port, etc. It can run on multiple operating systems such as Windows, Linux, FreeBSD and MacOS. It is a very useful tool for various use cases and can troubleshoot performance and operational issues. It comes with many tools to help you perform OS analysis and monitoring.

In this tutorial, we will learn how to install and use osquery on Debian 10.

prerequisites

  • A server running Debian 10.
  • The root password is configured on your server.

getting Started

Before starting, it is best to update the system software package to the latest version. You can use the following command to update all packages:

apt-get update -yapt-get upgrade -y

After updating all software packages, restart the system to apply the changes.

Install osquery

By default, osquery is not available in the Debian 10 default repository. Therefore, you will need to add the osquery repository to the system.

First, download and add the GPG key using the following command:

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B

Next, add the osquery repository using the following command:

apt-get install software-properties-common -yadd-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'

Next, update the repository and install osquery using the following command:

apt-get update -yapt-get install osquery -y

After the installation is complete, use the following command to start the osquery service:

osqueryctl start osqueryd

You can also use the following command to verify the status of osquery:

osqueryctl status osqueryd

You 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

Use osquery

osquery comes with three useful components osqueryi, osqueryd and osqueryctl. osqueryi is an osquery interactive shell and does not communicate with the daemon. You can use the shell to perform queries and browse the current state of the operating system. osqueryd is a host monitoring daemon that can be used to plan queries and record OS state changes. osqueryctl is a help script for testing configuration.

You can run the following command to connect to the osquery shell:

osqueryi

You should get the following output:

Using a virtual database. Need help, type '.help'

Next, run the .help command to view all the options available for osquery:

osquery> .help

You 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 many tables available for query. You can use the following command to list all tables:

osquery> .table

You 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 use the above table with osquery to find various system information.

Monitoring system with osquery

You can use osquery to monitor memory usage, process information, disk space, logged in users, and more.

First, use the following command to start the osquery shell:

osqueryi

Next, you can use the following command to get the system hostname, cpu core and physical memory information:

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 obtain information about the ssh_config file, run the following query:

osquery> select * from ssh_configs;

You should get the following output:

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 the 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 the 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 a list of currently logged 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 the 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 the system, run the following query:

osquery> select * from load_average;

You should get the following output:

+--------+----------+
| period | average  |
+--------+----------+
| 1m     | 0.000000 |
| 5m     | 0.000000 |
| 15m    | 0.000000 |
+--------+----------+
osquery> 

To get a list of the first five software packages in the 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 the processes running in the 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 previous login names, 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 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 the 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 the five 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  |
+-------+---------+

in conclusion

In the above tutorial, we learned how to install and use osquery in Debian 10. osquery is a very useful tool that can be used to find any backdoors, malware or any zombie process in the system. For more information about osquery, please visit osquery Documentation page.

Source

Sidebar