Splunk with DB Connect

Splunk can be used as a front-end to your Hydrolix cluster database. While we recommend the Hydrolix Search for Splunk method for ease of use and smaller system footprint, it can also be set up with our custom driver for the DB Connect Splunk Application. Splunk DB Connect gives users the ability to run SQL queries leveraging ClickHouse functions against a Hydrolix cluster.

Prerequisites⚓︎

• Make sure you have a Java Runtime Environment (JRE) available.
• Install Splunk Enterprise on your host.
• Install Splunk DB Connect into your Splunk Enterprise server.
• Ensure you have at least 5 GB of free disk space. Splunk won’t search your data unless it finds this much or more.

Install the Hydrolix JDBC Driver⚓︎

After installing Splunk DB Connect, download and install the Hydrolix JDBC Driver. This file must be copied to the correct file location and then described in a configuration file. In the examples below, $SPLUNK_HOME is the directory where your Splunk server is installed. On Linux systems, this is often /opt/splunk.

Find your drivers directory and copy the Hydrolix JDBC Driver into it. Version numbers and download paths may differ from the example below:

1
2
3
4

cd Downloads
tar xvzf hydrolix-jdbc.tgz
cd $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers/
cp ~/Downloads/hydrolix-jdbc-0.6.0.jar .

Update your connection type configuration to add Hydrolix. Edit this file in the $SPLUNK_HOME directory:

1

etc/apps/splunk_app_db_connect/default/db_connection_types.conf

Append the following entry to the bottom of the file:

1
2
3
4
5
6
7

[Hydrolix]
displayName = Hydrolix
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:clickhouse:http://<host>:<port>/<database>
jdbcUrlSSLFormat = jdbc:clickhouse:http://<host>:<port>/<database>?ssl=true
jdbcDriverClass = com.clickhouse.jdbc.ClickHouseDriver
ui_default_catalog = $database$

Restart your splunk instance using $SPLUNK_HOME/bin/splunk restart, or through the Splunk UI through “Settings->System:Server controls.”

Configure Identity with Credentials⚓︎

After your server has restarted, select the Splunk DB Connect application in the main menu:

Then under the Configuration tab, create a new basic identity:

On the "New Identity" page, add the username and password you are using to connect to Hydrolix.

Save and you should see your new identity in the UI.

Create a New Database Connection⚓︎

Finally, create a new database connection, making these changes to the form:

• Enter a new name for your new connection.
• Select the identity you just created in the step above.
• Make sure the connection type is “Hydrolix.”
• Put your Hydrolix cluster’s domain name in the “Host” field.
• Use port 8088 to access the Hydrolix cluster.
• Enter your Hydrolix project name as your “Default Database.” Later, you’ll use SQL syntax to select which table to query.
• Under “Connection Properties,” add a property whose key is “compress” and value is “0.”

You should have something which looks like the following:

Click the "Save" button in the upper-right corner and wait for it to validate and save the connection.

Query Your Data⚓︎

Use a query in the Splunk Search & Reporting application. For example, this query will show entries from the Hydrolix log:

1

| dbxquery connection="HydrolixDemo" [makeresults | addinfo | eval query="SELECT app, container, message FROM hydro.logs WHERE timestamp >= toDateTime(".info_min_time.") AND timestamp <= toDateTime(".info_max_time.") LIMIT 1000" | return query]

This query will replace .info_max_time. and .info_min_time. with the time range from the Splunk filter.