topic Re: How do I get the cumulative percentage in Splunk in Splunk Search

How do I get the cumulative percentage in Splunk

theouhuios — Mon, 28 Sep 2020 12:41:42 GMT

Hello

I am trying to get a cumulative percentage and have been unsuccessful with it.

The data is below. so the equation should be something like this

1st step would be (10/973)*100, 2nd would be ((10+9)/973)*100 3rd would be ((10+9+7)/973)*100 etc.. Any idea on how do I get it?

record.affectedCI count Total
1 WASBC3ST 10 973
2 WASDQWY3 9 973
3 WASBDH5X 7 973
4 WASBDPVY 7 973
5 WASBDP2B 6 973
6 WASBGLL6 6 973
7 WASBN18S 6 973

Re: How do I get the cumulative percentage in Splunk

bmacias84 — Thu, 25 Oct 2012 23:16:54 GMT

I am not sure if that what your _raw data looks like or if thats your searches output. Please provide a sample of the _raw or your search.

Re: How do I get the cumulative percentage in Splunk

bmacias84 — Fri, 26 Oct 2012 00:10:31 GMT

The only way I could think of solving this not knowing what your data looks like was to use streamstats and appendcols subsearch. The following was works, but was scrubbed.

First I use stats to sum <field> as <count> by <record> , easy part.



index=myindex sourcetype="mysource" | stats sum(FIELD) as count by record

Sample Return:



record      count

1   Record1     33

2   Record2 3

3   Record3 2

4   Record4 8

5   Record5 4

6   Record6 28

7   Record7 803

Next you have to pipe the result into a streamstats command sum <count> as <accumative_count> to find the sum of all pervious values.



streamstats sum(count) as accumative_count

Sample:



    record  count   accumative_count

1   Record1 33  33

2   Record2 3   36

3   Record3 2   38

4   Record4 8   46

5   Record5 4   50

6   Record6 28  78

7   Record7 803 881

You still need to find total which is easy.



index=myindex sourcetype="mysource" | stats sum(FIELD) as total

Sample:



    total

1   881

You still need to get the search to result set which is done by appending the total search to your main search by using appendcols.



index=myindex sourcetype="mysource" | stats sum(FIELD) as count by record | streamstats sum(count) as accumativ\e_count | appendcols [search index=myindex sourcetype="mysource" | stats sum(FIELD) as total ] | fields  record, count,accumative_count, total

Sample:



    record  count   accumative_count   total

1   Record1 33  33          881

2   Record2 3   36

3   Record3 2   38

4   Record4 8   46

5   Record5 4   50

6   Record6 28  78

7   Record7 803 881

Ok, but now total only returns one value in the total column and the rest are null. This is problem because you can perform math on null fields. To fix this add the filldown command.



index=myindex sourcetype="mysource" | stats sum(FIELD) as count by record | streamstats sum(count) as accumative_count | appendcols [search index=myindex sourcetype="mysource" | stats sum(FIELD) as total ] | filldown total | fields  record, count,accumative_count, total

Sample:



    record  count   accumative_count   total

1   Record1 33  33          881

2   Record2 3   36          881

3   Record3 2   38          881

4   Record4 8   46          881

5   Record5 4   50          881

6   Record6 28  78          881

7   Record7 803 881         881

Now we can do our percentage math.



eval percentage=((accumative_count/total)*100)

Let put it all together.



index="myindex" sourcetype="mysource" | stats sum(FIELD) as count by record | streamstats sum(count) as accumative_count | appendcols [search index=myindex sourcetype="mysource" | stats sum(FIELD) as total ] | filldown total| fields record, count,accumative_count, total | eval percentage=((accumative_count/total)*100) | table record, count, accumative_count, percentage, total

Final Result Sample:



    record  count   accumative_count   percentage  total

1   Record1 33  33          3.7457  881

2   Record2 3   36          4.0562  881

3   Record3 2   38          4.3132  881

4   Record4 8   46          5.2213  881

5   Record5 4   50          5.6753  881

6   Record6 28  78          8.5130  881

7   Record7 803 881         100 881

Hope this helps you and that I escaped all the special chars. Cheers

Please don't forget to click accept and up this post, if it helps you.

Additional reading:
Streamstats
Filldown
Appendcols
Howsubsearcheswork

Re: How do I get the cumulative percentage in Splunk

theouhuios — Mon, 28 Sep 2020 12:41:50 GMT

Hello

I did solve it up. Did it in a different way,

...| accum count as total_sum | eval Cum_per = ((total_sum)/Total)*100 .

Thanks for all the help @bmacias84. I really appreciate your help.

Re: How do I get the cumulative percentage in Splunk

bmacias84 — Fri, 26 Oct 2012 15:35:09 GMT

@theouhuios, You should post your complete solution. There always more than one way cheers.

Re: How do I get the cumulative percentage in Splunk

theouhuios — Mon, 28 Sep 2020 12:42:01 GMT

@bmacias84 , Here is the complete solution. I am a novice when it comes to splunk 😛 Thanks for the help though.

Re: How do I get the cumulative percentage in Splunk

mj41 — Wed, 30 Sep 2020 03:56:50 GMT

There is also "eventstats" that can get max of accumative_count in one go
| eval time_executing=round(time_executing/1000) | chart count by time_executing span=log2 | streamstats sum(count) as accumative_count | eventstats max(accumative_count) as total | eval pr1=round((count/total)*100,2) | eval pr2=round((accumative_count/total)*100,2), | eval time_executing=round(time_executing/1000) | chart count by time_executing span=log2 | streamstats sum(count) as accumative_count | eventstats max(accumative_count) as total | eval pr1=round((count/total)*100,2) | eval pr2=round((accumative_count/total)*100,2)

Re: How do I get the cumulative percentage in Splunk

thellmann — Tue, 13 Jul 2021 13:26:08 GMT

This worked perfectly, thanks 🙂