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
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.
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.
Thankyou! This worked 🙂
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
@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)
Thankyou! This helps.