DB Connect: Intermittent ORA-01843 errors when using rising input based on timestamp

[JWGOV]

Hello,

We are using DB Connect to collect logs from Oracle databases. We are using a rising mode input which requires the database statement be written in a way where column used for checkpointing is compared against a "?". Splunk DB Connect fills in the "?" with the check point value.

Occasionally we will get "ORA-01843: Not a Valid Month errors" on inputs. The error itself is understood.*

The question is, how do we rewrite the query to avoid this, when Splunk/DB Connect is handling how the "?" in the query  is replaced?

Here is an example query:
SELECT ACTION_NAME,
CAST((EVENT_TIMESTAMP at TIME zone America/New_York) AS TIMESTAMP) extended_timestamp_est
FROM AUDSYS.UNIFIED_AUDIT_TRAIL
WHERE event_timestamp > ?
ORDER BY EVENT_TIMESTAMP asc;

How can we format the timestamp in the "?" in a way that the database understands and meets the DB Connect rising input requirement?

Thank you!

*(Our understanding is that it means that the timestamp/time format in the query is not understood by the database. The fact that it happens only occasionally means there is probably some offending row within the results set.)

[Meett]

I agree with what @KendallW shared its hard to comment anything without checking actual data but this type of ERRORs can mainly happen due to mismatch in timestamps.

[KendallW]

Hi @JWGOV , As per the documentation here, for Oracle database inputs you may use any of varchar, int, float, real, bigint, number, timestamp, datetime, or date for the rising column.  You shouldn't really have to do any other formatting of data so long as the rising column is in one of those types, but...

Without seeing the actual data causing the ORA-01843 errors it's hard to say where the root cause lies, but consider using TO_NUMBER to convert the EVENT_TIMESTAMP field from a timestamp to a number, which would at least avoid this specific error.  Although, you still might have problems if some bad data lands in the EVENT_TIMESTAMP field and can't be converted to number, or if it is converted to a number from a different timestamp format which might result in a checksum error. 

Are you able to show an example when one of those errors occurred?