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
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?
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.
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
@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
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.