Threat Hunting with FleetDM and Osquery

Recently, the folks at Chainguard open-sourced some Osquery threat hunting queries, so I wrote a script to convert the queries to YAML docs for import into FleetDM.

2 months ago   •   3 min read

By 0xBEN
Table of contents

What is Osquery?

Osquery is an open source endpoint analytics tool that can query a massive variety of information about a host using SQL queries. Effectively, Osquery catalogs information about a host in a relational database.

It has many applications from cyber security, to IT operations, and policy enforcement. When combined with a SIEM or a centralized management server, Osquery becomes a formidable tool in the cybersecurity operator and system administrator's toolbox.

What is FleetDM?

FleetDM is an open source fork of the Kolide Fleet server. This central management server allows you to deploy and control your Osquery endpoints at scale. An operator can log into the FleetDM server and run multiple SQL queries against multiple Osquery endpoints at scale. It is incredibly powerful.

Threat Hunting Queries

A couple days ago (as of this writing), Thomas Strömberg announced that Chainguard had open-sourced their threat hunting queries that they use with Osquery.

The only problem for me is that I wanted a way to get them into FleetDM, so that I could run the queries from the control server. Now, I am not aware of any straightforward way to take the .sql files from the GitHub repo and convert them into a YAML document for import into the FleetDM server. Although, I would love it if someone would correct me on that.

Bulk Importing the Queries

Creating the YAML Documents

I got the idea to use the fleetctl apply tool to bulk import the queries from a YAML doc, as this is something you can do when installing FleetDM to import some standard queries.

I forked their GitHub repo and got to work on a PowerShell script that would parse the .sql files and convert them to the YAML template that could be used to import them with fleetctl .

GitHub - 0xBEN/osquery-defense-kit: Production-ready detection & response queries for osquery
Production-ready detection & response queries for osquery - GitHub - 0xBEN/osquery-defense-kit: Production-ready detection & response queries for osquery

The PowerShell script is designed to be idempotent. So, regardless if you run the script once or many times, existing queries should remain in the template, while new queries are added to the template file.

With that being the case, you can safely import the YAML document multiple times, as the fleetctl command will not create duplicate entries, but will update the existing query name with the same query – effectively overwriting the data with identical data.

Importing the YAML Documents

The first step would be run the script to generate the YAML documents (or you can use the existing ones in the repository).

cd osquery-defense-kit
pwsh -f ./Out-FleetYamlTemplate.ps1

Once you've run the script and the .yml documents are generated, it's time to import the queries into FleetDM. I show you how to do this in my notes here, except, instead of importing the standard query pack, you'll import the new YAML documents.

After authenticating to the API using fleetctl, I can import the queries in bulk

Once the import is complete, you can log into FleetDM and check out your new queries.

Wrapping Up

If you've followed along with me to the end, thank you for reading. And thank you to the folks at Osquery (Meta), FleetDM, and Chainguard for their contributions to the open source community.

Spread the word

Keep reading