Splunk DB Connect: ORA-01843: not a valid month

[jmmontejo]

Hi All,

We have a requirement to setup DB inputs to Splunk DB Connect app, but we're encountering ORA-01843:not a valid month in internal logs in one of their DBs. We didn't encounter the said error during setup via UI and successfully save the DB input. But during execution, there's no data being ingested to splunk due to the said internal error. Also their other DBs are fine just except on 1 DB.

Just want to ask if someone already encountered this issue and for possible fix. Not sure if it's on Splunk side or on their DBs that need to fix. Note: they have the same DB input query for all tables.

Query:
select * from table1 from timestamp > ? order by timestamp

Timestamp format when executing the DB from splunk app
2019-10-29 17:07:07.193

Connection Type: Oracle Service

Thanks.

[jmmontejo]

Any idea guys? or do I need to file a case support for this.

any help is much appreciated. Thanks

[performancemoni]

We are having the exact same issue, we made a db input that worked perfectly fine, and one day it stopped working because of this error. Did you find out what was the problem ?

[jmmontejo]

Hi,

yes. we had no choice but to change the rising column setup to other column except timestamp. you can use other column as long as it is unique and sequential manner.

Sometimes timestamp is the best choice but even splunk doc do not recommend using it in rising input setup
https://docs.splunk.com/Documentation/DBX/3.3.1/DeployDBX/Createandmanagedatabaseinputs

[youngsuh]

I am using the rising column has event timestamp UTC and still getting the error.  The previous version 3.2 worked by selecting a different date.  This no longer works.  Anything else that I can try other than call support.

[robinsonalex88]

I'm using an older version of DB Connect but when getting the "not a valid month" error I find that the DB connect logs also had the message "ORA-01013: user requested cancel of current operation" at the same time which I know is the message DB Connect gives when the timeout value is reached.  So generally increasing the Query Timeout will fix the problem for me.

[performancemoni]

I see, it makes sense, I think we skipped this recommendation in the Splunk Docs, thank you for the info. In our case we couldn't use another rising column, but we actually came up with another solution. I will describe it here if anyone needs it:

Basically we took note of the checkpoint value where the input was stuck on and then changed it to a value further in time (checkpoint value + 2 days). The input is working fine when starting from this new checkpoint value.

But we needed the recover the missing history between the old checkpoint value and the "checkpoint_value+2days" so we ran our query manually in a Splunk search like this:

| dbxquery query="select * from table where timestamp > to_date('***<old_checkpoint>***', 'YYYY-MM-DD HH24:MI:SS') AND timestamp< to_date('***<checkpoint+2days>***', 'YYYY-MM-DD HH24:MI:SS')" connection="<connection_name>"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| collect index=my_index sourcetype=my_sourcetype host=my_host ...

Somehow the query works fine when the timestamp is filtered with dates like this, and we index the events using the collect command with the correct metadata fields.

This is not a definitive solution, we might still encounter the same issue another day but we know how to bypass it with a little procedure.