Atlantic.Net Blog

How to Monitor Linux Server Security with Osquery

Hitesh Jethva
by Atlantic.Net (273 posts) under Tutorials, VPS Hosting
0 Comments

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.

Prerequisites

  • An Ubuntu 20.04 server on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 20.04 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your Ubuntu 20.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – 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 3 – 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 4 – 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!

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources