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
- :
- Is there a way to calculate percentile of a row va...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted
##

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

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

akarivaratharaj

Path Finder

02-18-2020
01:10 AM

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?

Highlighted
##

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

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

493669

Super Champion

02-18-2020
01:24 AM

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_.3C...

Highlighted
##

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

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

nickhillscpl

Ultra Champion

02-18-2020
03:01 AM

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
```

Highlighted
##

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

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

akarivaratharaj

Path Finder

02-18-2020
03:23 AM

@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

Highlighted
##

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

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

akarivaratharaj

Path Finder

02-18-2020
03:31 AM

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.