OSQuery- SQL Powered Operating System Instrumentation, Monitoring And Analytics
OSquery is a SQL powered operating system instrumentation, monitoring, and analytics framework.
Available for Linux, macOS, Windows and FreeBSD.
What is osquery?
osquery exposes an operating system as a high-performance relational database. This allows you to write SQL-based queries to explore operating system data. With osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events or file hashes.
SQL tables are implemented via a simple plugin and extensions API. A variety of tables already exist and more are being written: https://osquery.io/schema.
To best understand the expressiveness that is afforded to you by osquery, consider the following SQL queries:
List the users:
SELECT * FROM users;
Check the processes that have a deleted executable:
SELECT * FROM processes WHERE on_disk = 0;
Get the process name, port, and PID, for processes listening on all interfaces:
SELECT DISTINCT processes.name, listening_ports.port, processes.pid
 FROM listening_ports JOIN processes USING (pid)
 WHERE listening_ports.address=”0.0.0.0″;
Find every macOS LaunchDaemon that launches an executable and keeps it running:
SELECT name, program || program_arguments AS executable
 FROM launchd
 WHERE (run_at_load = 1 AND keep_alive = 1)
 AND (program != ” OR program_arguments != ”);
Check for ARP anomalies from the host’s perspective:
SELECT address, mac, COUNT(mac) AS mac_count
 FROM arp_cache GROUP BY mac
 HAVING count(mac) > 1;
Alternatively, you could also use a SQL sub-query to accomplish the same result:
SELECT address, mac, mac_count
 FROM
  (SELECT address, mac, COUNT(mac) AS mac_count FROM arp_cache GROUP BY mac)
 WHERE mac_count > 1;
These queries can be:
- Performed on an ad-hoc basis to explore operating system state using the osqueryi shell
- Executed via a scheduler to monitor operating system state across a set of hosts
- Launched from custom applications using osquery Thrift APIs
