All Apps and Add-ons

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

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

Labels (1)
0 Karma

fatmaBouaziz
Loves-to-Learn Everything

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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?

0 Karma

fatmaBouaziz
Loves-to-Learn Everything

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

tempsnip.png

 

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...