osquery 101 — Getting Started

I admit it… I’m a fanboy. A straight up osquery fanboy.

Oh… what is osquery you ask? Good question there sport.

osquery allows you to easily ask questions about your Linux and OSX infrastructure. Whether your goal is intrusion detection, infrastructure reliability, or compliance, osquery gives you the ability to empower and inform a broad set of organizations within your company.

That’s how Facebook describes it. I’d say osquery is the most effective way available to monitor an OSX or Linux host for security. But that’s just me. Still not bad no matter which definition you prefer.

The big question everyone asks though is how do you get started with osquery and so without further ado:

Install osquery

This one is pretty simple. Just follow these handy instructions.

OSX Side Note: If you’re on OSX and a fan of Homebrew (as I am) you’ll want to skip Homebrew this time and install the package directly. Homebrew gets weird about running things as root later on, and that will be a pain. Safe yourself the pain.

Using osqueryi

osquery provides two main interfaces to the user: osqueryi and osqueryd (we’ll get to osqueryd in another post). osqueryi is a REPL (Read-Evaluate-Print Loop) similar to $ irb or $ python. This lets you input a command and get back an immediate response. We can use this for a few things:

• One off, spot checking of specific system attributes.
• Developing new queries.
• Learning your way around osquery.

So lets give it a try. Get started by typing osqueryi at your terminal.

Now that you’re at the prompt you’re ready to start looking for things. osquery uses the sqlite query language for simplicity and descriptiveness. The most basic query format is:

> SELECT columns FROM table;

Don’t forget the semicolon “;” at the end of your query! You wouldn’t like it without the semicolon. In that it won’t do anything.

For example lets take a look at the OSX kernel extensions I have installed:

Ok, so that found the data I wanted, but a bit too much. Every OSX system has Apple written kernel extensions, which makes those less interesting. Using the sql concept of a where clause we can limit this just to non-Apple kernel extensions:

Much more useful. Lets take it one step further and make it more readable with sorting:

For those playing at home you can see I have 11 kernel extensions for 5 applications (and OSX itself). The final query that got this information is:

> SELECT name FROM kernel_extensions WHERE name NOT LIKE 'com.apple%' ORDER BY name;

Pretty straight forward, but by now the power should be evident. As you practice more you can express powerful and complex ideas using this syntax. For more on syntax take a look at the SELECT documentation for sqlite. This coupled with the osquery Tables reference will give you most of what you need. A couple of key, non-query, commands will help you get started:

• .help Gives you the osqueryi help dialogue
• .schema [TABLE] Prints the table columns and data types
• .exit It isn’t exit or quit or exit()

If you want some examples of what you can look for and how you can use the sqlite syntax take a look at the osquery Packs. Here are a few of my favorites:

Incident Response: open_sockets

> select distinct pid, family, protocol, local_address, local_port, remote_address, remote_port, path from process_open_sockets where path <> '' or remote_address <> '';

A typical SELECT FROM WHERE style query to find open network connections. Combine this with an IP blacklist to identify C2 activity.

Incident Response: startup_items

> select * from startup_items;

Startup Items is a common way for OSX malware to persist between restarts. Most users don’t even notice it’s there.

Vulnerability Management: chrome_extensions

> select chrome_extensions.* from users join chrome_extensions using (uid); 

Chrome extensions have a scary amount of access, especially in web based workflows. They’re worth investigating.

Want to Learn More?

I do too. There’s a lot to osquery. It’s not a solution, it’s a framework. On one hand that makes it customizable, on the other that means many parts of using it are left to the user. It may be free, but that means it’s an investment of time rather than money.

Kevin Thompson and I spoke about osqueryi and more at BSidesDFW 2015:

I’ll explore some of these ideas from our presentation in the next post in this series: osquery 110 — Programmatic osqueryi.

Originally published at sroberts.github.io on January 26, 2016.