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!

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...