topic Re: Calculate a percentage on chart count over some span in Splunk Search

Calculate a percentage on chart count over some span

nmulm — Tue, 16 Jun 2015 11:28:43 GMT

Hi there,

I have response time data in ms in a table field ElTime. I want to band this based on 1000ms second brackets then show the counts in each band and also show the percentage. For some reason I cannot seem to get the percentage working. I have been through several solutions on here, but so far haven't found one covering my scenario. My gut is that this should be an easy one but I can't get it working.

My basic query is ~

| chart count over ElTime span=1000 |sort -ElTime

I've tried a few things nothing which I can get to work e.g.

| eventstats count as total | chart count over EntsBWElapTime span=1000,eval Percent=(count/total)*100

Sry total newbie struggling to make this work!

The results I would hope to get would be like ~

Elapsed Time (ms) Count Percentage
0 1602364 99.82%
1000 1938 0.12%
2000 199 0.01%

Any pointers are much appreciated, I would also be interested in a solution that would allow defined bands rather than just steps e.g. 0-250ms,251-500 etc etc.

Re: Calculate a percentage on chart count over some span

chimell — Tue, 16 Jun 2015 13:09:26 GMT

Hi nmulm
Try this search code

enter something here | eventstats count as total | chart span=1000 count over EntsBWElapTime |eval Percent=(count/total)*100|table EntsBWElapTime  count Percent

Re: Calculate a percentage on chart count over some span

nmulm — Tue, 16 Jun 2015 13:29:53 GMT

Thanks for that 🙂

I think there may be an issue with the total value as the Percentage column just ends up blank, and if I include total in the table it is also blank. I am getting the counts banded correctly just not the percentages.

That was the thing that I was struggling with i.e. does eventstats calculate the total of the entire result set before the chart breaks out the EntsBWElapTIme into the relevent bands?

Re: Calculate a percentage on chart count over some span

chimell — Tue, 16 Jun 2015 13:36:22 GMT

In this case , i advise you to use stats command instead of eventstats

Try this

 enter something here | stats count as total | chart span=1000 count over EntsBWElapTime |eval Percent=(count/total)*100|table EntsBWElapTime  count Percent

Re: Calculate a percentage on chart count over some span

woodcock — Tue, 16 Jun 2015 13:43:12 GMT

Try this:

... | eval ElapsedTime = case (EITime<=250, "0-250", EITime<=500, "251-500", EITime<=750, "501-750", EITime<=1000, "751-1000", 1==1, "1000+") | top limit=0 ElapsedTime

Re: Calculate a percentage on chart count over some span

fdi01 — Tue, 16 Jun 2015 13:52:14 GMT

try like this:

...| eventstats count as total| bucket _time span=1000ms | stats count by "EntsBWElapTime" |eval Percent=(count/total)*100 +"%"

...| eventstats count as total | timechart span=1000ms count by EntsBWElapTime |eval Percent=(count/total)*100 +"%"

Re: Calculate a percentage on chart count over some span

nmulm — Tue, 16 Jun 2015 14:03:55 GMT

Excellent thanks this worked for me, for some reason the using eventstats \ stats count as total methods listed would not do it.

Re: Calculate a percentage on chart count over some span

woodcock — Tue, 16 Jun 2015 15:09:35 GMT

Don't forget to "Accept" my answer.