About lokeshvi

lokeshvi · ‎01-25-2020

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

lokeshvi · ‎01-20-2020

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?

lokeshvi · ‎01-20-2020

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

lokeshvi · ‎01-20-2020

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 · ‎01-20-2020

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.

lokeshvi · ‎01-19-2020

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)

Posts	6
Solutions	0
Karma Given	1
Karma Received	8
Member Since	‎01-19-2020

Online Status	Offline
Date Last Visited	‎06-05-2020 02:03 AM

Splunk DB Connect - Error after enabling "Upsert"

Re: Splunk DB Connect - Error after enabling "Upse...

Re: Splunk DB Connect - Error after enabling "Upse...

Re: Splunk DB Connect - Error after enabling "Upse...

Re: Splunk DB Connect - Error after enabling "Upse...

Re: Splunk DB Connect - Error after enabling "Upse...

Splunk DB Connect - Error after enabling "Upsert"