Help with latest and earliest

[Diana_a]

Hi,

I would like to know if someone can help me with this issue. 

I am trying to add a time constraint to an SPL and I have this so far....

index=... host=... source=... sourcetype=... 
...
| eval Range = "-1@w"
| where TEMPDATE >= (relative_time(now(),Range)) 

That is exactly 1 week behind meaning...

If the SPL runs on current week's Monday, then the data will be from that same Monday.
If the SPL runs on Tuesday, then the data will be from Monday and Tuesday of the current week.
and so on...

I need to be able to change it to as follow:

If the SPL runs on Monday (current week), then the data returned must be from the previous week Monday through Saturday.
If the SPL runs the rest of the week (Tuesday - Sunday), then the data must still be from the previous week through Saturday.

If the end of the month ends in the middle of a week, I'd like to have a month cut off and only run up until that day.

For example:  The Monday the SPL runs, and the previous week ends half way through the week, say, on a Wednesday, only get the data from Monday to Wednesday.

If the SPL runs on Monday June 5th, in this case, then get only get data from the previous week May 29, 30 and 31. 
If the SPL runs on Tuesday - Saturday, same as above, only get the data from Monday 29 through Wednesday 31st still.

I have so far:

earliest = "-2@w" latest=@w1

Thank you for any guidance. I am not sure how the earliest and latest works.

Diana

[yeahnah]

Hi @Diana_a 

Here's a run anywhere example that will hopefully get you going...

index=dummy [ | makeresults
 | eval now=now()
       ,today=strftime(now, "%e")
       ``` ,today=1 ```
       ,dayofweek=strftime(now, "%a")
       ``` ,dayofweek="Mon" ```
       ,atnowweekstart=strftime(relative_time(now, "@w+1d"), "%a %F:%T")
       ,atprevweekstart=strftime(relative_time(now, "-1w@w+1d"), "%a %F:%T")
       ,atprevweekend = strftime(relative_time(now, "-1w@w+7d-1s"), "%a %F:%T")
       ,latest=strftime(now, "%a %F:%T")
 | eval earliest=if(today=1 OR dayofweek="Mon", atprevweekstart, atnowweekstart)
 | eval latest=if(today=1 OR dayofweek="Mon", atprevweekend, latest)
 | foreach earliest latest [ eval <<FIELD>>=strptime('<<FIELD>>', "%a %F:%T") ]
 | return earliest=$earliest latest=$latest ]
| append [ | makeresults ]
| addinfo
| foreach info_*_time [ eval <<FIELD>>=strftime('<<FIELD>>', "%c") ]
| table info*time

Basically it sets the earliest and latest SPL time modifiers in subsearch so only events in the expected time period are returned.  You may need to make adjustments if the logic is not quite what you want but hopefully you are able to make any adjustments yourself by playing around with the subsearch query in another window.  

Here's a simplified version with epoch seconds that could be applied to your query. 

index=... host=... source=... sourcetype=... [ | makeresults 
 | eval now=now()
       ,today=strftime(now, "%e")
       ,dayofweek=strftime(now, "%a")
       ,atnowweekstart=strftime(relative_time(now, "@w+1d"), "%s")
       ,atprevweekstart=strftime(relative_time(now, "-1w@w+1d"), "%s")
       ,atprevweekend = strftime(relative_time(now, "-1w@w+7d-1s"), "%s")
       ,earliest=if(today=1 OR dayofweek="Mon", atprevweekstart, atnowweekstart)
       ,latest=if(today=1 OR dayofweek="Mon", atprevweekend, now)
 | return earliest=$earliest latest=$latest ]
| ... rest of your search ....

Hope it helps

[Diana_a]

Thanks I will go ahead and try it