Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Re: getting a mean of all search time and sum of a...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

cpeteman

Contributor

07-10-2013
04:32 PM

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.

1 Solution

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

using

Explorer

07-11-2013
08:21 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

using

Explorer

07-11-2013
08:21 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

cpeteman

Contributor

07-11-2013
08:23 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

jtrucks

Splunk Employee

07-10-2013
07:41 PM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

cpeteman

Contributor

07-11-2013
08:18 AM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

using

Explorer

07-11-2013
07:52 AM

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

Register for .conf21 Now!
Go Vegas or Go Virtual!

Get Updates on the Splunk Community!