Execute SQL statements and stored procedures with the dbxquery command

Splunk DB Connect has the dbxquery command for executing SQL statements and stored procedures within Splunk Enterprise searches and dashboards. Use SQL explorer to edit your query or write the dbxquery based on the syntax below.

Description

dbxquery is a custom search command for querying remote databases and generating events in Splunk Enterprise from the database query result set.

Syntax

   dbxquery connection=<string> [fetchsize=<int>] [maxrows=<int>] [timeout=<int>] [shortnames=<bool>] query=<string> OR procedure=<string> [params=<string1,string2>]

Required Arguments

connection

Syntax: connection=<string>

Description: Name of a configured database connection object.

query or procedure

query

Syntax: query=<string>

Description: A SQL query. You can also use a URL-encoded SQL query, but you must percent-encode all spaces (%20).

procedure

DB Connect supports stored procedures in databases beginning with version 3.0.0. A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name. It is stored in the database in compiled form so that several programs can share it. You can call the stored procedures of your database by using a procedure argument instead of a query argument. Many stored procedures expect variables in the form of an ordered list of arguments. You can pass these variables from Splunk to the stored procedure using the params argument. Note that the dbxquery command is a generating command and needs to be the first command in a search. See Cross-database queries on this page for examples of the syntax needed to pass parameters to dbxquery.

Syntax: procedure=<string>

Description: A stored procedure invocation.

This table lists procedure syntax formats for different types of databases.

Supported databases	Syntax
AWS RDS Aurora Informix MemSQL Microsoft SQL Server MySQL SAP SQL Anywhere Sybase ASE Sybase IQ IBM DB2 for Linux Teradata	dbxquery procedure="{call <procedure-name>}"
Oracle	dbxquery procedure="{call <procedure-name>(?)}"
Postgres AWS RedShift	dbxquery procedure="{?=call <procedure-name>}"

Important notes on stored procedure support in DB Connect:

• dbxquery only supports stored procedures which return a single result set.

IBM DB2 examples

• If you use an IBM DB2 for Linux or Teradata database, you must return the result as Cursor type and open it within the stored procedure. See the IBM DB2 for Linux stored procedure example below for details.

Stored procedure example (IBM DB2 for Linux)

CREATE OR REPLACE PROCEDURE TEST_IBMSP (IN actor_id varchar(30)) 
    DYNAMIC RESULT SETS 1 
  LANGUAGE SQL 
BEGIN 
 DECLARE result_set CURSOR WITH RETURN TO CLIENT FOR
 SELECT * FROM ACT WHERE ACTNO <= actor_id; 
 OPEN result_set;
END;

Oracle examples

• If you use an Oracle database, you must store the result in the first parameter and set it as OUT SYS_REFCURSOR. See the Oracle Stored procedure example below for details.

Stored procedure examples (Oracle)

Create stored procedure 1:

CREATE OR REPLACE PROCEDURE test_orasp_1(
  p_ref_cursor  OUT SYS_REFCURSOR, 
  p_var_in      IN  VARCHAR)
AS
BEGIN
 OPEN p_ref_cursor FOR
 SELECT 'you passed-in: '|| p_var_in out_var FROM dual;
END test_orasp_1;

Use stored procedure 1:

| dbxquery connection=splunk_test procedure="{call test_orasp_1(?,?) }" params="foo"

Create stored procedure 2:

CREATE OR REPLACE PROCEDURE TEST_ORASP_2(
    ref_cursor  OUT SYS_REFCURSOR,
    id          IN  VARCHAR)
AS
BEGIN
  OPEN ref_cursor FOR 
    SELECT * FROM soe.customers WHERE customer_id = id;
END TEST_ORASP_2;

Use stored procedure 2:

| dbxquery connection=splunk_test procedure="{call test_orasp_2(?,?) }" params="50865"

| makeresults count=1
| eval cust_id="50865"
| map search="| dbxquery connection=splunk_test procedure=\"{call test_orasp_2(?,?) }\" params=\"$cust_id$\" "

Stored procedure example (MS SQL Server)

CREATE PROCEDURE test_sp_no_param
AS
BEGIN
 SET NOCOUNT ON;
 SELECT * FROM city as c1 LEFT JOIN country as c2 on c1.country_id=c2.country_id;
END
GO

Optional Arguments

fetchsize

Syntax: fetchsize=<int>

Description: The number of rows to return at a time from the database. To avoid running out of memory, the query result set is divided into multiple pieces and returned to DB Connect one piece at a time. This argument specifies how many rows are in each of those pieces. Depending on the RPC server's maximum heap size and whether the target database table contains any unusually large columns, you may want to specify a smaller value for this argument. The maximum value for this option is 10,000.

Default: varies per database type

maxrows

Syntax: maxrows=<int>

Description: The maximum number of rows to return. If you do not specify a value for maxrows, dbxquery returns 100,000 rows at most. There is no maximum value for this argument, but retrieving a very large number of records may result in performance issues or out-of-memory messages. In this case, you should experiment with setting maxrows to a lower number that is manageable by your Splunk Enterprise server hardware.

Default: maxrows=100000

params

Syntax: params="string1,string2"

Description: The value(s) of the variable(s) you defined in query or procedure. The value of the params is in CSV format.

Note: The value of params="BOB" is different from params=" BOB". The space character is not skipped inside the quotation marks.

Example:The values of actor_id and actor_name are defined in params="3,BOB", which is 3 and BOB respectively.

dbxquery query="select * from actor where actor_id > ? and actor_name = ?" connection="mysql" params="3,BOB"

Another example for using stored procedure, 150 and BOB are the values for the variables in the procedure sakila.test_procedure.

dbxquery procedure="{call sakila.test_procedure(?, ?)}" connection="mysql" params="150,BOB"

shortnames

Syntax: shortnames=<bool>

Description: By default, the returned fields will be in the TABLE> form.<COLUMN>.<DATATYPE>. Set the shortnames argument to true to return fields called <COLUMN>.

Default: shortnames=true

Note: To emulate the output format of the dbquery command from DB Connect 1.x.x, set the output to csv and shortnames to true.

timeout

Syntax: timeout=<int>

Description: Specifies the timeout of your query in seconds. Set to zero to allow unlimited execution. Typically the value should be less than query job lifetime, which defaults to 10 minutes (600 seconds). For more information, see http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int).

Default: timeout=600

Examples

| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL" 

| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL" maxrows=100

Cross-database queries

DB Connect 2.2.0 and later includes improved support for using the dbxquery command in complex Search Processing Language (SPL) statements. You can now use dbxquery with subsearches, so that you can effectively perform cross-database queries—enriching or filtering data on the fly.

Using a command such as append with dbxquery, you can run a separate database search and add the output to the first search. For example, the following search query appends results obtained from a SQL Server database connection object to results obtained from a MySQL database connection object:

| dbxquery connection=mysql query="SELECT * FROM sakila.city" | append [dbxquery connection="sql_server" query="SELECT * FROM sakila.actor"]

You can use any of the following search commands to produce cross-database query results:

• append: Appends the results of a subsearch to the current results.
• appendcols: Appends the columns of the subsearch results with the input search results.
• join: Combines the results of a subsearch with the results of a main search. One or more of the columns must be common to each result set.
• map: Runs a search repeatedly for each input record or result. You can run the map command on a saved search, a current search, or a subsearch.

For more information about subsearches:

• Read About subsearches in the Splunk Enterprise Search Manual.
• Read How to use the search command in the Splunk Enterprise Search Manual.

Incompatible Arguments

The following dbxquery arguments are not functional. They do not produce errors if you leave them in the command line.

• wrap
• output

You can't use INSERT or UPDATE statements with dbxquery. Use dbxoutput if you want to write data to your database.