How to install and use osquery for monitoring and system security on Ubuntu 16.04

Sam - February 20, 2017

How to install and use osquery for monitoring and system security on Ubuntu 16.04

Tutorials

Introduction

osquery is an open source tool created by Facebook for querying various information about the state of your machines. The tools make low-level operating system analytics and monitoring both performant and intuitive. This includes information like running processes, kernel modules loaded, active user accounts and active network connections. osquery is a flexible tool and can be used for a variety of use cases to troubleshoot performance and operational issues. From a security perspective, it can be used to query your endpoints to detect, investigate, and proactively hunt for various types of threats. For example, if you suspect a malicious process is running on a system, you can query for the process by name or even a filename it has open. osquery exposes an operating system as a high-performance relational database.

In this article we will cover the installation of osquery and detailed instruction to use it for monitoring our system’s security and analytics on Ubuntu 16.04.

Prerequisites

The basic requirement that we need to complete this article is to have an Ubuntu 16,04 server root or sudo privileged user to perform system level tasks.

Supported distributions for osquery package installs are:

  • Ubuntu Xenial 16.04 LTS, Trusty 14.04 LTS, Precise 12.04 LTS

  • CentOS 6.6, 7.1

Now follow the step by step instructions to install and use osquery on Ubuntu 16.04.

Find the perfect Proxy Product.

Proxyrack offers a multiple options to suit most use cases, if you are unsure our 3 Day Trial allows you to test them all.

Security

Residential proxies

Never get blocked, choose your location

View all option available
Vault

Datacenter proxies

Super fast and reliable

View all option available
Try

3 Day Trial

Test all products to find the best fit

View all option available

Step 1 – Installing osquery on Ubuntu 16.04

osquery can installed through OS package management and standalone installers. In Ubuntu 16.04 osquery is available in an apt repository which can be installed by using below commands.

Let’s add the public key of osquery repository in Ubuntu 16.04 by using below command.

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

Next add the repository by issuing command below.

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

Then update your system with latest updates and security patches, so that the newly added repository should be available to install osquery package.

# apt-get update

Once your system is back with latest updates, use below command to install osquery on Ubuntu 16.04 as shown.

# apt-get install osquery

Osquery has been successfully installed with the following directory structure.

Osquery has been successfully installed with the following directory structure.

/etc/osquery/
/usr/share/osquery/osquery.example.conf
/usr/share/osquery/packs/{*}.conf
/var/log/osquery/
/usr/lib/osquery/
/usr/bin/osqueryctl
/usr/bin/osqueryd
/usr/bin/osqueryi

Step 2 – Osquery components

After installation of osquery, we will have access to its three useful components, that is osqueryi, osqueryd and osqueryctl. Whereas osqueryi is the osquery interactive query console/shell. It is completely standalone and does not communicate with a daemon and does not need to run as an administrator. Use the shell to prototype queries and explore the current state of your operating system.

To find all command-line options and flags available to the interactive shell, type below command.

# osqueryi --help

While osqueryd is the host monitoring daemon that allows you to schedule queries and record OS state changes. The daemon aggregates query results over time and generates logs, which indicate state change according to each query. The daemon also uses OS eventing APIs to record monitored file and directory changes, hardware events, network events, and more. To view all its available command line options use below command.

# osqueryd --help

The third component of osquery is osqueryctl which is 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.

# osqueryctl start osqueryd
# osqueryctl status osqueryd

Step 3 – Configuring

osquery

The osquery “configuration” is read from a config plugin. This plugin is a data retrieval method and is set to filesystem by default. The default config plugin, filesystem, reads from a file and optional directory “.d” based on the filename. The included init scripts set the default config path in Linux as follows.

/etc/osquery/osquery.conf and /etc/osquery/osquery.conf.d/

By default 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.

In an osquery configuration JSON, packs are defined as a top-level-key and consist of pack name to pack content JSON data structures. Run the command below to open a new file and put the following contents in it.

# vim /etc/osquery/osquery.conf
{
   "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" 
   },

Next is the scheduling section of the configuration file . 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.

Add below lines to the configuration file to add a scheduled query that looks at the crontab table every 300 seconds along with system profile and system information. You can add as many queries as you want by keeping the same format, so that it may not fail.

"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 that add special queries called decorators, which are queries that prepend data to other scheduled queries.

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

Now come to the final configurations section that is query packs. Configuration supports sets, called packs, of queries that help define your schedule. Packs are distributed with osquery and labeled based on broad categories of information and visibility. Every installation of osquery comes with a default set of packs located in the /usr/share/osquery/packs directory. Though you can use the packs from their default location, you may also copy them to the /etc/osquery directory. Let’s add below lines to the file to complete the osquery configurations.

"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"
   }
 }

That’s it, save and close the configuration file. You complete configurations should be look like as shown in the image below.

To make sure that configuration file is all good, check by using below command.

# osqueryctl config-check
I0218 16:59:17.279341 33681 rocksdb.cpp:195] Opening RocksDB handle: /var/osquery/osquery.db

The output shows that our configuration file is fine and we can move to the next step.

Step 4 – Osquery Access to Syslogs

Each query represents a monitored view of your operating system. The first time a scheduled query runs it logs every row in the resulting table with the “added” action. So, we need to modify the operating system’s syslog application to allow osquery to consume and query the system log by adding 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 in ‘/var/osquery/syslog_pipe’ file, osquery then populates its syslog table from information written to that pipe. Let’s add below the following lines to the file:

# vim /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 configuration file and restart rsyslog service to apply changes using below command.

systemctl restart rsyslog

Step 5 – osquery File Integrity Monitoring Pack

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.The pack that will monitor for file events in the /home, /etc, and /tmp directories every 300 seconds.

To do so, create a new file and put the following contents in it using any of your command line editor.

# vim /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, and then open the ‘osquery.conf’ once again to add the new pack section for monitoring file integrity.

# vim /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 run below command to validate configuration changes.

# osqueryctl config-check
 

Step 6 – Using osqueri

osquery provides two main interfaces to the user, that is osqueryi and osqueryd. osqueryi is a REPL (Read-Evaluate-Print Loop) similar to ‘$ irb’ or ‘$ python’. This lets you input a command and get back an immediate response. We can use this for following few things.

  • One off, spot checking of specific system attributes.

  • Developing new queries.

  • Learning your way around osquery.

You can run queries using ‘osqueryi’ command even if the daemon is not active, while still using the configuration file we built to configure the environment by using below command.

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

Using ‘–verbose’ at the end of the command is good practice as it will you the detailed logs and warnings, if there is any issue with osquery.

osquery uses the sqlite query language for simplicity and descriptiveness. Now we will use some of the following useful commands to check our system’s basic security using its most basic query format as shown below.

> SELECT columns FROM table;

Run below command to find the last command logs.

osquery> select * from last;

To see what type of crontab jobs are scheduled and who has created that. The following query will help you find malware that have been scheduled to run at specific intervals.

osquery> select command, path from crontab ;

Run below command to find all files which has been setui-enabled on the system while they are not supposed to be on the system to detect backdoored binaries.

osquery> select * from suid_bin ;

Run another command to find the backdoors, by finding all listening ports using below query.

osquery> select * from listening_ports ;

To check all file events on your system by using following command.

osquery> select target_path, action, uid from file_events ;

This will help you to identify that which services are running in your system and which are not and you can easily identify any abnormal activity on your system.

There are many other queries and examples , that you can follow and use to find your system security issues and also analyse the performance of your system by viewing the information you gain using osquery stats.

Step 7 – Using osqueryd

Osqueryd is a daemon that allows osquery to run queries at set intervals. To start ‘osquerd’ daemon we can use systemctl or osqueryctl command. While the results generated by osqueryd are written to a file called ‘osqueryd.results.log’ in the /var/log/osquery directory after starting its daemon.

# osqueryctl start osqueryd

After starting its daemon, you can see that its log file has been created in the ‘/var/log/osquer’ directory and you can see its logs, continuously growing.

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

Conclusion

In this article we have learned about the installation and configuration of osquery on Ubuntu 16.04. Osquery is one the best utility developed by Facebook to monitor and analyse your systems security and activities. There are several forms of eventing in osquery along with file modifications and accesses. These range from disk mounts, network reconfigurations, hardware attach and detaching, and process starting. Hopefully you enjoyed this article and interested to learn more about osquery.

Find the perfect Proxy Product.

Proxyrack offers a multiple options to suit most use cases, if you are unsure our 3 Day Trial allows you to test them all.

Security

Residential proxies

Never get blocked, choose your location

View all option available
Vault

Datacenter proxies

Super fast and reliable

View all option available
Try

3 Day Trial

Test all products to find the best fit

View all option available

Proxyrack - October 8, 2022

Social Media Security Report

ArticlesTutorials
Read Article

Daniel - May 9, 2022

How To Create A Custom SEO Tool

Tutorials
Read Article

Daniel - May 9, 2022

Best Proxies For Enterprises

Tutorials
Read Article

Daniel - April 7, 2022

What Is Autofill Magic?

Tutorials
Read Article

Get Started by signing up for a Proxy Product

View Plans