Alerting

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

Ashwini_5
Explorer

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.

Labels (1)
0 Karma

Ashwini_5
Explorer

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. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Ashwini_5
Explorer

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. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Forget you ever heard about the appendcols command.  It's 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

 

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!