topic Re: How to calculate sum of a field based on other distinct field? in Splunk Search

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

LearningGuy — Thu, 02 Nov 2023 02:29:56 GMT

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

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

yuanliu — Thu, 02 Nov 2023 04:38:05 GMT

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

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

ITWhisperer — Thu, 02 Nov 2023 09:54:04 GMT

| 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

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

LearningGuy — Thu, 02 Nov 2023 19:35:57 GMT

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

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

ITWhisperer — Thu, 02 Nov 2023 22:25:35 GMT

| 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

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

LearningGuy — Fri, 03 Nov 2023 05:02:17 GMT

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?

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

LearningGuy — Fri, 03 Nov 2023 05:03:23 GMT

Thank you so much for your assistance.

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

ITWhisperer — Fri, 03 Nov 2023 09:30:46 GMT

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