Create and manage database inputs

A database input object lets you fetch and index data from a database. Database inputs are what enable Splunk Enterprise to query your database, identify the data to consume, and then tag and index the data. Once you have set up a database input, you can use that in the same way that you use other data input you have defined in Splunk Enterprise.

Splunk DB Connect 3.1.x supports creating inputs using the templates created in Splunk Add-ons. You'll be saved the work of repeatedly recreating basic content of inputs. The templates of the add-on will be listed in the template field of DB Connect. These add-ons include:

Create a database input

You can create a new input from scratch. Additionally, if the add-on is a Splunk Supported Add-on, you can create your new input using a template.

Complete the following instructions to create a database input in DB Connect:

Note:

  • If you have not yet created an identity or a connection, you must do so before you can create a database input.
  • If you create the input by using a template, then to make the supported add-on appear in the Templates drop-down in the DB Connect UI, you first need to copy db_input_templates.conf from your add-on's default directory to your splunk_db_connect/local directory.
  • If you create the input by using a template, all the settings from the template will be set for the new input. You can change them based on your needs, but be aware that the changes will be saved to the input you create but not to the template.
  • Inputs only support SQL queries such as "select...from...where", not DDL such as "create table..."

To create a new input, click Data Lab > Inputs and then New Input.

  1. On the Set SQL Query page, complete the following steps and then click Next to go to the Set Properties page.
    1. Choose table. Choose the database table you want to use with this input.
    2. Specify SQL query. Specify a query to run to fetch data from your database.
    3. Choose input type. Specify the input type and related settings of this input.
  2. On the Set Properties page, complete the following steps and then click Finish.
    1. Basic information. Specify the name, description and the application of this input.
    2. Parameter settings. Configure the fetch size, execution frequency and max row to retrieve of this input.
    3. Metadata. Specify the metadata of this input, the value will be used by Splunk to index your data events.

Choose Table

  1. Choose a connection that you want to use for this input from the drop-down list under the Connection field. The drop-down list lists all the connections you have configured in DB Connect, you can also enter the connection name to search the connection you want to use.
    Once you have select the connection, Splunk DB Connect will try to validate the connection, and will display an error message if it is not able to do so. You cannot continue the new input setup process unless a valid connection is specified.
  2. From the corresponding dialog menus, choose the Catalog, Schema, and Table that contain the data you want to pull into Splunk platform.

Specify SQL query

After you choose the table, the corresponding SQL query will be displayed in SQL Editor, you can preview the result of the query. If you need further edit or write your own SQL query, you can write it directly in SQL Editor and click Execute SQL to preview the result. You can make complex SQL statements easier to read by clicking Format SQL.

Note: If you want to use SQL query from template, select the template from the drop-down menu. Be aware that using a template will overwrite the previous SQL you specified.

Note: Inputs only support SQL queries such as "select...from...where", not DDL such as "create table..."

Choose input type

Specify an input type for your query, either Batch or Rising. Then configure the related fields for the input type.

  • Batch. A batch input invokes the same database query each time the input is run and returns all results.
  • Rising. A rising input has a column that DB Connect uses to keep track of what rows are new from one input execution to the next.

Batch input

A batch input invokes the same database query each time the input is run and returns all results. It does not keep track of whether rows have been added since the last time the input was run. Batch input mode is ideal for unchanging historical data that will be indexed into Splunk once. It can also be useful for re-indexing data that updates through time, such as personnel records or state tables, though this has license impact; lookups may be a better choice.

To create a batch input type,

  1. Select Batch in the Type field.
  2. Choose the Timestamp column.

Specify which column contains the timestamp that will be used to order this data in the Splunk index. This value will be used to populate the _time variable and is required for indexing time-series data.

  • Current Index Time: Assigns indexed data a timestamp value that is equal to index time. Specify this option if your data has no timestamp.
  • Choose Column: Select the column that contains the timestamp value.
    • If this column is in a format which cannot be parsed as a timestamp by DB Connect, you will be shown a field where a [/topic/topic/body/section/ul/xref {""}) Java SimpleDateTimeFormat (xref] compatible parser can be entered so that Splunk can understand the timestamp value. [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Column (b]: This option only appears if you selected [/topic/topic/body/section/ul/li/b {""}) Choose Column (b] in the previous option. Choose the column that contains the timestamps you want to use. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Query timeout (b]: Enter the number of seconds to wait for the query to complete. The default is 30 if you leave it blank. (li]
[/topic/topic/body/section {""}) [/topic/topic/body/section/title {""}) Rising input (title] [/topic/topic/body/section/p {""}) A rising input has a column that DB Connect uses to keep track of what rows are new from one input execution to the next. When you create a rising input type, you must specify the rising column. You can specify rising column as any column whose value increases or decreases over time, such as a timestamp or sequential ID. For example, you can use columns such as row_id, transaction_id, employee_id, customer_id, last_updated, and so on. [/topic/topic/body/section/p/div {""}) (div] (p] [/topic/topic/body/section/p {""}) Note that timestamps are not ideal for rising columns, though they often are the best available choice. Using a timestamp for rising column can produce the following problem conditions: (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) A high rate of event generation can cause data duplication or loss, because checkpointing in a stream of timestamp-distinguished records assumes there is never more than one row created in a given time. If you set the time to a one second level of resolution and get five records per second, you lose or duplicate four records from every run. (li] [/topic/topic/body/section/ul/li {""}) Clock skew, NTP skew corrections, physical moves between timezones, and daylight savings events can cause data mis-ordering, duplication, or loss. If the skew is towards the future, then the resulting checkpoint value may temporarily or permanently stop data collection. (li] [/topic/topic/body/section/ul/li {""}) Non-numeric datetime values cannot be evaluated numerically, and lexical sorting can produce unpredictable results. If time series data is ordered lexically, then the resulting checkpoint value may temporarily or permanently stop data collection. (li] (ul] [/topic/topic/body/section/p {""}) To create a rising input, select [/topic/topic/body/section/p/b {""}) Rising (b] and configure the following fields: (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Rising column (b]: The rising column is the column from which DB Connect will update the checkpoint value each time the input is run. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Checkpoint value (b]: The checkpoint value is how DB Connect determines what rows are new from one input execution to the next. The first time the input is run, DB Connect will only select those rows that contain the value higher or lower than the value you specified in this column. Each time the input is finished running, DB Connect updates the input's checkpoint value with the value in the last row of the checkpoint column. (li] [/topic/topic/body/section/ul/li {""}) Update the SQL query that includes a ? symbol and order by clause for the checkpoint value. The question mark (?) as the checkpoint placeholder and an [/topic/topic/body/section/ul/li/i {""}) order by (i] clause. Every time the input is run, DB Connect replaces the question mark with the latest checkpoint value .[/topic/topic/body/section/ul/li/div {""}) (div] (li] (ul] [/topic/topic/body/section/p {""}) [/topic/topic/body/section/p/b {""}) Note: (b] [/topic/topic/body/section/p/div {""}) (div] (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) From DB Connect 3 and later, the rising column checkpoints of the input are stored in [/topic/topic/body/section/ul/li/codeph {""}) $SPLUNK_HOME/var/lib/splunk/modinputs/server/splunk_app_db_connect (codeph]. For each input, it creates a separate checkpoint file.[/topic/topic/body/section/ul/li/div {""}) (div] (li] [/topic/topic/body/section/ul/li {""}) To see what column types ([/topic/topic/body/section/ul/li/codeph {""}) varchar (codeph], [/topic/topic/body/section/ul/li/codeph {""}) number (codeph], [/topic/topic/body/section/ul/li/codeph {""}) timestamp (codeph], and so on) are supported as rising column value types in DB Connect-supported databases, see [/topic/topic/body/section/ul/li/xref {""}) supported rising column types by database (xref]. (li] (ul] [/topic/topic/body/section/p {""}) With a rising column mode input, you could: (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) Effectively create a "descending column," or a column wherein the checkpoint decreases every time the input runs.[/topic/topic/body/section/ul/li/codeblock {""}) SELECT abc, xyz FROM table WHERE abc < ? ORDER BY abc DSC (codeblock] (li] [/topic/topic/body/section/ul/li {""}) Customize the comparison operation to determine the checkpoint value by evaluating a SQL function result, while simultaneously avoiding using query wrapping.[/topic/topic/body/section/ul/li/codeblock {""}) SELECT CONCAT(last_name, first_name) as NAME, ACTOR_ID, FIRST_NAME, LAST_NAME FROM actor WHERE CONCAT(last_name, first_name) > ? ORDER BY CONCAT(last_name, first_name) (codeblock] (li] [/topic/topic/body/section/ul/li {""}) Customize the [/topic/topic/body/section/ul/li/codeph {""}) WHERE (codeph] clause to add additional conditions other than the comparison expression that is specified in rising column inputs.[/topic/topic/body/section/ul/li/codeblock {""}) SELECT *FROM (SELECT * FROM ACTOR WHERE ACTOR_ID > ?) ACTOR JOIN FILM_ACTOR ON actor.ACTOR_ID = FILM_ACTOR.ACTOR_ID JOIN FILM ON FILM_ACTOR.FILM_ID = FILM.FILM_ID ORDER BY ACTOR.ACTOR_ID (codeblock] (li] [/topic/topic/body/section/ul/li {""}) Use other advanced SQL features in the [/topic/topic/body/section/ul/li/codeph {""}) WHERE (codeph] clause—for example a [/topic/topic/body/section/ul/li/codeph {""}) CASE (codeph] statement. (li] (ul] [/topic/topic/body/section/p {""}) After the input type is specified, click [/topic/topic/body/section/p/b {""}) Next (b] to go to the [/topic/topic/body/section/p/b {""}) Set Properties (b] page. (p] (section] [/topic/topic/body/section {""}) [/topic/topic/body/section/title {""}) Basic information (title] [/topic/topic/body/section/p {""}) Configure the following fields, (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Name (b]: Specify the name of the input. Be aware the input name cannot contain space or special characters. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Description (b]: Optional. The description of the input. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Application (b]: The name of the Splunk Enterprise app where this input object will be saved. By default, the pop-up menu is set to Splunk DB Connect. This menu enables other apps to use DB Connect inputs, outputs, and lookups within their own context. (li] (ul] (section] [/topic/topic/body/section {""}) [/topic/topic/body/section/title {""}) Parameter settings (title] [/topic/topic/body/section/p {""}) Complete the following fields, (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Max Rows to Retrieve (b]: Optional. The maximum number of rows to retrieve with each query. If you set this to 0 or leave it blank, it will be unlimited. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Fetch size (b]: Optional. The number of rows to return at a time from the database. The default is 300 if you leave it blank. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Execution Frequency (b]: The number of seconds or a valid cron expression i.e. 0 18 * * * (every day at 6PM). (li] (ul] (section] [/topic/topic/body/section {""}) [/topic/topic/body/section/title {""}) Metadata (title] [/topic/topic/body/section/p {""}) The metadata fields are used by Splunk to index your data events. As you search the indexed data using Splunk Enterprise, data from the query can be identified by fields value you enter here. (p] [/topic/topic/body/section/ul {""}) [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Host (b]: Optional. The host defined on the connection will be used if you leave it blank. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Source (b]: Optional. The input name will be used if you leave it blank. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Source type (b]: Enter a sourcetype field value for Splunk Enterprise to assign to queried data as it is indexed. Click the field and enter a value, or choose an existing value from the menu that appears. (li] [/topic/topic/body/section/ul/li {""}) [/topic/topic/body/section/ul/li/b {""}) Index (b]: Enter an index value for the index in which you want Splunk Enterprise to store indexed data. You can enter the index name or choose it from the typeahead menu. (li] (ul] [/topic/topic/body/section/p {""}) [/topic/topic/body/section/p/b {""}) Note: (b] If you want to use the customized index, you have to make sure the index exists in Splunk platform. Otherwise you have to create the index in Splunk Enterprise first to prevent data loss. (p] (section]

Edit database inputs

To see a list of the defined database inputs, first click the Data Lab>Inputs tab. You will see a list of your database inputs.

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

  • Enable/Disable: Disable an input by clicking Enable/Disable here.
  • Edit: Edit the input by clicking the name or the Edit button.
  • Clone: Creates a copy of the input. You must give the copy a new name.
  • Delete: Deletes the input.

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

Supported rising column types by database

The following matrix summarizes what column types (varchar, number, timestamp, and so on) are supported as rising column value types in DB Connect-supported databases.

[/topic/topic/body/section/p {""}) The column marked [/topic/topic/body/section/p/b {""}) x (b] means this column is supported.[/topic/topic/body/section/p/div {""}) (div] * Information for Informix databases is not currently available. (p]

Use database inputs

Once you've configured a database input and your data has been indexed by Splunk Enterprise, you can use that input just as you do any other data input you've defined in Splunk Enterprise. Use the Search Processing Language (SPL) to write a series of commands and arguments.

For a quick view of what an input returns to Splunk Enterprise, go to the database inputs page by clicking Data Lab>Inputs in the top navigation bar, click the name of the input to view, and then click the Find Events button on the right side of the screen. The search app opens with a pre-populated search that searches on your input.

You can search for keywords and use Boolean operators such as AND, OR, and NOT, plus wildcard characters ("*"): 

manufacturing (widgets AND gadgets OR gewgaw*)

Fields in Splunk Enterprise correspond to columns in your database. Search for fields using the syntax: fieldname="field value" 

source="db2input"

Chain search commands and arguments together using the pipe ("|") character. For example, the following search retrieves indexed events from the database input db2input that contain the term "manufacturing" and, for those events, reports the most common LOCATION values: 

manufacturing source="db2input" | top LOCATION

To refine your search further, show only the fields you want using the fields keyword. The following search only shows the five fields listed after fields, plus a timestamp, for each event retrieved: 

source="db2input" | fields ADMRDEPT DEPTNAME DEPTNO LOCATION MGRNO

To learn more:

Use a REST API to manually trigger DB inputs

Manually trigger DB Inputs with REST API and be able to track the state of its execution (in-process, completed successfully, failed, etc).

  1. In the command line of your chosen operating system, navigate to the $SPLUNK_HOME directory
  2. Enter these commands to get the session key: 
    1. $SHELL
    2. zsh
    3. curl -k https://localhost:8089/services/auth/login -u <username>:<password> -d username=<username> -d password=<password>
      4. [/topic/topic/body/section/ol/li/ol/p {""}) (p][/topic/topic/body/section/ol/li/ol/note {""}) username and password are the login credentials you used for your Splunk instance. The user must have admin access. (note]
    3. [/topic/topic/body/section/ol/li {""}) Get the Request URL: [/topic/topic/body/section/ol/li/ol {""}) [/topic/topic/body/section/ol/li/ol/li {""}) Log into Splunk Web, and select DB Connect from the [/topic/topic/body/section/ol/li/ol/li/b {""}) Apps (b] menu. (li][/topic/topic/body/section/ol/li/ol/li {""}) In DB Connect, right-click anywhere on the dashboard, and select [/topic/topic/body/section/ol/li/ol/li/b {""}) Inspect (b]. (li][/topic/topic/body/section/ol/li/ol/li {""}) In the Splunk DB Connect Menu bar, select [/topic/topic/body/section/ol/li/ol/li/b {""}) Network > Configuration > Settings > General (b] from the menu bar. (li][/topic/topic/body/section/ol/li/ol/li {""}) In the page inspector, select [/topic/topic/body/section/ol/li/ol/li/b {""}) Name > Settings (b]. In case it is not there, refresh the page. (li][/topic/topic/body/section/ol/li/ol/li {""}) Select [/topic/topic/body/section/ol/li/ol/li/b {""}) Headers > General (b]. The Request URL is listed here. (li][/topic/topic/body/section/ol/li/ol/li {""}) Copy the Request URL to a word document. (li][/topic/topic/body/section/ol/li/ol/li {""}) In the Request URL, change the port number to [/topic/topic/body/section/ol/li/ol/li/codeph {""}) 8089 (codeph]. (li][/topic/topic/body/section/ol/li/ol/li {""}) In the Request URL, remove [/topic/topic/body/section/ol/li/ol/li/codeph {""}) /en-US/splunkd/__raw (codeph]. For example, [/topic/topic/body/section/ol/li/ol/li/ul {""}) [/topic/topic/body/section/ol/li/ol/li/ul/li {""}) Session ID:[/topic/topic/body/section/ol/li/ol/li/ul/li/div {""}) (div] [/topic/topic/body/section/ol/li/ol/li/ul/li/codeblock {""}) p^oVIYq^APf5Eu3i2pPrfuvD^Eecaqau8TudVGhfloRXYYY4uzmkXZx^9KNZDESJ1Ct1m5aXmuw67A4LMxl4Izf5oSp0gMSO37b8w35Ue7bQcboKT9u^ (codeblock] (li][/topic/topic/body/section/ol/li/ol/li/ul/li {""}) Request URL:[/topic/topic/body/section/ol/li/ol/li/ul/li/div {""}) (div] [/topic/topic/body/section/ol/li/ol/li/ul/li/codeblock {""}) http://localhost:8000/en-US/splunkd/__raw/servicesNS/pelluru_admin/splunk_app_db_connect/db_connect/settings (codeblock] (li][/topic/topic/body/section/ol/li/ol/li/ul/li {""}) Modified URL:[/topic/topic/body/section/ol/li/ol/li/ul/li/div {""}) (div] [/topic/topic/body/section/ol/li/ol/li/ul/li/codeblock {""}) https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/settings (codeblock] (li] (ul] (li] (ol] (li] [/topic/topic/body/section/ol/li {""}) Run the following command to get the REST API response after request: [/topic/topic/body/section/ol/li/codeblock {""}) curl -k -H "authorization: Splunk <session key>" <modified Request URL> (codeblock] [/topic/topic/body/section/ol/li/p {""}) Example: (p] [/topic/topic/body/section/ol/li/codeblock {""}) curl -k -H "authorization: Splunk p^oVIYq^APf5Eu3i2pPrfuvD^Eecaqau8TudVGhfloRXYYY4uzmkXZx^9KNZDESJ1Ct1m5aXmuw67A4LMxl4Izf5oSp0gMSO37b8w35Ue7bQcboKT9u^" https://localhost:8089/servicesNS/nobody/splunk_app_db_connect/db_connect/settings (codeblock] (li]