Splunk® DB Connect

Deploy and Use Splunk DB Connect

Download manual as PDF

NOTE - Splunk DB Connect version 1.x reached its End of Life on July 28, 2016. Please see the migration information.
This documentation does not apply to the most recent version of DBX. Click here for the latest version.
Download topic as PDF

Use database search commands

Splunk DB Connect provides the following commands for reading and writing to your database:

  • dbquery
  • dbinfo
  • dboutput
  • dbmonpreview

These commands are typically invoked as part of a search string with the following format:

    index=<myIndexName> | <DBConnectCommand>

You can also invoke the dbquery and dbinfo commands via the Explore database schema panel in the Splunk DB Connect app UI.

Note: For the commands described below, use the backslash (\) character to escape a literal backslash. For example, to escape the backslash in C:\home, use C:\\home. You do not need to escape backslashes in the \n, \r, and \t, and similar, formatting character codes.

dbquery

The dbquery command queries the specified database and returns table rows as Splunk search results.

Note: The dbinput command is an alias for the dbquery command.

Note: This command is for previewing database search results, and is not intended for regular use.

Syntax

dbquery <sql-database> <dbquery-sql>

Arguments

Argument Description
sql-database Name of a configured database listed in the database.conf file.
dbquery-sql The SELECT query string to execute.
Format options:
     "databaseName"
     db=databaseName
     database=databaseName

Example

| dbquery "mysql" "SELECT * FROM hosts WHERE active = 1" limit=25

Note: There is no "limit" argument (or default limit) for the number of rows the dbquery command returns. The limit command in the above example is a Splunk search command, which demonstrates how to return a more manageable (smaller) set of rows, in case your database table has many hundreds or thousands of rows. (You can use DB Connect and Splunk commands in the same search string.)

dbinfo

The dbinfo command retrieves database/table schema information as search results. The command relies on the JDBC generic metadata mechanism.

Note: The DB Connect UI includes a DB Info view that lets you interactively inspect the structure of your database. Use the DB Info view as a convenient alternative to running the dbinfo command directly.

Syntax

dbinfo type=<dbinfo-type> database=<sql-database> (table=<sql-table>) [flags...]

Arguments

Argument Description
type Type of information to retrieve:
tables = All specified database table names; (see flags qualifier)
columns = All column information for specified table; (see flags qualifier)
schemas = All database schema names
size = Specified table size (TBD) information
database Name of a configured database listed in the database.conf file.
table Name of database table.
flags The type option is qualified by by the following flag options:
tables:
     fetchSize= get table size (true or false)
     schemas= All ("*"), or table schema name
     (Optional) includeViews= true or false
columns:
     table= database table name
     (Optional) forceRefresh= Refresh first (true or false)
schemas: (No additional qualifying arguments)
size:
     table= database table name
     (Optional) forceRefresh= Refresh first (true or false)

Example

|  dbinfo type=columns database=mysql table=general_log

Note: The dbinfo command does not take an index (e.g. index=_internal) as the first part of the search string. Rather, you must specify the database you wish to access, as shown in the above example.

dboutput

Caution: The dboutput command overwrites existing database entries so use with caution.

The dboutput command provides database write capability. The command updates or creates records in the specified database table, for each result.

Note: The dboutput command is compatible with historical (non real-time) search queries only. Historical search queries use time ranges that are not real-time, such as -15m or -1d. The dboutput command is not compatible with real-time searches.

Syntax

dboutput type=<dboutput-mode> [streaming=<false|true>] (database=<sql-database>) ("<sql-statement>") | (table=<sql-table>) (key=<field>|keyField=<field> keyColumn=<sql-column>)? (notFound=<string>)? ((<field>( as <sql-column>)?)+|"*")

Arguments

Argument Description
type Output mode:
insert = Insert record
update = Update existing record
streaming (Optional) Stream data flag:
true = Stream data. Transfers data in multiple segments, so the process runs in multiple transaction with each segment representing a transaction. If any segment fail, transfer continues without recovering the failed segment, which may result in the loss of a record in one transaction. Use this for less critical data, such as performance metrics.
false = (Default, the same as not specifying the argument) Output is limited to 50,000 search results.
database Name of a configured database listed in the database.conf file.
"<sql-statement>" SQL write string.
table SQL table name
key (Optional) Only applies to type=update. The key columns to use for the SQL UPDATE statement.
keyField (Optional) SQL table key field, used with keyColumn option (if not using key option)
keyColumn (Optional) SQL table key column, used with keyField option.
notFound (Optional) Behavior if key is not found:
insert = Insert result, instead
ignore = Do nothing
fail = Rollback changes, if possible, and fail the execution
<field> as (Optional) Fields used for the update in the form of <fieldName> [AS <columnName>]. Use * as a wildcard.

Examples

Example 1

index=_internal | dboutput type=insert database=ASSET_DB table=jobs key=jobname host=myAssetsHost

Example 2

index=_internal | dboutput ASSET_DB table=jobs columns

Equivalency examples

These two examples are equivalent:

index="_internal" | dboutput database=mydb type=sql "INSERT INTO udata_test (host, sourcetype) VALUES ($host$, $sourcetype$)"

index="_internal" | dboutput database=mydb type=insert table=udata_test host sourcetype

These two examples are also equivalent:

index="_internal" | dboutput database=mydb type=sql "UPDATE udata_test SET host=$host$, sourcetype=$sourcetype$ WHERE uid=$uid$"

index="_internal" | dboutput database=mydb type=update table=udata_test key=uid host sourcetype

Example 3

Note: Use care when piping database queries through search commands as some commands might result in invalid SQL character sequences. For example, in the following search, for the query to work properly, you must rename the stats field, as shown:

index=_internal | stats dc(source) dc(sourcetype) | rename dc(source) AS dcs dc(sourcetype) AS dct | dboutput database=mysql type=sql "INSERT INTO t1 (a, b) VALUES ($dct$, $dcs$)"

dbmonpreview

The dbmonpreview command simulates the output of a database input.

1. Create a database input in inputs.conf, as shown in Configure input query parameters.

This creates a dbmon- stanza in the inputs.conf file.

2. Use the dbmonpreview command to preview the input.

Syntax

dbmonpreview <stanza> (<key>=<value>)

Arguments

Argument Description
stanza Input stanza name, prefixed with: dbmon-
key SQL database key

Example

| dbmonpreview dbmon-dump://mysql/t1

For more information on using Search, see the Splunk Search Manual in the Splunk Enterprise documentation.

PREVIOUS
Set up search head pooling
  NEXT
Troubleshooting

This documentation applies to the following versions of Splunk® DB Connect: 1.1, 1.1.1, 1.1.2, 1.1.3, 1.1.4, 1.1.5, 1.1.6, 1.1.7, 1.2.0, 1.2.1, 1.2.2


Comments

Jossplacencia, it's not completely clear to me what you're trying to do here. Regardless, I think this kind of troubleshooting is best performed by a support professional rather than in comments on a doc page. Please contact Splunk Support. They'll be able to help! --Matt

Mtevenan splunk, Splunker
January 8, 2016

I don't see the inserts into my table
I have the following code in my xml:
------------------------------------------
<form>
<label> Insert Team Member </label>
<description/>
<fieldset>

<input type="text" token="ID">
<label>Enter ID</label>
</input>
<input type="text" token="Nombre">
<label>Enter Name</label>
</input>
<input type="text" token="Email">
<label>Enter Email</label>
</input>
</fieldset>

<searchTemplate>
|dboutput database=test type=sql "INSERT INTO TMember (ID,Nombre,Email) VALUES ($ID$, $Nombre$, $Email$)"
</searchTemplate>
<row>
<panel>
<table>
<option name="showPager">true</option>
<option name="count">20</option>
</table>
</panel>
</row>
</form>
------------------------------------------------------------------------------------
but at the end only shows the message:
No results found.
and the insert data is not reflected y my database
HELP PLS

Jossplacencia
December 18, 2015

Dimitryz,<br /><br />Thanks. We've updated the dbmonpreview example. The correct syntax requires the stanza to follow the command. So in your case, you would enter:<br /><br />| dbmonpreview dbmon-tail://OracleXE/hr_test_tm3<br /><br />Thanks

Sroback splunk
April 24, 2014

dbmonpreview example don't seem to work also :<br /><br />index=_internal | dbmonpreview<br /><br />returning :<br /><br />Error in 'dbmonpreview' command: This command must be the first command of a search.<br />The search job has failed due to an error. You may be able view the job in the Job Inspector.<br /><br />I do have a stanza <br />source="dbmon-tail://OracleXE/hr_test_tm3" as explained in example <br /><br />How can I use the stanza with dbmonpreview ?<br /><br />Regards,<br />Dmitry

Dimitryz
April 24, 2014

Pasito,<br /><br />Yes, you are correct with respect to the dbinfo command. We've updated the dbinfo command example to show the correct usage.<br /><br />Thanks

Sroback splunk
December 3, 2013

The examples don't seem to be valid or at least in there current form. It doesn't seem to be possible to have index= | ...<br /><br />e.g<br /><br />index=_internal | dbinfo ASSET_DB table=jobs columns <br /><br />Will return an error in splunk:<br /><br />Error in 'dbinfo' command: This command must be the first command of a search.

Pasito
November 29, 2013

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters