writing new sql functions

osquery provides many additional SQL functions to help you do data conversion, file acquisition, string manipulation, hashing and more. For a complete list of extra SQL functions provided by osquery see the osquery Functions page. If none of these functions (or a combination of them) do what you need them to, you can extend osquery to add new functions (including aggregation functions) to handle your specific case.

You should note that doing this requires recompiling osquery as SQL functions are not currently exposed through the extensions framework. Thus you may want to consider submitting the function as a pull request to get it included in the next version of osquery.

non aggregate functions

Non aggregate functions are the simplest to write and understand (though aggregate functions are not much more complicated). In this example, we'll write a new function to convert a base10 into to base8 (octal). The file we are going to modify is sqlite_encoding and we're going to add our new function

static void sqliteOctEncode(sqlite3_context* context, int argc, sqlite3_value** argv)

You should do some basic error checking (especially if you plan to put it up as a pull request) but for now, let's just get it working. If you want to write the actual math to get the conversion to octal working be my guest, but the standard library has nice built in function for it as part of iostream.

static void sqliteOctEncode(sqlite3_context* ctx, int argc, sqlite3_value** argv) {
  unsigned long original_value;
  safeStrtoul((char*)(sqlite3_value_text(argv[0])), 10, original_value);
  std::string output;
  std::stringstream ss;
  ss << std::oct << original_value;
  ss >> output;
}

The final thing to do for our function is to update the value with a final call to sqlite3_result_text:

static void sqliteOctEncode(sqlite3_context* ctx, int argc, sqlite3_value** argv) {
  unsigned long original_value;
  safeStrtoul((char*)(sqlite3_value_text(argv[0])), 10, original_value);
  std::string output;
  std::stringstream ss;
  ss << std::oct << original_value;
  ss >> output;
  sqlite3_result_text(ctx, output.c_str(), static_cast<int>(output.size()), SQLITE_TRANSIENT);
}

Lastly, we need to register the function with the SQLite system and tell it how many parameters it take and a bunch of other information. That call should look something like below and be in the registerEncodingExtensions function:

sqlite3_create_function(db,
                        "to_oct",            // Function name
                        1,                   // Number of parameters
                        SQLITE_UTF8,         // Text encoding
                        nullptr,             // Arbitrary pointer. Retrieve with sqlite3_user_data()
                        sqliteOctEncode,     // Pointer to function for processing
                        nullptr,             // Function pointer to process each row in aggregation
                        nullptr);            // Function pointer to finish aggregation

Recompile osquery and use your new function as you would any other!

> select to_oct(65536);
+---------------+
| to_oct(65536) |
+---------------+
| 200000        |
+---------------+