topic Re: Splunk DB Connect - Output to MSSQL with decimal values in Splunk Search

Splunk DB Connect - Output to MSSQL with decimal values

phoenixdigital — Mon, 13 May 2013 05:07:57 GMT

I am aware this feature is not officially supported but thought I would post this question here.

We have a MSSQL table with the following format
datetime time
string identifier
numeric thisDecimalValue (with 4 decimal places)

This screenshot shows how Splunk DB Connect sees the table
http://imgur.com/tcG3kQe

Now this search/insert

sourcetype="this" earliest=-1m | eval time = _time | eval thisDecimalValue = round(thisDecimalValue,2) | fields time, identifier, thisDecimalValue  | dboutput type=insert database="MSSQL Instance" table="Splunk.outputTable" time, identifier, thisDecimalValue

Ends up with the error

command="dboutput", Unexpected error while performing dboutput: java.sql.SQLException: Cannot convert value "0.00" to required datatype LONG

We have tried with MSQQL numeric and decimal for the field typebut still no joy. Getting the admins to change the field type to float but they have not done this yet.

A colleague suggested using the dboutput.conf to create a custom SQL statement.
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/dboutputspec

Can someone please shed more light on how you formulate the sql.insert string to use the results from the Splunk search?

How is it triggered as well once you have created these stanzas?

Thanks

Re: Splunk DB Connect - Output to MSSQL with decimal values

Dan — Mon, 03 Jun 2013 22:24:31 GMT

Have you tried the float datatype? Did it work?

Your screenshot of dbinfo doesn't show the full definition of the decimal datatype - it is missing the precision and scale.

According to MS SQL, http://msdn.microsoft.com/en-us/library/ms187746.aspx, both decimal and numeric default to allowing 0 digits to the right of the decimal point ("the default scale is 0"). So it might not be possible to convert.

Using dboutput.conf is not really an option while this feature is in beta.

Re: Splunk DB Connect - Output to MSSQL with decimal values

phoenixdigital — Wed, 05 Jun 2013 04:42:40 GMT

Thanks for the response.

The admins changed it to a real and it appears to work now.
http://imgur.com/a/02b00

I am aware dboutput.conf is not fully supported as of yet but some an example of how to use it would be excellent if you could provide it.

Re: Splunk DB Connect - Output to MSSQL with decimal values

Dan — Wed, 05 Jun 2013 16:56:21 GMT

dboutput.conf is not supported in all versions of DB Connect 1.x. Trying to invoke | dboutput <stanza name> will throw a NotImplementedException.

If you want to manually generate the sql, you can either set sql, or sql.insert and sql.update. Make sure to also set advanced=true.
For the sql syntax, one hint is to look at what the debug logging shows about the auto-generated query. For an example, see: http://splunk-base.splunk.com/answers/88599/dboutput-bug-updating-mysql

UPDATE mytable SET ip=$ip$, NetAddress=$NetAddress$, ComputerName=$ComputerName$, Manufacturer=$Manufacturer$, Model=$Model$, mac=$mac$, IPAddress=$IPAddress$ WHERE ip = $ip$

So in your SQL you can reference any field in the search results by using $fieldname$.

To invoke the config is fairly simple, just use | dboutput <stanza name>