Using DB Connect With SQLite

I got a request the other day from Ari Brown for a hand with the Seattle Kegbot… here’s some notes on how we got to Splunking the data.

The system uses a SQLite backend, so of course DB Connect was our first stop. This app uses the ubiquitous JDBC interfaces to provide access to all sorts of structured data sources, and it provides all sorts of enterprise grade options. However, we were able to skip over a lot of complexity for our use case.

First we needed a JDBC driver, and a little bit of web searching turned one up which worked on the first try.

• Read the documentation for adding unsupported data sources
• Download the SQLite JDBC driver.
• Put the driver into DB Connect:

cp sqlite-jdbc-3.8.11.2.jar $SPLUNK_HOME/etc/apps/splunk_app_db_connect/bin/lib/

Now for the hard part: we needed to write a db_connection_types.conf stanza to describe this new type of data. We copied a working stanza for MySQL into a $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_connection_types.conf file and started editing, using Xerial’s docs and Splunk’s docs for reference. Splunk Answers was of course a huge help too! Here’s the stanza that we wrote:

[sqlite]
displayName = SQLite
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = org.sqlite.JDBC
jdbcUrlFormat = jdbc:sqlite:<database>
ui_default_catalog = $database$

Picking a service class took a minute, because at first I assumed that it would be something from the Xerial driver. Once I read the default stanzas for supported connection types more closely, I realized that most of them were using DBX2’s own default class and decided to give that a try. The driver class is from the SQLite JDBC driver though, and there I just copied from the Xerial docs site. We also needed to set a variable to point at the actual database, which is used in the JDBC URL and default catalog. This was done by referencing with brackets and dollar signs, respectively.

At this point, we were ready to try it out; we went to DB Connect’s Settings page, restarted the RPC server, and looked at the Drivers tab to see a green light by our new SQLite entry! Now we were ready to use the Connections UI to make a new connection.

The next step took a second to think through; because the SQLite database file was sitting on a local disk and has no user permissions control, what should we do about Identities? Luckily, we rapidly decided this was too much thinking, made a junk Identity record, and moved on. We put junk into the Host field too, and put the full disk path to the SQLite file into the Database field, resulting in this $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_connections.conf

[kegbot]
connection_type = sqlite
database = /Users/jcoates/Downloads/kegbot.sqlite
host = localhost
identity = jigglypuff
jdbcUrlFormat = jdbc:sqlite:<database>
jdbcUseSSL = 0

And that is all that it took.

----------------------------------------------------
Thanks!
Jack Coates