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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...