How to Monitor Linux Server Security with Osquery

Osquery is an open-source security threat monitoring tool developed by Facebook. It is used for querying system information including system version, kernel information, running processes, memory information, listening ports, login users, and more. Osquery helps system administrators to write SQL queries to identify, probe, and eliminate various types of threats. It supports several operating systems including Windows, Linux, FreeBSD, and macOS.

In this post, we will explain how to install and use Osquery on Ubuntu 20.04.

Step 1 – Install Osquery

By default, the Osquery package is not included in the Ubuntu default repository, so you will need to add the Osquery repository to your system.

First, install required dependencies using the following command:

apt-get install gnupg2 software-properties-common wget unzip -y

Next, add the Osquery GPG key with the following command:

export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY

Next, add the Osquery repository to APT using the following command:

add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'

Once the repository is added, update the repository and install Osquery with the following command:

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

Step 2 – Connect to Osquery Console

Osquery provides an interactive shell to execute queries and explore the current state of your operating system.

You can connect to the Osquery shell using the following command:

osqueryi

Once you are connected, you should get the following output:

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

Next, display the default settings of Osquery using the following command:

osquery> .show

You should see the following output:

osquery - being built, with love.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
osquery 4.9.0
using SQLite 3.35.5

General settings:
     Flagfile: 
       Config: filesystem (/etc/osquery/osquery.conf)
       Logger: filesystem (/var/log/osquery/)
  Distributed: tls
     Database: ephemeral
   Extensions: core
       Socket: /root/.osquery/shell.em

Shell settings:
         echo: off
      headers: on
         mode: pretty
    nullvalue: ""
       output: stdout
    separator: "|"
        width: 

Non-default flags/options:
  database_path: /root/.osquery/shell.db
  disable_database: true
  disable_events: true
  disable_logging: true
  disable_watchdog: true
  extensions_socket: /root/.osquery/shell.em
  hash_delay: 0
  logtostderr: true
  stderrthreshold: 0

Osquery has a lot of tables available for query. You can list them with the following command:

osquery> .tables

Sample output:

  => acpi_tables
  => apparmor_events
  => apparmor_profiles
  => apt_sources
  => arp_cache
  => atom_packages
  => augeas
  => authorized_keys
  => azure_instance_metadata
  => azure_instance_tags
  => block_devices
  => bpf_process_events
  => bpf_socket_events
  => carbon_black_info
  => carves
  => chrome_extension_content_scripts
  => chrome_extensions
  => cpu_time
  => cpuid
  => crontab
  => curl
  => curl_certificate
  => deb_packages
  => device_file
  => device_hash
  => device_partitions
  => disk_encryption
  => dns_resolvers
  => docker_container_fs_changes
  => docker_container_labels
  => docker_container_mounts
  => docker_container_networks
  => docker_container_ports 

Osquery has several modes to display the query output. Here, we will use line mode to display the output.

To set line mode, run the following command:

osquery> .mode line

Step 3 – How to Use Osquery

To get information about your running operating system, run:

osquery> SELECT * FROM system_info;

Sample output:

          hostname = ubuntu2004
              uuid = a83cffe2-50f4-4fea-9ef4-423853fdc122
          cpu_type = x86_64
       cpu_subtype = 6
         cpu_brand = QEMU Virtual CPU version 2.5+
cpu_physical_cores = 1
 cpu_logical_cores = 1
     cpu_microcode = 0x1
   physical_memory = 2084278272
   hardware_vendor = QEMU
    hardware_model = Standard PC (i440FX + PIIX, 1996)
  hardware_version = pc-i440fx-bionic
   hardware_serial = 
      board_vendor = 
       board_model = 
     board_version = 
      board_serial = 
     computer_name = ubuntu2004
    local_hostname = ubuntu2004

To filter the system information output, run:

osquery> SELECT hostname, cpu_type, physical_memory, hardware_vendor, hardware_model FROM system_info;

Sample output:

       hostname = ubuntu2004
       cpu_type = x86_64
physical_memory = 2084278272
hardware_vendor = QEMU
 hardware_model = Standard PC (i440FX + PIIX, 1996)

To display your operating system version, run:

osquery> SELECT * FROM os_version;

Sample output:

         name = Ubuntu
      version = 20.04 LTS (Focal Fossa)
        major = 20
        minor = 4
        patch = 0
        build = 
     platform = ubuntu
platform_like = debian
     codename = focal
         arch = x86_64

To display your kernel information, run:

osquery> SELECT * FROM kernel_info;

Sample output:

  version = 5.4.0-29-generic
arguments = ro net.ifnames=0 biosdevname=0 console=ttyS0 console=tty0
     path = /boot/vmlinuz-5.4.0-29-generic
   device = UUID=29a0b164-1ba1-45a7-b23a-cdb98f23edbc

To display all listening ports with the service name and PID, run:

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';

Sample output:

 name = unbound
 port = 53
  pid = 3407

 name = sshd
 port = 22
  pid = 649

 name = unbound
 port = 953
  pid = 3407

 name = darkstat
 port = 666
  pid = 6100

 name = darkstat
 port = 667
  pid = 6109

 name = apt-cacher-ng
 port = 3142
  pid = 4071

 name = ntpd
 port = 123
  pid = 483

To display information for the Apache package, run:

osquery> SELECT name, version FROM deb_packages WHERE name="apache2";

Sample output:

+---------+-------------------+
| name    | version           |
+---------+-------------------+
| apache2 | 2.4.41-4ubuntu3.4 |
+---------+-------------------+

To display system memory information, run:

osquery> SELECT * FROM memory_info;

Sample output:

+--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+
| memory_total | memory_free | buffers  | cached    | swap_cached | active    | inactive  | swap_total | swap_free |
+--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+
| 2084278272   | 1358233600  | 44519424 | 520896512 | 0           | 406622208 | 222449664 | 495411200  | 495411200 |
+--------------+-------------+----------+-----------+-------------+-----------+-----------+------------+-----------+

To display information of all network interfaces, run:

osquery> SELECT * FROM interface_addresses;

Sample output:

+-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+
| interface | address                       | mask                                    | broadcast     | point_to_point | type    |
+-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+
| lo        | 127.0.0.1                     | 255.0.0.0                               |               | 127.0.0.1      | unknown |
| eth0      | 69.87.221.220                 | 255.255.255.0                           | 69.87.221.255 |                | unknown |
| lo        | ::1                           | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff |               |                | unknown |
| eth0      | fe80::200:45ff:fe57:dddc%eth0 | ffff:ffff:ffff:ffff::                   |               |                | unknown |
+-----------+-------------------------------+-----------------------------------------+---------------+----------------+---------+

To check system uptime, run:

osquery> SELECT * FROM uptime;

Sample output:

+------+-------+---------+---------+---------------+
| days | hours | minutes | seconds | total_seconds |
+------+-------+---------+---------+---------------+
| 0    | 1     | 55      | 5       | 6905          |
+------+-------+---------+---------+---------------+

To list all users whose UID is greater than 1000, run:

osquery> SELECT * FROM users WHERE uid>=1000;

Sample output:

+-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+
| uid   | gid   | uid_signed | gid_signed | username | description | directory    | shell             | uuid |
+-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+
| 65534 | 65534 | 65534      | 65534      | nobody   | nobody      | /nonexistent | /usr/sbin/nologin |      |
| 65534 | 65534 | 65534      | 65534      | nobody   | nobody      | /            | /usr/sbin/nologin |      |
+-------+-------+------------+------------+----------+-------------+--------------+-------------------+------+

To check last logged in users, run:

osquery> SELECT * FROM last;

Sample output:

+----------+-------+------+------+------------+-----------------+
| username | tty   | pid  | type | time       | host            |
+----------+-------+------+------+------------+-----------------+
| root     | pts/0 | 1013 | 7    | 1629008887 | 106.213.193.155 |
| root     | pts/1 | 3372 | 7    | 1629010656 | 106.213.193.155 |
| root     | pts/2 | 4158 | 7    | 1629013021 | 106.213.193.155 |
+----------+-------+------+------+------------+-----------------+

To display all logged in users, run:

osquery> SELECT * FROM logged_in_users;

Sample output:

+-----------+----------+------------+------------------+------------+------+
| type      | user     | tty        | host             | time       | pid  |
+-----------+----------+------------+------------------+------------+------+
| boot_time | reboot   | ~          | 5.4.0-29-generic | 1629008369 | 0    |
| init      |          | /dev/tty1  |                  | 1629008378 | 491  |
| init      |          | /dev/ttyS0 |                  | 1629008378 | 484  |
| login     | LOGIN    | ttyS0      |                  | 1629008378 | 484  |
| login     | LOGIN    | tty1       |                  | 1629008378 | 491  |
| runlevel  | runlevel | ~          | 5.4.0-29-generic | 1629008383 | 53   |
| user      | root     | pts/0      | 106.213.193.155  | 1629008887 | 1013 |
| user      | root     | pts/1      | 106.213.193.155  | 1629010656 | 3372 |
| user      | root     | pts/2      | 106.213.193.155  | 1629013021 | 4158 |
+-----------+----------+------------+------------------+------------+------+

Conclusion

In the above guide, we explained how to install and use Osquery to get data from the operating system by running SQL-based queries. It is a very useful and easy-to-use tool to find backdoors, malware, zombie processes, and more. Get started with Osquery on your VPS from Atlantic.Net today!