Splunk Search

How to generate a search to find the number of days that exceeds mean by certain ranges?

Explorer

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?

0 Karma
1 Solution

SplunkTrust
SplunkTrust
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* 

View solution in original post

Esteemed Legend

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...

SplunkTrust
SplunkTrust
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* 

View solution in original post

Explorer

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++

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!