All Apps and Add-ons
Highlighted

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

Communicator

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 (EVENTTIMESTAMPUTC.)
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 splunkappdbconnectserver.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?

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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. 🙂

View solution in original post

0 Karma
Highlighted

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

Communicator

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((eventtimestamp at TIME zone 'UTC') AS TIMESTAMP) EVENTTIMESTAMPUTC,u.*
FROM UNIFIED
AUDITTRAIL u)
WHERE EVENT
TIMESTAMPUTC > ?
ORDER BY EVENT
TIMESTAMP_UTC ASC

to

SELECT *
FROM (
SELECT CAST((eventtimestamp at TIME zone 'UTC') AS TIMESTAMP) EVENTTIMESTAMPUTC,CAST((eventtimestamp) AS TIMESTAMP) EVENTTIMESTAMP2,u.*
FROM UNIFIEDAUDITTRAIL u)
WHERE EVENTTIMESTAMPUTC > ?
ORDER BY EVENTTIMESTAMPUTC ASC

And set the Timestamp field to EVENTTIMESTAMP2.