How do I get the cumulative percentage in Splunk

[theouhuios]

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

[mj41]

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)


[thellmann]

This worked perfectly, thanks 🙂 

[bmacias84 · Accepted solution]

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

[theouhuios]

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

sourcetype=incident earliest=-24h@h latest=@h | stats count by record.affectedCI | sort -count | eventstats sum(count) as total_count | accum count as running | eval Cum_Per= ((running/total_count)*100)| table record.affectedCI , running, Cum_Per | sort - running

[bmacias84]

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

[theouhuios]

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.

[bmacias84]

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.