How to calculate sum of a field based on other distinct field?

[LearningGuy]

Hello,

How to calculate sum of a field based on other distinct field?
For example: How to find sum for score of distinct vulnerability (exclude 0) group by ip? 

Thank you so much

Before calculation

ip	vuln	score
1.1.1.1	vuln1	0
1.1.1.1	vuln1	0
1.1.1.1	vuln2	3
1.1.1.1	vuln2	3
1.1.1.1	vuln2	3
1.1.1.1	vuln3	7
1.1.1.1	vuln3	7
2.2.2.2	vuln1	0
2.2.2.2	vuln4	0
2.2.2.2	vuln5	5
2.2.2.2	vuln5	5

After calculation
1.1.1.1:   sum  (vuln 2 [score]) + sum(vuln 3 [score])  = 3 + 7 = 10
2.2.2.2  : sum (vuln 5 [score]) = 5 

ip	sum (score of distinct vuln)
1.1.1.1	10
2.2.2.2	5

[LearningGuy]

Thank you so much for your assistance.

[ITWhisperer]

| stats values(score) as score by ip vuln
| stats dc(eval(if(score > 0,vuln,null()))) as dc_gt_0 dc(vuln) as dc_all sum(score) as total_score by ip

As also shown here

[LearningGuy]

Hello @ITWhisperer @yuanliu ,
Thank you so much for your help
Is it possible to do it in one stats, instead of two, so I can keep my previous original calculation?

I currently have stats ip with the following result

ip	dc(vuln)	dc(vuln) score > 0	count(vuln)	sum(score)
1.1.1.1	3	2	7	23
2.2.2.2	3	1	4	10

After adding "stats values(score) as score by ip vuln"  above the current stats ip,
count(vuln) no longer calculated the count of non distinct/original vuln   (7=>3,  4=>3)
sum(score) no longer calculated the count of non distinct/original score  (23=>10, 10=>5)

ip	dc(vuln)	dc(vuln) score > 0	count(vuln)	sum(score)	sum (dc(vuln) score > 0)
1.1.1.1	3	2	*3	*10	10
2.2.2.2	3	1	*3	*5	5

This is what I would like to have 

ip	dc(vuln)	dc(vuln) score > 0	count(vuln)	sum(score)	sum (dc(vuln) score > 0)
1.1.1.1	3	2	7	23	10
2.2.2.2	3	1	4	10	5

[ITWhisperer · Accepted solution]

| stats values(score) as score sum(score) as vuln_score count by ip vuln
| stats dc(eval(if(score > 0,vuln,null()))) as dc_gt_0 dc(vuln) as dc_all sum(score) as total_score sum(vuln_score) as vuln_score sum(count) as count by ip

[LearningGuy]

I tested your suggestion and it worked.
I accepted this as solution.
I appreciate your help. Thank you so much.

So, it's not possible in Splunk to make it only with 1 stats, correct?

[ITWhisperer]

Correct, it is not possible in one go because you are effectively grouping by two different dimension sets.

[yuanliu]

Do it in two steps just like you illustrated manually.

| stats values(score) as score by vuln ip
| stats sum(score) by ip

This is an emulation of your sample data to compare with real data

| makeresults format=csv data="ip,vuln,score   
1.1.1.1,vuln1,0 
1.1.1.1,vuln1,0 
1.1.1.1,vuln2,3 
1.1.1.1,vuln2,3 
1.1.1.1,vuln2,3 
1.1.1.1,vuln3,7 
1.1.1.1,vuln3,7 
2.2.2.2,vuln1,0 
2.2.2.2,vuln4,0 
2.2.2.2,vuln5,5 
2.2.2.2,vuln5,5"
``` data emulation ```

This emulation will give

ip	sum(score)
1.1.1.1	10
2.2.2.2	5