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:
- Embedded/local mode: only local connections using JDBC
- Server mode: remote connections using ODBC or JDBC
- 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”
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.
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
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.