How use Splunk DB Connect with H2 databases

Today I got a question from a colleague if it’s possible to connect a H2 database engine to Splunk. It would be great to index events from that database – as it contains security events coming from an anti-virus system.

To index events based on a RDBMS there is Splunk’s well-known DB Connect app (https://splunkbase.splunk.com/app/2686/). Unfortunately the DB Connect support matrix doesn’t mention anything with H2 database – so I decided to test it out.

H2 Database

I’ve never run into H2 before, it really seems to be a niche product. The installation is downloading and extracting the .zip file – awesome! It’s the size of 1.5 MB and has a great feature set like In-Memory Mode, Built-in Clustering / Replication…

http://www.h2database.com/html/features.html#comparison

By default H2 has 2 connection modes:

  1. Embedded/local mode: only local connections using JDBC
  2. Server mode: remote connections using ODBC or JDBC
  3. mixed

DB Connect setup

As always you need to install Java 8 for DB Connect. Even if openjdk is working fine I always recommend to use Oracle Java for support reasons. Extract DB Connect to your $SPLUNK_HOME$/etc/apps path and run the setup wizard if you have a full Splunk installation.. otherwise you can edit app.conf and inputs.conf to enable it and set the JRE path correctly.

app.conf

[install]
is_configured = 1

inputs.conf
[rpcstart://default]
javahome = /usr/local/jre1.8.0_111
useSSL = 0

Full installation procedure is documented in splunk docs at http://docs.splunk.com/Documentation/DBX/2.4.0/DeployDBX/Checklist .

Next you need to download H2 database (http://www.h2database.com/h2-2016-10-31.zip), extract and copy the bin/h2-1.4.193.jar to $SPLUNK_HOME$/etc/apps/splunk_app_db_connect/bin/lib directory.

Next configure a custom db type by creating the config file $SPLUNK_HOME$/etc/apps/splunk_app_db_connect/local/db_connection_types.conf. This is not implemented in the DB Connect WebGUI.

db_connection_types.conf:

[h2tcp]
displayName = H2-tcp
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:h2:tcp://: /
jdbcDriverClass = org.h2.Driver

[h2local]
displayName = H2-local
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:h2:/
jdbcDriverClass = org.h2.Driver

The [h2tcp] stanza defines the connection for server mode, while [h2local] defines embedded/local mode. After doing so and restarting Splunk you’ll see two new driver entries in DB Connect – stating “unsupported”

hc_272

Create credentials first, followed by a connection. Make sure to use TCP/9092 when connecting to a remote H2 instance. The remote instance has to be started using the –tcpAllowOthers parameter.

hc_274

A new connection will be saved in db_connections.conf. This is an example:

[h2remote]
connection_type = h2tcp
database = /tmp/h2demo
host = 127.0.0.1
identity = sa
jdbcUrlFormat = jdbc:h2:tcp://: /
jdbcUseSSL = 0
port = 9092

When defining an input to pull events out of the db this is done like always in inputs.conf. Here is an example:

inputs.conf

[mi_input://h2remote-users]
connection = h2remote
enable_query_wrapping = 1
index = test_high
interval = 60
max_rows = 10000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT * FROM INFORMATION_SCHEMA.USERS
sourcetype = dbx:h2
tail_rising_column_name = ID
ui_query_mode = advanced
tail_rising_column_checkpoint_value = 2

hc_275

H2 restrictions

Other than you might expect it’s not possible to use two applications writing or reading in local/embedded mode. You’ll receive the message “org.h2.jdbc.JdbcSQLException: Database may be already in use: null. Possible solutions: close all other connection(s); use the server mode [90020-193]”. This is by design and can be solved as mentioned before by starting H2 with the –tcp for local-only connections or –tcpAllowOthers for all other connections.

h2-restriction