topic Re: Ignore a value in a multivalue if found in another field in Splunk Search

Ignore a value in a multivalue if found in another field

aamer4zangi — Thu, 07 Jun 2018 15:26:18 GMT

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.

Re: Ignore a value in a multivalue if found in another field

rlalwani_splunk — Thu, 07 Jun 2018 16:52:45 GMT

Can you please provide a sample event?

Re: Ignore a value in a multivalue if found in another field

niketn — Thu, 07 Jun 2018 17:06:55 GMT

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

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

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

Re: Ignore a value in a multivalue if found in another field

aamer4zangi — Thu, 07 Jun 2018 17:08:08 GMT

I included a sample event as a image.

Re: Ignore a value in a multivalue if found in another field

aamer4zangi — Thu, 07 Jun 2018 17:58:49 GMT

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.

Re: Ignore a value in a multivalue if found in another field

kamlesh_vaghela — Fri, 08 Jun 2018 06:33:06 GMT

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

Re: Ignore a value in a multivalue if found in another field

aamer4zangi — Fri, 08 Jun 2018 16:43:23 GMT

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.