Running a stored procedure with DB Connect app

responsys_cm — Mon, 22 Jul 2013 20:05:09 GMT

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)

Re: Running a stored procedure with DB Connect app

responsys_cm — Mon, 22 Jul 2013 21:46:59 GMT

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

Re: Running a stored procedure with DB Connect app

responsys_cm — Tue, 23 Jul 2013 01:01:36 GMT

The database connection was set to read-only.

Re: Running a stored procedure with DB Connect app

ziegfried — Wed, 21 Aug 2013 15:19:26 GMT

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

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

topic Running a stored procedure with DB Connect app in Splunk Search

Running a stored procedure with DB Connect app

Re: Running a stored procedure with DB Connect app

Re: Running a stored procedure with DB Connect app

Re: Running a stored procedure with DB Connect app