How to create a search results that only takes the monthly data for every other Wednesday?

[ichesla1111]

Hello!

I am making a search that searches data from Sunday-Tuesday one week and Sunday-Wednesday the next week. 

I have a field called "date_wday" which contains data values representing the day of the week (sunday, monday, tuesday, .....,saturday).

My search: index=blah date_wday=monday OR tuesday OR sunday

My search successfully filters the data to pull from Sunday-Tuesday but how can I also have it add data from every other Wednesday?

ex. Week 1: Sunday-Tuesday
       Week 2: Sunday-Wednesday
       Week 3: Sunday-Tuesday
       ......


Thank you

[ichesla1111]

This is awesome, thank you!!! It worked.

[ITWhisperer]

index=blah [| makeresults 
    | eval week=mvrange(0,4)
    | mvexpand week
    | eval earliest=relative_time(relative_time(now(),"-1mon@mon+6d"),"+".week."w@w")
    | eval latest=relative_time(earliest,"+".(3+(week%2))."d")
    | fields earliest latest]

[bowesmana · Accepted solution]

You'll probably have to pull in data for Sun-Wed and then discard the data you don't want. You will have to do a calculation about which week it is, so you won't be able to add simple search criteria.

However, if you search back 2 weeks, it is presumably week 1 Sun-Tue and week 2 is Sun-Wed, but it you search back 3 weeks, does it mean the oldest week is Sun-Tue, 2nd oldest Sun-Wed and most recent Sun-Tue?

If so, you can calculate the age in weeks of each event and then add a where clause to filter out the Wednesday on even weeks, e.g. this example works on the _internal index and will treat the current week as including a wednesday, the previous not. If you search an even number of weeks, it will always give tuesdays on the oldest week and then alternate

index=_internal date_wday="sunday" OR date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" earliest=-2w@w latest=now
| eval age_in_weeks=floor((now() - _time) / (86400 * 7))
| where age_in_weeks % 2 == 0 OR date_wday!="wednesday"
| bin _time span=1w
| fields _time age_in_weeks date_wday
| stats min(age_in_weeks) as min_age max(age_in_weeks) as max_age values(date_wday) as date_wday count by _time

if you want to make if based on the search window and always exclude wednesday for the oldest week in the search range, you'll need to know the search range, so use 

| addinfo
| eval search_weeks=floor(now() - info_min_time / (86400 * 7)
...

to determine the number of search weeks and then tweak the where clause above as needed