What is Osquery?

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?

You can download Osquery from https://osquery.io/downloads/. Once installed you can run the command osqueryi from your systems command line. This will give you access to the Osquery virtual database.

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

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?

Osquery has a pretty fabulous documentation site available at:

Want to query more than one device?

Zercurity provides a SaaS platform to query all of your assets from one single interface. It’ll also automatically enrich your results for you. It’s free to signup and 5 hosts are included free.