compare data in two columns

[secure]

Hi 

i have data from two columns and using a third column to display the matches

| makeresults
| eval GroupA = 353649273, GroupB=353648649
| append
[ | makeresults | eval GroupA = 353649184, GroupB=353648566]
| append
[ | makeresults | eval GroupA = 353649091, GroupB=353616829]
| append
[ | makeresults | eval GroupA = 353649033, GroupB=353638941]
| append
[ | makeresults | eval GroupA = 353648797]
| append
[ | makeresults | eval GroupA = 353648680]
| append
[ | makeresults | eval GroupA = 353648745]
| append
[ | makeresults | eval GroupA = 353648730]
| append
[ | makeresults | eval GroupA = 353638941]
| fields - _time
| foreach GroupA [eval match=if(GroupA=GroupB,GroupA ,NULL)] | stats values(GroupA) values(GroupB) values(match)

however nothing is getting displayed in values(match). is there something wrong in the logic or alternate way to do it 

[yuanliu]

---

is there something wrong in the logic or alternate way to do it 

---

Yes, the logic is wrong with the given dataset.  But before I explain, please remember to post sample data in text for others to check even when screenshot helps illustrate the problem you are trying to diagnose.  So, here is your sample data:

GroupA	GroupB
353649273	353648649
353649184	353648566
353649091	353616829
353649033	353638941
353648797
353648680
353648745
353648730
353638941

From this dataset, it is easy to see that there is no match in any event. (One event is represented by one row.)  In addition to this, if you are going to compare GroupA and GroupB in their original names, there is no need to use foreach.  The logic expressed in your SPL can easily be implemented with

eval match=if(GroupA=GroupB,GroupA ,null())

Two SPL pointers: 1) use eval function null() is more expressive AND does not spend CPU cycles to look for a nonexistent field name such as NULL; more importantly, 2) foreach operates on a each event (row) individually.  If there is no match within the same event, match will always receive null value.

On the second point, @livehybrid makes a speculation of your real intent, which seems to be to seek not a match in individual events between string/numerical fields GroupA and GroupB, but to seek matches in the sets of all values of GroupA and all values of GroupB.  Is this the correct interpretation?  If so, your first logical mistake is to misinterpret the problem to be comparison within individual events.

A second mistake you make is in problem statement.

---

i have data from two columns and using a third column to display the matches

---

Given that there is no same-event match, your intention of "using a third column to display the matches" becomes impossible for volunteers here to interpret.  @livehybrid made an effort to interpret your intention as "if any value in the set of all values of GroupA matches any values in the set of all values of GroupB, display the matching values in GroupA together with ALL values of GroupB. (As opposed to any specific values of GroupB.)"  The output from that code is

GroupA	GroupB	match
353638941	353616829 353638941 353648566 353648649	1

Is this what you expect?  What if there are two distinct values in GroupA matching two values GroupB, should the column GroupA display the two matching values and the column GroupB still displaying the same five values?

It all comes down to the four golden rules in asking questions in this forum that I call Four Commandments:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at).
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

[VatsalJagani]

@secure- Probably custom command from this App might help.

https://splunkbase.splunk.com/app/4297

Kindly upvote if it helps!!!

[livehybrid · Accepted solution]

Hi @secure 

How about this?

| makeresults
| eval GroupA = 353649273, GroupB=353648649
| append
[ | makeresults | eval GroupA = 353649184, GroupB=353648566]
| append
[ | makeresults | eval GroupA = 353649091, GroupB=353616829]
| append
[ | makeresults | eval GroupA = 353649033, GroupB=353638941]
| append
[ | makeresults | eval GroupA = 353648797]
| append
[ | makeresults | eval GroupA = 353648680]
| append
[ | makeresults | eval GroupA = 353648745]
| append
[ | makeresults | eval GroupA = 353648730]
| append
[ | makeresults | eval GroupA = 353638941]
| fields - _time
| eventstats values(GroupB) AS GroupB
| eval match=IF(match(GroupB,GroupA),1,0)
| where match=1

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will