Kolide Osquery Workshop by Zach Wasserman

San Francisco - November 13-14 2018

Day One

Virtual Environment Setup

Both VirtualBox and Vagrant need to be installed to get the lab configured properly.

vagrant init zwass/ubuntu-osquery
vagrant up
vagrant ssh default

Digging Into SQL - osqueri

  • Use as part of scripts and manual exploration
  • To change the output you can use
    .mode line
    To change back to the default use:
    .mode pretty
    Within osqueryi you can look at the schema of a table by using the schema command, for example:
    .schema user_groups


Projection: Which fields should be returned?

SELECT * user_time + system_time FROM processes:
Sources: From which table(s) should the data be retrieved?
SELECT * FROM processes JOIN process_open_sockets;
Selection: What filters should be applied to the data?
SELECT * FROM processes JOIN hash WHERE processes.path = hash.path;
- LIKE Operator
select * from processes where processes.path like '/bin/%';

select * from processes where path not like '/bin/%' and path not like '/sbin/%' and path not like '/usr/%';
Presentation: Order and limit the results returned.
SELECT * FROM processes ORDER BY pid LIMIT 3;


select * from processes order by user_time + system_time desc limit 3;
select * from hash where path = '/tmp/mshelper/mshelper';
select * from launchd where program like '%pplauncher%';

mdfind - Run searches against the spotlight database.

Table Information avaible in osquery.

Kolide Blog Post about the mdfind functionality, Spotlight search across every Mac in your fleet with Osquery


Daemon Setup See pictures

  "schedule": {
    "time": {
      "query": "select * from time",
      "interval": 5

Types of Results Logs

  • Differential logs: The results of your scheduled queries are logged to the "results log". These are differential changes between the last (most recent) query execution and the current execution.
  • Snapshot logs: Snapshot logs are an alternate form of query result logging. A snapshot is an 'exact point in time' set of results, no differentials. If you always want a list of mounts, not the added and removed mounts, use a snapshot.

Osquery Packs

Discovery Queries - #1 & 3 in the excersise are typically best for discovery queries

Monitoring macOS hosts with osquery


Decorator queries are used to add additional "decorations" to results and snapshot logs. There are three types of decorator queries based on when and how you want the decoration data.

  • load: run these decorators when the configuration loads (or is reloaded)

  • always: run these decorators before each query in the schedule

  • interval: a special key that defines a map of interval times

  "decorators": {
    "load": [
      "SELECT version FROM osquery_info;",
      "SELECT uuid AS host_uuid FROM system_info;"
    "always": [
      "SELECT user AS username FROM logged_in_users WHERE user <> '' ORDER BY time LIMIT 1;"
    "interval": {
      "3600": [
        "SELECT total_seconds AS uptime FROM uptime;"

Osquery Internals

A really great resource and writeup on the internals of osquery is a blog post by titled, "Osquery: Under the Hood" by Zach Wasserman

Advanced SQL




Testing Query Performance

The osquery tooling provides a full-featured profiling script. The script can evaluate table, query, and scheduled query performance on a system. Before scheduling a set of queries on your enterprise hosts, it is best practice to measure the expected performance impact.

./tools/analysis/profile.py --config ~/example.conf

A good example of how the profile tool works and a real world example of its use can be found in a blog post by Nick Esposito.

Day Two

Both VirtualBox and Vagrant need to be installed to get the lab configured properly.

vagrant init zwss/osquery-audit
vagrant up
vagrant ssh default

Distributed Osquery Methods

  • Enrollment: Osquery agent provides an enroll secret, and receives a node key (per endpoint). All further calls are authenticated with the node key.
  • Configuration: Osquery agent requests a configuration from the server.
  • Logging: Osquery agent provides (buffered) logs to the server.
  • Distributed Queries: Osquery agent requests live queries to run and writes results back to the server.

Kolide Fleet - fleetctl

A good introduction and demonstration of how works feetctl is a presentation given by Mike Arpaia at QueryCon 2018.

Integrating Osquery


  • How will we get our basic osquery configuration and packs to the osqueryd agents in a timely manner?

    • Chef, Puppet, etc.
    • Push Configurations over TLS
  • What if we need to use different configurations across different instances?


How will we turn the logs generated by osquery into actionable insights?

  • First getting the logs off the host:

    • Filesystem logging + log forwarders
    • TLS logging
    • AWS logging
    • Kafka logging
  • More than one logger plugin can be used at a time

Events and System Auditing

File Integrity Monitoring

Track changes to all files within given path.

  • %: Match all files and folders for one level.
  • %%: Match all files and folders recursively.
  • %abc: Match all files ending with abc (one level).
  • abc%: Match all beginning with abc (one level).



Github Project

osquery go

Github Project

Extending Osquery with Go by Victor Vrantchan.