https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144309#M40155 <P>Can you point out the mistake in my query and the thought process that went when writing yours.</P> Mon, 29 Sep 2014 18:34:56 GMT vspreethi17 2014-09-29T18:34:56Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144307#M40153 <P>I am trying to calculate the average number of errors by calculating events(with error)/total events. <BR /> Here is my query </P> <PRE><CODE>...| stats count(_raw) as Total| appendcols[search .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count as errors by FIELDNAME ]|eval average = errors/Total|sort -errors </CODE></PRE> <P>Result:</P> <PRE><CODE>FIELDNAME | errors | Total| average ================================ abc 10 def 2 ghi 2 30 0.0666 jkl 1 mno 1 </CODE></PRE> <P>Expected Result</P> <PRE><CODE>FIELDNAME errors Total average ================================ abc 10 30 3.3 def 2 30 0.66 ghi 2 30 0.0666 jkl 1 30 0.33 mno 1 30 0.33 </CODE></PRE> <P>my question is why total is not calculated for all the events? what logic I am missing here. </P> <P>Thank you so much.</P> Mon, 29 Sep 2014 16:09:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144307#M40153 vspreethi17 2014-09-29T16:09:35Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144308#M40154 <P>Try this:</P> <PRE><CODE>&lt;your search&gt; .... error|rex "(?i)^[^\\.]*\\.\\w+:\\s+(?P.+)"|stats count AS errors by FIELDNAME | join [ ...| stats count(_raw) as Total ] | eval average = errors/Total|sort -errors </CODE></PRE> <P>Note: joins are expensive. </P> Mon, 29 Sep 2014 18:22:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144308#M40154 sk314 2014-09-29T18:22:37Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144309#M40155 <P>Can you point out the mistake in my query and the thought process that went when writing yours.</P> Mon, 29 Sep 2014 18:34:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144309#M40155 vspreethi17 2014-09-29T18:34:56Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144310#M40156 <P>I just went by your requirement. </P> <P>Just read up the documentation about appendcols and join. Specifically, appendcols synopsis states that it "Appends the fields of the subsearch results to current results, first results to first result, second to second, etc." In your case, the subsearch returns just one event (the total stats count), and that is why it was getting appended to only one event from your main search. In case of join, all events are combined based on common field (if none specified)... </P> <P>Hope this helps.</P> Mon, 29 Sep 2014 18:50:58 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144310#M40156 sk314 2014-09-29T18:50:58Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144311#M40157 <P>Thank you.</P> Mon, 29 Sep 2014 18:57:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-average-number-of-events-with-errors-by/m-p/144311#M40157 vspreethi17 2014-09-29T18:57:45Z