How to create a search for BST ( British Summer Time) Search Time Interval Change?

[djcascione]

Hi 

I have a SPL query that needs to adjust at search time when we are falling in and out of BST.  During BST, the search has to search between the hours of 19:00 & 7:00. Outside of BST, the search needs to adjust and search between the hours of 20:00 & 8:00. 

I have created a lookup where I capture the dates of when BST starts and stops. I have also created the logic max date and min date to identify the Sundays that start and end BST. This part is working

I need help to complete the search to filter results where if the date is outside of BST, to adjust from 19:00-7:00 search window to the 20:00 - 8:00   search window.

index=my_index
| eval year=strftime(_time,"%Y")
| lookup bst_lookup.csv year OUTPUTNEW date_sunday
| stats values(*) as * max(date_sunday) as maxdate min(date_sunday) as mindate latest(_time) as time by field
| eval isbst=if(time>mindate AND time<maxdate , 1,0)

Thanks!

[djcascione · Accepted solution]

Thanks for the help! I have it up and running with this SPL:

index=my_index
| eval year=strftime(_time,"%Y")
| lookup bst_lookup.csv year OUTPUTNEW date_sunday
| stats values(*) as * max(date_sunday) as maxdate min(date_sunday) as mindate latest(_time) as time by field
| eval isbst=if(time>mindate AND time<maxdate , 1,0)
| eval date_hour_max=19+isbst, date_hour_min=7+isbst
| search (date_hour<date_hour_min) OR (date_hour>date_hour_max)

[PickleRick]

OK. I still have a feeling that you're overcomplicating this and the final search is not very effective.

[PickleRick]

As @ITWhisperer alread said - the use case is a bit unclear.

If you want to specify your search timerange according to a different timezone (I suppose that's what you're trying to do since you want to take into account the fact that you're in daylight saving or not in your "home timezone"), you could use a subsearch and strptime with a timezone containing datetime string.

Something like

<my initial search> `| makeresults 
   | eval earliest=strptime("your_timestamp_in_different_time_zone","your_time_format_definition")
   | eval latest=strptime(...)
   | table earliest latest`
   | <rest of your search>

 That's one of the cases where subsearch is perfectly OK - it's small, quick and doesn't clog your servers.

[djcascione]

Id like the search to use the lookup table to adjust the search time interval window. During BST the logs displayed will be within the time range 19:00 & 7:00. Outside of BST, the search needs to adjust and search between the hours of 20:00 & 8:00.  I'll later use this to trigger an alert based on criteria in the logs ( example - user logging in after hours).

[ITWhisperer]

How are you specifying the time for your search?

[yuanliu]

The best solution is to make sure your application writes timezone in logs.  The second best is to make sure each universal forwarder is configured to set the correct time zone.  In either case, you wouldn't need to do this search time manipulation.

[ITWhisperer]

It is not clear what your usecase is here.

Time is stored in UTC which is unaffected by daylight saving clock changes such as BST.

On the day of the change, your selected time ranges include the hour shift e.g. Sat 20:00 GMT to Sun 07:00 BST i.e one time of the range is in GMT and the other is BST.

Please explain what you would like to do in this scenario