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.
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
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.
@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
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.
Can you please provide a sample event?
I included a sample event as a image.