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