Splunk Search

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

jrnastase
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

DalJeanis
Legend
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

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

DalJeanis
Legend
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* 

jrnastase
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
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...