Osquery is an awesome tool from Facebook. Osquery allows you to easily ask questions about your IT Infrastructure. Whether its Windows, Linux or Mac. You can ask anything you like from; intrusion detection, system information, compliance, installed applications, running processes. Osquery empowers you to understand every part of your IT infrastructure.

Traditionally, the software that you deploy for; monitoring, compliance, security etc. is responsible for a specific task. You have no control over the information that the software is fetching from your deployed infrastructure. Osquery flips this on its head. Need to know what processes are running on a given machine? A servers current CPU temperature? Verify a hard drive is encrypted? You can answer all these questions and much, much more.

Osquery uses an SQL (Structured Query Language) abstraction to give granular access to your system. Effectively allowing you to query your computer, just like you would a database.

SELECT * FROM processes;

Just like that, you can retrieve all of the running processes on your local machine. Need the running processes for a given user, say Apache?

SELECT * FROM processes AS p JOIN users AS u ON p.uid = u.uid WHERE u.username = ‘apache’’;

A little more complex granted. However, this is where Osquery’s power comes really shines. Its ability to join tables of abstracted data, filter it and transform it into anything you like within the SQLite's syntax. Is really powerful. Osquery provides abstractions across all facets of your system. Which are now accessible via a simple SQL query. A full list of Osquery’s schema is available here.

How do I get started?

Using a virtual database. Need help, type ‘.help’

You can use the command .tables to list all of the available tables that are available to you. Now you can get the table schema with the command below. In this example we’re fetching the schema for ‘users’;

osquery> .schema users
CREATE TABLE users(`uid` BIGINT, `gid` BIGINT, `uid_signed` BIGINT, `gid_signed` BIGINT, `username` TEXT, `description` TEXT, `directory` TEXT, `shell` TEXT, `uuid` TEXT, PRIMARY KEY (`uid`, `username`)) WITHOUT ROWID;

You can now start to construct your queries. Below I’m fetching just the user id (uid) and the username from the users' table and limiting the result to just the first 5 results.

SELECT uid, username FROM users LIMIT 5;

That’s pretty much it. Osquery is incredibly simple and infinitely powerful.

What to learn more?

Want to query more than one device?

Real-time security and compliance delivered.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store