Splunk Search
Highlighted

Subtracting value if another field value matches

Path Finder

Hi all,

Im running two searches one returns a number called "difference" and a field called "code2", the other searches is my main search which contains other various fields, however the ones that matter for this particular query are the "sum" and "code1" fields. What I'm trying to do is run a command so that when the fields "code1" and "code2" match, then the field value "difference" is subtracted from the field value "sum" giving me the final output of "final_number". Is this possible? It would like something like this-

First Search-

code1     sum 
1.1      100       
1.2      150
1.3      300

Second Search-

code2     difference
1.1           20
1.2           25
1.3           30

Desired Search Results-

code      final_number
1.1            130
1.2            125
1.3            270 

Thank you.

0 Karma
Highlighted

Re: Subtracting value if another field value matches

SplunkTrust
SplunkTrust

I think your first example is wrong and it should return 80 for code 1.1, but in any case:

| yoursearch1 OR yoursearch2
| eval code = coalesce(code1,code2)
| eval values = coalesce(sum,-difference)
| stats sum(values) as final_number by code
Highlighted

Re: Subtracting value if another field value matches

SplunkTrust
SplunkTrust
1st search ...
| table code1, sum 
| append [ 
  2nd search ....
  | table code2, difference
] 
| eval code_match=if(match(code1,code2),1,0)
| eval diff=if(code_match=1,sum-difference,sum)
| table code1, code2, diff, sum
Highlighted

Re: Subtracting value if another field value matches

SplunkTrust
SplunkTrust

Give this a try

Your first search | table code1 sum | rename code1 as code
| append [your second search | table code2 difference | rename code2 as code]
| stats values(*) as * by code | eval final_number=abs(sum-difference)
| table code final_number

View solution in original post