topic Re: getting a mean of all search time and sum of a single time in Splunk Search
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91724#M23666
<P>Good catch. I also don't think you need to specify by _time in a time chart. That is the purpose of a time chart yes?</P>Thu, 11 Jul 2013 15:18:34 GMTcpeteman2013-07-11T15:18:34Zgetting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91721#M23663
<P>Hey all, So the following seems to be a problem correctly piping stats stuff.</P>
<P>Right now mean and sum will always be the same:</P>
<PRE><CODE>search terms | bucket _time span=1m | stats count by punct,_time | stats mean(count),sum(count) AS sum by punct,_time |search sum>100
</CODE></PRE>
<P>What I want is to have a field that gives the mean count by punct over the entire search time, but when I use the code above it will give the mean by punct and _time which, since it only counts the same time and punct once, will always be the same as the sum. After this is fixed I intend to make the "|search" part check to see if sum is greater than the mean by a certain amount.</P>
<P>UPDATE: I am trying to use a subsearch to solve my problem:</P>
<PRE><CODE>search_terms | bucket _time span=1m | stats count by punct,_time | append [search index=auth| stats count by punct| stats sum(count) by punct] selfjoin
</CODE></PRE>
<P>However, as you might be able to tell this will only give the sum of the entire punct in the row with the last time stamp as opposed to all rows with that punct.</P>Wed, 10 Jul 2013 23:32:11 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91721#M23663cpeteman2013-07-10T23:32:11ZRe: getting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91722#M23664
<P>Perhaps try something like:</P>
<PRE><CODE>... | timechart span=1s count by punct,_time as scount | timechart span=1m mean(scount) AS smean,sum(scount) AS ssum by punct,_time
</CODE></PRE>
<P>See if that gives you the numbers you want. The above isn't tested, but it uses a method I use for creating means and stdevs.</P>
<P>I suspect the issue you are finding is that you are only counting by a single time block and doing a mean on that whole single number results in the mean being the same as the sum. Therefore, you have to do something like this by creating multiple numbers to work with first.</P>
<P>Another potential approach is to use timechart to shove things into 1 minute buckets but then test over a greater than single minute time period for a mean to compare against.</P>
<P>For other similar type of work, see my Splunklive presentation (and slides linked in comments) at: <A href="https://vimeo.com/66779015">https://vimeo.com/66779015</A></P>Thu, 11 Jul 2013 02:41:38 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91722#M23664jtrucks2013-07-11T02:41:38ZRe: getting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91723#M23665
<P>The "as scount" will give an error, it needs to be before the "by punct,_time".</P>Thu, 11 Jul 2013 14:52:48 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91723#M23665using2013-07-11T14:52:48ZRe: getting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91724#M23666
<P>Good catch. I also don't think you need to specify by _time in a time chart. That is the purpose of a time chart yes?</P>Thu, 11 Jul 2013 15:18:34 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91724#M23666cpeteman2013-07-11T15:18:34ZRe: getting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91725#M23667
<P>I think you're on the right track try this:</P>
<PRE><CODE>search_terms| bucket _time span=1m | stats count by punct,_time | join [search index=auth| stats count by punct| stats sum(count) by punct]
</CODE></PRE>Thu, 11 Jul 2013 15:21:57 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91725#M23667using2013-07-11T15:21:57ZRe: getting a mean of all search time and sum of a single time
https://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91726#M23668
<P>Perfect! Although I had gotten to that a little before you posted it's still right. <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P>Thu, 11 Jul 2013 15:23:53 GMThttps://community.splunk.com/t5/Splunk-Search/getting-a-mean-of-all-search-time-and-sum-of-a-single-time/m-p/91726#M23668cpeteman2013-07-11T15:23:53Z