Convert timestamp in event to specific timezone

[leftrightleft]

Hey Splunk Gurus-

I'm attempting to calculate the duration between when an event was first identified (which is an entry in the event "alert.created_at") and the "_time" timestamp.

I'm able to calculate this timestamp difference using strptime("alert.created_at") but the conversion of that time to epoch is relative to the viewers timezone.  The duration changes based on how you configure the Splunk UI timezone.

The "_time" field is set to "current" in props.conf

Here's my current search:

index=* alert.tool.name=* action="fixed" 
| eval create_time=strptime('alert.created_at', "%Y-%m-%dT%H:%M:%SZ")  
| eval duration = _time - create_time 

Here's a sample of the log:

{
    "action": "fixed",
    "alert": {
        "number": 2,
        "created_at": "2021-11-22T23:49:19Z"
    }
}

When I execute this search while my UI preferences are set to "GMT" the result is 1183959 which is the correct duration.  When I set that preference to "PST", the result is 1155159.  That number is wrong by exactly 8 hours.

Any suggestions on how to deal with this?  I'm fine with either a search-time solution or a config change in props.conf if that's best.

Thanks!  

[johnhuang]

Try this:

index=* alert.tool.name=* action="fixed"
| eval create_time=strptime('alert.created_at'."+00","%Y-%m-%dT%H:%M:%SZ%z")
| eval duration=_time-create_time

[PickleRick]

Unfortunately (or not - depending on how you look at it), there is no possibility to set a timezone different than your user's configured timezone for a single search. That's one.

And two - if there  is no timezone information within the parsed time string, it is parsed according to your local timezone. Makes sense.

So if you want to make sure the time string is parsed according to particular timezone, make sure it's included in the string and format your timespec string accordingly.

I'm not sure (would have to check but don't have access to my splunk at the moment) if "Z" is recognized or if you have to change it on the fly to UTC or GMT.

[leftrightleft]

Yeah that's kinda my hang up.  The timestamp contains a "Z" which is part of the ISO 8601 definition.  I was really hoping strptime() would be able to recognize it.

[PickleRick · Accepted solution]

It seems it is able to recognize it. In your strptime you don't use field code for timezone.

Check out my example - I render a local timestamp (I'm in CET, you might be somewhere else) but instead of my local timezone I add a constant "Z". Then I reparse the string with %Z interpreting timezone properly as UTC.

| makeresults 
| eval time=strftime(_time, "%Y-%m-%d %H:%M:%SZ")
| eval _time=strptime(time, "%Y-%m-%d %H:%M:%S%Z")

[leftrightleft]

It was as simple as getting that "%" in place.  Thanks!