Compare values of 2 columns in a table

[ramonnegronvz]

I have a table that has 2 columns with Transaction ID's shown by a stats values() as below:

| stats values(E-TransactionID) as E-TransactionID values(R-TransactionID) as R-TransactionID

I'd like to compare the values of both columns and only show the Transaction ID's from R-TransactionID that does NOT appear in the E-TransactionID column.

I've made the following attempts after the stats values() with no luck. Any help is GREATLY appreciated.

Attempt 1 (had to try this anyway):
| table R-TransactionID E-TransactionID
| where R-TransactionID != E-TransactionID

Attempt 2:
| eval match=if(R-TransactionID=E-TransactionID, "EQUAL", R-TransactionID)
| stats values(match) as TransactionID

Attempt 3:
| foreach R-TransactionID
[eval match=if(R-TransactionID!=E-TransactionID, R-TransactionID, "MATCH")]
| stats values(R-TransactionID) as R-TransactionID values(E-TransactionID) as E-TransactionID values(match) as TransactionID

Attempt 4 (similar to previous, but with table instead😞
| foreach R-TransactionID
[eval match=if(R-TransactionID!=E-TransactionID, R-TransactionID, "MATCH")]
| stats values(R-TransactionID) as R-TransactionID values(E-TransactionID) as E-TransactionID values(match) as TransactionID

[to4kawa · Accepted solution]

sample:

| makeresults count=20
| streamstats count
| stats values(count) as R
| eval E=split("1,4,6",",")
| eval tmp=mvsort(mvappend(E,R))
| stats count by tmp
| where count < 2

try mvappend and stats by

but,

index=index_p sourcetype=st_p message="string_1" OR message="string_2"
| stats dc(message) as flag count(eval(message="string_1")) as message1  by  transid
| where flag < 2 AND message1 > 0
| table transid

Your query does not need them.

[ramonnegronvz]

Thanks @to4kawa
Let me see how can I attempt using mvappend and stats to fit my need here.

[ramonnegronvz]

I must admit, that at the beginning I was trying to figure out how would this help, but after trying it out, and testing it out, it is perfect! Thanks so much @to4kawa!

[to4kawa]

Hi @ramonnegronvz
my answer is updated.

[richgalloway]

One problem is the values function returns a multi-value field which does not work well with other commands and functions. Usually, mvexpand helps, but you have two multi-value fields you're trying to match up.

Perhaps you could take a step back and describe the original problem you want to solve.

[ramonnegronvz]

Thanks @richgalloway
Within the same index/sourcetype, there are 3 messages which I need to track in order to come up with the Transaction ID's that "matter". In the below query, I'm correlating 2 of the strings within the log events to come up with the E-TransactionID's (from the values of transid). I'm doing another search (string_3) to come up with R-TransactionID, which tells me about the Transaction ID's that "matter", but I want to "ignore" them if the Transaction ID matches E-TransactionID. In other words, my goal is to find the Transaction ID's that appear on R-TransactionID but NOT on E-TransactionID.

From your comment, do you think if I change values to table, I'll be able to accomplish what I need?

Here's a "dummy-down" search query I'm running to come up with both E-TransactionID and R-TransactionID:

index=index_p sourcetype=st_p message="string_1"
| join transid
[ search index=index_p sourcetype=st_p message="string_2"
| table transid]
| stats values(transid) as E-TransactionID
| append
[search index=index_p sourcetype=st_p message="string_2"
| stats values(transid) as R-TransactionID]
| stats values(E-TransactionID) as E-TransactionID values(R-TransactionID) as R-TransactionID