query to consider having an event if its available in any specific weekday in the given time range

[Dhana]

Hello,

So the requirement was to find gaps of data unavailability(start time & end time)  in the  given time range, condition is that if specific weekday have event in  a certain period (say first week of Sunday) and in the same period if other week of same weekday(say Second week of Sunday)  does not have an event then my search still have to consider of having an event during Second Sunday too for calculating duration of data unavailability.

[Dhana]

Sunday Feb 1 - events at 10:00,10:04,11:00
Monday Feb 2nd - events at 9:00,9:01,9:04
Tuesday Feb 3rd - events 0
'
'
'
'
'
Sunday Feb 8- events at 9:00, 12:00
Monday Feb 9 - 0 events
Tuesday Feb 10 - events at 7:00

so in the given search span (time range can be anything, maybe 30 days, 90days etc), my search should least bother about the date. main concern is weekday and Time

data unavailability reporting  results expected

Sunday at 0 through sunday at 8:59 [539 minutes]
Sunday at 9:01 through sunday at 9:59 [duration b/w 9:01 to 9:59 minutes]
Sunday at 10:01 through sunday at 10:03
Sunday at 10:05 through sunday at 10:59
Sunday at 11:01 through sunday at 11:59
Sunday at 12:01 through Monday at 08:59
Monday at 09:02 through Monday at 09:03
Monday at 09:05 through Tuesday at 06:59

[ITWhisperer]

index=_internal sourcetype=splunkd_ui_access 
``` using internal as runanywhere example ```
``` start with timechart to generate events for each minute (span=1m) within time period of search ```
| timechart count span=1m by date_wday 
``` untable to get table of events ```
| untable _time weekday count
``` evaluate time for day of the week (used to maintain order) weekday (for display purposes) hour and minute ```
| eval weekday = strftime(_time,"%A")
| eval daynumber = strftime(_time,"%w")
| eval hour = strftime(_time,"%H")
| eval minute = strftime(_time,"%M")
``` sum the counts to find periods of zero activity ```
| stats sum(count) as count by daynumber weekday hour minute
| where count = 0
``` calculate minute into each day ```
| eval minuteofday=minute+(hour*60)
``` find consecutive minutes of inactivity ```
| streamstats  window=2 global=f range(minuteofday) as consecutive by daynumber weekday
``` anything other than 1 is the start of day or start of new period on inactivity ```
| eval start=if(consecutive == 1, 0, 1)
``` number groups of inactivity through day ```
| streamstats  global=f sum(start) as group by weekday daynumber
``` find start time and end time of inactivity ```
| stats min(minuteofday) as periodstart max(minuteofday) as periodend by daynumber weekday group
``` convert start and end times to hours and minutes ```
| eval periodstart=printf("%02d:%02d",floor(periodstart/60),periodstart%60)
| eval periodend=printf("%02d:%02d",floor(periodend/60),periodend%60)
``` Robert is your father's brother! ```

[Dhana]

Thank you so much @ITWhisperer , It helped.

[PickleRick]

OK, it's relatively easy - you group the events by weekday and time within the day and find event periods. But what's the use case? Because it seems a bit pointless to aggregate events over several different weeks.

[Dhana]

I got till the point of getting down to find the gap, but not quite getting to boil down to grouping weekdays and merging same weekday events to one to replace the time range which don't have events with one which have.

index=*
| timechart count(_raw) as events span=1m
| eval Availability=if(events=1,"1","0")
| streamstats count(eval(Availability=1)) as unique_count
| delta _time as Duration
| search Availability=0
| stats earliest(_time) as From sum(Duration) as Duration by Availability unique_count
| eval Duration1=Duration-60
| eval To=strftime(From+Duration1,"%c"), From=strftime(From,"%c")
| where Duration>300
| fields Availability From To Duration 

[PickleRick]

I'd approach that from a completely different end.

Firstly render your _time into weekday and time, then count.

Then find gaps.

At the end you might need to adjust it for the week wraparound.

[Dhana]

yeah, I have tried the path. I could not able to get to the part of considering having the events for weekday if they have in other.

index=**
| timechart count(_raw) as events span=1m
| eval Time=strftime(_time,"%A %T")
| stats count by Time events

[PickleRick]

You can do a simple trick - do a (modulo 86400*7) on the _time field. This way you'll "compress" all your events to the period of 1-7 Jan 1970. Then you'll be able to sort it anyway you want 🙂 (you might also offset it with any multiple of 86400 if you don't like the starting weekday).

[ITWhisperer]

Can you give an example of the events you are expecting Splunk to show?