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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...