Splunk Search

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

afrinakaffrey
New Member

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

Tags (1)
0 Karma

vnravikumar
Champion

Hi

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

alt text

0 Karma

renjith_nair
Legend

@afrinakaffrey ,

try

your search |eventstats values(ColB) as colBList
|eval colC=if(isnotnull(mvfind(colBList,ColA )),ColA ,null())
| fields - colBList
---
What goes around comes around. If it helps, hit it with Karma :slightly_smiling_face:
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...