Is it DB Connect 3 Input Timezone or Daylight Savings Time Issue?

[Muryoutaisuu]

Some facts beforehand

DB View Setup

We are in Timezone Europe/Zurich, that is CEST+0200 including daytime savings. Since the switch to daytime savings on 29. March, we're having issues with the time conversion on our Splunk DB connect inputs. We've never configured specific timezone on the db connect app or on the inputs. Normally it just worked fine.

We have a multiple DB views, from which we read and then index into Splunk. This particual View has these four columns:

EVENT_ID     NUMBER
LOG_LEVEL    NUMBER
EVENT_TIME   TIMESTAMP
EVENT        CLOB

It is a follow tail input, rising column set to EVENT_ID. The timestamp ist extracted from the EVENT_TIME column. The events are not updated, hence it's safe to index them with the follow tail method.

DB Input Setup

The input's SQL Query looks like this:

SELECT * FROM (SELECT * FROM "ONL"."DBG_EVENT_LOG" WHERE EVENT_TIME >= sysdate-1) t WHERE EVENT_ID > ? ORDER BY EVENT_ID ASC

WHERE EVENT_TIME >= sysdate-1 is there to improve query search time performance as it is a really big DB view.

The EVENT column contains several lines that belong together. Each line starts with another timestamp. When splunk indexes the events, it writes all columns prefixed by a timestamp, which I assume is the timestamp from EVENT_TIME. This gets us events in Splunk with following structure:

<timestamp>, EVENT_ID="<EVENT_ID>", EVENT_TIME="<EVENT_TIME>", EVENT="<EVENT>"

For some reason it omits LOG_LEVEL, but this is no problem at all.

Splunk Query for Analysing the Issue

I have following Splunk query to compare the timestamps to each other:

|search <base search for raw events>
|eval indextime=strftime(_indextime, "%c") 
|eval time=strftime(_time, "%c") 
|rex field=_raw "(?<firstdate>\S+\s\S+),"
|table time, EVENT_TIME, firstdate, indextime
|sort by indextime DESC

The |rex command just rexes the timestamp which prefixes all indexed events into Splunk field firstdate. I called it <timestamp> in the previous event structure.

Timestamps

So we have these four timestamps:

1. _time as time
2. _indextime as indextime
3. EVENT_TIME (incorporated into the splunk event)
4. firstdate (incorporated into the splunk event)

Now on to the issues...

Issue with no Timezones Set at all

I haven't set any timezone configuration at all. I let Splunk automatically decide, what timezone the servers run on and how the events should be converted. So I get following timestamps:

time                        EVENT_TIME                  firstdate                     indextime
Mon Apr 20 13:01:04 2020    2020-04-20 12:01:04.382576  2020-04-20 12:01:04.382     Mon Apr 20 12:01:30 2020
Mon Apr 20 13:01:04 2020    2020-04-20 12:01:04.369736  2020-04-20 12:01:04.369     Mon Apr 20 12:01:30 2020

Splunk indexes the event chronically at the wrong time, see difference of 1 hour between time and indextime.

Issue with all Timezones Set to Europe/Zurich

Now to try with explicitly set timezones, I configured the following:

1. Splunk starts with environment variable TZ="Europe/Zurich" set (in the systemd .service manifesto)
2. Added option -Duser.timezone="Europe/Zurich" via GUI in Splunk DB Connect App -> Configuration -> Settings -> General to both Task Server JVM Options and Query Server JVM Options.
3. Set DB Input Timezone to Europe/Zurich : +02:00 via GUI in Splunk DB Connect App -> Configuration -> Databases -> Connections -> Selecting the input and setting Timezone

When searching for events now, I get following differing timestamps:

time                        EVENT_TIME                  firstdate                     indextime
Mon Apr 20 12:17:41 2020    2020-04-20 12:17:41.679789  2020-04-20 11:17:41.679     Mon Apr 20 12:18:02 2020
Mon Apr 20 12:17:41 2020    2020-04-20 12:17:41.599967  2020-04-20 11:17:41.599     Mon Apr 20 12:18:02 2020

Either firstdate misses the daytime savings configuration and hence is only +01:00, or it actually has the daytime savings but is indexes as UTC, hence again only +01:00.

Questions

I'd be happy with either solution to one of these questions:

1. How to remove that prefixed timestamp?
2. How to disable timezone conversion for that timestamp?

Some Specs

Splunk       7.3.3 (build 7af3758d0d5e) 
DBX          3.1.4 and 3.3.0 (after upgrade)
ojdbc7.jar   12.1.0.2.0
java8        jdk1.8.0_202
OS Linux     RHEL7.6

Sorry for the long post, I just wanted to provide all possibly relevant information.

[fatmaBouaziz]

Hello,
did you manage to solve your problem? I have the same issue.

[PickleRick]

The safest solution to such problems is to:

1. Have the timestamp as epoch time or

2. Have the timezone info included in the timestamp

3. Have the timestamp always in the same pre-set timezone (without DST adjustments).

Otherwise timestamps can get nasty. Can you make your SQL query return the timestamp in epoch or make it include the timezone info?

[fatmaBouaziz]

That does not work for me:

Here is more explanation of the problem: I have db connect input scheduled at 0 8 * * * But they only appres in splunk index at 0 9 * * * , and that problem appears since the daylight savings (yesterday).

[PickleRick]

Look at https://docs.splunk.com/Documentation/DBX/3.8.0/DeployDBX/Createandmanagedatabaseconnections#Create_...

Especially at the part about timezones. If your database supports it you can format the timestamp in your SQL query as fixed timezone (like GMT) and set your connection settings to the same timezone.