mktime not picking up timezone information (Zulu/UTC, aka "Z")

[shawnce]

I have stream of events being generated by software running on customers systems (aka "endpoint") that are sent into our backend ultimately to be indexed by splunk. Each event contains at least one timestamp in it that we want to convert at search time to be usable as a time for things like bucketing, etc. These timestamps aren't pickup at index time (purposely for now).

The timestamps are in the following format: "2014-04-03T19:14:19.00200Z" (timezone is Zulu or UTC)

I am attempting to use the following to convert the ev_time field into a time that can later be used to bucket, etc. events based on time reported by an "endpoint".

index=myindex | convert timeformat="%FT%T.%5N%Z" mktime(ev_time) | fieldformat ev_time=strftime(ev_time, "%F %T.%3N %Z") | bucket ev_time span=1h | ...etc

This is working fine however the timezone isn't being picked up as UTC but instead is defaulting to PDT/PST (server & source type default timezone). This results in things being time shift forward by the difference between UTC and PDT/PST.

Is "Z" not picked up as UTC by mktime (aka by way of %Z)? Do we have to use "UTC" at the timezone qualifier in the timestamps?

[martin_mueller · Accepted solution]

It seems to work for me though - running this search:

| stats count | eval ev_time = "2014-04-03T19:14:19.00200Z" | eval converted_time_1 = ev_time | convert timeformat="%FT%T.%5N%Z" mktime(converted_time_1) | eval converted_time_2 = strftime(converted_time_1, "%F %T.%3N %Z")

gives me a converted_time_2 of 9:14PM in my server's timezone, which is UTC+2. The epoch timestamp in converted_time_1 of 1396552459.002 correctly resolves to 7:14PM UTC.

[martin_mueller]

No worries - to ease future debugging, you may want to take a look at the Search Exploder view shipped with SideviewUtils: http://apps.splunk.com/app/1486/

[shawnce]

Yeah so it looks like it working...

... | convert timeformat="%FT%T.%6N%Z" mktime(ev_time) AS ev_time2 | fieldformat ev_time3=strftime(ev_time2, "%F %T.%3N %Z") | table _time, ev_time, ev_time2, ev_time3

2014-04-03 14:35:18 2014-04-03T*21:35:03.47100Z 1396560903.47100 2014-04-03 **14*:35:03.471 PDT

...and testing another way it also works...

... | eval ev_time_org =ev_time | convert timeformat="%FT%T.%5N%Z" mktime(ev_time) | fieldformat ev_time=strftime(ev_time, "%F %T.%3N %Z") | table _time, ev_time, ev_time_org

2014-04-03 14:37:52 2014-04-03 14:37:14.583 PDT 2014-04-03T*21*:37:14.58300Z

Sorry all not sure why it appeared to be failing for me in early tests.

[shawnce]

Huh yeah... running your example on our system it looks to pick up the timezone correctly. ...off to double check my original attempts at indexed events

converted_time_1 1396552459.00200
converted_time_2 2014-04-03 12:14:19.002 PDT
ev_time 2014-04-03T19:14:19.00200Z

[somesoni2]

Based on the Splunk's zoneinfo database (http://en.wikipedia.org/wiki/List_of_zoneinfo_timezones), 'Z' is not listed as a Timezone value (to be identified by '%Z'). If you can change/replace the zone name from 'Z' to UTC or GMT or Zulu, that can work.