Splunk Search

Ignore a value in a multivalue if found in another field

aamer4zangi
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

kamlesh_vaghela
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

aamer4zangi
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

niketn
Legend

@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

aamer4zangi
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

rlalwani_splunk
Splunk Employee
Splunk Employee

Can you please provide a sample event?

0 Karma

aamer4zangi
Path Finder

I included a sample event as a image.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...