How do I map my personally TZ-adjusted time to another TZ?

woodcock — Tue, 14 Nov 2017 15:02:28 GMT

Occasionally, we need to do user-TZ-setting-agnostic stuff in a search and so we need to be able to say, despite the user's TZ setting, tell me what the hour-of-the-day was for each time in a known TZ (usually GMT). This way, regardless of which user runs (or rather, regardless of his TZ setting), the search, the logic does not change. How can this be done?

Re: How do I map my personally TZ-adjusted time to another TZ?

sshelly_splunk — Tue, 14 Nov 2017 15:11:09 GMT

something like :
index=someindex date_wday=monday date_hour=01
I am pretty sure the date_* fields are all relative, so that "should" work:) reply if it does not

Re: How do I map my personally TZ-adjusted time to another TZ?

woodcock — Tue, 14 Nov 2017 15:12:07 GMT

Here are the pieces of the puzzle:

This shows you the TZ settings for your users (probably just you, unless you have admin):

|rest/services/authentication/users/ splunk_server=local
| table tz

This shows you the possible TZ settings on your Search Head:

|rest/servicesNS/-/search/data/ui/manager 
| regex eai:data="Time zone" 
| head 1 
| rename eai:data AS _raw 
| table _raw 
| rex mode=sed "s/(?ms)^.*Default System Timezone --\"\/>[\s\r\n]+(.*?)<\/options>.*$/\1/" 
| eval raw=split(_raw, "<opt value=") 
| mvexpand raw 
| rex field=raw "^\"(?<value>[^\"]+)\"\s+label=\"(?<label>[^\"]+)\"" 
| fields - _raw raw
| search label="*" AND value="*"

This is what really does what we need:

|makeresults 
| eval MYtime=strftime(_time, "%m/%d/%Y %H:%M:%S %Z")
| eval UTCtime=MYtime
| rex field=UTCtime mode=sed "s/\s+\S+$/ UTC/"
| eval UTC_time=strptime(UTCtime, "%m/%d/%Y %H:%M:%S %Z")
| eval TZdelta = round(_time - UTC_time, 0)
| eval TZdeltaDuration = if((TZdelta<0), "-", "") . tostring(abs(TZdelta), "duration")
| rename COMMENT AS "Calcluate 2 hourmin values: 1 personal which varies, and 1 UTC-normalized which does not"
| eval MYhourmin=strftime(_time, "%H%M")
| eval UTChourmin=strftime(_time + TZdelta, "%H%M")

This is particularly important if you are having somebody else run scheduled searches who is not in your TZ. That is why we use a macro called My_TZ_to_Other_TZ(1) (using input argument other_TZ_INPUT) defined this way:

[ |makeresults
| rename COMMENT01of25 AS "This is necessary when using _time split-bys effefcted by TZ,"
| rename COMMENT02of25 AS "such as 'span=1d' (the start/end of a day varies by TZ)."
| rename COMMENT03of25 AS "If user 'nobody' runs your scheduled search, he's probably 'GMT'"
| rename COMMENT04of25 AS "and his days align that way and will not align to those of us"
| rename COMMENT05of25 AS "in other TZs like 'CDT'.  Using this macro has FIXME steps."
| rename COMMENT06of25 AS "#1: Carefully adjust the time that the scheduled search runs."
| rename COMMENT07of25 AS "For example, if you need to run your search every day"
| rename COMMENT08of25 AS "just after midnight, it must be set far enough after midnight"
| rename comment09of25 AS "That it will run after it is midnight in *your* TZ, otherwise"
| rename COMMENT10of25 AS "part of that last day will be cut off and results too low."
| rename COMMENT11of25 AS "#2: Extend the timepicker span of on both sides widely enough"
| rename COMMENT12of25 AS "that (even with daylight savings) it covers the maximum delta"
| rename COMMENT13of25 AS "between the two TZ values (yours vs. the one 'nobody' uses)."
| rename COMMENT14of25 AS "#3: Pick an initial _time split-by span NOT dependant on TZ;"
| rename COMMENT15of25 AS "anything other than days/weeks/months/quarters/years works."
| rename COMMENT16of25 AS "#4: After the initial 'stats', call the macro like this:"
| rename COMMENT17of25 AS "| eval _time = _time + My_TZ_to_Other_TZ(\"CDT\")"
| rename COMMENT18of25 AS "#5: Now re-stats with your TZ-dependant _time split-by span."
| rename COMMENT19of25 AS "#6: Undo the prevous time shift, calling the macro like this:"
| rename COMMENT20of25 AS "| eval _time = _time - My_TZ_to_Other_TZ(\"CDT\")"
| rename COMMENT21of25 AS "#7: Trim the extra partial data edges with code like this:"
| rename COMMENT22of25 AS "| eventstats min(_time) AS min_drop max(_time) AS max_drop"
| rename COMMENT23of25 AS "| where _time!=min_drop AND _time!=max_drop"
| rename COMMENT24of25 AS "| fields - min_drop_time max_drop_time"
| rename COMMENT25of25 AS "#8: Dump the data to lookup file or summary index."
| bin _time span=1d
| eval TZ_delta_seconds = _time - strptime(strftime(_time, "%m/%d/%Y %H:%M:%S") . " $other_TZ_INPUT$", "%m/%d/%Y %H:%M:%S %Z")
| eval TZ_delta_hours = TZ_delta_seconds / 60 / 60
| return $TZ_delta_seconds ]

You may have seen me gripe from time to time about the date_* fields and here is why. First of all, not all events have them; only those that have timestamps inside of the events. But if your events do have it, it is actually the thing that I am trying to fix here: it is a UTC/GMT-normalized value that is not normalized to your personal TZ setting. So if my events had the date_* values, I could have just used date_hour.

Re: How do I map my personally TZ-adjusted time to another TZ?

pgullette — Mon, 06 Aug 2018 22:10:20 GMT

I happened upon this post while needing to do something similar, and I came up with something that's a little different than your approach, and I thought you might find it useful. If nothing else, I'd like others' feedback if there is something wrong with my approach.

Basically, I'm using one of the date formatters that returns the number of minutes based on _time's offset. Then I'm subtracting that offset converted to seconds. Here is an example that seems to work for me in the tests I've run:

| makeresults
| head 1
| eval utc_time=_time - (tonumber(strftime(_time, "%Ez")) * 60), utc_time_formatted=strftime(utc_time, "%F %T UTC")

The only thing that may be a downside to this is that utc_time in my case isn't actually being converted to a UTC-based timestamp. If you used any of the %z formatters in a strftime, it would print whatever the default user's timezone is already as far as I can tell.

Re: How do I map my personally TZ-adjusted time to another TZ?

woodcock — Thu, 23 Apr 2020 23:06:37 GMT

Yes, I updated my answer along similar lines with a great deal of instructional detail on how to use such SPL.

topic Re: How do I map my personally TZ-adjusted time to another TZ? in Getting Data In

How do I map my personally TZ-adjusted time to another TZ?

Re: How do I map my personally TZ-adjusted time to another TZ?

Re: How do I map my personally TZ-adjusted time to another TZ?

Re: How do I map my personally TZ-adjusted time to another TZ?

Re: How do I map my personally TZ-adjusted time to another TZ?