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.
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.
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
|rex command just rexes the timestamp which prefixes all indexed events into Splunk field
firstdate. I called it
<timestamp> in the previous event structure.
So we have these four timestamps:
Now on to the issues...
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.
Now to try with explicitly set timezones, I configured the following:
-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.
Europe/Zurich : +02:00via 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
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.
I'd be happy with either solution to one of these questions:
Splunk 7.3.3 (build 7af3758d0d5e) DBX 3.1.4 and 3.3.0 (after upgrade) ojdbc7.jar 22.214.171.124.0 java8 jdk1.8.0_202 OS Linux RHEL7.6
Sorry for the long post, I just wanted to provide all possibly relevant information.
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?
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).
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.