Splunk Search

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

Builder

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
Labels (1)
• ### stats

1 Solution
SplunkTrust
``````| 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``````
Builder

Thank you so much for your assistance.

SplunkTrust
``````| 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

Builder

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
SplunkTrust
``````| 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``````
Builder

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?

SplunkTrust

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

SplunkTrust

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
Get Updates on the Splunk Community!

#### Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

#### Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

#### Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...