Splunk Search

Running a stored procedure with DB Connect app

responsys_cm
Builder

I'm trying to get Splunk to login to a MS SQL database and execute a stored procedure based upon data in the events. This is currently running on a Windows 2008 server (but may be moving to Linux shortly).

Based on what read here (http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Setupadatabaselookuptable), I should be able to specify the datatype for each field. How do I determine if my JDBC driver supports parameter metadata?

The definition for the lookup is:

[Bit9_Local_Approval]

advanced = 1

database = Bit9Alpha

input_fields = host_id,hash,file_state,ret_val

query = DECLARE @ret INTEGER\

EXEC @ret = bit9_public.ChangeLocalFileState @computerId=$host_id:INTEGER$, @fileHash = $hash:NVARCHAR$, @fileState=$file_state:INTEGER$,\

SELECT ret_val = @ret

I see the folloiwng error in the dbx.log:

2013-07-22 12:56:43.741 dbx7931:ERROR:BridgeSession - Exception occured while executing command: java.lang.AbstractMethodError: net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setNString(ILjava/lang/String;)V

java.lang.AbstractMethodError: net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setNString(ILjava/lang/String;)V

at com.splunk.util.Utils$SQL.applyArgumentAsSqlType(Utils.java:1305)

at com.splunk.dbx.sql.util.NamedPreparedStatement.set(NamedPreparedStatement.java:108)

at com.splunk.dbx.sql.util.NamedPreparedStatement.set(NamedPreparedStatement.java:118)

at com.splunk.dbx.lookup.DatabaseLookupExecutor.performAdvancedLookup(DatabaseLookupExecutor.java:174)

at com.splunk.dbx.lookup.DatabaseLookupExecutor.performLookup(DatabaseLookupExecutor.java:39)

at com.splunk.runtime.SplunkLookup.invoke(SplunkLookup.java:26)

at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:92)

at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:30)

Tags (1)
0 Karma

ziegfried
Influencer

Looks like the JDBC driver doesn't support the NVARCHAR datatype for the prepared statement. You can try VARCHAR instead:

...  @fileHash = $hash:VARCHAR$, ...
0 Karma

responsys_cm
Builder

The database connection was set to read-only.

0 Karma

responsys_cm
Builder

I tried the same lookup on a RHEL 6 box and got the same exception.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...