JDBC Driver for Splunk

The JDBC driver for Splunk runs SQL queries against Splunk for any software that supports JDBC including Tableau. SQL support includes functions, expressions, aggregation, and joins. Unlike the Splunk ODBC driver (no longer maintained and supported), the Splunk JDBC driver supports both saved searches and dynamic searches where an arbitrary search command can be sent to Splunk. The Splunk Solutions team created setup instructions for using the Splunk JDBC Driver with Tableau.

Simple Licensing

Terms and Agreement

Download

Splunk JDBC driver

Getting Started

Get started in 5 minutes

Features

  • Access data in Splunk from any software including Tableau, Oracle, SAP Lumira, and others.
  • Manipulate and control data using standard SQL functions not natively supported by Splunk for both saved searches and dynamic searches.
  • Perform SQL joins across Splunk searches. Join and merge data with other relational and NoSQL systems.
  • Full database and result set metadata support.

How it works

  • The SQL query is validated and translated into a Splunk REST request sent via the Splunk Java SDK API.
  • Metadata is exposed to relational systems through JDBC by building a schema by retrieving metadata and search results provided by Splunk.
  • SQL features not natively supported by Splunk are executed using the UnityJDBC virtualization engine.

Getting Started

Start using the JDBC Driver for Splunk in 5 minutes:

  1. Download the UnityJDBC Driver distribution which contains the Splunk JDBC Driver and sample JDBC code for querying Splunk. The sample program must be run against your own Splunk instance.
  2. Configure the connection. The JDBC Driver for Splunk can be used with any query or reporting software that supports JDBC. The connection information is: 
    	JDBC Driver class name:  splunk.jdbc.SplunkDriver
	URL format:              jdbc:splunk://<server>
    As part of the connection string, connection arguments can be passed into the driver that will affect its behavior.
    Connector Argument Details
    AttributeValuesDescription
    debug true, false The debug property will cause the driver to print out debug information to the console during its operation.
    user <username> User name for connection.
    password <password> Password for connection.
    encoding utf-8 Character encoding used.
    schema <file_location> File location of schema. Default schema location is splunk_schema.xml in the current directory.
    rebuildschema true, false If true, rebuilds schema for connection. If false, uses existing cached schema if available. Uses location provided in schema property.
    maxcsvrows 50000 If a query will have less than this number of rows, results are retrieved using a Splunk Job using CSV. If there will be more than this number of rows, then the job is done as an export job with JSON as output. CSV output is faster for smaller results. Set the value to 0 to always force JSON export jobs to be performed.
  3. Connect to a database and run your queries. To take the JDBC Driver for Splunk for a test drive, use your own Splunk instance. If you do not have an instance, follow this tutorial to quickly set one up. Sample connection information is: 
    URL:     jdbc:splunk://localhost:8089
Driver:  splunk.jdbc.SplunkDriver

    If the submitted query cannot be natively handled by the JDBC Driver for Splunk (such as for queries containing JOIN, GROUP BY or HAVING clauses), the query will be promoted to UnityJDBC for processing. UnityJDBC will parse the query into subqueries that will then be run on the specific Splunk tables and process the intermediate results to produce the final result.

    The trial version has no expiration date and is fully functioning except that it is limited to returning up to 100 results. If your query produces more than 100 results, purchase a Splunk JDBC license here for access to the complete result set.

Example Code Walkthrough

  1. Create a new instance of the JDBC Driver for Splunk (optional).
    2. 	Class.forName("splunk.jdbc.SplunkDriver");
  2. Connect to the URL. A schema will be created and cached. Control the schema location using the schema URL parameter. Note that caching schemas is recommended for good performance.
    3. 	String url="jdbc:splunk://localhost:8089?schema=splunk_schema.xml";
	con = DriverManager.getConnection(url, "admin", "admin");
  3. Create a statement and submit a query on a saved search.
    4. 	stmt = con.createStatement();
        // This query is refering to an already existing saved search in Splunk
	String sql = "SELECT bytes, clientip FROM \"Errors in the last 24 hours\"
	              WHERE bytes > 1000 ORDER BY bytes DESC LIMIT 10;";
	rst = stmt.executeQuery(sql);
  4. Create a statement and submit a query with a dynamic search.
    5. 	stmt = con.createStatement();
        // This query is a dynamic search executing any Splunk search command
	String sql = "SELECT * FROM DynamicSearch WHERE search = 'search error | head 10';";
	rst = stmt.executeQuery(sql);
  5. Print out your results.
    6. 	ResultSetMetaData meta = rst.getMetaData();
	int numColumns = meta.getColumnCount();
	System.out.print(meta.getcolumnName(1));
	for (int j = 2; j <= meta.getColumnCount(); j++)
		System.out.print(", " + meta.getColumnName(j));
	System.out.println();
	while (rst.next())
	{
		System.out.print(rst.getObject(1));
		for (int j = 2; j <= numColumns; j++)
			System.out.print(", " + rst.getObjects(j));
		System.out.println();
	}
  6. Close the statement and connection.
    7. 	rst.close();
	stmt.close();
	con.close();

Features and SQL Support for the JDBC Driver for Splunk

The following table shows which SQL queries can be handled directly by the JDBC driver for Splunk. Queries that cannot be run natively through the JDBC driver for Splunk will be automatically promoted to the UnityJDBC virtualization engine. Note: The trial version of UnityJDBC will return up to 100 results. If your query produces more than 100 results, upgrade your UnityJDBC license here. Note that the ODBC Driver released by Splunk does not support access from Java programs.

Important Notes:

  • Case-sensitive identifiers: SQL identifiers (table and field names) are not case-sensitive. Use delimited-identifiers (with double-quotes) to force case-sensitivity in SQL and always use double-quotes for SQL reserved words (e.g. "select").

  • SQL functions: Common SQL functions including string functions (concat, substr, instr), date functions (e.g. dateadd, datediff, day), trigonometry functions, mathematical functions (e.g. abs), bit functions, and others are supported. Complete list of SQL functions supported.

  • JDBC DatabaseMetaData and ResultSetMetaData Support: The JDBC Driver for Splunk supports the JDBC API allowing interoperability with database software using SQL and JDBC. This includes support for DatabaseMetaData and ResultSetMetaData. A schema is built on connection and can be cached (see Connection Parameters for more details). For DatabaseMetaData, common API methods used include getTables, getColumns, getPrimaryKeys, getIndexInfo, getDatabaseMajorVersion, among others.

  • Large requests - Large requests are handled by Splunk export jobs which have unlimited size.

Feature SQL Example JDBC Driver for Splunk UnityJDBC Virtualization Support
SELECT Clause
Select all fields from a saved search 
SELECT *
FROM   TestSearch
Select all fields from an index 
SELECT *
FROM   idx_main
Details
Details
Select a list of fields from a saved search 
SELECT bytes, clientip
FROM   TestSearch
Return results of dynamic search (To use filter the search field on the Splunk search expression and use the table name DynamicSearch.) 
SELECT *
FROM   DynamicSearch
WHERE  SEARCH = 'search error | head 10'
Select with field aliases 
SELECT bytes, clientip AS Client_IP_Address
FROM   TestSearch
Select expressions 
SELECT bytes/1024, 'IP:'+clientip
FROM   TestSearch
Select distinct 
SELECT DISTINCT clientip
FROM   TestSearch
FROM Clause
One table (search) query 
SELECT *
FROM   TestSearch
Multiple table query 
SELECT *
FROM   TestSearch S, TestSearch S2
WHERE  S.clientip = S2.clientip
Inner join clause 
SELECT *
FROM   TestSearch S INNER JOIN TestSearch S2
       ON S.clientip = S2.clientip
Left outer join clause 
SELECT *
FROM   TestSearch S LEFT OUTER JOIN TestSearch S2
       ON S.clientip = S2.clientip
Right outer join clause 
SELECT *
FROM   TestSearch S RIGHT OUTER JOIN TestSearch S2
       ON S.clientip = S2.clientip
Full outer join clause 
SELECT *
FROM   TestSearch S FULL OUTER JOIN TestSearch S2
       ON S.clientip = S2.clientip
WHERE Clause
Equality (=) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip = '207.121.32.146'
Greater than equal (>=) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  bytes >= 10000
Greater than (>) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  bytes > 10000
Less than equal (<=) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  bytes <= 10000
Less than (<) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  bytes < 10000
Not equal (!= or <>) comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip != '207.121.32.146'
Like comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip LIKE '207.%'
Two attribute comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  date_minute = date_second
Negation 
SELECT bytes, clientip
FROM   TestSearch
WHERE  NOT (clientip = '207.121.32.146')
AND 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip = '207.121.32.146' AND date_minute = 2
OR 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip = '207.121.32.146' OR date_minute = 2
NOT LIKE comparison 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip NOT LIKE '207.%'
Date and Timestamp Support
DateTime comparison with Current Time 
SELECT date_hour, _time
FROM   TestSearch
WHERE  _time < CURRENT_TIMESTAMP()
DateTime comparison using Splunk earliest and latest (can use relative time modifiers) 
SELECT date_hour, _time
FROM   TestSearch
WHERE  earliest='2014-06-10 05:31:30' AND latest='now'
group by Clause
Group by one attribute 
SELECT   clientip, COUNT(*) AS cnt
FROM     TestSearc
GROUP BY clientip
Group by multiple attributes 
SELECT   clientip, date_month COUNT(*) AS cnt
FROM     TestSearc
GROUP BY clientip, date_month
Aggregate Functions
Count(*) 
SELECT COUNT(*)
FROM   TestSearch
Count(attribute) 
SELECT COUNT(clientip)
FROM   TestSearch
Maximum 
SELECT MAX(bytes)
FROM   TestSearch
Minimum 
SELECT MIN(bytes)
FROM   TestSearch
Summation 
SELECT SUM(bytes)
FROM   TestSearch
Average 
SELECT avg(bytes)
FROM   TestSearch
Having Clause
Having clause 
SELECT   clientip, SUM(bytes) AS totalBytes
FROM     TestSearch
GROUP BY clientip
HAVING   totalBytes > 1
Order by Clause
Order by one attribute 
SELECT   _cd, linecount
FROM     TestSearch
ORDER BY _cd ASC
Order by multiple attributes 
SELECT   clientip, date_second, date_minute
FROM     TestSearch
ORDER BY date_second DESC, date_minute ASC
Order by expression 
SELECT   clientip, date_second, date_minute
FROM     TestSearch
ORDER BY date_second+1 DESC, date_minute-1 ASC
Limit/Offset Clause
Limit clause 
SELECT bytes, clientip
FROM   TestSearch
LIMIT  10
Offset clause 
SELECT bytes, clientip
FROM   TestSearch
LIMIT  10
offset 5
Union Clause
Two query union 
SELECT bytes, clientip
FROM   TestSearch
WHERE  clientip = '207.123.23.42'
UNION
SELECT bytes, clientip
FROM   TestSearch
WHERE  bytes > 10000

Setup Instructions