Reporting

How to create a 30 day search for specific time range (21:00 - 06:00) where the time carries over into the next day?

Explorer

This is what I have so far for my search:

index=logs sourcetype=Jobs earliest=-31d latest=-1d | where strftime(_time,"%H")>"20" AND strftime(_time,"%H")<"6" 

For example I am only getting results starting at:
2015-03-28 21:00:10
.
.
2015-03-28 23:59:58
2015-03-29 21:00:20 <--I am expecting the time to start from 00:00 and get events thru 06:00
2015-03-29 21:01:12

My goal to get events for the past 30 days between the times of 9pm and 6am the next day.
Thanks

1 Solution

Motivator

Three things:
1. No need for a where clause. Add your conditionals to the main search phrase.
2. You are testing strings, when you should be testing numbers.
3. If you're testing the timestamp, Splunk has the ready-made date_hour field for you - no need to strftime it out.

Try this:

index=logs sourcetype=Jobs earliest=-31d latest=-1d (date_hour > 20 OR date_hour < 6)

View solution in original post

Motivator

Three things:
1. No need for a where clause. Add your conditionals to the main search phrase.
2. You are testing strings, when you should be testing numbers.
3. If you're testing the timestamp, Splunk has the ready-made date_hour field for you - no need to strftime it out.

Try this:

index=logs sourcetype=Jobs earliest=-31d latest=-1d (date_hour > 20 OR date_hour < 6)

View solution in original post

Contributor

I have ran into the same problem, the only difference, is that, I need to consider date_hour of event not the splunk time, how can I achieve that?

| eval Today = strftime(now(), "%Y-%m-%d")  
| eval HOUR_INI_WINDOW = strptime("06:00:00","%H:%M:%S")
| eval HOUR_END_WINDOW = strptime("20:00:00","%H:%M:%S")
| WHERE DataCampanha = Today
| eval HOUR_INI = strptime(HOUR_INI_WINDOW ,"%H:%M:%S")
| eval HOUR_END = strptime(HOUR_END_WINDOW ,"%H:%M:%S")
| WHERE HOUR_INI > HOUR_INI_WINDOW OR HOUR_END > HOUR_END_WINDOW 

I need to filter events NOT inside this window ( 06:00:00 - 20:00:00 ), considering these datetimes are a field of each event, not using _time of splunk...

0 Karma

SplunkTrust
SplunkTrust

And, most importantly (implicitly covered by the answer), you should use OR instead of AND. An hour can't both be greater than 20 and less than 6.

Explorer

much thanks . I appreciate the time you took to help

0 Karma