Getting Data In

adjusting date_hour in report to reflect local timezone

Explorer

I log all my devices using GMT. When I run a report where I do a count by date_hour I would like to subtract 6 from the hour to reflect local time for the people reading the results. Any ideas?

Tags (2)
1 Solution

Splunk Employee
Splunk Employee

You should apply a TZ offset to your data at index-time as described here. That way, Splunk will know what timezone your data is coming from, and it will display it to you relative to the timezone that your Splunk server is running in.

So if you're indexing GMT data on a Splunk instance in PST, the timestamp in your event will be 09:00, but the Splunk _time field will display 01:00

View solution in original post

Communicator

It sounds like MHS is indexing data correctly, where GMT data is going into Splunk correctly as GMT data. The problem is that something like

| chart count by date_hour

displays buckets in GMT, because date_hour doesn't adjust to local time like _time does.

What I do is create my own local_date_hour at search time. Try the following to compare the difference:

| eval local_date_hour=strftime(_time,"%H") | chart count, first(date_hour) as date_hour by local_date_hour

Note, if your local timezone is GMT, you'll see the two hour columns have the same value. 🙂

Communicator

Splunk needs to fix this bug. date_hour should use the offset time specified in props.conf. Why have different behaviors for time across different operators (timechart offsets via props.conf TZ=GMT setting, but date_hour does not)? At least add us a local_date_hour. @BenjaminWyatt - this works for CST offset from GMT: eval date_hour = date_hour-6 | eval date_hour = if(date_hour<0, 24 + date_hour, date_hour) to account for negative date hours.

Communicator

The only answer I can think of for this is to create a calculated field that accounts for the timezone difference, or just add a "| eval date_hour=date_hour-5" to your searches. But agreed, this is very annoying behavior!

0 Karma

Influencer

THIS! It seems counter-intuitive to me that date_hour is based on the nominal timestamp in the log vs the displayed _time. Oh so many invalid reports because I didn't realize this. 😞

0 Karma

Splunk Employee
Splunk Employee

You should apply a TZ offset to your data at index-time as described here. That way, Splunk will know what timezone your data is coming from, and it will display it to you relative to the timezone that your Splunk server is running in.

So if you're indexing GMT data on a Splunk instance in PST, the timestamp in your event will be 09:00, but the Splunk _time field will display 01:00

View solution in original post

Splunk Employee
Splunk Employee

The reason this we want to do this at index time, rather than search time, is that Splunk is inherently a time-organized index, and therefore timestamps should be adjusted and stored with their absolute real time when written, so they can also be retrieved easily with their correct time, regardless of how the timestamp was expressed.

0 Karma