osquery - Part III - Queries and Packs

By Tony Lee and Matt Kemelhar

This series on osquery will take us on a journey from stand-alone agents, to managing multiple agents with Kolide Fleet, and then finally onto more advanced integrations and analysis.  So far, we have already covered the following topics:

Part I - Local Agent Interaction:  http://securitysynapse.blogspot.com/2019/05/osquery-part-i-local-agent-interaction.html
Part II - Kolide Centralized Management:  http://securitysynapse.blogspot.com/2019/05/osquery-part-ii-kolide-centralized.html


Even though we now have a centralized management platform, reading the query output in the Kolide Fleet UI does not scale to hundreds of thousands of hosts -- thus we need to integrate with a big data analytics platform so we can stack and perform statistical analysis on the data.  In order to do that, we first need to cover Query Packs and the resulting logs.

What is Query Pack?

The Kolide Fleet Web UI does an excellent job succinctly describing the query packs in the following manner:

"Osquery supports grouping of queries (called query packs) which run on a scheduled basis and log the results to a configurable destination.

Query Packs are useful for monitoring specific attributes of hosts over time and can be used for alerting and incident response investigations. By default, queries added to packs run every hour (interval = 3600s).

Queries can be run in two modes:

1. Differential = Only record data that has changed.
2. Snapshot = Record full query result each time.

Packs are distributed to specified targets. Targets may be individual hosts or groups of hosts called labels.

The results of queries run via query packs are stored in log files for your convenience. We recommend forwarding this logs to a log aggregation tool or other actionable tool for further analysis. These logs can be found in the following locations:

    Status Log: /path/to/status/logs
    Result Log: /path/to/result/logs"

Creating Saved Queries

Packs sound like a great step toward big data integration, but first we need to create a saved search by doing the following (our example below queries users):

In the Kolide Web UI, click Query on the left hand navigation > New Query

• Query Title:  Users Query
• SQL:  SELECT * FROM users
• Description:  Query all users
• Select Targets:  All Hosts

Click the Save button > Save as New

Figure 1:  Adding a new saved user query

Creating Query Packs

Now that we have a saved query, let's schedule it using a Pack.

Click Packs on the left hand navigation > New Pack

• Query Pack Title:  Users Pack
• Query Pack Description:  Query all users

Click the Save Query Pack button

Figure 2:  Creating a new users pack

In the next screen, on the far right hand side, select the Users Query that we created earlier and fill in the fields to define the pack properties:

• Interval:  60  (Just so we get some data to play with)
• Platform:  All
• Minimum version:  All
• Logging:  Snapshot  (Just so we get some data to play with)

Figure 3:  Defining the User Pack properties

Query Pack Output

With our current minimalist configuration (shown below in fleet.yaml), the packs logs are being sent by default to disk here:

• /tmp/osquery_result
• /tmp/osquery_status

cat /opt/fleet/conf/fleet.yaml 

mysql:
  address: 127.0.0.1:3306
  database: kolide
  username: root
  password: toor
redis:
  address: 127.0.0.1:6379
server:
  cert: /opt/fleet/ssl/fleetserver-cert.crt
  key: /opt/fleet/ssl/fleetserver-cert.key
  address: 0.0.0.0:443
auth:
  jwt_key: strong_key
logging:
  json: true

If we wanted to send the logs to a lager drive, we could add the following to our fleet.yaml configuration (the enable log rotation provides 500 Mb or 28 days of data):

filesystem:
  status_log_file: /path/to/drive/osquery/status.log
  result_log_file: /path/to/drive/osquery/result.log
  enable_log_rotation: true

(For our lab environment, we wrote it to:  /data/osquery/)

Just remember to restart the Kolide Fleet service using the following:

service fleet-service restart

For a full list of Fleet configuration options (such as sending to firehose, etc.):
https://github.com/kolide/fleet/blob/master/docs/infrastructure/configuring-the-fleet-binary.md

Conclusion

This article covered how to create saved queries, configure and schedule query packs to run on a regular basis, and how to send this data to a specified file so we can pick up the results and send them to a big data analytics platform.  In the next couple of articles we will cover how to manage the fleet manager and advanced integrations.

osquery - Part I - Local Agent Interaction

By Tony Lee and Matt Kemelhar

This series on osquery will take us on a journey from stand-alone agents, to managing multiple agents with Kolide, and then finally onto more advanced integrations, queries, and analysis.  Crawl, walk, run, right?  Ok, let's start crawling.

What is osquery?

osquery (https://osquery.io/) is an open source agent developed by Facebook that allows organizations to query endpoints of varying operating system using the same SQL syntax. These queries can be used for security, compliance, or DevOps as event-based, user-driven, or scheduled information gathering. Once the user learns the SQL syntax and osquery schema it will work the same across multiple operating systems [Windows, macOS, FreeBSD, Debian, RPM Linux, etc.] (for the most part).

For example, to list processes on Windows, it can be accomplished natively using the tasklist command.  For Linux/Unix this same task can be accomplished using the ps command.  If you are in osquery, regardless of the operating system, it can be accomplished with select * from processes;  While this may seem more cumbersome at first, there is an advantage of a single query and normalized output across all supported operating systems.

Installation

Installation is simple using one of the provided installers found here: 
https://osquery.io/downloads/official

There are installation instructions for each operating system in the docs section of the site:

https://osquery.readthedocs.io/en/stable/

For example, if you are looking for Windows installation instructions you would go here: 
https://osquery.readthedocs.io/en/stable/installation/install-windows/

For the majority of our article, it is simple, we will download the Windows .msi and double click it.

Interaction

Once osquery is installed (in this example on Windows), you can check to make sure the default installation path was created and populated.  In windows, it is:  C:\ProgramData\osquery


Then in a command prompt, check to see if the osqueryd agent is running using the following command:

C:\>sc.exe query osqueryd

SERVICE_NAME: osqueryd
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, NOT_PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0

        WAIT_HINT          : 0x0


If it is not running, try using:

C:\>sc.exe start osqueryd

Once running, we should be able to start the local client (osqueryi.exe) and run some queries.  By default it is located in:  c:\programdata\osquery\osqueryi.exe.  Run this from the command line and you will receive a new osquery prompt.  Try the following to ensure that the agent and client are working properly:

osquery> select * from uptime;

+------+-------+---------+---------+---------------+
| days | hours | minutes | seconds | total_seconds |
+------+-------+---------+---------+---------------+
| 21   | 10    | 17      | 34      | 1851454       |
+------+-------+---------+---------+---------------+

Here are a few useful commands to remember:

.help = help menu

.tables = list all the possible tables to query

.summary = version and configuration

.mode = change the output mode:  csv, column, line, list, pretty (default)

.exit = leave the program

Pro-tip:  The osqueryi client remembers command history so use the up and down arrows liberally.

Online Schema

We showed you a couple of queries so far, but how are you supposed to know what else exists?

1)  You can run .tables within the osqueryi client

2)  You can use the online schema (https://osquery.io/schema/) that contains every table, all columns, types, descriptions, and even displays the operating systems supported.

Figure 1:  The osquery schema - a great reference

Linux Example

For those with Linux, it is just as easy.  At the time of this writing here is the latest release:

Download:
wget https://pkg.osquery.io/deb/osquery_3.3.2_1.linux.amd64.deb


Install:
dpkg -i osquery_3.3.2_1.linux.amd64.deb


Usage:
root@ubuntu:~/osquery# osqueryi 
-- snip --
successfully completed!
Using a virtual database. Need help, type '.help'

osquery> select * from osquery_info;

Uninstall:

dpkg --remove osquery

Conclusion

Now that we understand the basics of osquery installation and local client usage, it should be very apparent that this will not scale to hundreds of thousands of hosts.  Thus, we need an osquery manager to make it enterprise ready.  However, we will leave this topic to the next article.