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.