Connecting Splunk and Denodo

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 23 Jul 2020
Tags: Connectivity JDBC driver Splunk XML data sources

Download document

You can translate the document:

Introduction

This document describes how to:

  • Access Splunk from Denodo using the Splunk REST API.
  • Access Denodo from Splunk via JDBC.

If you need to monitor Denodo from Splunk using the Denodo logs see Monitoring Denodo with Splunk.

Splunk is a software for searching, monitoring, and analyzing machine-generated big data, via a web-style interface. It captures, indexes and correlates real-time data in a searchable repository from which it can generate graphs, reports, alerts, dashboards, and visualizations.

It is possible to connect Denodo to Splunk in order to combine the information returned by Splunk with the rest of existing data sources. Additionally, it is possible to connect to Denodo from Splunk as well in order to search and monitor a data virtualization layer.

Connecting to Splunk from Denodo

Splunk offers a REST API for accessing, creating, updating and deleting resources. This way, we can manage our Splunk environment from Denodo via HTTP requests. The operations offered can be seen in the Splunk REST API reference guide, although in our example we are going to use just two requests types for Splunk saved searches: starting a new search and later getting the results of the search performed.

Our example scenario is very simple, we are using Splunk in our company to monitor the logs created by the corporate applications and Virtual DataPort is one of these applications that writes information to the logs. For this use case, we are going to focus on the vdp-queries log that includes information about the requests executed on a Virtual DataPort server. As we have seen previously, using Splunk we can monitor this log file and filter the entries in the log to get the requests that have failed.

Let’s suppose that we have configured a saved search in Splunk like:

source="C:\\Denodo Platform\\logs\\vdp\\vdp-queries.log" username=john status=ERROR

The Splunk search above will return the requests from our colleague John which were not executed successfully, these searches can be saved in Splunk so they can be executed again as needed in the future, in our case we are going to save the previous search with the name JohnErrorQueries. In addition, the saved searches can be managed via the Splunk REST API, so from Denodo, we can configure an XML data source to start the search using a POST request. Note that you can also use other output modes such as csv or json. In that case you would need to create a delimited file or a json data source. More information regarding the output mode of the Splunk REST API can be found here.

Using the search/jobs/export endpoint

Based on the Search endpoint descriptions of the REST API Reference Manual this endpoint is useful if you want to stream the search results as soon as they are available. The advantage of that endpoint is that it is easier to set up, but on the other hand if the search is too big you may use the other endpoint described in the next section (see “Using the search/jobs endpoint”).

For this approach, we will include the parameter  search in the post body that will contain the search we want to execute. Please ensure the saved search has the ‘Read’ permissions for the necessary users and roles. If we are running Splunk in a default local installation, the REST API will be running on the port 8089 so the request configuration will be like this:

Once the data source is created, we can simply save it and create a base view on top of it by clicking on the “CREATE BASE VIEW” option. The resulting base view should retrieve the information from Splunk like this:

We can see that the results are included in the register called result. When clicking on the information symbol, we can see more information and expand the fields by clicking on the plus symbol:

From here on, you could continue your data modeling and extract the information that you need.

Using the search/jobs endpoint

Following the Splunk reference guide, for starting a new search we can also use the endpoint search/jobs with the parameter search in the post body that will contain the search executed.

As the result of the POST request Virtual DataPort receives a search ID, this code identifies the search executed and it is needed to access its results. Notice that this identifier is introspected with type double but we have to define it as text type, in order to avoid issues with trailing zeros or decimals.

Then, to get the results we can send a GET request to the endpoint search/jobs/{search_id}/results replacing {search_id} with the search ID obtained from the previous request. We can create a new XML data source in Denodo using interpolation variables to query this URL, the data source configuration will look something like this:

The next step is to create the base views on both data sources and to join them sending the output from the view that creates the search (search ID) to the view that gets the results.

However, with this approach we cannot create the search in the REST API and consume it immediately. If we try to join directly the view that creates the search with the view that gets the results then we will not get any results at all. To make it work, we need to add a delay so Splunk has time to create the new search. This can be achieved in several ways, for instance creating a derived view that calls the Stored Procedure wait():

CREATE VIEW dv_wait500 AS

SELECT * FROM wait(500) union select 1;

Then, we can cross join the base view that sends the POST request with the view that executes the delay, doing this we ensure that Virtual DataPort will wait at least 500 milliseconds between creating the search and fetching the results. If you do not get any results back the 500 milliseconds may be not enough. In that case you could also increase the waiting time for example to 5000 milliseconds to give the Splunk Server more time to create the search.

As shown below, all the steps are integrated into one single view called dv_john_error_search_results using JOIN operations, the tree view shows how the views are combined for this Splunk example:

From this step we can start combining the information, transforming it by applying functions and other Virtual DataPort resources.

Connecting to Denodo from Splunk via JDBC

On the other hand, to connect to Denodo Virtual DataPort from Splunk we can use the Splunk app DB Connect and the JDBC driver provided by Denodo. With Splunk DB Connect, the users can lookup data in relational databases to enrich Splunk search results with business context, this app can be downloaded from the Splunkbase. In this document we have tested Splunk DB Connect version 3.1.4.

After installing the extension, we need to copy the Denodo JDBC driver in the classpath of Splunk DB Connect: <SPLUNK_HOME>/etc/apps/splunk_app_db_connect/drivers. Remember that the Denodo Virtual DataPort driver can be found under <DENODO_HOME>\tools\client-drivers\jdbc.

After adding the driver, configure a new connection type for Denodo appending the following configuration lines to the configuration file <SPLUNK_HOME>/etc/apps/splunk_app_db_connect/local/db_connection_types.conf:

[denodo]

displayName = Denodo

serviceClass = com.splunk.dbx2.DefaultDBX2JDBC

jdbcUrlFormat = jdbc:vdb://<host>:<port>/<database>

jdbcDriverClass = com.denodo.vdp.jdbc.Driver

port = 9999

ui_default_catalog = admin

testQuery = SELECT 1

Then, in the Configuration section of Splunk DB Connect you can create a new connection. Create a new identity for the Virtual DataPort user that you will use to connect to Splunk and a new connection selecting the identity created.

Finally, you can use the Search section to execute queries on Denodo using the Splunk syntax:

| dbxquery query="SELECT * FROM splunk_test_2.view1" connection="Denodo"

References

Using the REST API reference

Splunk DB Connect

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training