How to Setup osquery to Monitor Security Threat on Ubuntu

osquery monitor security toolOSQUERY is an open source tool developed by Facebook for querying various information related to the state of your machines like running processes, loaded Kernel modules, active logged in users, active listening ports etc. This system instrumentation framework make low-level operating system analytic and monitoring both efficient and intuitive. OSQUERY exposes an operating system as a high-performance virtual relational database that allows you to write SQL queries to explore operating system data and query the endpoints (Windows, OS X, Linux and FreeBSD) to identify, probe, and eliminate various types of threats. OSQUERY is different from traditional HIDS/IPS. Here you need to find out what data is there in the table for query and design queries based on these data. Also, it has built-in functionality to monitor file integrity, audit network connections and processes, and even log hardware device changes in real time. In this article, we will install OSQUERY in ubuntu 16 and check its usages through osqueryi and osqueryd.

1. Install OSQUERY

Add OSQUERY repository information to apt database and update the system.

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

To use add-apt-repository, you need to install python software properties.

 # sudo apt-get install software-properties-common python-software-properties

Add the repository data to apt-database

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

Update the system and install OSQUERY

 # sudo apt-get update
 # sudo apt-get install osquery

You can run OSQUERY either through the interactive shell or as a daemon mode. At first, let us explore how to use it through OSQUERY shell interface and thereafter we will check the daemon mode. To get the osquery shell, type osqueryi in the terminal for querying the osquery tables available out of the box.

 # osqueryi
 osquery>

To list all the logged in users in the system, run the following query.

 osquery> select * from logged_in_users ;

Find all the tables available for query.

 osquery> .tables

To describe the schema of a table, execute the following command.

 osquery> .schema table-name

Exit from the shell

 osquery> .exit

You can also run OSQUERY by passing command line flags. e.g

 # osqueryi --disable_events=false --worker_threads=2  --logger_plugin=filesystem --pidfile=/var/osquery/osquery.pidfile

To find out more command line flags available to OSQUERY interactive shell, execute the following command from the terminal.

 # osqueryi --help

We will explore more queries in a separate section but let us now configure OSQUERY.

2. Configure OSQUERY

It is easier to run OSQUERY with a configuration file. Rather than passing lot of command line parameters to run osquery interactive shell, these parameters can be written in the osquery configuration file. OSQUERY interactive shell (osqueryi) will read these parameters when you run osqueryi. OSQUERY does not come with a configuration file. Rather there is a sample configuration file ( /usr/share/osquery/osquery.example.conf ) that you can copy. It looks for the configuration file in /etc/osquery/osquery.conf . If the configuration file is not present, osqueryi will run with default options. Also, the configuration file is available to OSQUERY daemon.

The OSQUERY configuration file contains following three sections.

→ A list of options and settings read by both osqueryi and osquery daemon ( osqueryd )
→ Query Schedule: the set of SQL queries and intervals.
→ A list of packs that contains more specific/targeted queries. e.g File Change Monitoring pack: categories and paths of monitored files and directories

2.1 Options and settings

The complete list of options and settings can found in this wiki. The options and settings that will be used for this article are described below.

config_plugin: Config plugin name. The type of configuration retrieval, the default filesystem plugin reads a configuration JSON from disk.

logger_plugin: Logger plugin name. The default logger is filesystem. This writes the various log types as JSON to specific file paths. Multiple logger plugins may be used simultaneously, effectively copying logs to each interface. Separate plugin names with a comma when specifying the configuration (--logger_plugin=filesystem,syslog).

logger_path: Directory path for ERROR/WARN/INFO and results logging.

disable_logging: Disable ERROR/WARNING/INFO (called status logs) and query result logging.

schedule_splay_percent: Percent to splay config times. The query schedule often includes several queries with the same interval. It is often not the intention of the schedule author to run these queries together at that interval. But rather, each query should run at about the interval. A default schedule splay of 10% is applied to each query when the configuration is loaded.

pidfile: Path to the daemon pidfile mutex. The file is used to prevent multiple osqueryd processes starting.

events_expiry: Timeout to expire eventing publish subscribe results from the backing-store. This expiration is only applied when results are queried. For example, if --events_expiry=1 then events will only practically exist for a single select from the subscriber. If no select occurs then events will be saved in the backing store indefinitely.

database_path: If using a disk-based backing store, specify a path. osquery will keep state using a "backing store" using RocksDB by default. This state holds event information such that it may be queried later according to a schedule. It holds the results of the most recent query for each query within the schedule. This last-queried result allows query-differential logging.

verbose: Enable verbose informational messages.

worker_threads: Number of work dispatch threads used to process queries.

enable_monitor: Used to enable or disable the schedule monitor.

disable_events: Disable osquery Operating System event publish subscribe APIs. This will implicitly disable several tables that report based on logged events.

disable_audit: Used to disable receiving events from the operating system's audit subsystem.

audit_allow_config: Allow the audit publisher to change the auditing configuration

host_identifier: Field used to identify the host running osquery: hostname, uuid, ephemeral, instance.

enable_syslog: Turn on the syslog ingestion event publisher. This is an 'explicit'-enable because it requires external configuration of rsyslog or syslog-ng.

audit_allow_sockets: This allows the audit publisher to install socket-related rules.

schedule_default_interval: Optionally set the default interval value. This is used if you schedule a query which does not define an interval.

# sudo vi /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" 
  },
 ...........
 ...........
 ...........

2.2 Query Schedule

The schedule section contains queries identified by unique key followed by the interval specifies query frequency in seconds. The configuration for schedule section that we will use looks like below-

 ...........
 ...........
{
"schedule": {
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    },
    "largest_process": {
     "query": "select pid, name, uid, resident_size from processes order by resident_size desc limit 10;",
      "interval": 60
    }, 
 }
...........
...........

The first query looks to the crontab table in every 300 secs and the second query sniff loaded kernel extensions in every 10 seconds. The results of the query are cached on disk using RocksDB. On the first query run, all of the results are stored in RocksDB. On subsequent runs, only result-set-difference (changes) are logged to RocksDB.

Another special queries called decorators are used to prepend data to other scheduled queries. The following decorators will prepend the UUID of the host running osquery and the username of the user to every scheduled query.

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

2.3 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. For example, a "compliance" pack will include queries that check for changes in locked down operating system features and user settings. A "vulnerability management" pack may perform general asset management queries that build event logs around package and software install changes. The default set of packs located in the /usr/share/osquery/packs folder. Add these packs to the configuration 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"
 }

2.4 Create custom pack for file integrity monitoring

The packs that we have added in the previous section ship out of box. Now we want to add our own pack whose task is to monitor file integrity for the folders configured through file_paths. Create a file by the name fims.conf inside /usr/share/osquery/packs/ and add the following section.

# vi /usr/share/osquery/packs/fims.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/%%"
    ]
  }
}

The file_events query is scheduled to collect all of the FIM events that have occurred on any files within the paths specified by file_paths on a five minute interval. At a high level this means events are buffered within osquery and sent to the configured logger every five minutes.

Modify the packs section in osquery.conf to include above file.

# sudo vi /etc/osquery/osquery.conf
.................
"packs": {
     "fim": "/usr/share/osquery/packs/fims.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"
  }

The final OSQUERY configuration file looks 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": "true",
    "worker_threads": "2",
    "enable_monitor": "true",
    "disable_events": "false",
    "disable_audit": "false",
    "audit_allow_config": "true",
    "host_identifier": "hostname",
    "enable_syslog": "true",
    "syslog_pipe_path": "/var/osquery/syslog_pipe",
    "audit_allow_sockets": "true",
    "schedule_default_interval": "3600" 
  },
"schedule": {

"crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300
    },
    "largest_process": {
     "query": "select pid, name, uid, resident_size from processes order by resident_size desc limit 10;",
      "interval": 60
    } 
  },
  "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": {
     "fim": "/usr/share/osquery/packs/fims.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",
     "hardware-monitoring": "/usr/share/osquery/packs/hardware-monitoring.conf"
  }
}

Check the validity of the configuration file.

# sudo osqueryctl config-check

Run OSQUERY interactive shell in verbose mode.

# osqueryi --verbose

3. Configure OSQUERY to consume syslog-ng logs

In this step, we will configure OSQUERY to consume syslogs. Ubuntu has default rsyslog application that generates syslogs but in this article, we will find out how to configure syslog-ng so that logs generated by it are consumed by OSQUERY. The syslog table of OSQUERY queries logs forwarded over a named pipe from a properly configured syslog-ng daemon.  As syslog is ingested into osquery, it is written into the backing store (RocksDB) and made available for querying.  Therefore syslog-ng configuration will create a named pipe through which OSQUERY will consume the logs.

Install syslog-ng

 # sudo apt-get install syslog-ng

Change the group of files created by syslog-ng to syslog. Edit the syslog-ng main configuration file and change the group to adm.

# vi /etc/syslog-ng/syslog-ng.conf
 .........................
 .........................
 options { chain_hostnames(off); flush_lines(0); use_dns(no); use_fqdn(no);
 owner("root");  group("adm"); perm(0640); stats_freq(0);
 bad_hostname("^gconfd$"); threaded(yes);
 };
 .........................
 .........................

Next, create syslog-ng configuration for OSQUERY. The following section shows how to configure syslog-ng for system logs and rewrites these logs in CSV format. Then a template is used to format the message/log so that the log fields blend with syslog table of OSQUERY. Finally the logs are pushed to the named pipe for OSQUERY to consume. Permissions for the pipe must at least allow syslog-ng to read/write, and osquery to read.

 # vi /etc/syslog-ng/conf.d/osquery.conf

 source s_osquery {
 system();
 };
 rewrite r_csv_message {
 set("$MESSAGE", value("CSVMESSAGE") );
 subst("\"","\\\"", value("CSVMESSAGE"), flags(global) );
 };
 template t_csv {
 template("\"${ISODATE}\", \"${HOST}\", \"${LEVEL_NUM}\", \"${FACILITY}\", \"${PROGRAM}\", \"${MESSAGE}\"\n");
 template_escape(no);
 };
 destination d_osquery {
 pipe("/var/osquery/syslog_pipe" template(t_csv));
 };
 log {
 source(s_osquery);
 rewrite(r_csv_message);
 destination(d_osquery);
 };

The osquery configuration file ( /etc/osquery/osquery.conf ) that we have created earlier needs a little adjustment for syslog-ng. Change logger_plugin to syslog from filesystem or keep both.

 "logger_plugin": "syslog" or  "logger_plugin": "filesystem,syslog"

The following options are also needed for syslog ingestion.

 .................
 .................
 "enable_syslog": "true",
 "syslog_pipe_path": "/var/osquery/syslog_pipe",
 "disable_events": "false",
 .................
 .................

So we need to edit only logger_plugin options in the /etc/osquery/osquery.conf that we have created earlier and rest of the three options are already there in the configuration file.

Now restart syslog-ng and check whether the pipe has been created with proper permissions.

# sudo systemctl start syslog-ng

# ls -l /var/osquery/syslog_pipe
prw-r----- 1 root adm 0 Apr 27 14:29 /var/osquery/syslog_pipe

Check the logs flowing through the pipe

 # cat /var/osquery/syslog_pipe
 "2017-04-27T14:42:33+00:00", "ubuntu", "6", "auth", "sshd", "Accepted password for root from 117.227.81.214 port 61849 ssh2"
 "2017-04-27T14:42:33+00:00", "ubuntu", "6", "authpriv", "sshd", "pam_unix(sshd:session): session opened for user root by (uid=0)"
 "2017-04-27T14:42:41+00:00", "ubuntu", "5", "local3", "osqueryi", "severity=0 location=options.cpp:61 message=Verbose logging enabled by config option"
 ...................................
 ...................................

Start OSQUERY interactive shell.

# osqueryi
I0427 14:32:07.981422  3730 options.cpp:61] Verbose logging enabled by config option
I0427 14:32:08.085734  3730 syslog.cpp:97] Successfully opened pipe for syslog ingestion: /var/osquery/syslog_pipe
I0427 14:32:08.101172  3730 file_events.cpp:68] Added file event listener to: /etc/**
....................
I0427 14:32:08.101784  3730 audit.cpp:226] Adding audit rule: syscall=59 action=2 filter=''
....................
I0427 14:32:08.278990  3737 events.cpp:749] Starting event publisher run loop: syslog
I0427 14:32:08.279242  3736 events.cpp:749] Starting event publisher run loop: inotify
I0427 14:32:08.279353  3735 events.cpp:749] Starting event publisher run loop: audit

osquery>

Now query the syslog table.

osqueryi> select * from syslog;

The above query will display the syslogs consumed by osquery through the named pipe /var/osquery/syslog_pipe

4. Threat detection and DFIR through OSQUERYi

In this section, we will explore few basic security checks through osquery interactive shell.

a) Run the following query to find who are the logged in users in the system now.

osquery> select * from logged_in_users ;

b) Find all the previous logins

osquery> select * from last ;

c) To find the firewall rules, run the following query. If the following query does not produce any output that means the firewall is not configured.

osquery> select * from iptables ;

d) To find all the jobs scheduled by crontab, run the following query. With this query, you can find if there is any malware that has been scheduled to run at specific interval.

osquery> select command, path from crontab ;

e) Find the files that are setuid-enabled. By default few files are setuid-enabled in Ubuntu 16 but apart from these, what are the other files that are setuid-enabled. This will help to detect backdoor binaries.

osquery> select * from suid_bin ;

f) Find the list of loaded kernel modules.

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

g) Find all the listening ports to check if there is any backdoor to the system. If there is any open port that you have not configured then you might need to examine the process that opened this port.

osquery> select * from listening_ports ;

h) Find the file activity in the server along with the user responsible for by running the following query.

osquery> select * from  file_events ;

i) Find the top 10 largest processes by resident memory size.

osquery> select pid, name, uid, resident_size from processes order by resident_size desc limit 10;

j) Find all the running processes.

osquery> select * from processes;

k) Find the process count, name for the top 10 most active processes.

osquery> select count(pid) as total, name from processes group by name order by total desc limit 10;

l) Generally, Malware will listen to the ports and forwards reverse shell to the attacker. Run the following query and find the differences with any previous known safe state of your system.

osquery> SELECT DISTINCT process.name, listening.port, listening.address, process.pid FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;

m) Often attacker will delete the malicious binary file after running it in the system. To find such processes execute the following query.

osquery>  SELECT name, path, pid FROM processes WHERE on_disk = 0;

n) Run the following query to find if there is any bash reverse shell forwarded to the attacker.

osquery> SELECT * FROM processes WHERE cmdline LIKE '/bin/bash -i >& /dev/tcp/%';

5. OSQUERY in daemon mode

As we have seen, OSQUERY will return results of OS state in real time that can be queried at later stage. But it is not possible to run query all the time for threat detection. Apart from this, OSQUERY does not also include any alerting mechanism. The solution is to run OSQUERY as daemon that will run scheduled queries and packs included in the configuration file at regular interval and write the results in a file. Create your own packs inside /usr/share/osquery/packs and include the packs for monitoring in the OSQUERY configuration file. The results of scheduled queries and packs are written in the file /var/log/osquery/osqueryd.results.log This log file will be created only when OSQUERY started as daemon mode and OSQUERYD starts sending the results. The OSQUERY daemon will read the same configuration file /etc/osquery/osquery.conf. Remember to revert logger_plugin value to filesystem in /etc/osquery/osquery.conf if you have changed it earlier to 'syslog'.

To start the daemon, use any one of the following command.

# sudo systemctl start osqueryd
       OR
# sudo osqueryctl start

The results should be available within a short time once the OSQUERYD runs the scheduled queries and packs. Use the tail command to verify that results are written to the file /var/log/osquery/osqueryd.results.log

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

Now you can forward the results logs to any external application like ELK stack for log analysis and alert generation.

Conclusion

OSQUERY is awesome tool that exposes OS data to the virtual relational database and allows you to query the system data with SQL. osqueryi is useful for running one off queries to find any back-doors, malware, zombie process or find out logged in users etc. while osqueryd can be used for running scheduled queries and custom packs for storing results for long term and alert generation using any external application.

About Dwijadas Dey

Dwijadas Dey is working with GNU/Linux, Open source systems since 2005. Having avid follower of GNU/Linux, He believes in sharing and spreading the open source ideas to the targeted audience. Apart from freelancing he also writes for community. His current interest includes information and network security.

Author Archive Page

Have anything to say?

Your email address will not be published. Required fields are marked *

All comments are subject to moderation.