topic Re: Comparision of two fields Splunk in Splunk Search

Comparision of two fields Splunk

megha0794 — Wed, 07 Aug 2019 06:34:53 GMT

Hi ,

Suppose I write a query and if say I have a field (A) and field (B)

A B
1 1,3,4,5,8,9,10
5 1,3,4,5,8,9,10
7 1,3,4,5,8,9,10
2 1,3,4,5,8,9,10

I need to exclude all events where the values in A are a part of the comma separated values in B.
In this case , results should display only where A= 7 and A=2

Any help would be appreciated!
Regards,
Megha

Re: Comparision of two fields Splunk

kamlesh_vaghela — Wed, 07 Aug 2019 11:06:15 GMT

@megha0794

Can you please try this?

YOUR_SEARCH | table A B
| eval  flag = mvfind(split(B,","),A)
| where isnull(flag)

My sample search:

| makeresults 
| eval data="1|1,3,4,5,8,9,10&&5|1,3,4,5,8,9,10&&7|1,3,4,5,8,9,10&&2|1,3,4,5,8,9,10",data=split(data,"&&") 
| mvexpand data 
| eval A=mvindex(split(data,"|"),0),B=mvindex(split(data,"|"),1) 
| table A B
| eval  flag = mvfind(split(B,","),A)
| where isnull(flag)

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/MultivalueEvalFunctions#mvfind.28MVFIELD.2C.22REGEX.22.29

Re: Comparision of two fields Splunk

gaurav_maniar — Wed, 07 Aug 2019 11:12:09 GMT

Hi Megha,

append your existing query with following,

| eval c = if(like(B,"%"+A+"%"),1,0) | where c=1 | fields A, B

It will set value of field "C" to 1 only if A exists in B, than it will search for C=1

Accept and upvote the answer if it helps.

Re: Comparision of two fields Splunk

megha0794 — Thu, 08 Aug 2019 06:21:11 GMT

Thankyou! This worked 🙂

Re: Comparision of two fields Splunk

megha0794 — Thu, 08 Aug 2019 06:38:16 GMT

Thankyou! This helps.

Re: Comparision of two fields Splunk

megha0794 — Fri, 11 Oct 2019 09:06:58 GMT

Hi Gaurav,

If say my field value in A does not exactly match with B , then is there a way we can compare and do a match?
For eg:

A
1/text/text2.xlsx
text1/10/text3.xlsx

In both cases as '1' and '10' is a partial match to the comma separated values in B, so it needs to be excluded and say if I have a field value in A like the below - then it needs to be included:

text5/7/text6.xlsx.

In short the match can be in any portion of field A. The values in B will change dynamically and is pulled from lookup which gets appended over time. The ask is actually to compare all individual values in A with all the individual values in a field of the lookup. So in order to compare ,I am trying to merge all values of the field in the lookup and comma separate them to do a comparison with values in A. But the issue here is that the values in A will not be an exact match and there would be some extra text before of after the match. If there is some other way I can do a comparison, please do suggest.

Thanks in advance!
Megha