Ignore a value in a multivalue if found in another field

[aamer4zangi]

Good day,
Suppose I want to compare the data in Column A and Column B. Column B can be a multivalue field or not.
If any value of Column B exists in Column A, it should be ignored. Thus, Column C should have unique values.

Tried few techniques but not helpful so far. Any suggestions would be helpful.

[kamlesh_vaghela]

Hi

Can you please try following search?

YOUR_SEARCH
| eval row=1 
| accum row 
| fillnull ColumnB value=" "
| stats count by row ColumnA ColumnB 
| eval ColumnC=if(ColumnA!=ColumnB,ColumnB," ") 
| stats values(ColumnB) as ColumnB values(ColumnC) as ColumnC by row,ColumnA | table ColumnA ColumnB ColumnC

You can also try my following sample search.

| makeresults 
| eval ColumnA="foo",ColumnB="foo" 
| append 
    [| makeresults 
    | eval ColumnA="foo",ColumnB="foo,fee",ColumnB=split(ColumnB,",")] 
| append 
    [| makeresults 
    | eval ColumnA="foo",ColumnB="",ColumnB=split(ColumnB,",")] 
| append 
    [| makeresults 
    | eval ColumnA="foo",ColumnB="fee",ColumnB=split(ColumnB,",")] 
| append 
    [| makeresults 
    | eval ColumnA="foo",ColumnB="foo,fee,fff",ColumnB=split(ColumnB,",")] 
| eval row=1 
| accum row 
| fillnull ColumnB value=" "
| stats count by row ColumnA ColumnB 
| eval ColumnC=if(ColumnA!=ColumnB,ColumnB," ") 
| stats values(ColumnB) as ColumnB values(ColumnC) as ColumnC by row,ColumnA | table ColumnA ColumnB ColumnC

Thanks

[aamer4zangi]

Thanks for the reply.

This approach might work when I have a raw event with 3 columns. However, ColumnB is an extracted field and there are other columns as well. This approach changes the search results entirely.

[niketn]

@aamer4zangi if columnB is your multivalued field, try one of the following options:

 <yourCurrentSearch>
| stats values(columnA) as columnA by columnB
| where columnA!=columnB

Or

 <yourCurrentSearch>
| mvexpand columnB
| where columnA!=columnB

[aamer4zangi]

Thanks for the reply. 2nd approach was getting the results but realized different use cases in the data. As a result, most of the data is ignored.
Updated the question with updated pic to represent those use cases.

[rlalwani_splunk]

Can you please provide a sample event?

[aamer4zangi]

I included a sample event as a image.