Splunk Search

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

LearningGuy
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

ipvulnscore
1.1.1.1vuln10
1.1.1.1vuln10
1.1.1.1vuln23
1.1.1.1vuln23
1.1.1.1vuln23
1.1.1.1vuln37
1.1.1.1vuln37
2.2.2.2vuln10
2.2.2.2vuln40
2.2.2.2vuln55
2.2.2.2vuln55


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 

ipsum (score of distinct vuln)
1.1.1.110
2.2.2.25
Labels (1)
0 Karma
1 Solution

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

View solution in original post

LearningGuy
Builder

Thank you so much for your assistance.

0 Karma

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

LearningGuy
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

ipdc(vuln)dc(vuln) score > 0count(vuln)sum(score)
1.1.1.132723
2.2.2.231410


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)

ipdc(vuln)dc(vuln) score > 0count(vuln)sum(score)sum (dc(vuln) score > 0)
1.1.1.132*3*1010
2.2.2.231*3*55



This is what I would like to have 

ipdc(vuln)dc(vuln) score > 0count(vuln)sum(score)sum (dc(vuln) score > 0)
1.1.1.13272310
2.2.2.2314105
0 Karma

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

LearningGuy
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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

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

ipsum(score)
1.1.1.110
2.2.2.25
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...