topic Re: Is there a way to calculate percentile of a row values in table? in Splunk Search

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

akarivaratharaj — Tue, 18 Feb 2020 09:10:01 GMT

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?

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

493669 — Tue, 18 Feb 2020 09:24:53 GMT

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"]
|addtotals| stats perc95(Total) as "p95(Host)"

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

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

nickhills — Tue, 18 Feb 2020 11:01:04 GMT

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
|addtotals| streamstats perc95(Total) as "p95(Host)"
| fields - Total

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

akarivaratharaj — Tue, 18 Feb 2020 11:23:35 GMT

@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

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

akarivaratharaj — Tue, 18 Feb 2020 11:31:57 GMT

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.