Splunk Search

## Is there a way to calculate percentile of a row values in table?

Communicator

I have displayed percentile of certain metric values by grouping with month & host in a table representation. Whatever host category, we select from the dropdown, the value of the metrics will be displayed for each of its hosts. No. of servers in each host category will differs.

As like addtotals I want to take percentile of my entire row values. Below are sample data from my table panel.

``````Month  host1  host2  host3
Jan     90     40     78
Feb     36     27     56
:
Dec     12     49     22
``````

The command `addtotals` will sum all the values of a row and provide one more column with the cumulative value. But in my case instead of sum I want to do percentile of all those values. Below is the way I wanted

``````Month  host1  host2  host3  P95(host)
Jan     90     40     78
Feb     36     27     56
:
Dec     12     49     22
``````

Could anyone help me on this asap?

Tags (5)
Communicator

For now, I have tried using the percentile function for the specific values in one query and appended to my first query. With that I get percentile of each row values.

``````<Base query>
|search Host_Type=*
| chart useother=f limit=100 values(metric_perc) as "Metric(%)" over Month by Host
|appendcols [
|search Host_Type=*
| stats P95(metric_perc) as "Cumulative P95 of Hosts" by Month
]
``````

With this query I got the expected answer. As like `addtotals OR addcoltotals` if we have for Percentile or Max or any other aggregate functions, it will be useful. Because at the end of the results its just need to add one single command, and no need to repeat the query and use append command.

Super Champion

Hi @akarivaratharaj ,
Try below statistics function to get 95th percentile-

``````|makeresults|eval month="Jan", host1="40", host2="50", host3="70"
|append[|makeresults|eval month="Feb", host1="60", host2="50", host3="30"]
``````

Till makeresults is used to generate raw data only.
refer-https://docs.splunk.com/Documentation/Splunk/8.0.1/SearchReference/Aggregatefunctions#percentile_.3C...

Ultra Champion

Close, but use streamstats, and optionally remove the Total field:

``````|makeresults|eval month="Jan", host1="40", host2="50", host3="70"
|append[|makeresults|eval month="Feb", host1="60", host2="50", host3="30"]
|eval comment="Everything above this line makes sample data" |fields - comment
| fields - Total
``````
If my comment helps, please give it a thumbs up!
Communicator

@493669 @nickhillscpl Both of your methods are doing addition, which gives a single value nothing but sum of all the values and taking percentile upon that single value.

This will obviously give the same values as of Total value itself. Please refer the below output I got.

``````p95(Host)   Total
294.25  294.25
793.68  793.68
296.69  296.69
2552.53 2552.53
2160.30 2160.30
``````

My case is, there should be any addition at all, instead of the function `sum` (which is automatically done via `addtotals` command) there should be the function `Percentile` for whichever values we need for each row items

Get Updates on the Splunk Community!