topic Re: Timestamp difference in Getting Data In

Timestamp difference

uhkc777 — Tue, 06 Sep 2016 16:03:02 GMT

Hi,

Index time 4 hours behind the actual timestamp of the database row we are pulling in as event. This is resulting in wrong Order Line count for events which are created between midnight 12 through 4 AM.

Here is an example: Looks at
• Index Time: 9/1/2016 12:21:36 PM
• OrderEntryDate: 2016-09-01 16:21:35

Can you anyone suggest me how can i change the index time _time as Order Entry Date?

Re: Timestamp difference

jkat54 — Tue, 06 Sep 2016 16:11:03 GMT

Since the timezone is not referenced in the timestamp coming from the database, I suggest adjusting the query to modify the date.

If this is SQL you can use something like this:

https://msdn.microsoft.com/en-us/library/ms186819.aspx

SELECT DATEADD(hour, +4, DATEADD(second, yourTimeStampColumn, '1970-01-01'))

or maybe the + is not required. I'm not a SQL DBA, but I did stay at a holiday inn express last night 😉

Re: Timestamp difference

jkat54 — Tue, 29 Sep 2020 10:49:49 GMT

You could also add 4 hours in splunk search prior to any statistical analysis:

... | eval _time=_time+14400

Re: Timestamp difference

uhkc777 — Tue, 29 Sep 2020 10:53:14 GMT

I did that eval _time=_time+14400...but the problem is when you set the Timerangepicker as Today---you can't get the data between 12AM-4AM bcoz of date changes. Whatever the data I'm getting after 4AM i'm changing _time by using above search

Re: Timestamp difference

Runals — Tue, 06 Sep 2016 16:49:58 GMT

In the props.conf on your indexers create a stanza like

[ParMed:SalesOrder]
TZ = insert whatever is appropriate

This will automagically do the math to place the events at the right chronological time.

Couple links to check out
http://docs.splunk.com/Documentation/Splunk/6.4.3/Data/Applytimezoneoffsetstotimestamps
http://docs.splunk.com/Documentation/Splunk/latest/admin/propsconf

Re: Timestamp difference

jkat54 — Tue, 06 Sep 2016 17:45:52 GMT

Runals has the better answer here. Please see his answer and let us know if there are any issues after implementing that.

Re: Timestamp difference

uhkc777 — Tue, 06 Sep 2016 18:11:40 GMT

@Runals I think this will work out--I want to change whenever the host is 10-201-- TZ to UTC time.

[host::ip-10-201-38-20]
TZ=US/UTC-----------------------is this correct?

Re: Timestamp difference

uhkc777 — Tue, 06 Sep 2016 18:17:23 GMT

@jkat54 I think this will work out--I want to change whenever the host is 10-201-- TZ to UTC time.

[host::ip-10-201-38-20]
TZ=US/UTC-----------------------is this correct?

Re: Timestamp difference

Runals — Tue, 06 Sep 2016 18:30:56 GMT

I believe that will make the time zone for all logs from that host UTC which likely isn't what you want to do. Do you have instances where the logs for the same sourcetype but different hosts are configured to log in different timezones?

Re: Timestamp difference

uhkc777 — Tue, 06 Sep 2016 18:41:58 GMT

Yes I want to change all logs from that host @Runals....NO i don;t have it

Re: Timestamp difference

uhkc777 — Tue, 06 Sep 2016 18:48:34 GMT

@Runals That stanza is correct or not? because i didn't see any changes in _time

Re: Timestamp difference

Runals — Tue, 06 Sep 2016 19:17:58 GMT

The format of the stanza looks correct but depending on the version of Splunk you have you might have to restart the indexer(s). The data that has already been ingested is set. Setting the timezone will only impact new data.

Re: Timestamp difference

jkat54 — Tue, 06 Sep 2016 19:48:24 GMT

the time/date settings are set upon ingestion and will only affect newer data from this host. Also I think you want to set it to US/EDT instead as from what I can tell you want it to be eastern timezone and it's currently GMT... again from what I can tell.

If you set it to US/UTC its seemingly the same timezone it's already applied.