topic Re: How do you add the average and the standard deviation as a new field? in Splunk Search

How do you add the average and the standard deviation as a new field?

mpasha — Tue, 29 Sep 2020 23:36:01 GMT

Hi,

This might be trivial question, but I am having a hard time to figure it out. Any help is greatly appreciated.

Here is the problem:

I have logs from remote VPN servers reporting the sent and received data in each session for each user.

I am trying to calculate the average of the data sent, and standard deviation over a month, then add the average and twice the calculated standard deviation together as the alerting threshold for the user.
However, I cannot add the value of the average and the stddev !!

Here is the SPL I have developed for this:

eventtype=RAS AND (EventCode=20272) AND ConnectionID!="NA" AND UserID="XYZ"
| dedup ConnectionID
| bucket _time span=1mon@mon
| stats sum(Data_Sent) as Monthly_Total_Sent stdev(Data_Sent) as Monthly_Sent_Stdev by _time UserID
| eval Monthly_Avg_Sent(MB)=round(Monthly_Avg_Sent/(1024*1024),2), Monthly_Sent_Stdev(MB)=round(Monthly_Sent_Stdev/(1024*1024),1),Abnormal_Sent_Limit(MB)=2*Monthly_Sent_Stdev(MB)+Monthly_Avg_Sent(MB)

However, Splunk errors out on the Abnormal_Sent_Limit(MB) calculation!!

The error i see is:

Error in 'eval' command: The
'monthly_sent_stdev' function is
unsupported or undefined.

I also have tried Values() but with the same results. I mean :

Abnormal_Sent_Limit(MB)=2*values(Monthly_Sent_Stdev(MB))+values(Monthly_Avg_Sent(MB))

I am pretty sure i am doing something wrong, but I don't know what that is!!

Re: How do you add the average and the standard deviation as a new field?

chrisyounger — Thu, 07 Mar 2019 23:35:39 GMT

You need to do it like this:

eventtype=RAS AND (EventCode=20272) AND ConnectionID!="NA" AND UserID="XYZ" 
| dedup ConnectionID 
| bucket _time span=1mon@mon 
| stats sum(Data_Sent) as Monthly_Total_Sent stdev(Data_Sent) as Monthly_Sent_Stdev by _time UserID 
| eval 'Monthly_Avg_Sent(MB)'=round(Monthly_Avg_Sent/(1024*1024),2), 'Monthly_Sent_Stdev(MB)'=round(Monthly_Sent_Stdev/(1024*1024),1),'Abnormal_Sent_Limit(MB)'= 2 * 'Monthly_Sent_Stdev(MB)' + 'Monthly_Avg_Sent(MB)'

As a general rule, its a good idea to keep your field names very simple. and then just rename to proper names at the very end. That way you don't need to worry about single quoting fields.

Re: How do you add the average and the standard deviation as a new field?

mpasha — Fri, 08 Mar 2019 19:10:49 GMT

i changed the variable names but still nothing shows up!! here is what i have done again:

eventtype=RAS AND (EventCode=20272) AND ConnectionID!="NA" AND UserID="XYZ"
| dedup ConnectionID
| bucket _time span=1mon@mon
| stats sum(Data_Sent) as MTSent stdev(Data_Sent) as MSStdev avg(Data_Sent) as MAvgSent by _time UserID
| eval MTSentMB=round(MTSent/(1024*1024),2), MAvgSentMB=round(MAvgSent/(1024*1024),2),  MSentStdevMB=round(MSStdev/(1024*1024),1)
| eval  AbSentLimMB=2*(MSStdevMB)
| sort 0 -_time
| table _time UserID MTSentMB MAvgSentMB MMaxSentMB MSentStdevMB  AbSentLimMB

all but the AbSentLimMB return values!!