Splunk Search

Is there a way to calculate the percentile of a value within a range of values?

Kenshiro70
Path Finder

One of the most useful functions in Excel is percentilerank, which calculates the percentile of a value within a range of values. The closest I've been able to get is to do a p10, p20, p30, etc. and then search for everything below the value. I'm trying to show benchmarks and the function would be quite useful.

If you're not familiar with the percentilerank function, it works as follows:

  • You specify percentilerank(value, array of values)
  • Percentile rank returns the percentile for that value (e.g., 23rd percentile)

It's basically the converse of the perc commands.

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

This should be it:

... | stats count by value | sort + value
    | streamstats current=f sum(count) as rank | fillnull rank
    | eventstats sum(count) as total | eval percentile_rank = rank / total * 100

This will assign each unique value its absolute rank and then convert that to percentile rank using the total number of values.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

This should be it:

... | stats count by value | sort + value
    | streamstats current=f sum(count) as rank | fillnull rank
    | eventstats sum(count) as total | eval percentile_rank = rank / total * 100

This will assign each unique value its absolute rank and then convert that to percentile rank using the total number of values.

Kenshiro70
Path Finder

Nice - it even accounts for duplicate values!

0 Karma

sundareshr
Legend

Have you tried any of these? p<X>(Y) | perc<X>(Y) | exactperc<X>(Y) | upperperc<X>(Y)

http://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/CommonStatsFunctions

0 Karma
Get Updates on the Splunk Community!

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...