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.
📢 I'm proud to announce that we've open-sourced our #osquery detection & response ruleset: https://t.co/IsNvtzzn8z— Thomas Strömberg (@thomrstrom) October 20, 2022
It contains 130+ production-ready queries we found useful for detecting malware & other anomalous behavior on our endpoints, designed with alerting in mind. 🚨
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
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.
Once the import is complete, you can log into FleetDM and check out your new queries.
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.