How to configure time in Splunk DB Connect and the Add-on for Oracle Database?

[gn694]

I have Splunk DB Connect and the Add-on for Oracle Database installed and configured using the oracle:audit:unified template.

Initial configuration was done keeping the Timestamp column the default (EVENT_TIMESTAMP_UTC.)
This worked great.

Then in order to get the indexed timestamp to be local time I changed the Timestamp column to EVENT_TIMESTAMP. Doing this resulted in being prompted for the Datetime format (Java SimpleDateTimeFormat.)

I have tried entering in a few different formats, none of which work. No matter what I have tried, things are not working and I do not get any events indexed.

An example of this field's value is:
2018-05-14 11:24:33.349604 America/Chicago

The current Datetime formats that I am trying unsuccessfully are:
yyyy-MM-dd HH:mm:ss.SSSSSS Z
yyyy-MM-dd HH:mm:ss.SSSSSS z
yyyy-MM-dd HH:mm:ss.SSSSSS
yyyy-MM-dd HH:mm:ss.SSSSSS VV

I keep getting the following in splunk_app_db_connect_server.log:
java.time.format.DateTimeParseException: Text '2018-05-14 11:24:33.3928 America/Chicago' could not be parsed at index 20

Does anyone have any tips to troubleshoot or see what I am doing wrong?

[xpac · Accepted solution]

Your problem is that America/Chicago is not really a valid timezone information that Splunk can handle - it uses things like +06:00 or GMT.
You can see what is usually accepted on pages like strftime.org or the proper Splunk docs.
In this case, it seems to be Java that parses the timestamps, however their documentation shows the same.

Therefore - you might be able to have the database or the SQL query string set up in a way that returns the proper format.

Else, I don't know a way to fix this on the DB Connect/Splunk side...

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

[gn694]

OK, thankx for the confirmation.

I have updated the query so it now creates a new field without the TZ specified (which is OK in my case, as everything (Oracle servers, Splunk servers) are in the same time zone.

For anyone else who might have this problem reading this....

I modified the query for the orcle:audit:unified template from
SELECT *
FROM (
SELECT CAST((event_timestamp at TIME zone 'UTC') AS TIMESTAMP) EVENT_TIMESTAMP_UTC,u.*
FROM UNIFIED_AUDIT_TRAIL u)
WHERE EVENT_TIMESTAMP_UTC > ?
ORDER BY EVENT_TIMESTAMP_UTC ASC

to

SELECT *
FROM (
SELECT CAST((event_timestamp at TIME zone 'UTC') AS TIMESTAMP) EVENT_TIMESTAMP_UTC,CAST((event_timestamp) AS TIMESTAMP) EVENT_TIMESTAMP_2,u.*
FROM UNIFIED_AUDIT_TRAIL u)
WHERE EVENT_TIMESTAMP_UTC > ?
ORDER BY EVENT_TIMESTAMP_UTC ASC

And set the Timestamp field to EVENT_TIMESTAMP_2.