https://community.splunk.com/t5/Splunk-Search/Understanding-Math-on-the-Search-Line/m-p/118794#M31784 <P>I'm having trouble understanding the math rules on the search line, so instead of continuing to guess what might work and having splunk tell me my formatting is wrong, or give me non sensical results, I'm just going to ask for help.</P> <P>I have a lot of data that is aggregated in to 1 minute bins. For each 1 minute bins I have</P> <UL> <LI>x_i: mean value of my variable as measured in the 1 minute bin</LI> <LI>n_i: number of times I measured it in the 1 minute bin</LI> <LI>sigma_i: standard deviation of the variable measured in the 1 minute bin</LI> </UL> <P>I'd then like to move to 1 hour bins so I want to calculate a weighted average of my one minute bins. Mathematically this is very straight forward</P> <UL> <LI>x_ weighted_ average = sum(weight_ i * x_ i) / sum(weight_i)</LI> <LI>where traditionally</LI> <LI>weight_ i = 1 / error_ i^2</LI> <LI>and for counting statistics </LI> <LI>error_ i = stand_ deviation_ i / sqrt(n_ i)</LI> </UL> <P>putting that together we get</P> <P>x_ weighted_ average = sum (x_ i * n_ i / sigma_ i^2) / sum(n_ i)</P> <P>What is the best way to do this kind of math in the search line while aggregating? Or should I just pass it to a python script and back out?</P> <P>Thanks,<BR /> Tristan</P> Mon, 28 Oct 2013 23:12:12 GMT tristanmatthews 2013-10-28T23:12:12Z https://community.splunk.com/t5/Splunk-Search/Understanding-Math-on-the-Search-Line/m-p/118794#M31784 <P>I'm having trouble understanding the math rules on the search line, so instead of continuing to guess what might work and having splunk tell me my formatting is wrong, or give me non sensical results, I'm just going to ask for help.</P> <P>I have a lot of data that is aggregated in to 1 minute bins. For each 1 minute bins I have</P> <UL> <LI>x_i: mean value of my variable as measured in the 1 minute bin</LI> <LI>n_i: number of times I measured it in the 1 minute bin</LI> <LI>sigma_i: standard deviation of the variable measured in the 1 minute bin</LI> </UL> <P>I'd then like to move to 1 hour bins so I want to calculate a weighted average of my one minute bins. Mathematically this is very straight forward</P> <UL> <LI>x_ weighted_ average = sum(weight_ i * x_ i) / sum(weight_i)</LI> <LI>where traditionally</LI> <LI>weight_ i = 1 / error_ i^2</LI> <LI>and for counting statistics </LI> <LI>error_ i = stand_ deviation_ i / sqrt(n_ i)</LI> </UL> <P>putting that together we get</P> <P>x_ weighted_ average = sum (x_ i * n_ i / sigma_ i^2) / sum(n_ i)</P> <P>What is the best way to do this kind of math in the search line while aggregating? Or should I just pass it to a python script and back out?</P> <P>Thanks,<BR /> Tristan</P> Mon, 28 Oct 2013 23:12:12 GMT https://community.splunk.com/t5/Splunk-Search/Understanding-Math-on-the-Search-Line/m-p/118794#M31784 tristanmatthews 2013-10-28T23:12:12Z https://community.splunk.com/t5/Splunk-Search/Understanding-Math-on-the-Search-Line/m-p/118795#M31785 <P>I'd still do this in the search language but it's up to you. You break it down into individual <CODE>eval</CODE> and <CODE>stats</CODE> and <CODE>bin</CODE> statements. For some complex stuff you need <CODE>streamstats</CODE> and/or <CODE>eventstats</CODE> as well, or other more advanced commands, but here it's just a lot of brute force <CODE>eval</CODE> and <CODE>stats</CODE>.</P> <PRE><CODE>foo | bin _time span="1min" | stats avg(var1) as x_i count as n_i stdev(var1) as sigma_i by _time | eval error_i=sigma_i/sqrt(n_i) | eval weight_i=1/(error_i*error_i) | eval weight_times_count = weight_i * x_i | stats sum(weight_times_count) as sum_wtc sum(weight_i) as sum_weight | eval x_weighted_average=sum_wtc / sum_weight </CODE></PRE> Tue, 29 Oct 2013 00:06:17 GMT https://community.splunk.com/t5/Splunk-Search/Understanding-Math-on-the-Search-Line/m-p/118795#M31785 sideview 2013-10-29T00:06:17Z