How To Monitor Your System Security with osquery on Ubuntu 16.04

Introduction

osquery is an open-source security tool that takes an operating system and turns it into one giant database, with tables that you can query using SQL-like statements. With these queries, you can monitor file integrity, check on the status and configuration of the firewall, perform security audits of the target server, and more.

It’s a cross-platform application with support for recent versions of macOS, Windows 10, CentOS, and Ubuntu. It’s offically described as an “SQL-powered operating system instrumentation, monitoring, and analytics” framework, and originated from Facebook.

With osquery, you can run commands like select * from logged_in_users ; against your server, and get back a result like this:

Output
+-----------+----------+-------+------------------+------------+------+
| type      | user     | tty   | host             | time       | pid  |
+-----------+----------+-------+------------------+------------+------+
| login     | LOGIN    | ttyS0 |                  | 1483580429 | 1546 |
| login     | LOGIN    | tty1  |                  | 1483580429 | 1549 |
| user      | root     | pts/0 | 24.27.68.82      | 1483580584 | 1752 |
| user      | sammy    | pts/1 | 11.11.11.11      | 1483580770 | 4057 |
| boot_time | reboot   | ~     | 4.4.0-57-generic | 1483580419 | 0    |
| runlevel  | runlevel | ~     | 4.4.0-57-generic | 1483580426 | 53   |
+-----------+----------+-------+------------------+------------+------+

If this appeals to you, you’ll love using osquery as a system security monitoring and intrusion detection tool for your server.

Installing osquery gives you access to the following components:

• osqueryi: The interactive osquery shell, for performing ad-hoc queries.
• osqueryd: A daemon for scheduling and running queries in the background.
• osqueryctl: A helper script for testing a deployment or configuration of osquery. It can also be used instead of the operating system’s service manager to start/stop/restart osqueryd.

osqueryi and osqueryd are independent tools. They don’t communicate, and you can use one without the other. Most of the flags and options needed to run each are the same, and you can launch osqueryi using osqueryd’s configuration file so you can customize the environment without using lots of command-line switches.

In this tutorial, you’ll:

• Install osquery.
• Configure aspects of the operating system, like Rsyslog, that osquery needs to function properly.
• Set up a configuration file that can be used by both osqueryi and osqueryd.
• Work with osquery packs, which are groups of predefined queries you can add to a schedule.
• Perform ad-hoc queries using osqueryi to look for security issues.
• Start the daemon so that it can run queries automatically.

Logs generated by osqueryd, the daemon, are intended to be shipped off to external logging endpoints that require additional expertise to set up and use properly. This tutorial will not cover that configuration, but you’ll learn how to configure and run the daemon and save results locally.

Prerequisites

To complete this tutorial, you’ll need to have the following in place:

• An Ubuntu 16.04 server, configured with a non-root user with sudo privileges and a firewall. Follow the Initial Setup Guide for Ubuntu 16.04 to set this up.

You should also have a basic understanding of SQL and a fundamental knowledge of Linux system security.

Step 1 – Installing osquery on the Server

You can install osquery by compiling it from source, or by using the package manager. Since there’s no installable package in the official Ubuntu repository, you’ll have to add the project’s official Ubuntu repository to the system.

First, add the repository’s public key:

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

Then add the repository:

sudo add-apt-repository "deb [arch=amd64] https://osquery-packages.s3.amazonaws.com/xenial xenial main"

Update the package database:

sudo apt-get update

Finally, install osquery:

sudo apt-get install osquery

Out of the box, osquery is not incredibly useful; it is not a plug-and-play application. Whether you intend to use the interactive shell or the daemon, you have to pass some flags and options, either from the command line or via a configuration file. To view the flags and options available to the daemon, type:

osqueryd --help

The output will include dozens of command-line flags and configuration options. Shown below is a partial output from the test server used for this article.

Output
osquery 2.1.2, your OS as a high-performance relational database
Usage: osqueryd [OPTION]... 

osquery command line flags:

    --flagfile PATH                           Line-delimited file of additional flags
    --config_check                            Check the format of an osquery config and exit
    --config_dump                             Dump the contents of the configuration
    --config_path VALUE                       Path to JSON config file
    --config_plugin VALUE                     Config plugin name
    --config_tls_endpoint VALUE               TLS/HTTPS endpoint for config retrieval
    --config_tls_max_attempts VALUE           Number of attempts to retry a TLS config/enroll request
    --config_tls_refresh VALUE                Optional interval in seconds to re-read configuration
    --daemonize                               Run as daemon (osqueryd only)

...

...

osquery configuration options (set by config or CLI flags):

    --audit_allow_config                      Allow the audit publisher to change auditing configuration
    --audit_allow_sockets                     Allow the audit publisher to install socket-related rules
    --audit_persist                           Attempt to retain control of audit
    --aws_access_key_id VALUE                 AWS access key ID
    --aws_firehose_period VALUE               Seconds between flushing logs to Firehose (default 10)
    --aws_firehose_stream VALUE               Name of Firehose stream for logging
    --aws_kinesis_period VALUE                Seconds between flushing logs to Kinesis (default 10)
    --aws_kinesis_random_partition_key        Enable random kinesis partition keys
    --aws_kinesis_stream VALUE                Name of Kinesis stream for logging
    --aws_profile_name VALUE                  AWS profile for authentication and region configuration
    --aws_region VALUE                        AWS region

To see additional command-line flags only available to the interactive shell, type:

osqueryi --help

Running osqueryi is the easiest method for listing and querying the osquery tables available out of the box. As an example, launch it using the following command:

osqueryi --verbose

This will place you in an interactive shell, and you’ll see an output similar to this:

Output
I0105 01:52:54.987584  4761 init.cpp:364] osquery initialized [version=2.1.2]
I0105 01:52:54.987808  4761 extensions.cpp:351] Could not autoload extensions: Failed reading: /etc/osquery/extensions.load
I0105 01:52:54.987944  4761 extensions.cpp:364] Could not autoload modules: Failed reading: /etc/osquery/modules.load
I0105 01:52:54.988209  4761 init.cpp:606] Error reading config: config file does not exist: /etc/osquery/osquery.conf
I0105 01:52:54.988334  4761 events.cpp:886] Error registering subscriber: socket_events: Subscriber disabled via configuration
I0105 01:52:54.993973  4763 interface.cpp:307] Extension manager service starting: /home/sammy/.osquery/shell.em
Using a virtual database. Need help, type '.help'
osquery>

Because of the error and information messages in the output, it’s obvious that all parts of osquery are not functioning correctly. Certain queries, like select * from yara ;w ill return nothing, showing that the table has not been populated with data.

Other queries, like select time, severity, message from syslog ; will return a message like the following, indicating there’s more work we need to do:

Output
W1202 15:44:48.600539  1720 virtual_table.cpp:492] Table syslog is event-based but events are disabled
W1202 15:44:48.600587  1720 virtual_table.cpp:499] Please see the table documentation: https://osquery.io/docs/#syslog

We’ll make some modifications to our server’s configuration to resolve this issue.

Exit the console by typing:

.exit

In the next section, we’ll modify aspects of the operating system that osquery needs to function properly.

Step 2 – Allowing osquery to Access the System Log

In this step, we’ll modify the operating system’s syslog application to allow osquery to consume and query the system log. On Ubuntu 16.04, that means modifying the Rsyslog configuration file. And the only modification you need to make is append a few lines of code to the configuration file.

To begin, open the /etc/rsyslog.conf file:

sudo nano /etc/rsyslog.conf

We need to add some lines of configuration that tell Rsyslog what pipe to write to, and which syslog parameters to write to that pipe. By default, the pipe is /var/osquery/syslog_pipe. osquery then populates its syslog table from information written to that pipe.

Append the following lines to the file:

/etc/rsyslog.conf
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")

Save and close the file. To apply the changes, restart the syslog daemon:

sudo systemctl restart rsyslog

Now let’s create a configuration file that sets up some default options and schedules some queries.

Step 3 – Creating an osquery Configuration File

Creating a configuration file makes it easier to run osqueryi. Instead of having to pass a lot of command line options, osqueryi can read those options from a configuration file located in /etc/osquery/osquery.conf. And, of course, the configuration file will also be available to the daemon.

The configuration file also holds queries that need to be executed on schedule. However, most of the queries that you can run on schedule are shipped as what are called packs. Packs are files located in the /usr/share/osquery/packs directory.

osquery doesn’t come with a configuration file, but there’s a sample configuration file that you may copy over to /etc/osquery and modify. However, that file does not have all the options you need to run it on a Linux distribution like Ubuntu, so we’ll create our own.

There are three sections to the configuration file:

• A list of daemon options and feature settings. These can also be read by osqueryi.
• A list of scheduled queries to run and when they should run.
• A list of packs to be used to conduct more specific scheduled queries.

The following is a list of the options we’ll be using for our configuration file, what they mean and the values we’ll be setting them to. This list of options is sufficient for running osqueryi and osqueryd on Ubuntu 16.04 annd other Linux distributions.

• config_plugin: Where do want osquery to read its configurations from. By default, they are read from a file on disk, so the value for this is filesystem.
• logger_plugin: Specifies where osquery should write results of scheduled queries. Once again, we’ll use filesystem.
• logger_path: This is the path to the log directory where you’ll find files containing info, warnings, errors and results of scheduled queries. By default, this is /var/log/osquery.
• disable_logging: By setting the value of this to false we enable logging.
• log_result_events: By setting this to true, each line in the results logs will represent a state change.
• schedule_splay_percent: This tells osquery that when a large number of queries are scheduled at the same interval, to run them spread out in order to limit any performance impact on the server. Default value is 10 , which is a percentage.
• pidfile: Where to write the process id of the osquery daemon. The default is /var/osquery/osquery.pidfile.
• events_expiry: In seconds, how long to hold subscriber results in the osquery backing store. Out of the box, this is set to 3600.
• database_path: The path to osquery’s database. We’ll use the default, which is /var/osquery/osquery.db.
• verbose: With logging enabled, this is used to enable or disable verbose informational messages. We’ll set this to false.
• worker_threads: Number of work dispatch threads used to process queries. This is set to 2 by default, so we’ll be leaving it at that.
• enable_monitor: Used to enable or disable the schedule monitor. We’ll enable it, so the value will be true.
• disable_events: Used to regulate osquery’s publish/subscribe system. We need this enabled, so the value here will be false.
• disable_audit: Used to disable receiving events from the operating system’s audit subsystem. We need it enabled, so the value used here will be false.
• audit_allow_config: Allow the audit publisher to change the auditing configuration. Default is true.
• audit_allow_sockets: This allows the audit publisher to install socket-related rules. The value will be true.
• host_identifier: This is used to identify the host running osquery. When aggreagting results from multiple servers, it helps to easily determine what server a particular log entry is coming from. The value is either hostname or uuid. Out of the box, it’s set to hostname, so we’ll use that value.
• enable_syslog: In order for osquery to consume syslog information, this has to be set to true.
• schedule_default_interval: When a scheduled query’s interval is not set, use this value. It’s in seconds, and we’ll set it to 3600.

You’ve already seen how to view all the command line flags and configuration options avialable to osqueryi and osqueryd, but the above options will be enough to run osquery on this server.

Create and open the configuration file using the following command:

sudo nano /etc/osquery/osquery.conf

The configuration file uses the JSON format. Copy the following content into the file:

{
  "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": "hostname",
    "enable_syslog": "true",
    "audit_allow_sockets": "true",
    "schedule_default_interval": "3600" 
  },

The next part of the configuration file is the scheduling section. Each query is identified by a key or name, which must be unique in the file, followed by the query to run and the interval, in seconds, to run the query. We’ll add a scheduled query that looks at the crontab table every 300 seconds.

Add these lines to the configuration file:

  "schedule": {
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    }
  },

You can write any number of queries that you’d like. Just keep the correct format. If you don’t, the file will fail validation. For example, to add a couple more queries, add these lines:

  "schedule": {
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    },
    "system_profile": {
      "query": "SELECT * FROM osquery_schedule;"
    }, 
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    }
  },

After the scheduled queries, you can add special queries called decorators, which are queries that prepend data to other scheduled queries. The decorator queries shown here will prepend the UUID of the host running osquery and the username of the user to every scheduled query.

Append these lines to the file:

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

Finally, we can point osquery to a list of packs that contain more specific queries. Every installation of osquery comes with a default set of packs located in the /usr/share/osquery/packs directory. One of the packs is for macOS, while the rest are for Linux systems. Though you can use the packs from their default location, you may also copy them to the /etc/osquery directory.

Add these lines to the file to complete the file.

  "packs": {
     "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
     "incident-response": "/usr/share/osquery/packs/incident-response.conf",
     "it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
     "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
  }
}

Note the closing curly brace at the end, which matches the opening curly brace on the first line of the file. Your completed configuration file should look like this:

{
  "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": "hostname",
    "enable_syslog": "true",
    "audit_allow_sockets": "true",
    "schedule_default_interval": "3600" 
  },
  "schedule": {
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    },
    "system_profile": {
      "query": "SELECT * FROM osquery_schedule;"
    }, 
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    }
  },
  "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",
     "incident-response": "/usr/share/osquery/packs/incident-response.conf",
     "it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
     "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
  }
}

Save and close the file, then validate it using the following command:

sudo osqueryctl config-check

The output should look like this:

Output
I0104 11:11:46.022858 24501 rocksdb.cpp:187] Opening RocksDB handle: /var/osquery/osquery.db

If there’s an error, the output will indicate the location of the error so you can fix it.

Once you have a valid configuration file, you can move on to configuring the osquery pack required for file integrity monitoring.

Step 4 – Setting Up the osquery File Integrity Monitoring Pack

Keeping a watchful eye on the integrity of the files on your server is a critical aspect of monitoring its system security. To this end, osquery offers a ready solution.

The packs you added to the configuration in the previous section ship out of the box. In this section, we’ll add one more pack to the list, which will contain the query and directives that will be used for file integrity monitoring. For this exercise, we’ll call the file fim.conf.

Create this file and open it in your editor:

sudo nano /usr/share/osquery/packs/fim.conf

We’ll create a pack that will monitor for file events in the /home, /etc, and /tmp directories every 300 seconds. The complete setup for the pack file is shown in the following file listing. Copy it into the file.

/usr/share/osquery/packs/fim.conf
{
  "queries": {
    "file_events": {
      "query": "select * from file_events;",
      "removed": false,
      "interval": 300
    }
  },
  "file_paths": {
    "homes": [
      "/root/.ssh/%%",
      "/home/%/.ssh/%%"
    ],
      "etc": [
      "/etc/%%"
    ],
      "home": [
      "/home/%%"
    ],
      "tmp": [
      "/tmp/%%"
    ]
  }
}

Save and close the file.

To make the new file and its rules available to osquery, reference it in the pack list at the end of /etc/osquery/osquery.conf. Open the file for editing:

sudo nano /etc/osquery/osquery.conf

Then modify the packs section to include the new file:

/etc/osquery/osquery.conf
...

"packs": {
     "fim": "/usr/share/osquery/packs/fim.conf",
     "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
     "incident-response": "/usr/share/osquery/packs/incident-response.conf",
     "it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
     "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
  }

Save and close the file. And just to be sure that you didn’t make a mistake in the file, validate it again:

sudo osqueryctl config-check

Now let’s start using osqueryi to query the system.

Step 5 – Using osqueryi to Perform Ad-hoc Security Checks

There are many places where osquery comes in handy. In this section, you’ll perform various security checks on your system using osqueryi, the interactive shell. Keep in mind that at this point, we’ve still not started the osquery daemon. And that’s the beauty of osquery - you can run queries using osqueryi even if the daemon is not active, while still using the configuration file we built to configure the environment.

To launch osquery with a configuration file, type:

sudo osqueryi --config_path /etc/osquery/osquery.conf --verbose

Let’s start with the basic security checks and work our way up from there. For example, who else other than you is logged into the system now? Find out with this query:

select * from logged_in_users ;

The output should look like this:

Output
+-----------+----------+-------+------------------+------------+------+
| type      | user     | tty   | host             | time       | pid  |
+-----------+----------+-------+------------------+------------+------+
| boot_time | reboot   | ~     | 4.4.0-57-generic | 1483580419 | 0    |
| runlevel  | runlevel | ~     | 4.4.0-57-generic | 1483580426 | 53   |
| login     | LOGIN    | ttyS0 |                  | 1483580429 | 1546 |
| login     | LOGIN    | tty1  |                  | 1483580429 | 1549 |
| user      | root     | pts/0 | 11.11.11.11      | 1483580584 | 1752 |
| user      | sammy    | pts/1 | 11.11.11.11      | 1483580770 | 4057 |
+-----------+----------+-------+------------------+------------+------+

In this output, there are two real user accounts logged into the machine, and they’re both from the same IP address. That IP address should be a known IP address. If it’s not, you should investigate where that login came from.

The previous query tells you who’s logged in now, but what about previous logins? You can find out by querying the last table, like this:

select * from last ;

The output indicated nothing out of the ordinary, so no other person has logged into the machine recently:

Output
+----------+-------+------+------+------------+------------------+
| username | tty   | pid  | type | time       | host             |
+----------+-------+------+------+------------+------------------+
| reboot   | ~     | 0    | 2    | 1483580419 | 4.4.0-57-generic |
| runlevel | ~     | 53   | 1    | 1483580426 | 4.4.0-57-generic |
|          | ttyS0 | 1546 | 5    | 1483580429 |                  |
| LOGIN    | ttyS0 | 1546 | 6    | 1483580429 |                  |
|          | tty1  | 1549 | 5    | 1483580429 |                  |
| LOGIN    | tty1  | 1549 | 6    | 1483580429 |                  |
| root     | pts/0 | 1752 | 7    | 1483580584 | 11.11.11.11      |
| sammy    | pts/1 | 4057 | 7    | 1483580770 | 11.11.11.11      |
+----------+-------+------+------+------------+------------------+

Is the firewall configured and activated? Is the firewall still running? If in doubt, run this query to find out:

select * from iptables ;

If there’s no output, then it means the IPTables firewall has not been configured. For an Internet-facing server that’s not a good thing, so you better configure the firewall.

You can run the previous command, modified to filter on particular columns, like this:

select chain, policy, src_ip, dst_ip from iptables ;

That query should give an output like the following. Look for any unusual source and destination IP addresses that you did not configure:

Output
+---------+--------+---------+-----------+
| chain   | policy | src_ip  | dst_ip    |
+---------+--------+---------+-----------+
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 127.0.0.0 |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| INPUT   | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| FORWARD | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| FORWARD | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| OUTPUT  | ACCEPT | 0.0.0.0 | 0.0.0.0   |
| OUTPUT  | ACCEPT | 0.0.0.0 | 0.0.0.0   |
+---------+--------+---------+-----------+

What type of jobs are scheduled in crontab? Did you schedule them? This query will help you find malware that have been scheduled to run at specific intervals:

select command, path from crontab ;

And the output should take this form. Any command in there that looks suspicious demands further investigation:

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              |
| root test -x /etc/cron.daily/popularity-contest && /etc/cron.daily/popularity-contest --crond                                          | /etc/cron.d/popularity-contest |
+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+

Are there files on the system that are setuid-enabled? There are quite a few on any Ubuntu 16.04 server, but which ones are they, and are there any that are not supposed to be on the system? Answers to these question will help you detect backdoored binaries. Run this query periodically and compare its results against older results so that you can keep an eye on any additions. That query is:

select * from suid_bin ;

A partial output from this query might look like this:

Output
+-------------------------------+----------+-----------+-------------+
| path                          | username | groupname | permissions |
+-------------------------------+----------+-----------+-------------+
| /bin/ping6                    | root     | root      | S           |
| /bin/su                       | root     | root      | S           |
| /bin/mount                    | root     | root      | S           |
| /bin/umount                   | root     | root      | S           |
| /bin/fusermount               | root     | root      | S           |
| /bin/ntfs-3g                  | root     | root      | S           |
| /bin/ping                     | root     | root      | S           |
| /sbin/mount.ntfs-3g           | root     | root      | S           |
| /sbin/mount.ntfs              | root     | root      | S           |
| /sbin/unix_chkpwd             | root     | shadow    | G           |
| /sbin/pam_extrausers_chkpwd   | root     | shadow    | G           |
| /usr/bin/chage                | root     | shadow    | G           |
| /usr/bin/locate               | root     | mlocate   | G           |
| /usr/bin/chfn                 | root     | root      | S           |
| /usr/bin/chsh                 | root     | root      | S           |
| /usr/bin/newuidmap            | root     | root      | S           |
| /usr/bin/write                | root     | tty       | G           |
| /usr/bin/mlocate              | root     | mlocate   | G           |
| /usr/bin/at                   | daemon   | daemon    | SG          |
| /usr/bin/sg                   | root     | root      | S           |

To view a list of loaded kernel modules, run this query:

select name, used_by, status from kernel_modules where status="Live" ;

This is another query you’ll want to run periodically and compare its output against older results to see if anything’s changed.

Yet another method that will help you find backdoors on the server is to run a query that lists all the listening ports. To do that, execute this query:

select * from listening_ports ;

On a fresh server with only SSH running on port 22, the output would look like this:

Output
+-------+------+----------+--------+---------+
| pid   | port | protocol | family | address |
+-------+------+----------+--------+---------+
| 1686  | 22   | 6        | 2      | 0.0.0.0 |
| 1686  | 22   | 6        | 10     | ::      |
| 25356 | 0    | 0        | 0      |         |
+-------+------+----------+--------+---------+

On your server, if the output only includes ports you know the server should be listening on, there’s nothing to worry about. But if there were other ports open, you’ll want to investigate what those ports are.

To look at file activity on the server, run this query:

select target_path, action, uid from file_events ;

The output shows all recent file activity on the server, along with the user id responsible for the activity.

Output
+---------------------------+---------+------+
| target_path               | action  | uid  |
+---------------------------+---------+------+
| /home/sammy/..bashrc.swp  | CREATED | 1000 |
| /home/sammy/..bashrc.swp  | UPDATED | 1000 |
| /home/sammy/..bashrc.swp  | UPDATED | 1000 |
| /home/sammy/.bashrc       | UPDATED | 1000 |
| /home/sammy/..bashrc.swp  | DELETED | 1000 |
| /home/sammy/..bashrc.swp  | CREATED | 1000 |
| /home/sammy/..bashrc.swp  | UPDATED | 1000 |
| /home/sammy/..bashrc.swp  | UPDATED | 1000 |
| /home/sammy/.bashrc       | UPDATED | 1000 |
| /home/sammy/.bashrc       | UPDATED | 1000 |
| /home/sammy/.bashrc       | UPDATED | 1000 |
| /home/sammy/..bashrc.swp  | DELETED |      |
| /etc/test_file.txt        | DELETED |      |
| /home/sammy/.bash_history | UPDATED | 1000 |
| /home/sammy/.bash_history | UPDATED | 1000 |
| /etc/secret_file.md       | CREATED | 0    |
| /etc/secret_file.md       | UPDATED | 0    |
| /etc/secret_file.md       | UPDATED | 0    |
+---------------------------+---------+------+

There are many queries like those that you can run on your server to get an idea of possible security issues.

If you’re unsure about a table’s schema, use the following command to find out:

.schema name-of-table

And you can the list the available tables with:

.tables

There are many more examples in the packs that ship with osquery, and many are designed to run periodically by osqueryd. In the next section, you’ll learn how to start the daemon to run those queries.

Step 6 – Running osqueryd

osqueryd, the daemon, allows osquery to run queries at set intervals. Those queries include the ones you configured in Step 4, the ones in the packs we specified in that step, and in the FIM pack we configured in Step 5. If you haven’t studied the them yet, now is a good time to take a look at the contents of /usr/share/osquery/packs.

Results generated by osqueryd are written to a file called osqueryd.results.log in the /var/log/osquery directory. Out of the box, that file does not exist. It only gets created when the daemon is started and starts generating results.

You can start osqueryd using either systemctl or osqueryctl. Both accomplish the same thing, so it doesn’t matter which one you use. osqueryd will check for the existence of a configuration file when it starts, and alert you if it doesn’t find one. It will remain running without a configuration file, although it won’t do anything useful.

But since you have already set up a configuration file, all you need to do here is start the daemon:

sudo systemctl start osqueryd

Or you can type:

sudo osqueryctl start

In a few minutes after starting the daemon, the size of /var/log/osquery/osqueryd.results.log should increase. You can see that happening by typing and repeating the next command:

ls -lh /var/log/osquery/osqueryd.results.log

The increasing size of the file indicates that the results of the scheduled queries are being written to disk. Unfortunately, osquery does not have an alerting facility like OSSEC, so you can’t see the results of scheduled queries unless you view the results file. You can do that with the tail command, which will continuously stream the last 10 lines of that file to your screen:

sudo tail -f /var/log/osquery/osqueryd.results.log

Press CTRL+C to stop tailing the log.

Longer-term, you would want to ship the query results log to an external analysis platform that you can work with. Viable open source options include Doorman, Zentral, and ElasticSearch.

Conclusion

osquery is a powerful tool, useful for running one-off and scheduled queries using a familiar SQL syntax. osqueryi is the osquery component for writing one-off queries, while osqueryd is for scheduling queries. To make sense of the results of scheduled queries, you need to ship them off to an external log analysis platform. You can find more information about osquery at https://osquery.io/.