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
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
record count
1 Record1 33
2 Record2 3
3 Record3 2
4 Record4 8
5 Record5 4
6 Record6 28
7 Record7 803
streamstats sum(count) as accumative_count
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
index=myindex sourcetype="mysource" | stats sum(FIELD) as total
total
1 881
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
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
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
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
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
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)
This worked perfectly, thanks 🙂
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
record count
1 Record1 33
2 Record2 3
3 Record3 2
4 Record4 8
5 Record5 4
6 Record6 28
7 Record7 803
streamstats sum(count) as accumative_count
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
index=myindex sourcetype="mysource" | stats sum(FIELD) as total
total
1 881
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
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
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
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
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
@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
@theouhuios, You should post your complete solution. There always more than one way cheers.
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.
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.