Detect deviations over historical query

[__________o7___]

I have a query that looks like:

index=proxy filter_category="Blocked"
| eval hrmarker=strftime(_time, "%H")
| eval date=strftime(_time, "%m/%d") 
| eval weekday=strftime(_time, "%a") 
| stats dc(ip) AS "Counter" by hrmarker,date,weekday 
| stats avg(Counter) AS "Avg" stdev(Counter) AS "Stdev" by hrmarker,weekday 

What I would like to do, is show all of the Day/Hours that were greater than 1 deviation from the norm, based on the Day of the week/Hour from the query.

Is there any way to do this without re-running the entire query?

If the query must be rerun, what is the best way to go about it?

[gauldridge]

You need to add the value of Counter into your second stats:

| stats values(Counter) AS Counter avg(Counter) AS "Avg" stdev(Counter) AS "Stdev" by hrmarker,weekday

Then you can add an eval and a where clause to see your desired results:

| eval OverNorm=if(Counter>(Avg+Stdev),"yes","no") | where OverNorm="yes"

Alternatively, you could just do the comparison in the where clause and not have a flag for where it's over or not.