osquery - Part I - Local Agent Interaction

By Tony Lee and Matt Kemelhar

This series on osquery will take us on a journey from stand-alone agents, to managing multiple agents with Kolide, and then finally onto more advanced integrations, queries, and analysis.  Crawl, walk, run, right?  Ok, let's start crawling.

What is osquery?

osquery (https://osquery.io/) is an open source agent developed by Facebook that allows organizations to query endpoints of varying operating system using the same SQL syntax. These queries can be used for security, compliance, or DevOps as event-based, user-driven, or scheduled information gathering. Once the user learns the SQL syntax and osquery schema it will work the same across multiple operating systems [Windows, macOS, FreeBSD, Debian, RPM Linux, etc.] (for the most part).

For example, to list processes on Windows, it can be accomplished natively using the tasklist command.  For Linux/Unix this same task can be accomplished using the ps command.  If you are in osquery, regardless of the operating system, it can be accomplished with select * from processes;  While this may seem more cumbersome at first, there is an advantage of a single query and normalized output across all supported operating systems.

Installation

Installation is simple using one of the provided installers found here: 
https://osquery.io/downloads/official

There are installation instructions for each operating system in the docs section of the site:

https://osquery.readthedocs.io/en/stable/

For example, if you are looking for Windows installation instructions you would go here: 
https://osquery.readthedocs.io/en/stable/installation/install-windows/

For the majority of our article, it is simple, we will download the Windows .msi and double click it.

Interaction

Once osquery is installed (in this example on Windows), you can check to make sure the default installation path was created and populated.  In windows, it is:  C:\ProgramData\osquery


Then in a command prompt, check to see if the osqueryd agent is running using the following command:

C:\>sc.exe query osqueryd

SERVICE_NAME: osqueryd
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, NOT_PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0

        WAIT_HINT          : 0x0


If it is not running, try using:

C:\>sc.exe start osqueryd

Once running, we should be able to start the local client (osqueryi.exe) and run some queries.  By default it is located in:  c:\programdata\osquery\osqueryi.exe.  Run this from the command line and you will receive a new osquery prompt.  Try the following to ensure that the agent and client are working properly:

osquery> select * from uptime;

+------+-------+---------+---------+---------------+
| days | hours | minutes | seconds | total_seconds |
+------+-------+---------+---------+---------------+
| 21   | 10    | 17      | 34      | 1851454       |
+------+-------+---------+---------+---------------+

Here are a few useful commands to remember:

.help = help menu

.tables = list all the possible tables to query

.summary = version and configuration

.mode = change the output mode:  csv, column, line, list, pretty (default)

.exit = leave the program

Pro-tip:  The osqueryi client remembers command history so use the up and down arrows liberally.

Online Schema

We showed you a couple of queries so far, but how are you supposed to know what else exists?

1)  You can run .tables within the osqueryi client

2)  You can use the online schema (https://osquery.io/schema/) that contains every table, all columns, types, descriptions, and even displays the operating systems supported.

Figure 1:  The osquery schema - a great reference

Linux Example

For those with Linux, it is just as easy.  At the time of this writing here is the latest release:

Download:
wget https://pkg.osquery.io/deb/osquery_3.3.2_1.linux.amd64.deb


Install:
dpkg -i osquery_3.3.2_1.linux.amd64.deb


Usage:
root@ubuntu:~/osquery# osqueryi 
-- snip --
successfully completed!
Using a virtual database. Need help, type '.help'

osquery> select * from osquery_info;

Uninstall:

dpkg --remove osquery

Conclusion

Now that we understand the basics of osquery installation and local client usage, it should be very apparent that this will not scale to hundreds of thousands of hosts.  Thus, we need an osquery manager to make it enterprise ready.  However, we will leave this topic to the next article.