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:
- Differential = Only record data that has changed.
- 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
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
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.
