Bench Talk

(Source: sdecoret/Shutterstock.com)

Many times, when investigating security incidents or working to determine the effectiveness of a security control, you need real-time, granular inventory data about a set of systems. You might want to know what software packages are installed, what processes are running, or whether a specific security update has been installed. If your responsibility spans both cloud and on-premise systems, you will likely be working with a mix of operating systems, including Windows, Mac, and Linux. To help you answer these inventory questions across multiple platforms, consider using the open-source tool osquery. Osquery gathers a rich set of data from a system that you can then easily query using basic Structured Query Language (SQL) commands. For example, you can query the shell history, the contents of the hosts file, what operating system version, all from a single command-line tool. Osquery provides great insight and can help answer some questions about your systems without running multiple commands or having to write custom scripts.

In its simplest form, osquery consists of a daemon and client that run on a computer. The daemon collects information about the host based on the osquery configuration files. The configuration files tell osquery what to collect. The data is stored locally on that computer in a database. The easiest way to quickly access this information is to run the osqueryi binary. This opens a simple command line from which you can create and run simple SQL queries to show data about your system.

It is very easy to take osquery for a test drive. Visit osquery.io and download the package for whichever operating system you want. If you run Windows, you would download the osquery.msi and run that to install the program into c:\Program Files\osquery. Open a terminal prompt, navigate to that folder and run osqueryi.exe to launch osquery using the default configuration. Using this default config lets you immediately query interesting data about your system and will give you a sense of what it is capable of, Then you can expand your deployment for how it might best fit your environment or organization. 

In its most basic state, you can interactively query a host using basic SQL syntax. For example, to show the names of the running services on your Windows computer, run the query:

osquery> SELECT display_name FROM services WHERE status='RUNNING';

Osquery organizes groups of similar queries into packs. Examples of packs included with a default installation include hardware-monitoring, it-compliance, and osquery-monitoring. Security specific packs also include queries to let you search for Windows and Mac attacks and Windows hardening. The queries tap into the specific features of the operating system. For example, the Windows-hardening pack includes queries that look for specific values in the registry that might result in a less secure configuration.

Osquery is one place to ask various questions about the system it is monitoring. For example, you can query the installed patches:

osquery>SELECT * from patches;

Or find out the system's default gateway:

osquery> SELECT * from routes WHERE gateway='0.0.0.0'

As another example, to see all of the startup items from a Windows computer, simply run the query:

osquery> SELECT * FROM startup_items;

The information returned includes the name of the item, the path to its location, what user the item is running under, and how the items is invoked, either through the registry or in the Startup folder.

These are all very basic queries to be sure, and the real power of osquery is to integrate the collection of this data into other tools and automation. For example, you can set an alert if a program is started under a privileged account.

If you are unsure what you can query, type:

osquery>.table

And to see a list of the tables that osquery has created, run the command:

osquery>.schema <table name>

for a listing of the fields that you can use in your queries.

Osquery is popular and you’ll find quite a few online resources and tutorials for how to use the tool and create very useful queries.

Osquery benefits from the contributions from a strong open-source community. Several community projects can be installed or deployed to extend the value of osquery for your installation. For example, many sysadmins choose to roll out osquery across all their servers and deploy a control server that manages this fleet of osquery installations. This control server lets you centrally query data from several different systems as well as manage the operation of the individual osquery agents. All in all, osquery is flexible and extensible and a great tool for sysadmin toolbox.