SQL tables are used to represent abstract operating system concepts, such as running processes.

A table can be used in conjunction with other tables via operations like sub-queries and joins. This allows for a rich data exploration experience. While osquery ships with a default set of tables, osquery provides an API that allows you to create new tables.

You can explore current schema here: https://osquery.io/schema. Tables that are up for grabs in terms of development can be found on Github issues using the "virtual tables" + "up for grabs tag".

New Table Walkthrough

Let's walk through an exercise where we build a 'time' table. The table will have one row, and that row will have three columns: hour, minute, and second.

Column values (a single row) will be dynamically computed at query time.

Table specifications

Under the hood, osquery uses libraries from SQLite core to create "virtual tables". The default API for creating virtual tables is relatively complex. osquery has abstracted this complexity away, allowing you to write a simple table declaration.

To make table-creation simple osquery uses a table spec file.The current specs are organized by operating system in the specs source folder.For our time exercise, a spec file would look like the following:

  1. # This .table file is called a "spec" and is written in Python
  2. # This syntax (several definitions) is defined in /tools/codegen/gentable.py.
  3. table_name("time")
  5. # Provide a short "one line" description, please use punctuation!
  6. description("Returns the current hour, minutes, and seconds.")
  8. # Define your schema, which accepts a list of Column instances at minimum.
  9. # You may also describe foreign keys and "action" columns.
  10. schema([
  11.     # Declare the name, type, and documentation description for each column.
  12.     # The supported types are INTEGER, BIGINT, TEXT, DATE, and DATETIME.
  13.     Column("hour", INTEGER, "The current hour"),
  14.     Column("minutes", INTEGER, "The current minutes past the hour"),
  15.     Column("seconds", INTEGER, "The current seconds past the minute"),
  16. ])
  18. # Use the "@gen{TableName}" to communicate the C++ symbol name.
  19. implementation("time@genTime")

You can leave the comments out in your production spec. Shoot for simplicity, do NOT go "hard in the paint" and do things like inheritance for Column objects, loops in your table spec, etc.

You might wonder "this syntax looks similar to Python?". Well, it is! The build process actually parses the spec files as Python code and meta-programs necessary C/C++ implementation files.

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.

NOTICE: the CMake build provides custom defines for each platform and platform version.

Specfile nuances

Each column in a specfile may have keyword arguments that effect how the SQLite behaves. If you require a column to be present in the WHERE predicate, like a path in the file table, then it must be reflected in the spec.

  • required=True: This will create a warning if the table is used and the column does not appear in the predicate.
  • index=True: This sets the PRIMARY KEY for the table, which helps the SQLite optimizer remove potential duplicates from complex JOINs. If multiple columns have index=True then a primary key is created as the set of columns.
  • additional=True: This is weird, but use additional if the presence of the column in the predicate would somehow alter the logic in the table generator. This tells SQLite not to optimize out any use of this column in the predicate.
  • hidden=True: Sets the HIDDEN attribute for the column, so a SELECT * FROM will not include this column.The table may also set attributes:
  1. attributes(user_data=True)

There are several attributes that help with table documentation and optimization. These are keyword arguments in the attributes optional method.

  • event_subscriber=True: Indicates that the table is an abstraction on top of an event subscriber. The specfile for your subscriber must set this attribute.
  • user_data=True: This tells the caller that they should provide a uid in the query predicate. By default the table will inspect the current user's content, but may be asked to include results from others.
  • cacheable=True: The results from the table can be cached within the query schedule. If this table generates a lot of data it is best to cache the results so that queries needing access in the schedule with a shorter interval can simply copy the already generated structures.
  • utility=True: This table will be included in the osquery SDK, it is considered a core/non-platform specific utility.
  • kernel_required=True: This is rare, but tells the caller that results are only available if the osquery kernel extension is running.Specs may also include an extended_schema for a specific platform. They are the same as schema but the first argument is a function returning a bool. If true the columns are added and not marked hidden, otherwise they are all appended with hidden=True. This allows tables to keep a consistent set of columns and types while providing a good user experience for default selects.

Creating your implementation

As indicated in the spec file, our implementation will be in a function called genTime. Since this is a very general table and should compile on all supported operating systems we can place it in osquery/tables/utility/time.cpp. The directory osquery/table/ contains the set of implementation categories. Each category may contain a platform-restricted directory. If a table requires a different implementation on different platform, use these subdirectories. Place implementations in the corresponding category using your best judgment. CMake will discover all files within the platform-related directory at build time.

Here is that code for osquery/tables/utility/time.cpp:

  1. // Copyright 2004-present Facebook. All Rights Reserved.
  3. #include <ctime>
  4. #include <osquery/rows/time.h>
  5. #include <osquery/tables.h>
  7. namespace osquery {
  8. namespace tables {
  10. TableRows genTime(QueryContext &context) {
  11.   TableRows results;
  12.   if (!context.isAnyColumnUsed({TimeRow::HOUR, TimeRow::MINUTES, TimeRow::SECONDS})) {
  13.     return results;
  14.   }
  16.   TimeRow* r = new TimeRow();
  18.   time_t _time = time(0);
  19.   struct tm* now = localtime(&_time);
  21.   r->hour_col = now->tm_hour;
  22.   r->minutes_col = now->tm_min;
  23.   r->seconds_col = now->tm_sec;
  25.   std::unique_ptr<TableRow> tr(r);
  26.   results.push_back(std::move(tr));
  27.   return results;
  28. }
  29. }
  30. }

Key points to remember:

  • Your implementation function should be in the osquery::tables namespace.
  • Your implementation function should accept on QueryContext& parameter and return an instance of TableRows.
  • Your implementation function should use context.isAnyColumnUsed to run only the code necessary for the query

Using where clauses

The QueryContext data type is osquery's abstraction of the underlying SQL engine's query parsing. It is defined in osquery/tables.h.

The most important use of the context is query predicate constraints (e.g., WHERE col = 'value'). Some tables MUST have a predicate constraint, others may optionally use the constraints to increase performance.

Examples:

hash requires a predicate, since the resultant rows are the hashes of the EQUALS constraint operators (=). The table implementation includes:

  1.   auto paths = context.constraints["path"].getAll(EQUALS);
  2.   for (const auto& path_string : paths) {
  3.     boost::filesystem::path path = path_string;
  4.     [...]
  5.   }

processes optionally uses a predicate. A syscall to list process pids requires few resources. Enumerating "/proc" information and parsing environment/argument uses MANY resources. The table implementation includes:

  1.   for (auto &pid : pidlist) {
  2.     if (!context.constraints["pid"].matches<int>(pid)) {
  3.       // Optimize by not searching when a pid is a constraint.
  4.       continue;
  5.     }
  6.     [...]
  7.   }

SQL data types

Data types like TableRows, TableRow, DiffResults, etc. are osquery's built-in data result types. They're all defined in include/osquery/database.h.

TableRow is an interface; each table has a generated implementation with strongly-typed fields for each column in the table. There's also DynamicTableRow, which is backed by a std::map<std::string, std::string> mapping column names to the string representations of their values. DynamicTableRow exists to support tables that were written before the strongly-typed row support was added, and for plugins.

TableRows is just a typedef for a std::vector<TableRow>. Table rows is just a list of rows. Simple enough.

To populate the data that will be returned to the user at runtime, your implementation function must generate the data that you'd like to display and populate a TableRows list with the appropriate TableRows. Then, just return the TableRows.

In our case, we used system APIs to create a struct of type tm which has fields such as tm_hour, tm_min and tm_sec which represent the current time. We can then set our three fields in our TimeRow variable: hour_col, minutes_col and seconds_col. Then we push that single row onto the TableRows variable and return it. Note that if we wanted our table to have many rows (a more common use-case), we would just push back more TableRow maps onto results.

Building new tables

If you've created a new .{c,cpp,mm} file in the correct folder within osquery/tables, CMake will discover and attempt to compile your implementation.

Return to the root of the repository and execute make. This will generate the appropriate code and link everything properly. If you need to add additional linking it gets a tad more complicated. Consider the following code within the table's CMakeLists.txt:

  2. if(APPLE)
  3.   # Add "-framework CoreFoundation" to the linker flags.
  4.   ADD_OSQUERY_LINK_ADDITIONAL("-framework CoreFoundation")
  5.   # Search for any library named magic
  6.   ADD_OSQUERY_LINK_ADDITIONAL("magic")
  7. elseif(FREEBSD)
  8.   # Search for any library named magic
  9.   ADD_OSQUERY_LINK_ADDITIONAL("magic")
  10. else()
  11.   # Search for any library named blkid
  12.   ADD_OSQUERY_LINK_ADDITIONAL("blkid")
  13.   # Search for the exact library name: libmagic.so
  14.   ADD_OSQUERY_LINK_ADDITIONAL("libmagic.so")
  15. endif()

This is a bit confusing but allows the table implementations to track three types of linking requests. The first is verbatim the linking flags needed for some tables; the second is a default search for static or shared library names; the final is the exact name of a library within the system's/build's configuration.

When supplying the "second type", a search name, CMake will decide to link using static or shared libraries based on environment settings. The cases where a shared library is ONLY allowed are usually related to that library being available by default on the operating system or platform.

Testing your table

If your code compiled properly, launch the interactive query console by executing ./build/[darwin|linux]/osquery/osqueryi and try issuing your new table a command: SELECT * FROM time;. (If your table implementation has nontrivial conditional code based on the columns used in the query, try issuing more focused commands to test that logic as well.)

Run the leaks analysis to check for memory leaks:

  1. ./tools/analysis/profile.py --leaks --query "select * from time" --verbose

If your table parses content from the filesystem you should define fuzzing rules. In your table specification add:

  1. fuzz_paths([
  2.     "/path/to/directory/used",
  3. ])

Then run ./tools/analysis/fuzz.py —table time.

Getting your query ready for use in osqueryd

You don't have to do anything to make your query work in the osqueryd daemon. All osquery queries work in osqueryd. It's worth noting, however, that osqueryd is a long-running process. If your table leaks memory or uses a lot of systems resources, you will notice poor performance from osqueryd. For more information on ensuring a performant table, see performance overview.

When in doubt, use existing open source tables to guide your development.