Splunk Search

Compare values of 2 columns in a table

ramonnegronvz
New Member

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

0 Karma
1 Solution

to4kawa
Ultra Champion

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.

View solution in original post

0 Karma

to4kawa
Ultra Champion

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.

0 Karma

ramonnegronvz
New Member

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

0 Karma

ramonnegronvz
New Member

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!

0 Karma

to4kawa
Ultra Champion

Hi @ramonnegronvz
my answer is updated.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

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

ramonnegronvz
New Member

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

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...