topic Re: How do I convert a timestamp from one timezone to another (inline) in Splunk Search

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

weidertc — Thu, 23 Jul 2020 04:24:04 GMT

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?

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

richgalloway — Wed, 22 Jul 2020 14:47:36 GMT

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.

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

to4kawa — Wed, 22 Jul 2020 14:57:51 GMT

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

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

weidertc — Wed, 22 Jul 2020 15:18:07 GMT

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

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

to4kawa — Wed, 22 Jul 2020 20:24:46 GMT

| 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.

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

weidertc — Thu, 23 Jul 2020 21:01:05 GMT

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.

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

to4kawa — Thu, 23 Jul 2020 23:08:13 GMT

_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.

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

weidertc — Fri, 24 Jul 2020 15:59:30 GMT

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.

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

to4kawa — Fri, 24 Jul 2020 18:34:34 GMT

| 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.

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

weidertc — Tue, 28 Jul 2020 20:52:27 GMT

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!

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

senior_splunk — Mon, 17 Aug 2020 18:35:12 GMT

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

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

to4kawa — Mon, 17 Aug 2020 22:31:21 GMT

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

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

senior_splunk — Mon, 17 Aug 2020 22:34:53 GMT

@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/\..*$//"

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

to4kawa — Mon, 17 Aug 2020 22:41:39 GMT

| 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