Splunk Search

How do time based lookups apply user time zone?

Champion

I have a time based lookup set up with a lookup file containing time values of full days, such as 2017-08-14 (with a proper time_field strftime value in the lookup definition of course). When I do something like

| makeresults count=5 | streamstats count | eval _time=relative_time(_time, "-".count."d@d") | lookup timebased_lookup_definition

with a lookup containing one value for some day within the last five days on my single instance environment on my local machine, this works as expected. But when I use the same setup on a search head which is in a different time zone than my user is configured to use (that splunk server is UTC, I am in GMT+1), only the results a day older than the value in the lookup are properly looked up, which seems weird. However, if I use

| eval _time=relative_time(_time, "-".count."d@d+2h")

to change the time used for the lookup to account for the difference between the time zones, it works fine again.
So, how are time zones applied in time based lookups? I would have expected them to ignore any user time zone setting, but the results I'm seeing suggest that time zone is taken into account at some point. Unfortunately, I can't change the way the dates are held in the lookup, meaning I can't add a timezone to them or anything like that, and I need this to work for users in different time zones. I came up with something as ugly as

| eval tz=strftime(now(), "%z"), direction=substr(tz, 1, 1), value=tonumber(tz)*0.6, _time=relative_time(_time, "@d".direction.value."min")

to individually generate a dynamic _time value based on the users timezone, but I would like to use a better solution if possible, and also generally understand how time zones are applied in time based lookups.
Thanks for your contributions!

1 Solution

Ultra Champion

Trying my best to follow since I don't have the lookup to replicate with.

I'm guessing the savedsearch would run as the timezone of either the user who owns it (if its scheduled) or the user who runs it (if kicked off on a dashboard panel).

If the timezone is just an arbitrary field in the lookup then Splunk treats it as such and doesn't really know that it should be adjusted other than adjusting time to the user running it.

Regardless, why not keep everything in UTC and then either (1) switch to the local time zone at the end of the search (like a formatting) or (2) inform in the title that it's UTC.

Sorry for the over simplification. You're welcome to post a sanitized version of the lookup to see if we can replicate this.

View solution in original post

Ultra Champion

Trying my best to follow since I don't have the lookup to replicate with.

I'm guessing the savedsearch would run as the timezone of either the user who owns it (if its scheduled) or the user who runs it (if kicked off on a dashboard panel).

If the timezone is just an arbitrary field in the lookup then Splunk treats it as such and doesn't really know that it should be adjusted other than adjusting time to the user running it.

Regardless, why not keep everything in UTC and then either (1) switch to the local time zone at the end of the search (like a formatting) or (2) inform in the title that it's UTC.

Sorry for the over simplification. You're welcome to post a sanitized version of the lookup to see if we can replicate this.

View solution in original post

Champion

Thanks for taking the time to look into this. I'll try to be more detailed. To reproduce, you need the following contents in your lookup file:

date,input,output
2017-01-05,foo,10

The lookup definition looks like this:

[timebased_lookup_definition]
filename = yourfile.csv
time_field = date
time_format = %Y-%m-%d

Now run the following search:

| makeresults count=5 | streamstats count | eval _time=relative_time(1504821600, "-".count."d@d") | eval input="foo" | lookup timebased_lookup_definition input OUTPUT output

The date on and after which the lookup adds "10" to the output field depends on which time zone your user is configured to use relative to the time zone the splunk server is in, apparently. Try changing your user time zone to see the effect (as in, use the same time zone for your splunk user as your splunk server is in, and then use a different one). I would have expected the results to be the same regardless of time zone settings.

0 Karma

Ultra Champion

I'm still struggling here. The input is the word 'foo' in this example. All this example search does is generate the text "10" in the output column and I'm not seeing the time field being used at all.

Taking a step back, is the relevant discussion around if relative_time is also relative to the users timezone? If so, then I would expect this to be working as designed and switching to the @day of the user running the search. Do you want to have something that snaps to the same time regardless of timezone? To put it another way (and to keep this focused on your real problem rather than debugging the example search), do you want a means to ensure the snapto goes to the same epoch moment regardless of timezone?

If so, I wonder if the now(), which renders the time in epoch (regardless of timezone of course) would be of help for you here.

What's the larger thing you're trying to do? We're super focused on the behavior of this one eval function but I'm wondering about the larger story in case there's an alternative solution we can find.

0 Karma

Champion

The story behind this is that there is data presented to the user aggregated per day, and for every day there is a default value for something that is output by a lookup. It is possible that this default value needs to differ temporarily, and users want to set the different value with a start and end date, without changing the default value. What I have done to enable this is have two lookups, one with the default value (non-timebased) and one lookup that users enter two dates into (begin and end date). I then set two time based lookups on the second lookup file. One of them uses the begin date, the other uses the end date (the second one also uses a minimum offset of 1 second to include the day of the last date, but that is not important here). I calculate the effective value with

if(NOT value_begin=value_end, value_begin, null())

which sets the value from the second lookup whenever the two time based lookups did not return the same value for a result, which is exactly the duration between the start and end date. I then coalesce the default value and this one.

So what I have splunk-wise are results with _time values containing the beginning of days (it shouldn't matter if they come from, say, | timechart span=1d or from | eval _time=relative_time()), and two time based lookups run against this data. This works perfectly fine as long as the splunk server and the user running the search are using the same time zone. When it breaks because of different time zones, I assume it does so because of how the time based lookup looks up the _time value. I think the problem is not with where _time comes from since the value in _time is time zone agnostic (it's an epoch after all). I don't know how time based lookups are handled however, which is why I'm asking here.

0 Karma

Ultra Champion

So here's my thoughts, but obviously let me know if I am still missing the point.

Splunk will do a best guess on most things unless told specifically. So in this case, the lookup doesn't have a timezone, so when it runs, it would run applying the timezone of the user who created the lookup. Now, I haven't seen the sourcecode to say I'm positive about this but this is my guess based on other behaviors.

So, if you want to have something apply only if it occurs between two time points, you would do well to treat everything as epochs or times with timezones (essentially the same thing). It appears that the lookup is using relative days because there is no timezone.

Part of the confusion might have been because while validating the behavior, relative_time was being used...which is relative to the user. So you could replace that part with a subtraction of 86400 (seconds in a day) multiplied by the count subtracted from epoch to calculate number of days away.

I hope that helps?

Champion

Ah, yeah that is indeed the problem. relative_time applies the user time zone, not sure how I missed that, thanks... I'll try to work with seconds instead. Duh!
Apparently, I can go back to the original assumption that time based lookups do not care about time zones, they work with epoch values everywhere. I just messed up my epoch before feeding it into the lookup.

0 Karma

Ultra Champion

Oh too funny! So classic case of debugging the debugging rather than the original issue. I hate and love when I do that (hate cause I wasted time; love cause it means the issue isn't as hard as I thought)!

0 Karma

Ultra Champion

Still reading, but wanted to float another idea for the if statement:
case(value_begin!=value_end, value_begin)
Without testing, my coffee-less brain says that should be the same thing.

0 Karma

Champion

It isn't. Compare these:

| makeresults count=3 | eval a="1", b = a | streamstats count | eval a=if(count=3, null(), a), b=if(count>1, null(), b) | fields - _time count
| eval a_uneq_b = if(a!=b, a, null()) | eval not_a_eq_b = if(NOT a=b, a, null())

!= only works if the field exists, check here for details.

0 Karma

Ultra Champion

Ha ha ha. Yes, I certainly ASSUMED that the field exists. Fair point.
Also an option might be to use the isnull function but I haven't validated it like you did so just sharing an unqualified thought that popped into head.

0 Karma

Ultra Champion

Is your time_format really %Y.%m.%d? It should be %Y-%m-%d. I'm guessing this is trivial and just something you typo-ed while creating the example but not the real issue here.

0 Karma

Champion

Right, that was a typo - sorry. Corrected the comment

0 Karma