Splunk Search
Highlighted

## getting a mean of all search time and sum of a single time

Contributor

Hey all, So the following seems to be a problem correctly piping stats stuff.

Right now mean and sum will always be the same:

``````search terms | bucket _time span=1m | stats count by punct,_time | stats mean(count),sum(count) AS sum by punct,_time |search sum>100
``````

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.

UPDATE: I am trying to use a subsearch to solve my problem:

``````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
``````

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.

Tags (5)
1 Solution
Highlighted

## Re: getting a mean of all search time and sum of a single time

Splunk Employee

Perhaps try something like:

``````... | timechart span=1s count by punct,_time as scount | timechart span=1m mean(scount) AS smean,sum(scount) AS ssum by punct,_time
``````

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.

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.

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.

For other similar type of work, see my Splunklive presentation (and slides linked in comments) at: https://vimeo.com/66779015

Highlighted

## Re: getting a mean of all search time and sum of a single time

Explorer

The "as scount" will give an error, it needs to be before the "by punct,_time".

Highlighted

## Re: getting a mean of all search time and sum of a single time

Contributor

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?

Highlighted

## Re: getting a mean of all search time and sum of a single time

Explorer

I think you're on the right track try this:

``````search_terms| bucket _time span=1m | stats count by punct,_time | join [search index=auth| stats count by punct| stats sum(count) by punct]
``````
Highlighted

## Re: getting a mean of all search time and sum of a single time

Contributor

Perfect! Although I had gotten to that a little before you posted it's still right. 🙂