Hydrolix Search for Splunk

Overview

Use Hydrolix as a back-end datastore for your existing Splunk tables to take advantage of low-latency queries, long-term retention, and cost savings.

Hydrolix Search for Splunk can query raw data tables for quick charting. It does this via a new hdxsearch command for Splunk SPL, which has the following features:

• With minimal configuration, it queries your Hydrolix clusters.
• It automatically finds the primary timestamp for the specified table.
• It applies time range filtering from the Splunk UI.
• It can limit query results to protect the Splunk UI.

For simplicity, the hdxsearch command offers a simple list of fields for SELECT statements. This limitation does not apply to WHERE clauses. If you need more flexibility in the SELECT portion of your queries, see our Splunk with DB Connect method which gives you full ClickHouse SQL capabilities.

Hydrolix Search for Splunk doesn't work with Hydrolix summary tables.

Installation

In the Splunk Enterprise UI, inside the Apps menu, select Find More Apps.

Type Hydrolix into the search box and select the Hydrolix Search application from the results on the right.

Click the Install button. The installation process may require logging in to Splunkbase with your Splunk username and password.

Configuration

Cluster Credentials

On installation, you will be directed to the Hydrolix Search setup form. This example of the form has two clusters configured.

Fill in the configuration fields:

📘

Splunk queries use port 8088 on Hydrolix clusters

Hydrolix Search for Splunk uses the ClickHouse HTTP query interface to the cluster on tcp/8088.

Select a default cluster to run queries against by clicking the MAKE DEFAULT CLUSTER bubble on the right-hand side of the configuration line for that cluster.

Multiple Clusters

If you will be using more than one cluster or user account from this Splunk instance, add them to the list with the OR ADD CLUSTER option. Clicking the plus sign will open up a new row of configuration.

Save the Configuration

Once the configuration is done, select Save Changes to apply changes. Your new cluster settings will replace any previously-saved settings, and you will be automatically directed to the query screen of the Hydrolix Search for Splunk application.

🚧

Saving changes will overwrite all settings

Reconfigure an existing cluster

To reconfigure Hydrolix Search for Splunk, inside the Apps menu, select Manage Apps. Type Hydrolix into the search box and under Actions, click Set up. This will present the same cluster configuration screen as on initial installation, populated with the currently-configured clusters.

Query

The following are some example queries along with parameters and query settings which can be used to customize Splunk queries to Hydrolix.

Quickstart Sample Query

Here's an example of the query screen showing a query and results.

| hdxsearch table="hydro.logs" fields="app,rows_read,bytes_read,source_type" where="app='query-head'"

Which generates the following SQL query for a cluster using the default LIMIT clause:

SELECT app, rows_read, bytes_read, source_type
FROM hydro.logs
WHERE timestamp > toUnixTimestamp($SPLUNK_MIN_TIME)
AND timestamp < toUnixTimestamp($SPLUNK_MAX_TIME)
AND app='query-head'
LIMIT 5000

The following is a simple example. Replace my_project.my_table with the Hydrolix project and table of your own choosing:

| hdxsearch table="my_project.my_table" fields="*"

Though the results of this query can be large depending on how much data is in the cluster, the time picker in the upper right-hand corner of the query interface and the value of the LIMIT clause will act as guardrails to avoid returning too much data or using excessive compute resources.

Note that Hydrolix Search for Splunk does not support Splunk's real-time UI, so the time picker only provides relative options.

Query Parameters

As well as the required table and fields parameters, you can specify a WHERE clause, adjust the row limit, and adjust other settings as parameters to the hdxsearch command:

📘

Performance Note: Limiting Fields

Because Hydrolix is a columnar data store, the number of fields returned by the query should be limited to accelerate execution and reduce compute resources. Instead of using wildcards in the fields parameter, specify only the required columns.

Example queries

• Return all fields from my_project.my_table, limited by the Splunk UI's time picker and the default 5,000 maximum row limit

  Text

  | hdxsearch table="my_project.my_table" fields="*"
  

• Return the reqHost and reqMethod columns from my_project.my_table.

  Text

  | hdxsearch table="my_project.my_table" fields="reqHost, reqMethod"
  

• Bypass the 5,000-row limit and return rows where the reqHost field is my.hostname.com and the reqMethod is POST. The contents of the where parameter are passed along to Hydrolix in an SQL WHERE clause.

  Text

  | hdxsearch table="my_project.my_table" fields="reqHost, reqMethod" limit=0 where="reqHost IN ('my.hostname.com') AND reqMethod='POST'"
  

• Aggregates aren't supported by the simple SELECT statements available, so we depend on Splunk's SPL to count the number of rows. Use limit=0 to request all data be included in the aggregation.

  Text

  | hdxsearch table="my_project.my_table" fields="reqHost, reqMethod" limit=0  
  | stats count by reqHost
  

• Output the raw value of the UA field into the Event column of the SQL query result.

  Text

  | hdxsearch table="my_project.my_table" fields="reqHost, reqMethod, UA" raw="UA"
  

• Run a query on a named cluster. This requires a defined cluster in the Hydrolix Search App with the name SecondCluster..

  Text

  | hdxsearch table="my_project.my_table" fields="reqHost, reqMethod" cluster="SecondCluster"
  

Default query settings

Query settings in Hydrolix clusters implement configurable limits to protect cluster resources and the applications receiving results.

Circuit breakers

Hydrolix Search for Splunk uses the following circuit breakers for queries.

❗️

These circuit breakers are not configurable.

❗️

Tripping the circuit breakers returns an error rather than results

If a query triggers a circuit breaker, an error will be displayed in the Splunk UI. There will be no results.

Query options

Hydrolix Search for Splunk uses the following default value for query caching with each query.

You can use the SQL SETTINGS clause when executing queries to disable this default query option or to use additional options.

LIMIT clause

Hydrolix Search for Splunk attaches a LIMIT 5000 clause to each query by default which limits a result set to 5000 rows. This limit can be adjusted when setting up the Splunk configuration by setting the Default result count limit or in the search console.

🚧

The Default result count limit should not be higher than hdx_query_max_result_rows

The cluster configured Default result count limit attaches a LIMIT clause for all hdxsearch queries to that cluster. The non-configurable hdx_query_max_result_rows circuit breaker caps count of response rows in the Hydrolix query system. Do not set Default result count limit larger than hdx_query_max_result_rows. Doing so will result in the circuit breaker tripping before the limit is ever reached.

Troubleshooting

• “Invalid Argument" messages when making queries.
  • This usually means that your table name or field name(s) don’t exist. Double check your table and field names.
• The query returns no results and there is an Error in 'hdxsearch' command in the Splunk UI. This may mean you have hit the hdx_query_max_result_rows circuit breaker. You can try the following to have query results return prior to the circuit breaker tripping:
  • Lower the Default result count limit setting if it is above hdx_query_max_result_rows.
  • Use the LIMIT clause to limit the number of rows returned.
  • Filter the data returned with a WHERE clause
  • Retrieve portions of data using an OFFSET...FETCH clause