SQL tips and tricks

If you are having trouble configuring Splunk DB Connect database inputs, or if you have written overly complex Splunk Processing Language (SPL) queries to use with your DB Connect inputs, consider instead refining your SQL queries.

• Use the dbxquery search command to write SQL queries directly in the Splunk Enterprise Search & Reporting app.
• When you set up a new database input, use Advanced Query Mode to enter your custom SQL query. In step 2 of the input creation process, you specify a query to run to fetch data from your database. Instead of the default Simple Query Mode, which limits you to simply choosing catalog, schema, and table, choose Advanced Query Mode, then enter your SQL query into the field. You can preview your query results before saving the input.

About NULL values

Splunk does not distinguish NULL and empty values. In other words, for Splunk a NULL value is equivalent to an empty string. If you want to replace NULL value by a well identified value you can use fillnull or eval commands. NULL values can also been replaced when writing your query by using COALESCE function. You can consult your database's documentation about this function for details.

It is also important to note that DB Connect cannot accept NULL values for a rising column or a column used as index time in the inputs definition. Such values will cause input jobs to fail.

Use CAST or CONVERT to handle certain column types

Splunk Enterprise supports ASCII text, and cannot directly index binary fields like Binary Large Object (BLOB) and Character Large Object (CLOB). If Splunk Enterprise does not support a column type in your database, or you want to change a column type to a specific type before sending the data to Splunk Enterprise, use the CAST or CONVERT SQL functions to change the type of the column.

The CAST and CONVERT functions convert a value from one data type to another. CONVERT provides additional style functionality and more complex conversions, but Oracle discourages its use in current Oracle database releases.

Use CAST or CONVERT to improve date and time handling

Splunk Enterprise assigns timestamps to indexed event data at index time. You can also specify a column for timestamp extraction when you configure an input. If the column is DATETIME, no additional configuration is required.

If they are not in DATETIME columns, you need to help Splunk recognize the timestamps in your database. If the column is a VARCHAR or string, then you can pre-process the data using Java DateTimeFormatter. Alternatively, you can convert the timestamp to the correct data type using a custom SQL statement with CAST, CONVERT, or TO_TIMESTAMP functions. Use SQL to change your string column type into a DATETIME column to set the index time value.

Many data sources may contain multiple date or time columns that you do not need to set the index time value for. However, you do need to present the data in readable, friendly formats. In this scenario, use a custom SQL statement with CAST or CONVERT functions to turn epoch values into a locale-oriented date and time, shift a local time to UTC, or trim a detailed timestamp to a broader date value.

Use REPLACE or SUBSTRING to modify value quoting

If you have problems involving quotation mark processing during search-time field extraction or indexing (such as the "Incomplete field values are extracted when the value contains double quotes" troubleshooting issue), modify your quotation marks. Modify quotation marks, or any punctuation mark, in event data before Splunk Enterprise processes the events by using either the REPLACE or SUBSTR SQL functions.

Use the REPLACE function to replace every occurrence of a quotation mark (") or other character with another character or string. Use the SUBSTR function to return a portion of the string, beginning at a position in the string that you specify.

Be aware that these SQL functions are not equivalent to the replace(X,Y,Z) and substr(X,Y,Z) evaluation functions that you can use in Splunk Enterprise with the eval, fieldformat, and where search commands. The REPLACE or SUBSTR SQL functions execute before Splunk Enterprise receives data, while the replace(X,Y,Z) or substr(X,Y,Z) evaluation functions execute at search time in Splunk Enterprise. Use the former to ensure that Splunk Enterprise properly indexes values with spaces or other delimiter characters.

Use AS to change column names

To change the names of columns before either indexing or running lookups, you can use the AS keyword to create an alias. You can use aliases to create a temporary name for columns, making the column headings easier to read when you are retrieving results.

The basic syntax to alias a column in SQL is as follows:

<column_name> AS <alias_name>

In this example, <column_name> is the name of the column that you want to alias, and <alias_name> is the alias you want to assign to the column.

In the following example, you rename the MIN(price) field as cheapest. Therefore, cheapest is the name of the second column that Splunk Enterprise returns to DB Connect in the result set.

SELECT category, MIN(price) AS cheapest
FROM items
GROUP BY category

Use CASE, COALESCE, or CONCAT to compare and combine two fields

You have several options to compare and combine two fields in your SQL data. The following examples describe situations in which you can use CASE, COALESCE(), or CONCAT() to compare and combine two column values. Use either query wrapping (inline views) or an advanced mode database input to use the resulting columns as a rising column. Inline views are enabled by default. For more information, see Use inline views (query wrapping) and Use an advanced mode database input, in this topic.

CASE

Use the CASE expression to take actions based on the result of a conditional expression. For example, use a SQL statement like the following if you have two fields that you want to conditionally merge into one.

CASE WHEN first_name IS NOT NULL THEN last_name AS full_name

COALESCE()

The COALESCE() function is shorthand for the CASE expression, and returns the first non-null expression that it finds within its arguments. For example, use a SQL statement like the following if you have several fields that you want to conditionally merge into one. This returns the first existing field.

COALESCE(first_name,middle_name,last_name) AS full_name

CONCAT()

Use the CONCAT() function to concatenate, or combine, fields. It is equivalent to the || operator. For example, use a SQL statement like the following if you have two fields that have values that you want to merge into one:

CONCAT(first_name,last_name) AS full_name

The following example is only possible with an advanced mode database input, which replaces the question mark (?) character with a checkpoint value. For more information about advanced mode, see Advanced in the Create and manage database inputs topic.

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))