Splunk DB Connect - Error after enabling "Upsert"

[lokeshvi]

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 [QuartzScheduler_Worker-26] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dboutput connection_name=localmssql_conn stanza_name=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)

[AndySplunks]

Move your uniqueKey to be the first field you map. That solved the issue for me for an Oracle connection.

So either rebuild your output via the GUI or edit db_outputs.conf so the uniquekey is listed first.

[boromir]

Using the key value for the upsert as the first column in the output worked for me. Output to MySQL.

[dinu1701]

Hi @lokeshvi ,

I am using the exact configuration like how you mentioned, still I am facing the same issue. Can you please help in sharing the solution if its working for you. I have set the same unique in the db end and as well the same column in splunk for using UPSERT still I am not able to establish the connection.

db_outputs.conf 

[AddressDBTestNew]
connection = fls-test
customized_mappings = NAME:NAME:12,CODE:CODE:12,STREET:STREET:12,STATE:STATE:12,ZIPCODE:ZIPCODE:12,COUNTRY_CODE:COUNTRY_CODE:12,CITY:CITY:12,BRANCH:BRANCH:12,PARENT_NUMBER:PARENT_NUMBER:12,PARTNER_TYPE:PARTNER_TYPE:12,PARMA_ID:PARMA_ID:12
disabled = 0
interval = 0 23 * * *
is_saved_search = 0
query_timeout = 30
scheduled = 1
search = | inputlookup address-lookup.csv \
| table NUMBER NAME STREET STATE ZIPCODE CITY ISOCODE BRANCH PARENTNUMBER PARTNER_TYPE PARMA_ID\
| rename NUMBER as CODE, ISOCODE as COUNTRY_CODE, PARENTNUMBER as PARENT_NUMBER\
| where NOT (CODE="null" OR NAME="UNKNOWN")
table_name = "ADMIN"."dbo"."ADDRESS"
ui_query_catalog = ADMIN
ui_query_schema = dbo
ui_query_table = ADDRESS
unique_key = CODE
using_upsert = 1

[danielurbinapjc]

Hi, I have the same issue on my instance, below my scenario:

• I have a Splunk cloud (current version 7.2.6) signed for my company.
• Installed a Splunk Enterprise (same versión as cloud) and enabled Search Head.
• on my Search Head, I installed dbconnect 3.2 and setup the environment with the java version that splunk Recomends (version 4.2).
• Setup everything (connection, login for a MS Sql) and I can use output feature except if I try to use the upsert option.

Without the upsert option, Splunk send data to my DB, but if I setup a primary key on my DB and enable the upsert option, then return a error that said 'uniqueKey not found in mappings'.

I have read everything that I found, can anyone give me a hand with this?

Best regards.

[lokeshvi]

I got the answer. The problem was with my driver. I downloaded the Microsoft JDBC Driver for SQL Server 7.0. But splunk recommends us to use only 4.2. The download link may show you the latest drivers, but make sure you use only the version listed in the https://docs.splunk.com/Documentation/DBX/3.2.0/DeployDBX/Installdatabasedrivers page. Right now splunk suggests us to use 4.2, which I have downloaded and taken the jar from \sqljdbc_4.2.8112.200_enu\sqljdbc_4.2\enu\jre8 location of the unzip. Since I am running a JDK 8. Now I am no longer getting this error in my upsert functionality(same query).

Microsoft JDBC Driver for SQL Server 4.2 download link
The article which gave me direction to figure out my mistake since I tried to downgrade the Splunk DB Connect 3.2 to 3.1.4. That's when I got the error which is listed in the article.

Please note, similar to me, if someone has already placed the JDBC driver version > 4.2 in the C:\Program Files\Splunk\etc\apps\splunk_app_db_connect\drivers folder. You may not be able to delete the jar file directly. You will get error stating that the file is in use by java.exe. That time, start your pc in safe mode. Delete the jar file(JDBC driver version> 4.2). Then copy the 4.2 jdbc into the driver folder. in splunk web open splunk db connect app --> configurations --> settings --> drivers. click on Reload, then you will see the driver getting updated to 4.2.

Finally it should be similar to below

[ashajambagi]

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

[lokeshvi]

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.

[ashajambagi]

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

[lokeshvi]

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

[localmssql_output]
connection = localmssql_conn
customized_mappings = id:id:4,name:Name:12,Dept:Team:12
disabled = 1
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 = Office
unique_key = id
using_upsert = 1
query_timeout = 30

[localmssql_output_with_uniqueKey]
connection = localmssql_conn
customized_mappings = ID:ID:4
disabled = 1
interval = */1 * * * *
is_saved_search = 0
query_earliest_time = -24h@h
query_latest_time = now
scheduled = 1
search = | makeresults \
| eval ID=3,FirstName="Lokesh",LastName="V",age=25
table_name = "OfficePOCs"."dbo"."Persons"
ui_query_catalog = OfficePOCs
ui_query_schema = dbo
ui_query_table = Persons
unique_key = ID
using_upsert = 1
query_timeout = 30

[Testing]
connection = localmssql_conn
customized_mappings = id:ID:4,name:FirstName:12
disabled = 1
interval = */1 * * * *
is_saved_search = 0
query_earliest_time = -24h@h
query_latest_time = now
scheduled = 1
search = | makeresults\
| eval id="3",name="lok",name2="lokiii",age=3
table_name = "OfficePOCs"."dbo"."Personsv1"
ui_query_catalog = OfficePOCs
ui_query_schema = dbo
ui_query_table = Personsv1
unique_key = ID
using_upsert = 1
query_timeout = 30

[lokeshvi]

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

[ashajambagi]

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

[lokeshvi]

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?

[jmolinajda]

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?

[dinu1701]

@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 ?