Create and manage database outputs

A database output object lets you define how to send data from Splunk Enterprise to a database on a recurring basis. Defining database outputs is useful if you want to store your indexed Splunk Enterprise data in a relational database.

Create a database output

From within Splunk DB Connect, click the Data Lab > Outputs tab and click New Output.

Complete the following steps to create a database output.

1. Set up search. Perform a SPL or a saved search to search the event data in Splunk platform.
2. Choose table. Choose the table from the database which Splunk event data will be exported to.
3. Fields mapping. Map the Splunk Enterprise fields you selected to columns in your database.
4. Set properties. Configure the basic settings and parameters of the output.

Set up search

In this step, you define the Splunk Enterprise fields that you want to output to the database table as columns:

1. Perform a search of your Splunk Enterprise data. You can either enter a search using the Search Processing Language (SPL), or you can run a saved search.
2. To select a saved search, click Saved Search and then choose the one you want to use from the drop-down list. Splunk Enterprise performs the search and displays the results in a table. Each column corresponds to a Splunk Enterprise field.
3. Specify the time range you want to run the search. Be aware that you cannot choose a real-time time range since it will keep running the search all the time. It is a known issue in DB Connect 3.1.0.

Tips:

• You can fine-tune the format of your output directly from this search field by using standard search commands. For example, if you want Splunk Enterprise to display and use output to send data to your database as key-value pairs, use the eval search command here. If you want to change the quoting pattern, use rex.
• If you want Splunk Enterprise to send a specific number of rows each time the output runs, use the head search command. For example, to specify that the output should send no more than 1000 rows each time it runs, define your search as follows:

index=main sourcetype=foo status=ERROR | head 1000

• If you want to use the Saved Search, set the permission of the saved search to This app only (splunk_app_db_connect) or All apps.

4. Run the search and if you are ok with the result, click Next.

Choose table

In this step, you choose the table from the database which Splunk event data will be exported to.

1. Connection: Choose the database connection you want to use with this output. DB Connect validates the connection and displays an error message if it is not able to do so. You cannot continue the new output setup process unless you have specified at least one valid connection from the Connection dialog.
2. From the corresponding dialog menus, choose the Catalog, Schema, and Table that contain the columns you want to map the Splunk event data to.
3. Once you have chosen the table, you can preview the data on the Table Schema Preview page. It lists the column name, data type, column size and related fields of the table.
4. Click Next.

Fields mapping

In this step, you map Splunk data field(s) to the database column(s).

• Fields Mapping. Click Add Search Field and choose the field name of Splunk data you want to output. Then select the column name in the Table Column field. The data of the selected field will be exported to the column in your database table. You can add multiple Splunk fields/table columns pairs if you need to.
• UPSERT Configuration. UPSERT allows either inserting a row, or on the basis of the data of the column already existing, UPDATE that existing row instead. If you choose to Enable UPSERT, then specify the Key column in your table.
  
  UPSERT Example: UPSERT is enabled and the id column is set as the key column.

  id	name
  1001	Jack
  1002	Michael

  If there are two data events to be exported to this table, one is 'id=1001, name=Dianel', the other is 'id=1003, name=Leo', then the updated table is

  id	name
  1001	Dianel
  1002	Michael
  1003	Leo

Set properties

Basic information

• Name: The output name cannot contain any spaces. Do not use special characters in the output name.
• Description: Optional. The short description of this output.
• Application: The name of the Splunk Enterprise app in which DB Connect saves this output object. By default, the pop-up menu selects Splunk DB Connect. This menu enables other apps to use DB Connect inputs, outputs, and lookups within their own context.

Parameter Settings

• Query Timeout: Enter the number of seconds for a single query to wait to complete. The default is 30 if you leave it blank.
• Batch size: Enter the size of a batch. The default is 1000 if you leave it blank.
• Scheduling: Configure Execution Frequency if you want to schedule this output, you can enter the number of seconds or a valid cron expression e.g. 0 18 * * * (every day at 6PM).

Edit database outputs

To see a list of the database outputs you defined, navigate to the Configuration > Outputs tab.

To edit a database output, click its name. You can make changes to a database output using the following buttons on the output page:

• Enable/Disable: Enable or disable an output.
• Edit: Edit an output by clicking its name or the Edit button.
• Clone: Create a copy of the output. You must give the copy a unique name.
• Delete: Delete the output.

You can also edit any of the attributes of a database output listed in Create a database output, except its name. To change the name of an output, clone it, give the clone the name you want, and then delete the original output.

Enable output to multi-byte character sets

DB Connect can send data that is in a multi-byte character set, such as Traditional Chinese, using a database output. Depending on your database, you may need to change certain settings to the database to properly receive and store the data.

• MySQL: When creating a connection to a MySQL database, customize the JDBC URL by adding some additional query parameters. For more information, access MySQL documentation for Inserting unicode UTF-8 characters into MySQL.
• PostgreSQL: By default, this database supports multi-byte character sets. You do not need to take additional steps.
• Microsoft SQL Server: Ensure your database columns' data types are N-variant types, such as NVARCHAR versus VARCHAR).
• Oracle: Change your database character set to AL32UTF8. For more information, access Oracle's documentation for Supporting Multilingual Databases with Unicode.
• Other databases: Consult your database's documentation for more information about enabling multi-byte character sets.

Use database outputs

Database outputs run automatically at the frequency you set during the "scheduling" step of the setup process. To verify that database outputs are working properly, query your database after a few executions of the output operation to ensure that DB Connect is sending your Splunk Enterprise data properly.

• DB Connect 3 does not support running scheduled tasks (input or output) on the search head in the Search head cluster deployment. You must run the scheduled task on a heavy forwarder.
• In a distributed environment, the heavy forwarder will need to be able to search the Indexers in order to output to the DB. See Deploy a distributed search environment in the Distributed Search manual to learn how to set up distributed search on your deployment's heavy forwarders.

Use modular alert to run database output

DB Connect provides a modular alert which allows users to actively respond to events and send alerts. You can configure the DBX output alert action on the Alert Actions Manager page. See alert action manager.

To use the DB Connect modular alert:

1. Navigate to the Search page in DB Connect.
2. Create a search, then select Save as > Alert.
3. Enter the title and optional description.
4. In the Trigger Actions field, select DBX output alert action.
5. Enter the Output Name. The output name must exist in DB Connect.

For details about custom alert, see Create custom alert.

Use dbxoutput command to run database output

dbxoutput is a search command you can use to run database outputs that you have defined in DB Connect.

Syntax

   dbxoutput output=<string>

Required Arguments

output

Syntax: output=<string>

Description: Name of a configured database output object.

chunksize

Syntax: <integer>

Description: Specifies the number of events that will be processed to be output to the database in a single operation (not to be confused with the batch_size property of the DB Output).

Default: 1000.

Example

The following example uses the output dbx_output to send the results of a search query to a database.

<search query> | dbxoutput output="dbx_output"