Introduction

Splunk DB Connect is a generic SQL database extension for Splunk that enables easy integration of database information with Splunk queries and reports. For more information, refer to the About Splunk DB Connect.

This guide provides complete documentation to integrate Splunk DB Connect with Snowflake for ingesting data from the User, Device, and Applications tables. It ensures reliable data ingestion, deduplication via checkpointing, and optimized performance.

Prerequisites

• Splunk Enterprise (v8.2 or later)

• Splunk DB Connect App (v3.18.2)

• Splunk DBX Add-on for Snowflake JDBC (compatible with JRE environment)

• OpenJDK 11 installed (for native memory operation support: Download link)

• Snowflake read-only user with access to required tables: Steps

• Whitelist Splunk IP in Snowflake account (if applicable)

Configuration Overview

1. Create the Snowflake Identity & grant necessary privileges (optional).

2. Install and set up Splunk DB Connect App from the Splunk Base.

3. Add configuration to setup Snowflake Connection using the app.

4. Setup input configuration to query Snowflake DB data & ingest it to Splunk indices.

5. Query Splunk indices & perform data validation.

Snowflake Login

Identity Creation

-- Step 1: Create custom role for Splunk integration

USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROLE splunk_role;

-- Step 2: Grant warehouse access to the Splunk role

GRANT USAGE, OPERATE ON WAREHOUSE <MY_WH> TO ROLE splunk_role;

-- USAGE: Allows the role to use the warehouse

-- OPERATE: Allows the role to resume/suspend the warehouse

-- Step 3: Create Splunk DB Connect user with the role and warehouse

-- Force change temporary password on first login

USE ROLE SECURITYADMIN;

CREATE OR REPLACE USER SPLUNK_DB_USER

password = 'abcd1234'

must_change_password = true

default_warehouse = <MY_WH>

default_role = splunk_role;

-- Step 4: Assign the role to the user

GRANT ROLE splunk_role TO USER SPLUNK_DB_USER;

Grant Privileges

-- Grant database usage

GRANT USAGE ON DATABASE <MY_DB> TO ROLE splunk_role;

-- Grant schema usage

GRANT USAGE ON SCHEMA <MY_DB>.OCSF TO ROLE splunk_role;

-- Grant SELECT on required tables

GRANT SELECT ON TABLE <MY_DB>.OCSF.DEVICE TO ROLE splunk_role;

GRANT SELECT ON TABLE <MY_DB>.OCSF.USER TO ROLE splunk_role;

GRANT SELECT ON TABLE <MY_DB>.OCSF.APPLICATIONS TO ROLE splunk_role;

Key Points

• If you are creating a provisioning script, make sure it's run by a Snowflake admin user with both SECURITYADMIN and SYSADMIN privileges

• Replace <MY_WH> with the Snowflake Warehouse from which you want to query.

• Replace <MY_DB> with the Snowflake Database you want to query.

Splunk Configuration

1. Login to the Splunkbase using your Splunk account credentials.
    

2. Download Splunk DB Connect and Splunk DBX Add-on for Snowflake JDBC, save them to a temporary location that you can access from your Splunk Enterprise instance.
    

3. Log in to Splunk Web, go to Apps > Manage Apps, then click Install app from file.
    
   

4. Choose file that you downloaded; splunk_app_db_connect-<version>.tgz and click Upload. Similarly, upload splunk-dbx-add-on-for-snowflake-jdbc_<version>.tgz file.
    

5. Navigate to Settings > Server controls under SYSTEM section.
    

6. Click on Restart Splunk.
    

Connection Setup

There are two steps to setup a new connection:

1. Create New Identity

2. Create New Connection

Create New Identity

1. Navigate to the Configuration > Databases > Identities tab and click New Identity > Basic Identity.
    

2. On the Settings tab, enter the following fields & click on Save:

   1. Identity Name: Enter a name for the identity

   2. Username: Enter the name of the database user you want to connect as.

   3. Password: Enter the password for the user you entered in the Username field. (encrypted format)
      
       

NOTE: We are keeping the default role & permissions for this identity. Make sure that the database user has limited access to the data you want to query.

Create New Connection

1. Navigate to the Configuration > Databases > Connections tab & click on Select New Connection.
    

2. On the New Connection page, complete the following fields:

   1. Connection name

   2. Identity: Select the identity you created previously.

   3. Connection Type: Select Snowflake from a list of supported databases.

   4. Timezone: If you want to convert data of date and time types read from the database into the Splunk server's local time zone, enter the source database time zone for the conversion. Keep None.

   5. Enable SSL: Leave unchecked, as SSL support is not available for Snowflake connection types.

   6. JDBC URL Preview: Check Edit JDBC URL option and replace the host with your snowflake Account Identifier.
       

   7. Connection Properties (optional): Splunk uses the provided properties during the creation of the connection.

      1. user: select username for snowflake identity

      2. password: select password for snowflake identity

      3. warehouse: select snowflake warehouse

      4. db: select snowflake database

      5. schema: select schema to query (i.e. OCSF)
          

NOTE: Splunk doesn’t guarantee query restriction based on the above properties. Make sure your snowflake identity has restricted access.

1. Select Save to save the connection.
    

NOTE: If the connection you create is valid, you can save the connection successfully. Otherwise Splunk displays an error message for you to check the configuration of the connection and save it again.

DB Input Configuration

Create Index

Before proceeding to Data Input Configuration, create Splunk indexes for data output.

1. Navigate to Settings > Indexes under DATA section.
    

2. Click on New Index.
    

3. On the New Index window, add appropriate index name and click on Save to proceed with default general settings.
    

Create New input

1. In Splunk DB Connect, select Data Lab > Inputs and then New Input.
    

2. On the Set SQL Query page, complete the following steps and then select Next to go to the Set Properties page.

   1. Choose table: Select the database table you want to use with this input.

      1. Connection: Select the connection you created previously

      2. Catalog: Select the snowflake db

      3. Schema: Select the schema (i.e. CDP)

      4. Table: Select the table to query (i.e. DEVICE/USER/APPLICATION)
          

   2. Specify SQL query: Specify a query to run to retrieve data from your database.

• 1. Choose input mode: Specify the input mode as Event and related templates for this input.

  2. Choose input type: Specify the input type and related settings of this input.

     1. Input Type: Select input type as Rising

     2. Rising Column: Select unique column for data checkpointing (i.e. ID)

     3. Checkpoint Value: 0

     4. Timestamp: Choose Column

     5. Column: Select timestamp column to index the Splunk data (i.e. CREATED_TIME, START_TIME)

     6. Query Timeout: Leave blank (Default: 30 seconds)
         

  3. Execute Query: Click on Execute Query to review results before proceeding
      
     

1. On the Set Properties page, complete the following steps and then select Finish.

   1. Basic information: Specify the name, description and the application of this input.

      1. Name: Add data input name

      2. Description: Add relevant description

      3. Application: Choose Splunk DB Connect

   2. Parameter settings: Configure the fetch size, execution frequency and max row to retrieve this input.

      1. Max Rows to Retrieve: Keep blank (to achieve max performance)

      2. Execution Frequency: Enter the number of seconds or a valid cron expression to execute query periodically (i.e. 60 seconds)
          

   3. Metadata: Specify the metadata of this input, Splunk uses the value to index your data events.

      1. Host: Keep blank (to use host defined on the connection)

      2. Source: Add name to uniquely identify source per data input (i.e. snowflake:device_table)

      3. Source Type: Add source type to structure data (i.e. snowflake:device)

      4. Index: Add the destination index for input data (i.e. databee)
          

NOTE: Make sure that index exists, before proceeding to the Data Input Configuration.

Clone Input

1. From the Actions, choose Clone option to copy configuration and create New Input for each Snowflake table to query.
    

2. Choose Table & Verify below configurations to query USER data.
    
    

3. Similarly, verify the configurations below to query APPLICATION data.
    
    

Data Validation

1. Record count by SourceType

1. Query Device Records

1. Query User Records

1. Query Application Records

Troubleshooting Tips

• If there is any error in Snowflake Connection Setup, verify that snowflake identity has enough access to query snowflake data for mentioned connection properties.
  To verify the JDBC connection properties from the Snowflake, refer this.
   

• If you encounter an "Invalid Connection" error and receive a Trace ID or error reference, click on Search to view detailed logs.
   

• In case of any error related to JDBC driver or version compatibility, navigate to Configuration > Settings and verify below settings.

  • JRE Installation Path: Set as OpenJDK11 installed version (By default: JAVA_HOME env variable)
     

  • Drivers: Snowflake driver should be listed as installed
     

References

• https://splunkbase.splunk.com/app/2686

• https://splunkbase.splunk.com/app/6153

• https://docs.splunk.com/Documentation/DBX/3.18.2/DeployDBX/Checklist

• https://docs.splunk.com/Documentation/DBX/3.8.0/JDBCSnowflake/About

• https://community.snowflake.com/s/article/Integrating-Snowflake-and-Splunk-with-DBConnect