Splunk Search

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

djcascione
Explorer

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!

Labels (1)
0 Karma
1 Solution

djcascione
Explorer

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)

View solution in original post

0 Karma

djcascione
Explorer

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)

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

djcascione
Explorer

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).

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

How are you specifying the time for your search?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...