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

[cpeteman]

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.

[using · Accepted solution]

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]

[cpeteman]

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

[jtrucks]

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

[cpeteman]

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?

[using]

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