topic Re: How do you calculate the mean of a timewrap series? in Reporting

How do you calculate the mean of a timewrap series?

mmdacutanan — Mon, 19 Nov 2018 20:04:39 GMT

I am trying to write a query that will count the number of errors for the last 5 minutes and then I want to compare if that error total is greater than the average for the previous 15 minutes. Then I want to trigger an alert if series s0 is greater than the mean (for alerting purposes).

I found part of the solution from Splunk Answers (https://answers.splunk.com/answers/151921/how-to-set-up-alert-when-error-count-of-latest-week-is-greater-than-average-of-all-weeks-in-past-30-days.html), but my eval expression to calculate the mean field does not seem to work; the field is just empty. What am I not doing right here?

Query is below:

index=cisco_uc sourcetype=RCD| where like (Variable10,"Tx|%")
| bucket _time span=5m
| stats latest(Variable10) as Variable10 by _time Variable2
|timechart span=5m count(eval(like(Variable10,"%|U%"))) as U_Count
|timewrap 5min series=short
|eval mean=(U_Count_s1 + U_Count_s2 + U_Count_s3)/3
|where U_Count_s0 > mean

Re: How do you calculate the mean of a timewrap series?

HiroshiSatoh — Tue, 20 Nov 2018 02:26:53 GMT

Try this!

|timechart span=5m count(eval(like(Variable10,"%|U%"))) as U_Count
↓
|timechart span=5m count(eval(if(like(Variable10,"%|U%"),1,0))) as U_Count

Re: How do you calculate the mean of a timewrap series?

skoelpin — Tue, 20 Nov 2018 03:03:32 GMT

Try like this. I tested against an ITSI summary index, so replace line 1 with your query and replace those _raw values with your field names. Also, you may not want to count the latest 5 minute bucket since it's still accepting new data and the counts may be artificially low. In the example below, I'm comparing the latest full 5 minute bucket count with the average of 3 - 15 minute buckets preceding it. It runs an hour timerange in under 1 second!

index=itsi_summary 
| bin _time span=5m
| stats count(_raw) AS _raw by _time
| streamstats sum(_raw) AS fifteen_min_window time_window=15m 
| streamstats sum(_raw) AS five_min_window time_window=5m
| streamstats count AS primary_key
| eval five_min_value=if(primary_key=2,'five_min_window',"")
| eval fifteen_min_value=if(primary_key=4,'fifteen_min_window',"")
| eval average=fifteen_min_value/3
| stats max(five_min_value) AS five_min_count max(average) AS avg_count_fifteen_min
| eval compare=if('average'>'five_min_value',"Avg is greater than latest bucket","Latest bucket is greater Than Average")

Re: How do you calculate the mean of a timewrap series?

skoelpin — Mon, 26 Nov 2018 14:36:45 GMT

@mmdacutanan did this solve your problem? If so, can you accept an answer to close out the question?

Re: How do you calculate the mean of a timewrap series?

mmdacutanan — Sat, 01 Dec 2018 01:49:12 GMT

Hi skoelpin! Thank you so much! I think this gets me close enough to what I need. 🙂 Btw, Are you just doing the last 20 minutes?

Re: How do you calculate the mean of a timewrap series?

mmdacutanan — Tue, 29 Sep 2020 22:14:13 GMT

Hello again skoelpin! Thank you so much again for the query you provided. That gave me idea on how to come up with my own query. I don't use streamstats often but I will now! 🙂 Here is my final query. I added some stdev calculations. I don't want to get too many false alerts so I thought of calculating upper and lower limits of the standard deviation:

index=uc sourcetype=rcd| where like (Variable10,"Tx|%|NS|%")
| bucket _time span=5m
| stats latest(Variable10) as Variable10 by _time Variable2
| timechart span=5m count(Variable10) as NS_Count
| streamstats count as pri_key
| streamstats avg(NS_Count) as avg, stdev(NS_Count) as stdev window=3
| eval avg=round(avg,2)
| eval stdev=round(stdev,2)
| eval lowerBound=(avg-stdev*2)
| eval upperBound=(avg+stdev*2)
| eval 5m_value=if(pri_key=4,'NS_Count',"")
| eval 15m_prev_upperBound=if(pri_key=3,'upperBound',"")
| eval 15m_prev_lowerBound=if(pri_key=3,'lowerBound',"")
| eval 15m_prev_avg=if(pri_key=3,'avg',"")
| eval 15m_prev_stdev=if(pri_key=3,'stdev',"")
| stats values(5m_value) as 5m_value values(15m_prev_upperBound) as 15m_prev_upperBound values(15m_prev_lowerBound) as 15m_prev_lowerBound values(15m_prev_avg) as 15m_prev_avg values(15m_prev_stdev) as 15m_prev_stdev
| eval boolean=if('5m_value'>'15m_prev_upperBound',"1","0")

Re: How do you calculate the mean of a timewrap series?

mmdacutanan — Sat, 01 Dec 2018 03:30:33 GMT

Thanks HiroshiSatoh! skoelpin's query actually brought me closer to the solution. I posted my final query above.