Splunk Search

How do I convert a timestamp from one timezone to another (inline)?

weidertc
Communicator

I have an issue where logs contain timestamps in zulu and the server uses local time for its index.  I need to calculate delays now since the logs are initially written on vendor servers and there can be between 0 delay or over an hour behind.

I can't find any functions that do this and am not having luck either converting a timestamp from one timezone to another or converting timezones to seconds to do any math on.

timestamp log was written

 

2020-07-22T12:59:12.301063Z

 

timezone log was indexed from _time

 

-0400

 

I am not an admin and have no control or influence over the servers or its configuration.

Is there an easy way to do this inline in the query?

Labels (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| fields - _time
| eval epoch=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval my_local_TZ=mvindex(split(strftime(epoch,"%FT%T %:::z")," "),1)
| eval my_local_time=ceil(epoch).".".mvindex(split(epoch,"."),1)
| eval time_args = if( -1 * my_local_TZ >= 0, "+".substr(my_local_TZ,2,3), printf("%03d",-1 * my_local_TZ))
| eval UTC = ceil(relative_time(epoch,time_args."h")).".".mvindex(split(epoch,"."),1)
| convert ctime(UTC) ctime(my_local_time)

I'm sorry. I've some mistake. I fixed it.

View solution in original post

to4kawa
Ultra Champion
| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| eval UTC=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval EST=ceil(relative_time(UTC,"-4h")).".".mvindex(split(UTC,"."),1)
| convert ctime(UTC) ctime(EST)

how about this? It isn't very easy, I guess.

0 Karma

weidertc
Communicator

The problem with this is that the offset is hardcoded.  I cannot determine the time zone of the server as it changes and I need some SPL that is aware of the server's timezone.

This may be enough though.  It looks like I need a table of timezone offsets and its relative time equivalent (e.g. -4h) then i can extract the timezone from _time and look it up in the table to get the relative time equivalent.

It's unfortunate that it's this hard.

Thanks

0 Karma

to4kawa
Ultra Champion
| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| eval UTC=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval my_local_TZ=mvindex(split(strftime(UTC,"%FT%T %:::z")," "),1)
| eval my_local_time=ceil(relative_time(UTC,my_local_TZ."h")).".".mvindex(split(UTC,"."),1)
| convert ctime(UTC) ctime(my_local_time)

you don't need hard coded.

0 Karma

senior_splunk
Explorer

@to4kawa  thank you for this but I do have a question as I am still fairly new to Splunk.  How can I have the corrected time not show the microseconds?

I have this:   08/17/2020 10:18:43.071000

and I need this:  08/17/2020 10:18:43

 Thanks in advance!

 

0 Karma

to4kawa
Ultra Champion

@senior_splunk 

I can't tell you anything about the time without knowing the details.

First, please clarify whether you are talking about logs, display or field processing.

0 Karma

senior_splunk
Explorer

@to4kawa  apologies, I should have been more clear.  The output of your code produces time that is adjusted correctly but includes microseconds in the output.  I was simply looking for a way to produce the output without them.

A co-worker who is much better with splunk than I am gave me the following which does the trick

rex field=my_local_time mode=sed "s/\..*$//"

0 Karma

to4kawa
Ultra Champion

| eval my_local_time=ceil(epoch).".".mvindex(split(epoch,"."),1)

My query is deliberately adding microseconds here.

just simply:

| eval my_local_time=ceil(epoch)

@senior_splunk 

weidertc
Communicator

This is an improvement.  For some reason though it doubles the offset.  i am at -4h, but 2020-07-22T12:59:12.301063Z is reduced by 8 to become 07/22/2020 04:59:12.301063.

I think Splunk strptime() is converting the timezone.  It uses the timezone of the logged in user instead of the server local time.  It'll only work if i am in the same timezone as the server, which is fine for me but not usually the case with others, and then the rest of the lines re-apply the timezone to double it.

0 Karma

to4kawa
Ultra Champion

_time is epoch time. default  is UTC. when it displays, it changes user preference time zone.

https://en.wikipedia.org/wiki/Unix_time

if index setting is good, time displays appropriate time zone. it is no problem. 

0 Karma

weidertc
Communicator

What I'm saying is the offset is doubled with your SPL.  I don't think Splunk will let you use strptime or strftime in a solution because it uses your local time which can't be a factor.  I am only trying to work with vendor time and splunk server local time.

0 Karma

to4kawa
Ultra Champion
| makeresults
| eval _raw="2020-07-22T12:59:12.301063Z"
| fields - _time
| eval epoch=strptime(_raw."+0000","%FT%T.%6QZ%z")
| eval my_local_TZ=mvindex(split(strftime(epoch,"%FT%T %:::z")," "),1)
| eval my_local_time=ceil(epoch).".".mvindex(split(epoch,"."),1)
| eval time_args = if( -1 * my_local_TZ >= 0, "+".substr(my_local_TZ,2,3), printf("%03d",-1 * my_local_TZ))
| eval UTC = ceil(relative_time(epoch,time_args."h")).".".mvindex(split(epoch,"."),1)
| convert ctime(UTC) ctime(my_local_time)

I'm sorry. I've some mistake. I fixed it.

weidertc
Communicator

I tested this out in multiple timezones and it produces the correct and same answer.

it's odd how it takes all this, but it works.

Thank you!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Splunk does not have a function for converting time zones.  Go to https://ideas.splunk.com to suggest one or to up-vote someone else's idea.

Splunk stores times in UTC and then renders them in the user's selected zone.  I suggest you change your Splunk preferences to display time in UTC so you see the true time of the event.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...