Splunk Search

Ignore a value in a multivalue if found in another field

Path Finder

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.

alt text

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

0 Karma

SplunkTrust
SplunkTrust

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

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Path Finder

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.

0 Karma

Splunk Employee
Splunk Employee

Can you please provide a sample event?

0 Karma

Path Finder

I included a sample event as a image.

0 Karma