Hello all!
I'm trying to find the number of days that the daily count of my event exceeds the daily mean + standard deviation for a 3-week period. I also need to return the number of days that exceeds the mean + 2 stdevs and mean + 3 stdevs, and keep it all together.
Is there an easy way to do this?
index="index" field="field" sourcetype="sourcetype"
| bucket _time span=1d
| stats count as EventCount by _time
| rename COMMENT as "This section adds records for the days which had zero counts. Remove if unwanted."
| appendpipe [| stats min(_time) as mintime max(_time) as maxtime | eval _time=mvrange(mintime,maxtime,86400) | eval EventCount=0]
| stats max(EventCount) as EventCount by _time
| rename COMMENT as "This section calculates which days were beyond n stdevs, and sets a flag to count them up."
| eventstats avg(EventCount) as avgEventCount stdev(EventCount) as stdevEventCount
| eval logs1=if(EventCount> avgEventCount+1*stdevEventCount,1,0)
| eval logs2=if(EventCount> avgEventCount+2*stdevEventCount,1,0)
| eval logs3=if(EventCount> avgEventCount+3*stdevEventCount,1,0)
| rename COMMENT as "This section adds records to show the specific dates beyond n stdevs, for test purposes. Remove when working if unwanted."
| eval day = strftime(_time,"%Y-%m-%d")
| eval days1=if(EventCount> avgEventCount+1*stdevEventCount,day,0)
| eval days2=if(EventCount> avgEventCount+2*stdevEventCount,day,0)
| eval days3=if(EventCount> avgEventCount+3*stdevEventCount,day,0)
| rename COMMENT as "This section calculates and reports your answers."
| stats sum(log*) as log*, values(day*) as day*
Check out this Q&A for a very in-depth conversation on this topic (don't forget to up-vote):
https://answers.splunk.com/answers/511894/how-to-use-the-timewrap-command-and-set-an-alert-f.html#an...
index="index" field="field" sourcetype="sourcetype"
| bucket _time span=1d
| stats count as EventCount by _time
| rename COMMENT as "This section adds records for the days which had zero counts. Remove if unwanted."
| appendpipe [| stats min(_time) as mintime max(_time) as maxtime | eval _time=mvrange(mintime,maxtime,86400) | eval EventCount=0]
| stats max(EventCount) as EventCount by _time
| rename COMMENT as "This section calculates which days were beyond n stdevs, and sets a flag to count them up."
| eventstats avg(EventCount) as avgEventCount stdev(EventCount) as stdevEventCount
| eval logs1=if(EventCount> avgEventCount+1*stdevEventCount,1,0)
| eval logs2=if(EventCount> avgEventCount+2*stdevEventCount,1,0)
| eval logs3=if(EventCount> avgEventCount+3*stdevEventCount,1,0)
| rename COMMENT as "This section adds records to show the specific dates beyond n stdevs, for test purposes. Remove when working if unwanted."
| eval day = strftime(_time,"%Y-%m-%d")
| eval days1=if(EventCount> avgEventCount+1*stdevEventCount,day,0)
| eval days2=if(EventCount> avgEventCount+2*stdevEventCount,day,0)
| eval days3=if(EventCount> avgEventCount+3*stdevEventCount,day,0)
| rename COMMENT as "This section calculates and reports your answers."
| stats sum(log*) as log*, values(day*) as day*
If it helps, this is what I tried before, but it only works if values exist for logs++
index="index" field = "field" sourcetype="sourcetype"
| bucket _time span=1d
| stats count by _time
| eventstats avg(count) as average stdev(count) as standard_deviation
| where count>average+standard_deviation
| eventstats count as logs
| where count>average+(2*standard_deviation)
| eventstats count as logs+
| where count>average+(3*standard_deviation)
| eventstats count as logs++