topic how to compare a field value with next whole column and fetch the result in next column? in Splunk Search

how to compare a field value with next whole column and fetch the result in next column?

afrinakaffrey — Wed, 24 Jul 2019 08:01:49 GMT

for example,

Col A Col B Col C
apple apple apple
orange apple orange
pineapple orange pineapple
grapes pineapple grapes
banana grapes null
kiwi cucumber null
mango radish null

I have a data in column A and Column B , apple in ColA should compare with whole ColB , once the data is identified should fetch the data and results in ColC

Re: how to compare a field value with next whole column and fetch the result in next column?

renjith_nair — Wed, 24 Jul 2019 13:12:05 GMT

@afrinakaffrey ,

try

your search |eventstats values(ColB) as colBList
|eval colC=if(isnotnull(mvfind(colBList,ColA )),ColA ,null())
| fields - colBList

Re: how to compare a field value with next whole column and fetch the result in next column?

vnravikumar — Wed, 24 Jul 2019 14:21:59 GMT

Try this

source="dummydata.csv" host="ind1" sourcetype="csv" 
| rename "Col A" as A, "Col B" as B 
| table A,B,temp 
| stats list(A) as A,list(B) as B,list(temp) as temp 
| eval tempB=mvjoin(B,",") 
| eval AandB =mvzip(A,B,"#") 
| table AandB tempB 
| mvexpand AandB 
| eval tempAandB=split(AandB,"#") 
| eval A=mvindex(tempAandB,0),B=mvindex(tempAandB,1) 
| table A,B,tempB 
| eval C=if(in(A,'tempB'),A,null()) 
| makemv tempB delim="," 
| eval C=if(mvfind(tempB, 'A')>0,A,null()) 
| table A,B,C

Data used:

Col A,Col B
apple,apple
orange,apple
pineapple,orange
grapes,pineapple
banana,grapes
kiwi,cucumber
mango,radish