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.
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…
By default H2 has 2 connection modes:
- Embedded/local mode: only local connections using JDBC
- Server mode: remote connections using ODBC or JDBC
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.
[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.
[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:
[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
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.