One of Facebook’s great invention in the technology field is the osquery. This is a relational database used to show the whole operating system based on structured query language. It can be installed Windows, Linux and mac-OS operating systems.

In this guide, we are going to demonstrate how to install, configure and use osquery to monitor your Linux environment. We shall be using Ubuntu 20.04 LTS for this demonstration.

How To Install Osquery on Ubuntu 20.04

Prerequisites

Before we can start installing and using osquery, make sure that you have the following:

  1. Linux host
  2. User with Sudo rights.

Follow the steps below to install Osquery on Ubuntu 20.04;

Step 1 – Update system

Update and upgrade your system.

sudo apt-get update
sudo apt-get upgrade

You might want to reboot your system after the upgrade to make sure that some of the changes have taken effect, e.g Kernel upgrades which require system reboot to change to the latest version.

Step 2 – Configure osquery repository

Configure osquery repo on Ubuntu by running the commands below:

export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80Bsudo 
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY
sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'

Install osquery with the following apt commands:

sudo apt install osquery -y

How to Configure Osquery on Ubuntu 20.04

After a successful installation, you will be required to do some configurations on your system.

We need to install rsyslog to collect logs. We will then be required to give rsyslog access to system logs.

Rsyslog can be installed using the command below:

sudo apt install rsyslog -y

To give rsyslog access to system logs, we will edit the file under /etc/rsyslog.d/osquery.conf and add the following content.

sudo vim /etc/rsyslog.d/osquery.conf

Add the content below to the file:

template(
name="OsqueryCsvFormat"
type="string"
string="%timestamp:::date-rfc3339,csv%,%hostname:::csv%,%syslogseverity:::csv%,%syslogfacility-text:::csv%,%syslogtag:::csv%,%msg:::csv%\n"
)
*.* action(type="ompipe" Pipe="/var/osquery/syslog_pipe" template="OsqueryCsvFormat")

Create a custom oquery configuration file under /etc/osquery/ called osquery.conf and add the content below:

sudo vim /etc/osquery/osquery.conf

Add content below:

{
    "options": {
        "config_plugin": "filesystem",
        "logger_plugin": "filesystem",
        "logger_path": "/var/log/osquery",
        "disable_logging": "false",
        "log_result_events": "true",
        "schedule_splay_percent": "10",
        "pidfile": "/var/osquery/osquery.pidfile",
        "events_expiry": "3600",
        "database_path": "/var/osquery/osquery.db",
        "verbose": "false",
        "worker_threads": "2",
        "enable_monitor": "true",
        "disable_events": "false",
        "disable_audit": "false",
        "audit_allow_config": "true",
        "host_identifier": "hakase-labs",
        "enable_syslog": "true",
        "syslog_pipe_path": "/var/osquery/syslog_pipe",
        "force": "true",
        "audit_allow_sockets": "true",
        "schedule_default_interval": "3600"
    },


    "schedule": {
        "crontab": {
            "query": "SELECT * FROM crontab;",
            "interval": 300
        },
        "system_info": {
            "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
            "interval": 3600
        },
        "ssh_login": {
            "query": "SELECT username, time, host FROM last WHERE type=7",
            "interval": 360
        }
    },

    "decorators": {
        "load": [
            "SELECT uuid AS host_uuid FROM system_info;",
            "SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
        ]
    },

    "packs": {
        "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf"
    }
}

We can now start and enable osquery service.

sudo systemctl start osqueryd
sudo systemctl enable osqueryd

Restart rsyslog service:

sudo systemctl restart rsyslog

You can check and confirm that osquery service is up and running

sudo systemctl status osqueryd

If running you should get an output similar to one below:

How to use Osquery on Linux

In the next steps, we shall demonstrate on how to gain access to osquery’s interactive shell and get the metrics.

You should know that osquery is a relational database that gets system metrics from rsyslog and saves the data in tables, where users can now run queries aganist them and get the metrics. It used MySQL syntax.

To gain interactive shell access to osquery, run the command below:

$ osqueryi

You can get help from the osquery shell by issuing the .help command.

Getting Tables

You might want to list the existing tables to know what to look for and where.

The command used in this case is:

osquery> .tables

Get information about users

Use the command below to get the information about users existing on the server. Some of the expected output from the users table is username, directory, description, shell assigned etc.

osquery> select * from users;

You can filter the specific details you want to extract from the users table, for example, you might be concerned about the usernames on the server.

osquery> select username from users;

Check system information

You can get the details of the system from the .system_info table.

osquery> select * from system_info;

Get information about your memory

You can get the information about your memory i.e total memory, current memory consumption and more.

osquery> select * from memory_info;

Get network statistics for an interface

You can also get the network statistics for the available interface from .interface_details table.

osquery> select * from interface_details;

Monitor resource utilization

As an administrator, you need to be aware of which services are taking much of your resources such as cpu and memory.

You can check the top 5 CPU intensive services by running the query below:

SELECT pid, uid, name, ROUND((
  (user_time + system_time) / (cpu_time.tsb - cpu_time.itsb)
) * 100, 2) AS percentage
FROM processes, (
SELECT (
  SUM(user) + SUM(nice) + SUM(system) + SUM(idle) * 1.0) AS tsb,
  SUM(COALESCE(idle, 0)) + SUM(COALESCE(iowait, 0)) AS itsb
  FROM cpu_time
) AS cpu_time
ORDER BY user_time+system_time DESC
LIMIT 5;

For memory consumption, run the command below:

SELECT pid, name, ROUND((total_size * '10e-7'), 2) AS used FROM processes ORDER BY total_size DESC LIMIT 5;

We have established that we can view almost all the necessary metrics and details of our system using osquery. I hope this helps you out there as s sysadmin. Cheers!