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

@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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...