Splunk Search

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

cpeteman
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.

0 Karma
1 Solution

using
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]

View solution in original post

using
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]

cpeteman
Contributor

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

0 Karma

jtrucks
Splunk Employee
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

--
Jesse Trucks
Minister of Magic
0 Karma

cpeteman
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?

0 Karma

using
Explorer

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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...