All Apps and Add-ons
Highlighted

Splunk DB Connect - Error after enabling "Upsert"

Explorer

Problem Scenario.
I am using Splunk DB Connect 3.2 with MS SQL Server as a connection
Establishing the connection and having Splunk input from the connection are working fine
The output is also working fine if I do not enable the "Upsert"
Whenever I try to enable the "Upsert" value, the job always fails.

Below are the error messages I am receiving. Request assistance.

2020-01-19 23:50:01.109 +0530 [QuartzSchedulerWorker-26] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dboutput connectionname=localmssqlconn stanzaname=Testing state=error sql='SELECT [ID],[FirstName] FROM "OfficePOCs"."dbo"."Personsv1" WHERE ID=? ; UPDATE "OfficePOCs"."dbo"."Personsv1" SET [ID]=?,[FirstName]=? WHERE ID=?' message='uniqueKey not found in mappings'

2020-01-19 23:50:01.109 +0530 [QuartzScheduler_Worker-26] ERROR org.easybatch.core.job.BatchJob - Unable to write records
java.lang.RuntimeException: uniqueKey not found in mappings
at com.splunk.dbx.service.output.OutputServiceImpl.lambda$processUpdate$2(OutputServiceImpl.java:126)
at java.util.Optional.orElseThrow(Unknown Source)
at com.splunk.dbx.service.output.OutputServiceImpl.processUpdate(OutputServiceImpl.java:126)
at com.splunk.dbx.service.output.OutputServiceImpl.output(OutputServiceImpl.java:67)
at com.splunk.dbx.server.dboutput.recordwriter.DbOutputRecordWriter.writeRecords(DbOutputRecordWriter.java:47)
at org.easybatch.core.job.BatchJob.writeBatch(BatchJob.java:203)
at org.easybatch.core.job.BatchJob.call(BatchJob.java:79)
at org.easybatch.extensions.quartz.Job.execute(Job.java:59)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Communicator

Did you map primary key in DB which you used as unique key in upsert?

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Explorer

I tried making the column "ID" as Unique and also tried Primary Key. Still I get this error. But from the tutorials and documentation there was no mention that the column we are setting as Upsert needs to be Unique or Primary. Please suggest.

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Communicator

The error is related to unique key.
Can you share $SPLUNKHOME/etc/apps/splunkappdbconnect/local/db_outputs.conf ?

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Explorer

Yes please. Below are the 3 outputs I created. But still getting error in the 3 outputs.

[localmssqloutput]
connection = localmssql
conn
customizedmappings = id:id:4,name:Name:12,Dept:Team:12
disabled = 1
interval = */1 * * * *
is
savedsearch = 0
query
earliesttime = -24h@h
query
latesttime = now
scheduled = 1
search = | makeresults \
| eval id = 7,name="Testing",Dept="Test"
table
name = "OfficePOCs"."dbo"."Office"
uiquerycatalog = OfficePOCs
uiqueryschema = dbo
uiquerytable = Office
uniquekey = id
using
upsert = 1
query_timeout = 30

[localmssqloutputwithuniqueKey]
connection = localmssql
conn
customizedmappings = ID:ID:4
disabled = 1
interval = */1 * * * *
is
savedsearch = 0
query
earliesttime = -24h@h
query
latesttime = now
scheduled = 1
search = | makeresults \
| eval ID=3,FirstName="Lokesh",LastName="V",age=25
table
name = "OfficePOCs"."dbo"."Persons"
uiquerycatalog = OfficePOCs
uiqueryschema = dbo
uiquerytable = Persons
uniquekey = ID
using
upsert = 1
query_timeout = 30

[Testing]
connection = localmssqlconn
customized
mappings = id:ID:4,name:FirstName:12
disabled = 1
interval = */1 * * * *
issavedsearch = 0
queryearliesttime = -24h@h
querylatesttime = now
scheduled = 1
search = | makeresults\
| eval id="3",name="lok",name2="lokiii",age=3
tablename = "OfficePOCs"."dbo"."Personsv1"
ui
querycatalog = OfficePOCs
ui
queryschema = dbo
ui
querytable = Personsv1
unique
key = ID
usingupsert = 1
query
timeout = 30

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Explorer

Stanza : localmssql_output

The Splunk Search Query as input is as below.
| makeresults
| eval id = 7,name="Testing",Dept="Test"

The DB Output table(MS SQL) into which I am trying to insert the data is as below
select * from dbo.OfficeV1

id int
Name varchar
Team varchar
LastUpdated datetime

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Communicator

try this

[localmssql_output]
connection = localmssql_conn
customized_mappings = id:id:4,name:Name:12,Dept:Team:12
disabled = 0
interval = /1 *
is_saved_search = 0
query_earliest_time = -24h@h
query_latest_time = now
scheduled = 1
search = | makeresults \
| eval id = 7,name="Testing",Dept="Test",table_name = "OfficePOCs"."dbo"."Office"
ui_query_catalog = OfficePOCs
ui_query_schema = dbo
ui_query_table = OfficeV1
unique_key = id
using_upsert = 1
query_timeout = 30
0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Explorer

still no luck....having the same errors as above mentioned. Not sure what's wrong, the input works perfectly fine both Batch and Rising modes. Output also works fine withhout enabling "Upsert" mode. Only output is not working in "Upsert" mode.

Any thing I am missing?

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Engager

Currently experiencing the same issue on our instance, also running version 3.2 and using MS SQL.

I have an open case with Splunk support, performed some troubleshooting with a technician, but I'm still waiting to hear back from them on a possible resolution.

Perhaps this a bug with the application?

0 Karma
Highlighted

Re: Splunk DB Connect - Error after enabling "Upsert"

Loves-to-Learn

@jmolinajda have you arrived at any solution, I am still facing that issue. I have set the primary key at the DB end to be the exact same field in the splunk db_outputs.conf. I was not facing any issue in the older versions but the newer version is throwing this error. Can you help ?

0 Karma