The reason we installed DBConnect 2 is to export data out of Splunk to Oracle. We followed the steps outlined in the
Splunk documentation and created an output to Oracle Database. The output is attempting to insert data to a table with an Oracle DATE column and 5 other VARCHAR columns. After schdueling the output and looking at the healt tabe I noticed that the output operation is failing every time with the following error: MESSAGE="ORA-01861: literal does not match format string
2015-07-29 12:15:02 INFO HealthLogger: ... FUNCTION=RollbackTransaction LABEL=JP MAX_MEMORY=1037959168 MESSAGE="ORA-01861: literal does not match format string" PROTOCOL=HTTP SQL="INSERT INTO \"PROD\".\"SUBSCRIPTIONS\"
(START_TIME,USERNAME,ACCT_SESSION_ID,NAS_IP,NAS_IDENTIFIER,SUBSCRIPTION_NAM
E) VALUES (?,?,?,?,?,?)" SQL_CODE=1861 SQL_STATE=22008 STATE=completed
After some research I realized this error has to do with the Oracle Date Column. I am formatting the first column as a
Date Time field in my Splunk query using strftime(_time,"%F %T") format. Here is the Splunk Query:
index=main sourcetype=exportRecord | eval dateTime=strftime(_time,"%F %T"))
However Oracle by default does not like the date/time format and it needs the format to be specified using TO_DATE Oracle function. Db Connect is inserting data into the table using parametrized SQL which I cannot change to add TO_DATE function. I cannot find any documentation on how to control this from Splunk. Any help is greatly appreciated.
Second question is with regards to the frequency and control of the data that is exported. Assuming this output operation is fixed so that it works I could not find any documentation on how Splunk keeps track of what data it has exported across runs and does not re-export data again. For example if I have this output job run once every hour how does Splunk Db Connect only export the data since its last successful invocation and the current time where the DB Output job is running. Again referring to Splunk documentation I found the following section:
Finalize your output
In the last step, finalize the output by entering the following settings, and then click Save:
Execution Frequency: Enter the number of seconds (or a valid cron expression) between output executions. For example, entering 120 instructs DB Connect to wait two minutes after it's done sending data to your database before doing it again. Be aware that only new events will be sent at each execution.
I am trying to see if I have to do anything special to make sure Splunk only outputs new events at each execution per statement above.
... View more