Is it possible to get the count of events matching a p99 value?

[jrs42]

I've been asked to show how many events are over the p99 (for example) value for a search.  I'm hopefully just missing something obvious, but I've not figured out how, or if, this is possible.

Thx

[ITWhisperer]

You could try something like this

index=foo message="magic string" duration > [search index=foo message="magic string" | stats p99(duration) as search]
| stats count as "# of Events with Duration > p99"

[marysan]

@jrs42 
you can use 'stats' instead of 'eventstats' to optimize :
index=foo message="magic string"
| stats p99(duration) as p99val, count(eval(duration > p99(duration))) as count

[jrs42]

I have something that I think works, but I don't know how (in)efficient it is:

index=foo message="magic string"
| eventstats p99(duration) as p99val
| where duration > p99val
| stats count as "# of Events with Duration > p99"

It seems to take a long time to complete as soon as I add in the "| stats count" bit.  Simply getting events seems pretty quick.

Is this a good approach and/or how can I improve it?

[bowesmana]

Actually, while your technique is correct, as you are ONLY interested in count of duration>p99, you should use the fields statement to ONLY send the data you care about to the search head, i.e.

index=foo message="magic string"
| fields - _raw
| fields duration
| eventstats p99(duration) as p99val
| where duration > p99val
| stats count as "# of Events with Duration > p99"

those two fields statements will mean that the only piece of data being sent to the SH is 'duration'

[jrs42 · Accepted solution]

I just realized I was way, way overthinking this.  BY DEFINITION, a p99 value is one that 99% of the events are at or below, thus leaving ONE PERCENT of the total number of events higher.   All I need is:

index=foo message="magic string"
| stats count as total
| eval over_p99=round(total / 100, 0)
| fields - total

which is fast.  If I wanted # over p95, just use round(total / 100 * 5, 0) etc. 

I will be keeping your approach in mind, however, as that's still really good to know.  Ty!

[bowesmana]

The performance issue is using eventstats, which means all the data is pushed to the search head to do the calculations. It's not the ultimate stats that is actually slow. Unfortunately I believe this is the general technique for comparing event values against any event aggregation.

One slightly left of field alternative is to pre-calculate the p99 and write that to a lookup, e.g.

index=foo message="magic string"
| stats p99(duration) as p99 
| outputlookup p99_tmp.csv

and then the second search just does this, which, if you are using the same time ranges and have a large amount of data, may be quicker.

index=foo message="magic string"
| eval p99=[ | inputlookup tmp.csv | return $p99]
| where duration>p99
| stats count as "# of Events with Duration >= p99"

[marysan]

Hi 
I think it works for you:
index=foo message="magic string"
| eventstats p99(duration) as p99val
| stats count(eval(duration > p99val)) as count

[jrs42]

While that solution does work it's equally slow as the one I posted.  Thank you, but I'm still hoping there's a better/faster way 🙂