ODBC tutorial: How to connect to splunk using ODBC

Introduction

In this tutorial, you will learn how to connect to Splunk using the ZappySys JSON ODBC Driver for ODBC connections. This method allows you to extract Splunk event data, execute search jobs, and retrieve results for reporting, analysis, or integration into other workflows such as SQL Server or Excel.

We will use the ZappySys JSON ODBC Driver to access the Splunk REST API and perform the necessary operations for connecting, retrieving search jobs, and fetching results using Splunk’s Search API.

Prerequisites

  • Download and install] the ZappySys JSON ODBC Driver
  • A Splunk account
  • Download Splunk Enterprise or register with Splunk here

Steps

Step 1: Configure Splunk Dataset

  1. Start Splunk: First, launch Splunk on your machine from the program menu.

  2. Log into Splunk: After starting Splunk, you will be redirected to the browser-based Splunk interface. Log in with your credentials and click Add Data to add WinEvent logs for searching in the next phase.

  3. Choose Data Source: Select a data source to monitor. For example, you can monitor WinEvents.

  4. Configure Data Source: Choose the necessary configuration and click Next to proceed through the review steps. Click Next again to complete the process.

  5. Complete Data Input Setup: The local event logs input has now been successfully created for searching.

  6. Access the Search Page: You will be redirected to the search page where you can begin querying your event data.

  7. Now, you are ready to import the WinEvents search data into SQL Server, which will be done in the following sections.

Step 2: Configure the ZappySys ODBC Driver

  1. Start by searching for ODBC in the Windows Start menu and open the ODBC Data Source Administrator.

  2. Go to the User DSN or System DSN tab and click Add to create a new data source.

  3. In the ODBC Data Source Setup window, select the ZappySys JSON Driver and click Continue.

Step 3: Connect to Splunk

If you encounter any issue, you can check Splunk API documentation here.

  1. Use the following URL to configure your Splunk connection:
    https://localhost:8089/services/search/jobs/

  2. In the Connection Type field, select HTTP connection and click Configure.

  3. Use the same URL from the last step. In the Credential Type, select Basic - UserID/Password and enter your Splunk login credentials.

  4. After entering your credentials, click OK to save the connection.

  5. In the JSON driver, set the POST method for the connection.

  6. Use the following data in the body of the request (replace source and host with your actual data):

    search=search source="WinEventLog:*" host="DESKTOP-NEQNKSU" earliest=-1d&output_mode=json
    
  7. Test the connection to ensure everything is configured correctly.

  8. If you encounter SSL issues, go to Advanced HTTP options and check Ignore SSL related errors.

  9. After testing, go to the Preview tab to view the request and retrieve the SID (Search Job ID) for the next step.

  10. Create a New JSON Driver for Results, use the same HTTP connection configuration and a new SID URL:
    https://localhost:8089/services/search/jobs/{{sid}}/results?output_mode=json&offset=0&count=5

  11. In the Filter field, enter $.results[*] to specify the data you want to retrieve.

  12. Go to the Pagination tab, select URL parameter mode, and set the offset parameter with an increment of 5. For more details, refer to this article.

  13. Test the connection and preview the data to ensure everything is working as expected.

  14. In the preview tab, select the table you need and preview the data before importing it.

Conclusion

Download ODBC PowerPack to access Splunk integration data, providing a powerful and automated way to retrieve event data for analysis, reporting, or integration into workflows. Whether you’re tracking system logs, monitoring performance metrics, or building custom reports, this integration simplifies and accelerates your workflow, making data retrieval from Splunk seamless and efficient.

Visit our official page to discover more connectors, powerful automation features, and real-time data integration options. Start building smarter, faster, and more scalable solutions today with ZappySys ODBC PowerPack.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.