All Apps and Add-ons
Highlighted

DB Connect 3 Input Timezone or Daylight Savings Time Issue

Communicator

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 EVENTID. The timestamp ist extracted from the EVENTTIME 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.

0 Karma