topic Why is the same timestamp getting indexed differently in two different Splunk servers? in Getting Data In

Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Tue, 09 Jun 2015 16:22:41 GMT

Hi,

I am using DBConnect to fetch two timestamps from an Oracle database table, let's call them TS1 and TS2, having the following values (TS1 is the time used for indexing the data, whereas TS2 is the Rising Column😞

**TS1 TS2

02-JUN-2015 06:05:16 02-JUN-2015 10:21:14**

On my local Development system (a Windows system with Splunk version 6.2), the above data is indexed as it is, i.e.

TS1 = 02-JUN-2015 06:05:16, TS2 = 02-JUN-2015 10:21:14

However, on an Integration Testing Server (a Linux server with Splunk version 6.1.5), the same data is indexed as:

TS1 = 02-JUN-2015 06:05:16, TS2 = 02-JUN-2015 **11:21:14**

As we can see, only TS2 is off by 1 hour on the Linux server.

I am not sure why there is this difference between the way Splunk is indexing the Data. I have not made any props.conf changes to any of these two servers related to Time Zone configurations.

The Windows server is running on UTC with Daylight Saving Time enabled, and the Linux server is in BST.

Could someone please let me know what could be the reason behind this behaviour of the Linux server (Splunk version 6.1.5).

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

ppablo — Tue, 09 Jun 2015 16:33:06 GMT

Hi @SwatiApte

Would you be able to add what version of DB Connect you're using? DB Connect 1 or DB Connect 2?

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Tue, 09 Jun 2015 17:05:18 GMT

Splunk did a somewhat nasty thing in Splunk v6(dot???? I don't know which version for sure): They changed the precedence rules for TZ in props.conf and this may be the problem. In v5 and earlier (and maybe some versions of v6), The only way to set TZ was to have a configuration on the indexer (or, if a Heavy Forwarder, on the forwarder). In v6(?) this was changed and Splunk will honor any TZ setting from any forwarder (Universal, Light, Heavy) and this will be propagated internally to the indexers and this will OVERRIDE any setting that was deployed to the Indexers. This is a HUGE change and could cause a TOTALLY different TZ behavior just by upgrading the version of Splunk you are running and changing NOTHING else.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

bmacias84 — Tue, 09 Jun 2015 21:24:12 GMT

Have you checked your time zone preferences for your user. It defaults to the TZ of the local system. Edit your account and set Time Zone. Splunk handles offsets for users.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Wed, 10 Jun 2015 07:43:52 GMT

The DBConnect version is 1.1.6 on both the servers.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Wed, 10 Jun 2015 07:44:42 GMT

Hi,

The timezone preference is set to Default in both the servers.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Wed, 10 Jun 2015 07:48:29 GMT

Hi,

On both of these servers, we have a single instance of Splunk that does the data capturing, indexing and searching. We do not have separate instances for Forwarders, the TZ configuration would not have been overridden is what I feel.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Wed, 10 Jun 2015 12:54:21 GMT

Unless your Forwarder is your Indexer, then the situation that I described is entirely possible.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Wed, 10 Jun 2015 12:57:12 GMT

In certain situations (if you do not tell Splunk what the TZ value should be), it may use the TZ value of the Indexer's host OS; is one Indexer is in a different TZ than the other?

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Wed, 10 Jun 2015 12:58:36 GMT

Hi,

Our Forwarder and Indexer is the same Splunk instance.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

bmacias84 — Wed, 10 Jun 2015 16:31:05 GMT

Thats my point. When default is set Splunk will use the system Time/ Timezone. BST is one hour ahead of UTC so thats why your data shows up as off. In your profile set the TZ to GMT or what ever timezone you want. Splunk will automatically apply search offsets.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Wed, 10 Jun 2015 16:40:13 GMT

If your TZ value is default then you will see this problem; set it to an explicit value; if it is UTC then use:

TZ = UTC

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Thu, 11 Jun 2015 11:14:47 GMT

But both the servers are on BST, and both are set to Default Time Zones, then why the difference in results?

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Thu, 11 Jun 2015 14:37:35 GMT

It is an interesting question and I would like to know the answer if you ever find it but the solution is to use TZ=BST, in any case.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

woodcock — Sun, 14 Jun 2015 19:58:21 GMT

Forgive me for not noticing this earlier (you said it clearly enough) but your problem is not with TS1 (which Splunk is using for timestamping and setting the _time value for each event) but with TS2 which is a non-timestamp time that is inside the data. If that is really the case then what you are saying makes absolutely no sense at all.

First of all, Splunk will never (without some serious configuration work on your part) re-write data inside an event; it is always preserved, indexed, and passed back to searches as it was when it was received/forwarded.
Secondly, all of Splunk's time and TZ settings apply ONLY to the timesteamp (i.e. to _time).

The only way that what you are saying makes sense is if the generator of the data (a combination of DB Connect and your DB) is behaving differently when you connect from Dev vs. when you connect from Integration. I will admit that I am light on my DB Connect experience but I do know that v2 is very different from previous versions so have you upgraded to v2?

Another thing to check is to ensure that your SQL user, your SQL string, and your DB Connection is identical between the 2 servers. I have a strong suspicion that this is not the case and that whatever difference you find will be the root cause of the discrepancy.

Re: Why is the same timestamp getting indexed differently in two different Splunk servers?

SwatiApte — Mon, 28 Sep 2020 20:20:06 GMT

Hi,

Further to this, we created a simple java program that connects to the database and prints a single record. We executed this program from both our local Development system and on the Integration Testing server. We used a simple resultSet.getTimeStamp function on the result. We noticed that both the systems are returning the same value for each of the datetime attributes. Ex: "2015-06-18 09:48:35.0"

The issue is only when we execute the query from DBConnect, where it seems to convert the results returned into epoch format and that is where it seems to be adding an hour. Even when we use DBConnect, the results are fine when we apply the to_char function on the datetime attribute. For. e.g, select update_time, to_char(update_time,'dd-mon-yyyy hh24:mi:ss') from tablename returns the string representation of the attribute perfectly fine, but adds one hour to the first column (epoch format) and this addition is only happening on the integration server.

Regards
Swati