creating a new table

Introduction

The core of osquery is a SQL language where tables represent abstract operating system concepts. osquery provides a simple API for creating new tables. Any new table you write can be used in conjunction with existing tables via sub-queries, joins, etc. This allows for a rich data exploration experience.

Perhaps you want to expose some information about a part of the operating system which isn't currently implemented by osquery. Perhaps you want to use osquery to query something proprietary and internal. All of these use-cases are supported and more, using osquery's table API.

Creating your own table

This guide is going to take you through creating a new, very simple osquery table. We'll show you how to get all the knobs turning and leave the creative programming as an exercise for the reader.

The table that we're going to be implementing is going to be a "time" table. The table will have one row and that row will have three columns:

• hour
• minute
• second

The values of the columns will be determined by the current time, which will be dynamically computed at query time.

Declare the schema for your table

Under the hood, osquery uses a set of lower level libraries from SQLite core to create what SQLite calls "virtual tables". The API for creating virtual tables is very complex. In order to make creating new tables as easy as possible, osquery comes with a set of tools to make this easier.

Instead of writing low-level C code which consumes SQLite APIs, you can write a simple table declaration. osquery developers call these files "table specs", short for "table specifications". For our time table our spec would look like this:

# use the table_name function to define what the name of
# your table is
table_name("time")

# define your schema using the schema function, which 
# accepts a list of Column instances
schema([
    # each column can be creating inline for maximum
    # readability. declare the name of your column
    # as well as the type of the column. Currently
    # supported options are "std::string and "int"
    Column(name="hour", type="std::string"),
    Column(name="minutes", type="std::string"),
    Column(name="seconds", type="std::string"),
])

# use the implementation function to declare where in
# osquery codebase your table is implemented. the string
# that you pass to this function is made up of two bits
# which are separated by an @ symbol. the first bit is
# the path of the implementation file and the second bit
# is the name of the function which implements the table.
#
# the general pattern here is:
#  "osquery/tables/{topic}/{table_name}@gen{tableName}"
implementation("osquery/tables/utility/time@genTime")

Feel free to leave the comments out in your production spec. The function names are pretty intuitive.

As an aside, you may be thinking that the syntax used for declaring the schema of tables is very similar to Python. Well, that's because it is! The build process actually parses the spec files as if they were Python code and meta-programs necessary C/C++ implementation files.

Although it's possible to get fancy here and try to use inheritance for Column objects, use loops in your table spec, etc. please don't.

Where do I put the spec?

You may be wondering how osquery handles cross-platform support while still allowing operating-system specific tables. The osquery build process takes care of this by only generating the relevant code based on a directory structure convention.

• Cross-platform table specs are stored in osquery/tables/specs/x/
• Mac OS X specific table specs are stored in osquery/tables/specs/darwin

Creating your implementation

Building your code

Testing out your table