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
- :
- Conditional Sum to find 99% average of total data

- 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

Conditional Sum to find 99% average of total data

sepkarimpour

Path Finder

08-25-2017
05:31 AM

Is there a way to using conditions to find all the values (SUM and COUNT) above a certain value to be used as part of another calculation?

My logic is to find all the values greater than or equal to the 99th percentile value from a query (e.g. if I have a 1000 queries, I'd want the sum of the last 10 queries), take that value away from the sum of the total query count and divide that by the total count minus the count of queries that are greater than equal to the 99th percentile.

I can replicate my idea on Excel with the idea I had in mind:

=(SUM(C2:C101)-SUMIF(C2:C101,PERCENTILE.INC(C2:C101,0.99)))/(COUNT(C2:C101)-COUNTIF(C2:C101,PERCENTILE.INC(C2:C101,0.99)))

But I'm not sure how to replicate this on Splunk - any ideas?

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

DalJeanis

SplunkTrust

08-25-2017
11:16 AM

You are trying too hard to do all the work yourself.

On the other one, I posted one version of code to calculate the avg of the 99th percentile. Here's a more direct route - use an `eval`

in the stats to `null`

out the ones you dont want to aggregate.

```
your query that gets the data by queryName
| fields ResponseTime queryName
| eventstats perc99(ResponseTime) as p99Resp by queryName
| stats count(eval(if(ResponseTime<-p99Resp,Responsetime,null()))) as P98Count,
avg(eval(if(ResponseTime<-p99Resp,Responsetime,null()))) as p98ResponseAvg,
stdev(eval(if(ResponseTime<-p99Resp,Responsetime,null()))) as p98ResponseStdev,
min(eval(if(ResponseTime<-p99Resp,Responsetime,null()))) as p98ResponseMin,
max(eval(if(ResponseTime<-p99Resp,Responsetime,null()))) as p98ResponseMax
by queryName
```

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

sepkarimpour

Path Finder

08-29-2017
02:21 AM

*less than or equal to*

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

Richfez

SplunkTrust

08-25-2017
06:28 AM

I presume this is related to your previous question. 🙂 (Which is not a problem, they do appear somewhat distinct - if the answer to one is the same or similar to the answer to the other we can figure out how to handle that later).

An example may help - please correct where this is wrong. Example will use 10 values and a 90%, and I'm going to make up most of the math because I'm lazy.

Ten events with some values: 1, 5, 6, 7, 8, 9, 10,11,12,18

Your 90% says that the values 1 and 18 are not inside the 90% <- made up.

What exactly do you want to do with this example, then?

Sum 1+18 = 19,

then take that 19 away from 5+6+7+...+12=68 == 49 ?

OR then take that 19 away from the WHOLE sum of 87-19 = 68 ? <-- this, I think?

Divide that (68) by the total count (10?) == 6.8,

oh no, 68/8 because you are missing two that got removed? == 8.5 <-- this I think?

That's 8.5. Since my data was more or less symmetrical about the average that's not much different from the average of the whole set (8.7) but certainly different data will give different results.

So is that what you are trying to do?

If not, please correct the example to do what you want. I think this will help a LOT of people help you find an answer.

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

sepkarimpour

Path Finder

08-25-2017
06:43 AM

Yes, they're related to each other - I had an idea pop into my head for the first one, but I thought I'd write the question differently.

The example you provided is almost what I'm looking for - I'll explain what I mean:

I'm looking to use all but the last value so, in this case, the 10th value would be removed but the search include the first value as well. My idea is that either, I could get the sum of the counts that are within the 99% range (or here, it would be all the values <= 12) and then dividing by 9 (since 10 - 1 = 9)

The results would be: Sum=69 and new Average=7.7 (rounded to 1 d.p)

Yes, ideally, once I get all these averages, I can do further analysis on the ones that exceed a certain value.

State of Splunk Careers

Find out what your skills are worth!

Read the report >