Timestamp difference

[uhkc777]

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?

[Runals]

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

[uhkc777]

@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?

[uhkc777]

@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?

[Runals]

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?

[uhkc777]

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

[uhkc777]

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

[jkat54]

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.

[Runals]

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.

[jkat54]

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 😉

[jkat54]

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

... | eval _time=_time+14400

[uhkc777]

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

[jkat54]

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