Retrieve stats for this current and previous week

[srikanth88infy]

Hi,

I have the following simple query 

searchQuery | stats count, p50(duration), p99(duration) by uri_path

and we query against the last 7 days to get the p99 of the response times for each uri_path. 

Im trying to include the another column called `p99(lastWeekDuration)`.  

[rnowitzki]

Hi @srikanth88infy,

Another option, when you to look at the last week as a "full week",  and not -7days

| <search to get this weeks durations>
|appendcols [search earliest=-1w@w latest=@w  <search to get last weeks durations> ]

"-1w@w" means: Beginning of last week (by default sunday).  "@w" means beginning of this week.
If you want to start monday, you could put earliest to "-1w@w+1d" and latest "@w+1d"
 
But it uses a subsearch, so from a performance perspective @gcusello's approach is better.

[gcusello]

Hi @srikanth88infy,

if it's acceptable to have results in two rows, please try something like this:

searchQuery 
| eval week=if(_time<now()-604800,"this week","last week")
| stats count p50(duration), p99(duration) by uri_path week

otherwise, try something like this:

searchQuery 
| eval duration0=if(_time<now()-604800,duration,"0"), duration1=if(_time>=now()-604800,"0",duration)
| stats count p50(duration0) AS "Duration this week", p99(duration1)  AS "Duration last week" by uri_path

 Ciao.

Giuseppe

[srikanth88infy]

@gcusello Thanks for the hint. But in the solution suggested, we are creating 2 new fields duration0 and duration1 for all the events, which are initialized to value 0 based on the _time field value.
Doesn't it affect the P99 calculation?  Can we ignore the 0 values when calculating p99? 

[gcusello]

Hi @srikanth88infy,

You continue to use duration, but there's the need to divide thembetween weeks before stats command because it isn't possible in one stats command to calculate P90 at at the same time use use eval.

With my solution you calculate duration for the events of the first week and of the second one, so you can calculate p90 in stats for both the weeks.

Ciao.

Giuseppe