Hi Experts, I need to compare two columns from the table where it has similar value

[Ashwini_5]

Kindly help on the below scenario where I need to compare two different columns created using  different sourcetype. 

For Ex: 

|appendcols [search index="X" sourcetype="xy" |table ID,CASE_ID|] [search index="X" sourcetype="YZ" OR sourcetype="ABC"|table Role,Name,NewID|

Now here,  I need to Match ID and NewID which has similar results but not is same row. 

ID      NewID

123   789

456  123 

789  987

987 456  

Now, the result should come match for the data. 

I have tried many ways like (|foreach ID [eval status =if (match (ID, NewID), "YES", "NO")]. But nothing worked .  Please provide you suggestion.

[Ashwini_5]

Hi @richgalloway 

I have tried as per your suggestion. But it didn't worked out . 

I need query after that which compares values in both tables like foreach loop and give me final table which contains only unique values from both tables.

I tried | foreach ID [eval Result=if((ID=NewID), "YES", "NO")]| stats values(ID) values(NewID) values(Result). 

But not worked 😞 Please provide any other suggestion. 

[richgalloway]

Sorry my first suggestion didn't work out.  I misunderstood the problem.

It would help if you shared a proposed output for the four sample events.

The foreach command iterates over specified fields within a single event.  It does not compare fields across multiple events.  There are a few commands that look at a specific field across events (streamstats and eventstats are two), but I can think of none that compare different fields in different events.

Consider writing a custom command.

[Ashwini_5]

Hi @richgalloway 

Thank you for your suggestion. Now I am trying to create a custom command using python script. 

Kindly suggest if there is any sample procedure to go thorough it. 

[richgalloway]

The Dev manuals have some sample external commands.  See https://dev.splunk.com/enterprise/

[richgalloway]

Forget you ever heard about the appendcols command.  Its limitations make it less useful than it appears.

Instead, use append and then re-group the events using stats.

<your search that returns ID>
| append <search that returns NewID> | rename NewID as ID
| stats values(*) as * by ID