https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384238#M46751 <P>I want to update a database table with data from Splunk. I need to join on an ID field. So if my data has a row with ID = A and the database table has a row with ID = A, that row should be updated with my new data. If my row has an ID that isn't already in the database table, add the new row. </P> <P>I've tried dbxoutput, but that seems to only append all the new data without joining on any field. How have others achieved this?</P> Mon, 07 May 2018 20:05:55 GMT matstap 2018-05-07T20:05:55Z https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384238#M46751 <P>I want to update a database table with data from Splunk. I need to join on an ID field. So if my data has a row with ID = A and the database table has a row with ID = A, that row should be updated with my new data. If my row has an ID that isn't already in the database table, add the new row. </P> <P>I've tried dbxoutput, but that seems to only append all the new data without joining on any field. How have others achieved this?</P> Mon, 07 May 2018 20:05:55 GMT https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384238#M46751 matstap 2018-05-07T20:05:55Z https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384239#M46752 <P>You can have dbxquery to call store procedure to achieve.</P> <P><A href="http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Commands#Required_Arguments">http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Commands#Required_Arguments</A></P> Tue, 08 May 2018 23:33:40 GMT https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384239#M46752 mchang_splunk 2018-05-08T23:33:40Z https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384240#M46753 <P>If you want to upsert functionality in a database, "Enable Upsert" should be checked with a proper column name(in your case - ID column) under "Upsert Configuration" section in the 3rd step(Fields Mapping) when creating an database output.</P> <P>Please check the following Splunk doc. <BR /> <A href="http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Createandmanagedatabaseoutputs#Fields_mapping" target="_blank">http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Createandmanagedatabaseoutputs#Fields_mapping</A></P> <P>I have tested this funtionality using EMPLOYEES table on Oracle database and it worked for me. After configuring the database output using DB Connect UI, $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_outputs.conf shows the following. Please note that EMPLOYEE_ID is configured for unique_key and using_upsert as 1 below: </P> <P>db_outputs.conf<BR /> [EMP]<BR /> connection = oracle<BR /> customized_mappings = COMMISSION_PCT:COMMISSION_PCT:3,DEPARTMENT_ID:DEPARTMENT_ID:3,EMAIL:EMAIL:12,EMPLOYEE_ID:EMPLOYEE_ID:3,FIRST_NAME:FIRST_NAME:12,JOB_ID:JOB_ID:12,LAST_NAME:LAST_NAME:12,HIRE_DATE:HIRE_DATE:93,PHONE_NUMBER:PHONE_NUMBER:12,SALARY:SALARY:3,MANAGER_ID:MANAGER_ID:3<BR /> disabled = 0<BR /> is_saved_search = 0<BR /> scheduled = 0<BR /> search = index=main sourcetype=dbx | search EMPLOYEE_ID=173<BR /> table_name = "SPLUNK"."EMPLOYEES"<BR /> ui_query_schema = SPLUNK<BR /> ui_query_table = EMPLOYEES<BR /> <STRONG>unique_key = EMPLOYEE_ID<BR /> using_upsert = 1</STRONG></P> Tue, 29 Sep 2020 19:29:54 GMT https://community.splunk.com/t5/All-Apps-and-Add-ons/How-to-update-database-table-rows-with-Splunk-data/m-p/384240#M46753 kheo_splunk 2020-09-29T19:29:54Z